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
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
测试数据量太小,无法弄出什么效果,至少无法测试出你想要的效果还是你描述下,你对这个查询有什么疑问,觉得太慢?另外,b.*,拆成b.pid,b.RAISEDTIME。
*虽然方便,但不是个好主意。
2:数据量只是介绍一下这个数据大致是什么样子的,所以没弄太大的数据。
2:testB 实际表的字段比较多,这里只是拿出2个举个例子,
确实*比逐个列举要慢点。我想知道的就是类似我们这种数据分布,这种查询需求,用这样的联合查询性能如何?select b.* from testA a,testB b
where b.pid= a.pid and a.path like '100.101%'
order by b.raisedtime desc 这种查询和下面这种单表查询比,因为可以转换成下面这种。
哪个更快点,对数据库而言实现区别大不大。select b.* from testB b
where b.pid='oid=123'
or b.pid='oid=234'
or b.pid='oid=124'
or b.pid='oid=125'
or b.pid='oid=126'
order by b.raisedtime desc
要不你就新建个索引就俩字段
pid ,path 试试
select b.* from testB b
exsits(select 1 from testA a
where b.pid= a.pid and a.path like '100.101%' )
order by b.raisedtime desc
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);
这一大批可能相当多,1000个,2000个。这样子的话,查询1 和查询2虽然结果相同,但是查询1看起来简洁的多,
不知道效率是否比查询2效率要高。查询1:
select b.* from testA a,testB b
where b.pid= a.pid and a.path like '100.101%'
order by b.raisedtime desc
查询2:
select b.* from testB b
where b.pid='oid=123'
or b.pid='oid=234'
or b.pid='oid=124'
or b.pid='oid=125'
.............
or b.pid='oid=999'
order by b.raisedtime desc