Oracle
為什麼Oracle數據庫中的優化器不使用索引？
當我執行以下查詢時看到完全不同的結果哪裡有問題？
在這裡，我執行了一個簡單的選擇查詢，需要幾秒鐘才能完成：
SELECT * FROM PRODUCTION.VERY_SMALL_TABLE L INNER JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX R on R.ID_1 = L.ID or R.ID_2 = L.ID其執行計劃是：
---------------------------------------------------------------------------------------------------------------------------------------------------------------- | 身份證 | 操作 | 姓名 | 行 | 字節 | 成本 (%CPU)| 時間 | 開始| 停站 | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | 選擇聲明 | | 72M| 9554M| 11M (1)| 00:07:16 | | | | 1 | 查看 | VW_ORE_65071C6B | 72M| 9554M| 11M (1)| 00:07:16 | | | | 2 | UNION-ALL | | | | | | | | | 3 | 嵌套循環 | | 50M| 6821M| 6056K (1)| 00:03:57 | | | | 4 | 嵌套循環 | | 50M| 6821M| 6056K (1)| 00:03:57 | | | | 5 | 表訪問完全 | 非常小表 | 7 | 98 | 3 (0)| 00:00:01 | | | | 6 | 分區範圍全部 | | 7246K| | 621 (0)| 00:00:01 | 1 |1048575| |* 7 | 索引範圍掃描 | ID_2_INDX | 7246K| | 621 (0)| 00:00:01 | 1 |1048575| | 8 | 按本地索引 ROWID 訪問表| BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX | 7246K| 877M| 865K (1)| 00:00:34 | 1 | 1 | | 9 | 嵌套循環 | | 21M| 2870M| 5097K (1)| 00:03:20 | | | | 10 | 嵌套循環 | | 42M| 2870M| 5097K (1)| 00:03:20 | | | | 11 | 表訪問完全 | 非常小表 | 7 | 98 | 3 (0)| 00:00:01 | | | | 12 | 分區範圍全部 | | 6098K| | 621 (0)| 00:00:01 | 1 |1048575| |* 13 | 索引範圍掃描 | ID_1_INDX | 6098K| | 621 (0)| 00:00:01 | 1 |1048575| |* 14 | 按本地索引 ROWID 訪問表| BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX | 3049K| 369M| 728K (1)| 00:00:29 | 1 | 1 | ---------------------------------------------------------------------------------------------------------------------------------------------------------------但是當我想使用上面的查詢來創建這樣的表時：
CREATE TABLE DUMMY_SCHEMA.DUMMY_TABLE AS (SELECT * FROM PRODUCTION.VERY_SMALL_TABLE L INNER JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX R on R.ID_1 = L.ID or R.ID_2 = L.ID);執行計劃更改為：
------------------------------------------------------------------------------------------------------------------------------------ | 身份證 | 操作 | 姓名 | 行 | 字節 | 成本 (%CPU)| 時間 | 開始| 停站 | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | 創建表語句 | | 222G| 28T| 1971M (1)| 21:23:21 | | | | 1 | 載入為選擇 | DUMMY_TABLE | | | | | | | | 2 | 嵌套循環 | | 222G| 28T| 1200M (1)| 13:01:53 | | | | 3 | 表訪問完全 | 非常小表 | 7 | 98 | 3 (0)| 00:00:01 | | | | 4 | 分區範圍全部 | | 31G| 3756G| 171M (1)| 01:51:42 | 1 |1048575| |* 5 | 表訪問完全 | BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX | 31G| 3756G| 171M (1)| 01:51:42 | 1 |1048575| -------------------------------------------------------------------------------------------------------------------------------------並且創建表查詢需要太長時間才能完成！！
添加第一行提示，這樣 oracle 可能會以與 datagrip 查詢相同的方式響應。
CREATE TABLE DUMMY_SCHEMA.DUMMY_TABLE AS (SELECT /*+ first_rows */ * FROM PRODUCTION.VERY_SMALL_TABLE L INNER JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX R on R.ID_1 = L.ID or R.ID_2 = L.ID);作為替代方案，您可以分析您的表，以便優化器可以使用最新的統計資訊。
可以清楚地看到，雖然優化器對您的查詢 (
VW_ORE_*****) 執行了 OR 擴展，但它無法在您的CREATE TABLEDDL語句中執行它。我找不到對此的任何官方參考，但似乎 OR 擴展並未在 CTAS 的頂層執行。您可以嘗試以下方法：
CREATE TABLE DUMMY_SCHEMA.DUMMY_TABLE AS WITH G as ( SELECT /*+ materialize */ * FROM PRODUCTION.VERY_SMALL_TABLE L INNER JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX R on R.ID_1 = L.ID or R.ID_2 = L.ID ) SELECT * FROM G;如果您的情況，這是一個簡化的範例：
create table t1 (id1 number not null, id2 number not null); insert into t1 select rownum, rownum + 1000000 from dual connect by level <= 1000000; commit; create index t1_id1 on t1(id1); create index t1_id2 on t1(id2); create table t2 (id number not null); insert into t2 select rownum from dual connect by level <= 5; insert into t2 select rownum + 1000000 from dual connect by level <= 5; commit; exec dbms_stats.gather_table_stats(user, 'T1'); exec dbms_stats.gather_table_stats(user, 'T2');然後（由於我的表與您的不同，我需要設置它以實現與您的相似的結果並避免 B-tree 索引點陣圖計劃）：
alter session set "_b_tree_bitmap_plans"=false;現在查詢（執行或擴展，使用兩個索引）：
SQL> select t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id); ID1 ID2 ID ---------- ---------- ---------- 1 1000001 1 2 1000002 2 3 1000003 3 4 1000004 4 5 1000005 5 1 1000001 1000001 2 1000002 1000002 3 1000003 1000003 4 1000004 1000004 5 1000005 1000005 10 rows selected. SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5ax9k9jpjczk9, child number 0 ------------------------------------- select t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id) Plan hash value: 2262899810 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 66 (100)| | | 1 | VIEW | VW_ORE_5133193F | 18 | 702 | 66 (0)| 00:00:01 | | 2 | UNION-ALL | | | | | | | 3 | NESTED LOOPS | | 10 | 160 | 33 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 10 | 160 | 33 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | T2 | 10 | 50 | 3 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | T1_ID1 | 1 | | 2 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 3 (0)| 00:00:01 | | 8 | NESTED LOOPS | | 8 | 128 | 33 (0)| 00:00:01 | | 9 | NESTED LOOPS | | 10 | 128 | 33 (0)| 00:00:01 | | 10 | TABLE ACCESS FULL | T2 | 10 | 50 | 3 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | T1_ID2 | 1 | | 2 (0)| 00:00:01 | |* 12 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("T1"."ID1"="T2"."ID") 11 - access("T1"."ID2"="T2"."ID") 12 - filter(LNNVL("T1"."ID1"="T2"."ID")) Note ----- - this is an adaptive planCTAS：
SQL> create table t5 as with g as (select t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id)) select * from g; Table created. SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID cafkj1bahx8hu, child number 0 ------------------------------------- create table t5 as with g as (select t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id)) select * from g Plan hash value: 2652785614 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | CREATE TABLE STATEMENT | | | | 6268 (100)| | | 1 | LOAD AS SELECT | T5 | | | | | | 2 | OPTIMIZER STATISTICS GATHERING | | 20 | 320 | 6267 (3)| 00:00:01 | | 3 | NESTED LOOPS | | 20 | 320 | 6267 (3)| 00:00:01 | | 4 | TABLE ACCESS FULL | T2 | 10 | 50 | 3 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL | T1 | 2 | 22 | 626 (3)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter(("T1"."ID1"="T2"."ID" OR "T1"."ID2"="T2"."ID"))沒有 OR 擴展，沒有索引。
帶有 CTE 和
materialize提示的 CTAS：SQL> create table t4 as with g as (select /*+ materialize */ t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id)) select * from g; Table created. SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------- SQL_ID c80w81ckc4727, child number 1 ------------------------------------- create table t4 as with g as (select /*+ materialize */ t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id)) select * from g Plan hash value: 653110608 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------------- | 0 | CREATE TABLE STATEMENT | | | | 73 (100)| | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6722_A1FFB3 | | | | | | 3 | VIEW | VW_ORE_5133193F | 18 | 558 | 68 (0)| 00:00:01 | | 4 | UNION-ALL | | | | | | | 5 | NESTED LOOPS | | 10 | 160 | 33 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 10 | 160 | 33 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | T2 | 10 | 50 | 3 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | T1_ID1 | 1 | | 2 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 11 | 3 (0)| 00:00:01 | | 10 | NESTED LOOPS | | 8 | 128 | 33 (0)| 00:00:01 | | 11 | NESTED LOOPS | | 10 | 128 | 33 (0)| 00:00:01 | | 12 | TABLE ACCESS FULL | T2 | 10 | 50 | 3 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | T1_ID2 | 1 | | 2 (0)| 00:00:01 | |* 14 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 11 | 3 (0)| 00:00:01 | | 15 | LOAD AS SELECT | T4 | | | | | | 16 | OPTIMIZER STATISTICS GATHERING | | 18 | 702 | 3 (0)| 00:00:01 | | 17 | VIEW | | 18 | 702 | 3 (0)| 00:00:01 | | 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6722_A1FFB3 | 18 | 558 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 8 - access("T1"."ID1"="T2"."ID") 13 - access("T1"."ID2"="T2"."ID") 14 - filter(LNNVL("T1"."ID1"="T2"."ID"))執行 OR 擴展，使用索引。請注意，您需要
materialize提示，沒有它，它的行為與原始 CTAS 相同：SQL> create table t5 as with g as (select t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id)) select * from g; Table created. SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID cafkj1bahx8hu, child number 0 ------------------------------------- create table t5 as with g as (select t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id)) select * from g Plan hash value: 2652785614 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | CREATE TABLE STATEMENT | | | | 6268 (100)| | | 1 | LOAD AS SELECT | T5 | | | | | | 2 | OPTIMIZER STATISTICS GATHERING | | 20 | 320 | 6267 (3)| 00:00:01 | | 3 | NESTED LOOPS | | 20 | 320 | 6267 (3)| 00:00:01 | | 4 | TABLE ACCESS FULL | T2 | 10 | 50 | 3 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL | T1 | 2 | 22 | 626 (3)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter(("T1"."ID1"="T2"."ID" OR "T1"."ID2"="T2"."ID"))