请教一个联合查询思路和效率的问题
CREATE TABLE testA (
pid NVARCHAR2(300) NOT NULL,
path NVARCHAR2(300) NOT NULL
);
CREATE INDEX IDX_testA ON testA (path);
CREATE TABLE testB (
pid NVARCHAR2(300) NOT NULL,
RAISEDTIME DATE NOT NULL
);
CREATE INDEX IDX_testB ON testB (pid);
数据形式类似如下:
testA 的数据不会很多,最多5w多条吧。
testB 的数据会比较多,500-800w。因为pid对应的path可能会变化,所以没用把path放到testB表里面。
insert into testA values('oid=123','100.101');
insert into testA values('oid=124','100.101.201');
insert into testA values('oid=125','100.101.202');
insert into testA values('oid=126','100.101.203');
insert into testA values('oid=127','100.102');
insert into testB values('oid=123',sysdate);
insert into testB values('oid=124',sysdate);
insert into testB values('oid=125',sysdate);
insert into testB values('oid=126',sysdate);
insert into testB values('oid=127',sysdate);
下面这种查询,在上面这种数据分布情况下,效率如何。有没有更好的思路?
select b.* from testA a,testB b
where b.pid= a.pid and a.path like '100.101%'
order by b.raisedtime desc
查询计划如下:
SELECT STATEMENT, GOAL = RULE
SORT ORDER BY
TABLE ACCESS BY INDEX ROWID TEMP TESTB
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID TEMP TESTA
INDEX RANGE SCAN TEMP IDX_TESTA
INDEX RANGE SCAN TEMP IDX_TESTB