Oracle
嵌套表作為條件阻止使用索引
以下連接子查詢的查詢
TABLE ACCESS FULL在主表使用嵌套表作為條件時執行。當條件是 simple=時，相同的查詢會INDEX RANGE SCAN按預期執行。（以下 DDL）為什麼帶有嵌套表的查詢不使用索引？
SELECT m.id, sub.cnt FROM m LEFT JOIN ( SELECT d.m_id, COUNT(1) AS cnt -- (+ other functions) FROM d GROUP BY d.m_id ) sub ON ( sub.m_id = m.id ) WHERE m.id IN ( SELECT /*+ CARDINALITY(tab,1) */ COLUMN_VALUE FROM TABLE( NEW t(1) ) tab ) -- WHERE m.id = 1 -- alternative condition條件按預期
m.id = 1使用索引：d---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000K| 37M| 1 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 1000K| 37M| 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_M | 1 | 13 | 0 (0)| 00:00:01 | | 3 | VIEW | | 1000K| 24M| 1 (0)| 00:00:01 | | 4 | SORT GROUP BY | | 1000K| 12M| 1 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN| IDX_D | 1000K| 12M| 1 (0)| 00:00:01 | ----------------------------------------------------------------------------使用嵌套表不使用索引：
------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSp|Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1000K| 39M| | 185K (4)| 00:37:05 | |* 1 | HASH JOIN OUTER | | 1000K| 39M| 2640K| 185K (4)| 00:37:05 | | 2 | MERGE JOIN SEMI | | 100K| 1464K| | 30 (4)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_M | 10M| 123M| | 0 (0)| 00:00:01 | |* 4 | SORT UNIQUE | | 1 | 2 | | 30 (4)| 00:00:01 | | 5 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1 | 2 | | 29 (0)| 00:00:01 | | 6 | VIEW | | 100M| 2479M| | 4898 (100)| 00:00:59 | | 7 | HASH GROUP BY | | 100M| 1239M| | 4898 (100)| 00:00:59 | | 8 | TABLE ACCESS FULL | D | 100M| 1239M| | 571 (95)| 00:00:07 | ------------------------------------------------------------------------------------------------------DDL
CREATE TABLE m ( id NUMBER); ALTER TABLE m ADD CONSTRAINT pk_m PRIMARY KEY ( id ); CREATE TABLE d ( m_id NUMBER ); CREATE INDEX idx_d ON d ( m_id ); CREATE TYPE t AS TABLE OF NUMBER; EXEC dbms_stats.set_table_stats ( sys_context( 'userenv', 'current_schema' ), 'M', numrows => 1000 ); EXEC dbms_stats.set_table_stats ( sys_context( 'userenv', 'current_schema' ), 'D', numrows => 10000 );
嗯….這是一個有趣的。就像我這樣的凡人而言，Oracle 優化器有點像一個黑匣子……喬納森·劉易斯寫了一本 536 頁的關於這個主題的書，這只是**基礎知識*（這不是睡前閱讀！） .
兩個問題。
a) 您是否在每個查詢之前/之後刷新記憶體 - 可能第一個會影響第二個 - 即優化器在第一個查詢之後發現它不值得使用索引？
b) 如果你在“M”和“D”中增加這些數字會發生什麼？是否會切換回 INDEX 掃描？我猜這樣的點會到來（但見點a）
只是幾個想法 - 我手頭沒有 Oracle 系統。
我不知道我在做什麼™，但以下等效查詢似乎對我有用（至少，在您編輯查詢以使用
LEFT JOIN…之前它是等效的）：SELECT m.id, sub.cnt FROM m JOIN ( SELECT d.m_id, COUNT(1) AS cnt -- (+ other functions) FROM d WHERE d.m_id IN ( SELECT COLUMN_VALUE FROM TABLE( NEW t(1) ) ) GROUP BY d.m_id ) sub ON ( sub.m_id = m.id )這會給我：
---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 31 (100)| | | 1 | NESTED LOOPS | | 1 | 39 | 31 (7)| 00:00:01 | | 2 | VIEW | | 1 | 26 | 31 (7)| 00:00:01 | | 3 | HASH GROUP BY | | 1 | 15 | 31 (7)| 00:00:01 | | 4 | NESTED LOOPS | | 1 | 15 | 30 (4)| 00:00:01 | | 5 | SORT UNIQUE | | 1 | 2 | 29 (0)| 00:00:01 | | 6 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1 | 2 | 29 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | IDX_D | 1 | 13 | 0 (0)| | |* 8 | INDEX UNIQUE SCAN | PK_M | 1 | 13 | 0 (0)| | ----------------------------------------------------------------------------------------------------我將相關謂詞手動下推到派生表中。我會將 CBO 中缺少的功能添加到無法按預期工作的預期下推操作列表中（例如將任何謂詞下推到派生表中
UNION）甲骨文 - 一個神秘的裝置，只有它的力量才能超越！