在Oracle数据库中的大表联合查询速度慢的问题表A 有 6个字段, 共1000万条数据TableA(c1,c2,c3,c4,c5,c6)表B有30个字段, 共20万条数据
TableB(b1,b2,b3,b4,...b30)c1,a1为自动增长ID
c2与b1是相关联的
b1,c2已经建了索引现在要对表A与表B联合查询。分页显示SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (
select c1,c2,b1,b3,b4 from
TableA INNER JOIN TableB
ON TableA.c2= TableB.b1
) A
WHERE ROWNUM <= 20
)
WHERE RN >= 1发现速度很慢,应该怎么样优化?
再加入hint试试
如果允许的话,试试c2,c1建索引,b1,b3,b4建索引
(
SELECT A.*, ROWNUM RN
FROM (
select c1,c2 from
TableA ) A
WHERE ROWNUM <= 20
)
WHERE RN >= 1 不联合查询是不用1秒的,SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (
select c1,c2,b1,b3,b4 from
TableA INNER JOIN TableB
ON TableA.c2= TableB.b1
) A
WHERE ROWNUM <= 20
)
WHERE RN >= 1
联合查询要3到4分钟
ANALYZE table tableB COMPUTE STATISTICSsqlplus的话,用set autotrace on 显示执行计划
Operation Object Name Rows Bytes Cost TQ In/Out PStart PStopSELECT STATEMENT 3 53200.2735894507
VIEW 3 264 53200.2735894507
COUNT STOPKEY
HASH JOIN 10 M 504 M 53200.2735894507
TABLE ACCESS FULL TABLEA 229 K 4 M 2435.62515704857
TABLE ACCESS FULL TABLEB 10 M 298 M 19145.2123743017
(
SELECT A.*, ROWNUM RN
FROM (
select c1,c2,b1,b3,b4 from
TableA INNER JOIN TableB
ON TableA.c2= TableB.b1
) A
WHERE ROWNUM <= 20
)
WHERE RN >= 1 另外就是,你的索引建好了没有?分析了吗?
VIEW 3 264 10 M
COUNT STOPKEY
NESTED LOOPS 10 M 504 M 10 M
TABLE ACCESS FULL TABLEA 10 M 298 M 19145.2123743017
TABLE ACCESS BY INDEX ROWID TABLEB 1 20 1.00142957293938
INDEX UNIQUE SCAN IDX_TABLEB_B1 1 .000752545242876267
如果你现在的是走RBO,可改再改变一下表的顺序,试试速度还能不能快一些
SELECT /*+FIRST_ROWS*/ * FROM
(
SELECT A.*, ROWNUM RN
FROM (
select c1,c2,b1,b3,b4 from
TableB INNER JOIN TableA --注:这里B和A换了顺序
ON TableB.b1 = TableA.c2
) A
WHERE ROWNUM <= 20
)
WHERE RN >= 1