遠程數據庫的表超過20個索引的影響詳細解析

2020-10-29 21:47:13

字體：大中小

來源：轉載

供稿：網友

昨天同事參加了一個研討會,有提到一個案例。一個通過dblink查詢遠端數據庫，原來查詢很快，但是遠端數據庫增加了一個索引之后，查詢一下子變慢了。

經過分析，發現那個通過dblink的查詢語句，查詢遠端數據庫的時候，是走索引的，但是遠端數據庫添加索引之后，如果索引的個數超過20個，就會忽略第一個建立的索引，如果查詢語句恰好用到了第一個建立的索引，被忽略之后，只能走Full Table Scan了。

聽了這個案例，我查了一下，在oracle官方文檔中，關于Managing a Distributed Database有一段話：

Several performance restrictions relate to access of remote objects:

Remote views do not have statistical data.
Queries on partitioned tables may not be optimized.
No more than 20 indexes are considered for a remote table.
No more than 20 columns are used for a composite index.

說到，如果遠程數據庫使用超過20個索引，這些索引將不被考慮。這段話，在oracle 9i起的文檔中就已經存在，一直到12.2還有。

那么，超過20個索引，是新的索引被忽略了？還是老索引被忽略了？如何讓被忽略的索引讓oracle意識到?我們來測試一下。
（本文基于12.1.0.2的遠程庫和12.2.0.1的本地庫進行測試，如果對測試過程沒興趣的，可以直接拉到文末看“綜上”部分）

（一）初始化測試表：

--創建遠程表：DROP TABLE t_remote; CREATE TABLE t_remote (col01 NUMBER,col02 NUMBER,col03 VARCHAR2(50),col04 NUMBER,col05 NUMBER,col06 VARCHAR2(50),col07 NUMBER,col08 NUMBER,col09 VARCHAR2(50),col10 NUMBER,col11 NUMBER,col12 VARCHAR2(50),col13 NUMBER,col14 NUMBER,col15 VARCHAR2(50),col16 NUMBER,col17 NUMBER,col18 VARCHAR2(50),col19 NUMBER,col20 NUMBER,col21 VARCHAR2(50),col22 NUMBER,col23 NUMBER,col24 VARCHAR2(50),col25 NUMBER,col26 NUMBER,col27 VARCHAR2(50));alter table t_remote modify (col01 not null);INSERT INTO t_remoteSELECTrownum, rownum, rpad('*',50,'*'),rownum, rownum, rpad('*',50,'*'),rownum, rownum, rpad('*',50,'*'),rownum, rownum, rpad('*',50,'*'),rownum, rownum, rpad('*',50,'*'),rownum, rownum, rpad('*',50,'*'),rownum, rownum, rpad('*',50,'*'),rownum, rownum, rpad('*',50,'*'),rownum, rownum, rpad('*',50,'*')FROM dualCONNECT BY level <= 10000;commit; create unique index t_remote_i01_pk on t_remote (col01);alter table t_remote add (constraint t_remote_i01_pk primary key (col01) using index t_remote_i01_pk);create index t_remote_i02 on t_remote (col02);create index t_remote_i03 on t_remote (col03);create index t_remote_i04 on t_remote (col04);create index t_remote_i05 on t_remote (col05);create index t_remote_i06 on t_remote (col06);create index t_remote_i07 on t_remote (col07);create index t_remote_i08 on t_remote (col08);create index t_remote_i09 on t_remote (col09);create index t_remote_i10 on t_remote (col10);create index t_remote_i11 on t_remote (col11);create index t_remote_i12 on t_remote (col12);create index t_remote_i13 on t_remote (col13);create index t_remote_i14 on t_remote (col14);create index t_remote_i15 on t_remote (col15);create index t_remote_i16 on t_remote (col16);create index t_remote_i17 on t_remote (col17);create index t_remote_i18 on t_remote (col18);create index t_remote_i19 on t_remote (col19);create index t_remote_i20 on t_remote (col20); exec dbms_stats.gather_table_stats(user,'T_REMOTE');

--創建本地表：drop table t_local; CREATE TABLE t_local (col01 NUMBER,col02 NUMBER,col03 VARCHAR2(50),col04 NUMBER,col05 NUMBER,col06 VARCHAR2(50)); INSERT INTO t_localSELECTrownum, rownum, rpad('*',50,'*'),rownum, rownum, rpad('*',50,'*')FROM dualCONNECT BY level <= 50; COMMIT; create index t_local_i01 on t_local (col01);create index t_local_i02 on t_local (col02);create index t_local_i03 on t_local (col03);create index t_local_i04 on t_local (col04);create index t_local_i05 on t_local (col05);create index t_local_i06 on t_local (col06); exec dbms_stats.gather_table_stats(user,'t_local');  create database link dblink_remote CONNECT TO test IDENTIFIED BY test USING 'ora121';  SQL> select host_name from v$instance@dblink_remote; HOST_NAME----------------------------------------------------------------testdb2 SQL> select host_name from v$instance; HOST_NAME----------------------------------------------------------------testdb10 SQL>

可以看到，遠程表有27個字段，目前還只是在前20個字段建立了索引，且第一個字段是主鍵。本地表，有6個字段，6個字段都建索引。

（二）第一輪測試，遠程表上有20個索引。

測試場景1：

在遠程表20索引的情況下，本地表和遠程表關聯，用本地表的第一個字段關聯遠程表的第一個字段：

select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25from t_local l, t_remote@dblink_remote rwhere l.col01=r.col01;select * from table( dbms_xplan.display_cursor(null, null, 'typical LAST') );PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 04schqc3d9rgm, child number 0-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col01=r.col01Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 53 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 53 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  1 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL01","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL01"  (accessing 'DBLINK_REMOTE' ) 23 rows selected.SQL> -- 我們這里注意一下，WHERE :1="COL01"的存在，正是因為這個條件，所以在遠程是走了主鍵而不是全表掃。我們把這個語句帶入到遠程執行。遠程：SQL> explain plan for 2 SELECT "COL01","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL01";PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------Plan hash value: 829680338-----------------------------------------------------------------------------------------------| Id | Operation     | Name   | Rows | Bytes | Cost (%CPU)| Time  |-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT   |     |  1 | 63 |  2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T_REMOTE  |  1 | 63 |  2 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN   | T_REMOTE_I01_PK |  1 |  |  1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 - access("COL01"=TO_NUMBER(:1))14 rows selected.

我們可以看到，對于遠程表的執行計劃，這是走主鍵的。

測試場景2：

在遠程表20索引的情況下，本地表和遠程表關聯，用本地表的第一個字段關聯遠程表的第20個字段：

select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25from t_local l, t_remote@dblink_remote rwhere l.col01=r.col20;select * from table( dbms_xplan.display_cursor(null, null, 'typical LAST') );PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 5rwtbwcnv0tsm, child number 0-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col01=r.col20Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 103 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 103 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  2 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL20","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL20"  (accessing 'DBLINK_REMOTE' ) 23 rows selected.SQL> 遠程：PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------Plan hash value: 3993494813----------------------------------------------------------------------------------------------------| Id | Operation       | Name   | Rows | Bytes | Cost (%CPU)| Time  |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT     |    |  1 | 63 |  2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_REMOTE  |  1 | 63 |  2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN     | T_REMOTE_I20 |  1 |  |  1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 - access("COL20"=TO_NUMBER(:1))14 rows selected.SQL>

我們可以看到，對于遠程表的執行計劃，這是走索引范圍掃描的。

測試場景3：

在遠程表20索引的情況下，本地表和遠程表關聯，用本地表的第2個字段關聯遠程表的第2個字段：

select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25from t_local l, t_remote@dblink_remote rwhere l.col02=r.col02;select * from table( dbms_xplan.display_cursor(null, null, 'typical LAST') );PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 81ctrx5huhfvq, child number 0-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col02=r.col02Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 103 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 103 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  2 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL02","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL02"  (accessing 'DBLINK_REMOTE' ) 23 rows selected.SQL> 遠程：SQL> explain plan for  2 SELECT "COL02","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL02";Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------Plan hash value: 2505594687----------------------------------------------------------------------------------------------------| Id | Operation       | Name   | Rows | Bytes | Cost (%CPU)| Time  |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT     |    |  1 | 63 |  2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_REMOTE  |  1 | 63 |  2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN     | T_REMOTE_I02 |  1 |  |  1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 - access("COL02"=TO_NUMBER(:1))14 rows selected.SQL>

我們可以看到，對于遠程表的執行計劃，這是走索引范圍掃描的。

測試場景4：

在遠程表20索引的情況下，本地表和遠程表關聯，用本地表的第2個字段關聯遠程表的第20個字段：

select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25from t_local l, t_remote@dblink_remote rwhere l.col02=r.col20;select * from table( dbms_xplan.display_cursor(null, null, 'typical LAST') );PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 407pxjh9mgbry, child number 0-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col02=r.col20Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 103 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 103 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  2 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL20","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL20"  (accessing 'DBLINK_REMOTE' )23 rows selected.SQL> 遠程：SQL> explain plan for 2 SELECT "COL20","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL20";Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------Plan hash value: 3993494813----------------------------------------------------------------------------------------------------| Id | Operation       | Name   | Rows | Bytes | Cost (%CPU)| Time  |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT     |    |  1 | 63 |  2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_REMOTE  |  1 | 63 |  2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN     | T_REMOTE_I20 |  1 |  |  1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 - access("COL20"=TO_NUMBER(:1))14 rows selected.SQL>

我們可以看到，對于遠程表的執行計劃，這是走索引范圍掃描的。

（三）建立第21個索引：

create index t_remote_i21 on t_remote (col21);exec dbms_stats.gather_table_stats(user,'T_REMOTE');

（四）遠程表上現在有21個索引，重復上面4個測試：

測試場景1：

PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 04schqc3d9rgm, child number 1-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col01=r.col01Plan hash value: 830255788-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 156 (100)|   |  |  ||* 1 | HASH JOIN   |   | 50 | 6300 | 156 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE | 10000 | 644K| 153 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("L"."COL01"="R"."COL01")Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL01","COL25","COL26","COL27" FROM "T_REMOTE" "R" (accessing  'DBLINK_REMOTE' ) 28 rows selected.SQL>--我們看到，這里已經沒有了之前的 WHERE :1="COL01",即使不帶入到遠程看執行計劃，我們也可以猜到它是全表掃。遠程：SQL> explain plan for 2 SELECT "COL01","COL25","COL26","COL27" FROM "T_REMOTE" "R";Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------Plan hash value: 4187688566------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  |------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   | 10000 | 615K| 238 (0)| 00:00:01 || 1 | TABLE ACCESS FULL| T_REMOTE | 10000 | 615K| 238 (0)| 00:00:01 |------------------------------------------------------------------------------8 rows selected.SQL>

我們可以看到，對于遠程表的執行計劃，如果關聯條件是遠程表的第一個字段，第一個字段上的索引是被忽略的，執行計劃是選擇全表掃描的。

測試場景2：

PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 5rwtbwcnv0tsm, child number 1-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col01=r.col20Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 103 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 103 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  2 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL20","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL20"  (accessing 'DBLINK_REMOTE' ) 23 rows selected.SQL> 遠程：SQL> explain plan for 2 SELECT "COL20","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL20";Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------Plan hash value: 3993494813----------------------------------------------------------------------------------------------------| Id | Operation       | Name   | Rows | Bytes | Cost (%CPU)| Time  |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT     |    |  1 | 63 |  2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_REMOTE  |  1 | 63 |  2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN     | T_REMOTE_I20 |  1 |  |  1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 - access("COL20"=TO_NUMBER(:1))14 rows selected.SQL>

我們可以看到，對于遠程表的執行計劃，如果關聯條件是遠程表的第20個字段，這第20個字段上的索引是沒有被忽略的，執行計劃是走索引。

測試場景3：

PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 81ctrx5huhfvq, child number 1-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col02=r.col02Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 103 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 103 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  2 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL02","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL02"  (accessing 'DBLINK_REMOTE' ) 23 rows selected.SQL> 遠程：SQL> explain plan for 2 SELECT "COL02","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL02";Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------Plan hash value: 2505594687----------------------------------------------------------------------------------------------------| Id | Operation       | Name   | Rows | Bytes | Cost (%CPU)| Time  |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT     |    |  1 | 63 |  2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_REMOTE  |  1 | 63 |  2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN     | T_REMOTE_I02 |  1 |  |  1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 - access("COL02"=TO_NUMBER(:1))14 rows selected.SQL>

我們可以看到，對于遠程表的執行計劃，如果關聯條件是遠程表的第2個字段，這第2個字段上的索引是沒有被忽略的，執行計劃是走索引。

測試場景4：

PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 407pxjh9mgbry, child number 1-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col02=r.col20Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 103 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 103 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  2 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL20","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL20"  (accessing 'DBLINK_REMOTE' ) 23 rows selected.SQL> 遠程：SQL> explain plan for 2 SELECT "COL20","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL20";Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------Plan hash value: 3993494813----------------------------------------------------------------------------------------------------| Id | Operation       | Name   | Rows | Bytes | Cost (%CPU)| Time  |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT     |    |  1 | 63 |  2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_REMOTE  |  1 | 63 |  2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN     | T_REMOTE_I20 |  1 |  |  1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 - access("COL20"=TO_NUMBER(:1))14 rows selected.SQL>

我們可以看到，對于遠程表的執行計劃，如果關聯條件是遠程表的第20個字段，這第20個字段上的索引是沒有被忽略的，執行計劃是走索引。

我們目前可以總結到，當遠程表第21個索引建立的時候，通過dblink關聯本地表和遠程表，如果關聯條件是遠程表的第1個建立的索引的字段，那么這個索引將被忽略，從而走全表掃描。如果關聯條件是遠程表的第2個建立索引的字段，則不受影響。

似乎是有效索引的窗口是20個，當新建第21個，那么第1個就被無視了。

（五）建立第22個索引，我們在來看看上述猜測是否符合。

create index t_remote_i22 on t_remote (col22);exec dbms_stats.gather_table_stats(user,'T_REMOTE');

（六），目前遠程表有22個索引，重復上面4個測試：

測試場景1：

PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 04schqc3d9rgm, child number 2-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col01=r.col01Plan hash value: 830255788-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 156 (100)|   |  |  ||* 1 | HASH JOIN   |   | 50 | 6300 | 156 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE | 10000 | 644K| 153 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("L"."COL01"="R"."COL01")Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL01","COL25","COL26","COL27" FROM "T_REMOTE" "R" (accessing  'DBLINK_REMOTE' ) 28 rows selected.SQL>

測試場景2：

PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 5rwtbwcnv0tsm, child number 2-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col01=r.col20Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 103 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 103 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  2 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL20","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL20"  (accessing 'DBLINK_REMOTE' )23 rows selected.SQL>

測試場景3：

PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 81ctrx5huhfvq, child number 2-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col02=r.col02Plan hash value: 830255788-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 156 (100)|   |  |  ||* 1 | HASH JOIN   |   | 50 | 6300 | 156 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE | 10000 | 644K| 153 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("L"."COL02"="R"."COL02")Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL02","COL25","COL26","COL27" FROM "T_REMOTE" "R" (accessing  'DBLINK_REMOTE' ) 28 rows selected.SQL>

測試場景4：

PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 407pxjh9mgbry, child number 2-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col02=r.col20Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 103 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 103 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  2 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL20","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL20"  (accessing 'DBLINK_REMOTE' )23 rows selected.SQL>

上述的測試，其實是可以驗證我們的猜測的。oracle對于通過dblink關聯訪問遠程表，只是會意識到最近創建的20個索引的字段。這個意識到索引的窗口是20個，一旦建立了一個新索引，那么最舊的一個索引會被無視。

（七）我們嘗試rebuild索引，看看有沒有效果：

rebuild第2個索引

alter index t_remote_i02 rebuild;exec dbms_stats.gather_table_stats(user,'T_REMOTE');

（八）在第2個索引rebuild之后，重復上面4個測試：

--測試場景1：PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 04schqc3d9rgm, child number 0-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col01=r.col01Plan hash value: 830255788-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 156 (100)|   |  |  ||* 1 | HASH JOIN   |   | 50 | 6300 | 156 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE | 10000 | 644K| 153 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("L"."COL01"="R"."COL01")Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL01","COL25","COL26","COL27" FROM "T_REMOTE" "R" (accessing  'DBLINK_REMOTE' )28 rows selected.SQL> --測試場景2：PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 5rwtbwcnv0tsm, child number 0-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col01=r.col20Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 103 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 103 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  2 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL20","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL20"  (accessing 'DBLINK_REMOTE' )23 rows selected.SQL> --測試場景3：PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 81ctrx5huhfvq, child number 0-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col02=r.col02Plan hash value: 830255788-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 156 (100)|   |  |  ||* 1 | HASH JOIN   |   | 50 | 6300 | 156 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE | 10000 | 644K| 153 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("L"."COL02"="R"."COL02")Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL02","COL25","COL26","COL27" FROM "T_REMOTE" "R" (accessing  'DBLINK_REMOTE' )28 rows selected.SQL>--測試場景4：PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 407pxjh9mgbry, child number 0-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col02=r.col20Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 103 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 103 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  2 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL20","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL20"  (accessing 'DBLINK_REMOTE' )23 rows selected.SQL>

所以我們看到，索引rebuild，是不能起到重新“喚醒”索引的作用。

（九）我們嘗試 drop and recreate 第2個索引。

drop index t_remote_i02;create index t_remote_i02 on t_remote (col02); exec dbms_stats.gather_table_stats(user,'T_REMOTE');

（十）重復上面的測試3和測試4：

測試3：PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 81ctrx5huhfvq, child number 1-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col02=r.col02Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 103 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 103 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  2 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL02","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL02"  (accessing 'DBLINK_REMOTE' )23 rows selected.SQL>測試4：PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID 407pxjh9mgbry, child number 1-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col02=r.col20Plan hash value: 631452043-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 103 (100)|   |  |  || 1 | NESTED LOOPS  |   | 50 | 6300 | 103 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE |  1 | 66 |  2 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL20","COL25","COL26","COL27" FROM "T_REMOTE" "R" WHERE :1="COL20"  (accessing 'DBLINK_REMOTE' )23 rows selected.SQL> 此時，其實我們可以預測，遠程表此時col03上的索引是用不到的，我們來測試驗證一下：測試5：PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------SQL_ID bhkczcfrhvsuw, child number 0-------------------------------------select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 from t_local l,t_remote@dblink_remote r where l.col03=r.col03Plan hash value: 830255788-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   |  |  | 157 (100)|   |  |  ||* 1 | HASH JOIN   |   | 500K| 89M| 157 (1)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 5400 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE | 10000 | 781K| 153 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("L"."COL03"="R"."COL03")Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL03","COL25","COL26","COL27" FROM "T_REMOTE" "R" (accessing  'DBLINK_REMOTE' )28 rows selected.SQL>

我們可以看到，通過drop之后再重建，是可以“喚醒”第二個索引的。這也證明了我們20個索引識別的移動窗口，是按照索引的創建時間來移動的。

綜上：

1. 對于通過dblink關聯本地表和遠程表，如果遠程表的索引個數少于20個，那么不受影響。
2. 對于通過dblink關聯本地表和遠程表，如果遠程表的索引個數增加到21個或以上，那么oracle在執行遠程操作的時候，將忽略最早創建的那個索引，但是會以20個為窗口移動，最新建立的索引會被意識到。此時如果查詢的關聯條件中，使用到最早創建的那個索引的字段，由于忽略了索引，會走全表掃描。
3. 要“喚醒”對原來索引的意識，rebuild索引無效，需要drop & create索引。
4. 在本地表數據量比較少，遠程表的數據量很大，而索引數量超過20個，且關聯條件的字段時最早索引的情況下，可以考慮使用DRIVING_SITE的hint，將本地表的數據全量到遠程中，此時遠程的關聯查詢可以意識到那個索引。可見文末的例子。是否使用hint，需要評估本地表數據全量推送到遠程的成本，和遠程表使用全表掃的成本。

附：在22個索引的情況下，嘗試采用DRIVING_SITE的hint：

SQL> select l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 2 from t_local l, t_remote@dblink_remote r 3 where l.col02=r.col02 4 ;50 rows selected.Elapsed: 00:00:00.03Execution Plan----------------------------------------------------------Plan hash value: 830255788-----------------------------------------------------------------------------------------------| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |   | 50 | 6300 | 156 (0)| 00:00:01 |  |  ||* 1 | HASH JOIN   |   | 50 | 6300 | 156 (0)| 00:00:01 |  |  || 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  || 3 | REMOTE   | T_REMOTE | 10000 | 644K| 153 (0)| 00:00:01 | DBLIN~ | R->S |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("L"."COL02"="R"."COL02")Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL02","COL25","COL26","COL27" FROM "T_REMOTE" "R" (accessing  'DBLINK_REMOTE' )Statistics----------------------------------------------------------  151 recursive calls   0 db block gets  246 consistent gets   26 physical reads   0 redo size  2539 bytes sent via SQL*Net to client  641 bytes received via SQL*Net from client   5 SQL*Net roundtrips to/from client   10 sorts (memory)   0 sorts (disk)   50 rows processedSQL>--可以看到遠程表示走全表掃。

SQL> select /*+DRIVING_SITE(r)*/ l.col06,l.col05,l.col04,r.col27, r.col26,r.col25 2 from t_local l, t_remote@dblink_remote r 3 where l.col02=r.col02 4 ;50 rows selected.Elapsed: 00:00:00.03Execution Plan----------------------------------------------------------Plan hash value: 1716516160-------------------------------------------------------------------------------------------------------------| Id | Operation     | Name   | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|-------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT REMOTE  |    | 50 | 6450 | 103 (0)| 00:00:01 |  |  || 1 | NESTED LOOPS    |    | 50 | 6450 | 103 (0)| 00:00:01 |  |  || 2 | NESTED LOOPS    |    | 50 | 6450 | 103 (0)| 00:00:01 |  |  || 3 | REMOTE     | T_LOCAL  | 50 | 3300 |  3 (0)| 00:00:01 |  ! | R->S ||* 4 | INDEX RANGE SCAN   | T_REMOTE_I02 |  1 |  |  1 (0)| 00:00:01 | ORA12C |  || 5 | TABLE ACCESS BY INDEX ROWID| T_REMOTE  |  1 | 63 |  2 (0)| 00:00:01 | ORA12C |  |-------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("A2"."COL02"="A1"."COL02")Remote SQL Information (identified by operation id):---------------------------------------------------- 3 - SELECT "COL02","COL04","COL05","COL06" FROM "T_LOCAL" "A2" (accessing '!' )Note----- - fully remote statement - this is an adaptive planStatistics----------------------------------------------------------  137 recursive calls   0 db block gets  213 consistent gets   25 physical reads   0 redo size  2940 bytes sent via SQL*Net to client  641 bytes received via SQL*Net from client   5 SQL*Net roundtrips to/from client   10 sorts (memory)   0 sorts (disk)   50 rows processedSQL>--可以看到本地表是走全表掃，但是遠程表使用了第2個字段的索引。

總結

以上就是本文關于遠程數據庫的表超過20個索引的影響詳細解析的全部內容，希望對大家有所幫助。感興趣的朋友可以繼續參閱本站：SQL提取數據庫表名及字段名等信息代碼示例、MySQL數據庫表分區注意事項大全【推薦】等，有什么問題可以直接留言，小編會及時回復大家的。感謝朋友們對本站的支持！

上一篇：90%程序員面試會遇到的索引優化問題

下一篇：淺談數據庫事務四大特性