昨天同事參加了一個研討會,有提到一個案例。一個通過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_remote
SELECT
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,'*'),
rownum, rownum, rpad('*',50,'*')
FROM dual
CONNECT 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_local
SELECT
rownum, rownum, rpad('*',50,'*'),
rownum, rownum, rpad('*',50,'*')
FROM dual
CONNECT 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.col25
from t_local l, t_remote@dblink_remote r
where 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.col01
Plan 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.col25
from t_local l, t_remote@dblink_remote r
where 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.col20
Plan 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.col25
from t_local l, t_remote@dblink_remote r
where 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.col02
Plan 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.col25
from t_local l, t_remote@dblink_remote r
where 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.col20
Plan 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.col01
Plan 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.col20
Plan 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.col02
Plan 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.col20
Plan 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.col01
Plan 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.col20
Plan 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.col02
Plan 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.col20
Plan 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.col01
Plan 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.col20
Plan 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.col02
Plan 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.col20
Plan 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.col02
Plan 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.col20
Plan 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.col03
Plan 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，將本地表的數據全量到遠程中，此時遠程的關聯查詢可以意識到那個索引?？梢娢哪┑睦印Ｊ欠袷褂胔int，需要評估本地表數據全量推送到遠程的成本，和遠程表使用全表掃的成本。
附：在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.03
Execution 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 processed
SQL>
--可以看到遠程表示走全表掃。
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.03
Execution 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 plan
Statistics
----------------------------------------------------------
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 processed
SQL>
--可以看到本地表是走全表掃，但是遠程表使用了第2個字段的索引。
總結
以上就是本文關于遠程數據庫的表超過20個索引的影響詳細解析的全部內容，希望對大家有所幫助。感興趣的朋友可以繼續參閱本站：SQL提取數據庫表名及字段名等信息代碼示例、MySQL數據庫表分區注意事項大全【推薦】等，有什么問題可以直接留言，小編會及時回復大家的。感謝朋友們對本站的支持！
您可能感興趣的文章:- Oracle數據庫中建立索引的基本方法講解
- 什么是數據庫索引 有哪些類型和特點
- mysql數據庫索引損壞及修復經驗分享
- pymongo為mongodb數據庫添加索引的方法
- oracle數據庫索引失效
