我有下面这样的oracle sql查询,我只想提取一个与t2表匹配的名称。我正在使用查询获取记录,但它占用了很多时间,因为我在t1表中有4000多万个名称,在t2表中有0.02万个名称。
col1:的t1数据
N Wind Pudding Dr
Hasty Pudding Ln
Banana Pudding on the Go
Saint Barbaras R C Church
St Barbaras Rd
col1:的t2数据
Pudding
Barbaras
查询:
select t2.col1,t1.col1
from t1, t2
where t1.col1 like '% t2.col1 %';
输出为:
Pudding N Wind Pudding Dr
Barbaras Saint Barbaras R C Church
未测试:
select t2.col1,
(select t1.col1 from t1
where t1.col1 like '%' || t2.col1 || '%''
fetch first 1 row only)
from t2
也许不是最有效的方法。。。
还有几个选项,可以选择与t2
列值匹配的任何t1
表的行。
一:
SQL> select max(t1.col), t2.col
2 from t1 join t2 on instr(t1.col, t2.col) > 0
3 group by t2.col;
MAX(T1.COL) COL
---------------------------------------- ----------
St Barbaras Rd Barbaras
N Wind Pudding Dr Pudding
二:
SQL> select t1_col, t2_col
2 from (select t1.col t1_col,
3 t2.col t2_col,
4 row_number() over (partition by t2.col order by null) rn
5 from t1 join t2 on instr(t1.col, t2.col) > 0
6 )
7 where rn = 1;
T1_COL T2_COL
---------------------------------------- ----------
Saint Barbaras R C Church Barbaras
N Wind Pudding Dr Pudding
SQL>
尝试使用上下文索引
CREATE INDEX text_indx ON t1(col1) INDEXTYPE IS CTXSYS.CTXCAT;
SELECT t2.col1,t1.col1
FROM t1, t2
WHERE CONTAINS(t1.col1, t2.col1) > 0;