昨天同事参加了一个研讨会,有提到一个案例。一个通过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数据库表分区注意事项大全【推荐】等，有什么问题可以直接留言，小编会及时回复大家的。感谢朋友们对本站的支持！
您可能感兴趣的文章:
- Oracle数据库中建立索引的基本方法讲解
- 什么是数据库索引 有哪些类型和特点
- mysql数据库索引损坏及修复经验分享
- pymongo为mongodb数据库添加索引的方法
- oracle数据库索引失效
