我不是在寻找以下的优化,只是一个解释.我有这样的疑问:
SELECT COUNT(LARGE_A.id_a), SUM(LARGE_A.b_integer)
FROM LARGE_A
INNER JOIN MEDIUM_A ON LARGE_A.id_a = MEDIUM_A.id
INNER JOIN MEDIUM_B ON LARGE_A.id_b = MEDIUM_B.id
WHERE
MEDIUM_A.a_varchar2 LIKE 'Example%' AND
EXTRACT(YEAR FROM MEDIUM_B.a_datetime) = 2000 AND
LARGE_A.a_integer BETWEEN 0 AND 1000;
属性的类型写在它们的名称中.ID是整数.表LARGE_A
有1 000 000行,中等行各有LARGE_A
000行.ID是主键,但是表LARGE_A
具有复合键(id_a, id_b)
.
然后我得到了下面的执行计划,取自V$SQL
视图,因为AutoTrack对这一点撒谎.
SELECT STATEMENT
|SORT AGGREGATE
||HASH JOIN
|||NESTED LOOPS
||||NESTED LOOPS
|||||STATISTICS COLLECTOR
||||||NESTED LOOPS
|||||||TABLE ACCESS FULL LARGE_A
|||||||TABLE ACCESS BY INDEX ROWID MEDIUM_A
||||||||INDEX UNIQUE SCAN PK(MEDIUM_A)
|||||INDEX UNIQUE SCAN PK(MEDIUM_B)
||||TABLE ACCESS BY INDEX ROWID MEDIUM_B
|||TABLE ACCESS FULL MEDIUM_B
Pk表示括号中的表的主键(一个整数)上的非聚集索引.我在这里不用IOT.
为什么优化器首先从索引PK(MEDIUM_B)
逐个查询记录,然后使用ROWID获取其余行,然后再次运行全表扫描?