远程数据库的表超过20个索引的影响详细解析

2023-09-22 16:50:07 134

昨天同事参加了一个研讨会,有提到一个案例。一个通过dblink查询远端数据库，原来查询很快，但是远端数据库增加了一个索引之后，查询一下子变慢了。

经过分析，发现那个通过dblink的查询语句，查询远端数据库的时候，是走索引的，但是远端数据库添加索引之后，如果索引的个数超过20个，就会忽略第一个建立的索引，如果查询语句恰好用到了第一个建立的索引，被忽略之后，只能走FullTableScan了。

听了这个案例，我查了一下，在oracle官方文档中，关于ManagingaDistributedDatabase有一段话：

Severalperformancerestrictionsrelatetoaccessofremoteobjects:

Remoteviewsdonothavestatisticaldata.
Queriesonpartitionedtablesmaynotbeoptimized.
Nomorethan20indexesareconsideredforaremotetable.
Nomorethan20columnsareusedforacompositeindex.

说到，如果远程数据库使用超过20个索引，这些索引将不被考虑。这段话，在oracle9i起的文档中就已经存在，一直到12.2还有。

那么，超过20个索引，是新的索引被忽略了？还是老索引被忽略了？如何让被忽略的索引让oracle意识到?我们来测试一下。
（本文基于12.1.0.2的远程库和12.2.0.1的本地库进行测试，如果对测试过程没兴趣的，可以直接拉到文末看“综上”部分）

（一）初始化测试表：

--创建远程表：
DROPTABLEt_remote;
CREATETABLEt_remote(
col01NUMBER,
col02NUMBER,
col03VARCHAR2(50),
col04NUMBER,
col05NUMBER,
col06VARCHAR2(50),
col07NUMBER,
col08NUMBER,
col09VARCHAR2(50),
col10NUMBER,
col11NUMBER,
col12VARCHAR2(50),
col13NUMBER,
col14NUMBER,
col15VARCHAR2(50),
col16NUMBER,
col17NUMBER,
col18VARCHAR2(50),
col19NUMBER,
col20NUMBER,
col21VARCHAR2(50),
col22NUMBER,
col23NUMBER,
col24VARCHAR2(50),
col25NUMBER,
col26NUMBER,
col27VARCHAR2(50)
);
altertablet_remotemodify(col01notnull);
INSERTINTOt_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,'*')
FROMdual
CONNECTBYlevel<=10000;
commit;
createuniqueindext_remote_i01_pkont_remote(col01);
altertablet_remoteadd(constraintt_remote_i01_pkprimarykey(col01)usingindext_remote_i01_pk);
createindext_remote_i02ont_remote(col02);
createindext_remote_i03ont_remote(col03);
createindext_remote_i04ont_remote(col04);
createindext_remote_i05ont_remote(col05);
createindext_remote_i06ont_remote(col06);
createindext_remote_i07ont_remote(col07);
createindext_remote_i08ont_remote(col08);
createindext_remote_i09ont_remote(col09);
createindext_remote_i10ont_remote(col10);
createindext_remote_i11ont_remote(col11);
createindext_remote_i12ont_remote(col12);
createindext_remote_i13ont_remote(col13);
createindext_remote_i14ont_remote(col14);
createindext_remote_i15ont_remote(col15);
createindext_remote_i16ont_remote(col16);
createindext_remote_i17ont_remote(col17);
createindext_remote_i18ont_remote(col18);
createindext_remote_i19ont_remote(col19);
createindext_remote_i20ont_remote(col20);

execdbms_stats.gather_table_stats(user,'T_REMOTE');

--创建本地表：
droptablet_local;

CREATETABLEt_local(
col01NUMBER,
col02NUMBER,
col03VARCHAR2(50),
col04NUMBER,
col05NUMBER,
col06VARCHAR2(50)
);

INSERTINTOt_local
SELECT
rownum,rownum,rpad('*',50,'*'),
rownum,rownum,rpad('*',50,'*')
FROMdual
CONNECTBYlevel<=50;

COMMIT;

createindext_local_i01ont_local(col01);
createindext_local_i02ont_local(col02);
createindext_local_i03ont_local(col03);
createindext_local_i04ont_local(col04);
createindext_local_i05ont_local(col05);
createindext_local_i06ont_local(col06);

execdbms_stats.gather_table_stats(user,'t_local');


createdatabaselinkdblink_remoteCONNECTTOtestIDENTIFIEDBYtestUSING'ora121';


SQL>selecthost_namefromv$instance@dblink_remote;

HOST_NAME
----------------------------------------------------------------
testdb2

SQL>selecthost_namefromv$instance;

HOST_NAME
----------------------------------------------------------------
testdb10

SQL>

可以看到，远程表有27个字段，目前还只是在前20个字段建立了索引，且第一个字段是主键。本地表，有6个字段，6个字段都建索引。

（二）第一轮测试，远程表上有20个索引。

测试场景1：

在远程表20索引的情况下，本地表和远程表关联，用本地表的第一个字段关联远程表的第一个字段：

selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25
fromt_locall,t_remote@dblink_remoter
wherel.col01=r.col01
;
select*fromtable(dbms_xplan.display_cursor(null,null,'typicalLAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID04schqc3d9rgm,childnumber0
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col01=r.col01
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||53(100)||||
|1|NESTEDLOOPS||50|6300|53(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|1(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL01","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL01"
(accessing'DBLINK_REMOTE')

23rowsselected.
SQL>
--我们这里注意一下，WHERE:1="COL01"的存在，正是因为这个条件，所以在远程是走了主键而不是全表扫。我们把这个语句带入到远程执行。
远程：
SQL>explainplanfor
2SELECT"COL01","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL01";
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Planhashvalue:829680338
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|63|2(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWID|T_REMOTE|1|63|2(0)|00:00:01|
|*2|INDEXUNIQUESCAN|T_REMOTE_I01_PK|1||1(0)|00:00:01|
-----------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2-access("COL01"=TO_NUMBER(:1))
14rowsselected.

我们可以看到，对于远程表的执行计划，这是走主键的。

测试场景2：

在远程表20索引的情况下，本地表和远程表关联，用本地表的第一个字段关联远程表的第20个字段：

selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25
fromt_locall,t_remote@dblink_remoter
wherel.col01=r.col20
;
select*fromtable(dbms_xplan.display_cursor(null,null,'typicalLAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID5rwtbwcnv0tsm,childnumber0
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col01=r.col20
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||103(100)||||
|1|NESTEDLOOPS||50|6300|103(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|2(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL20","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL20"
(accessing'DBLINK_REMOTE')

23rowsselected.
SQL>
远程：
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Planhashvalue:3993494813
----------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|63|2(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWIDBATCHED|T_REMOTE|1|63|2(0)|00:00:01|
|*2|INDEXRANGESCAN|T_REMOTE_I20|1||1(0)|00:00:01|
----------------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2-access("COL20"=TO_NUMBER(:1))
14rowsselected.
SQL>

我们可以看到，对于远程表的执行计划，这是走索引范围扫描的。

测试场景3：

在远程表20索引的情况下，本地表和远程表关联，用本地表的第2个字段关联远程表的第2个字段：

selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25
fromt_locall,t_remote@dblink_remoter
wherel.col02=r.col02
;
select*fromtable(dbms_xplan.display_cursor(null,null,'typicalLAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID81ctrx5huhfvq,childnumber0
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col02=r.col02
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||103(100)||||
|1|NESTEDLOOPS||50|6300|103(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|2(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL02","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL02"
(accessing'DBLINK_REMOTE')

23rowsselected.
SQL>
远程：
SQL>explainplanfor
2SELECT"COL02","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL02";
Explained.
SQL>select*fromtable(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Planhashvalue:2505594687
----------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|63|2(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWIDBATCHED|T_REMOTE|1|63|2(0)|00:00:01|
|*2|INDEXRANGESCAN|T_REMOTE_I02|1||1(0)|00:00:01|
----------------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2-access("COL02"=TO_NUMBER(:1))
14rowsselected.
SQL>

我们可以看到，对于远程表的执行计划，这是走索引范围扫描的。

测试场景4：

在远程表20索引的情况下，本地表和远程表关联，用本地表的第2个字段关联远程表的第20个字段：

selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25
fromt_locall,t_remote@dblink_remoter
wherel.col02=r.col20
;
select*fromtable(dbms_xplan.display_cursor(null,null,'typicalLAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID407pxjh9mgbry,childnumber0
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col02=r.col20
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||103(100)||||
|1|NESTEDLOOPS||50|6300|103(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|2(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL20","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL20"
(accessing'DBLINK_REMOTE')
23rowsselected.
SQL>
远程：
SQL>explainplanfor
2SELECT"COL20","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL20";
Explained.
SQL>select*fromtable(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Planhashvalue:3993494813
----------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|63|2(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWIDBATCHED|T_REMOTE|1|63|2(0)|00:00:01|
|*2|INDEXRANGESCAN|T_REMOTE_I20|1||1(0)|00:00:01|
----------------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2-access("COL20"=TO_NUMBER(:1))
14rowsselected.
SQL>

我们可以看到，对于远程表的执行计划，这是走索引范围扫描的。

（三）建立第21个索引：

createindext_remote_i21ont_remote(col21);
execdbms_stats.gather_table_stats(user,'T_REMOTE');

（四）远程表上现在有21个索引，重复上面4个测试：

测试场景1：

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID04schqc3d9rgm,childnumber1
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col01=r.col01
Planhashvalue:830255788
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||156(100)||||
|*1|HASHJOIN||50|6300|156(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|10000|644K|153(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-access("L"."COL01"="R"."COL01")
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL01","COL25","COL26","COL27"FROM"T_REMOTE""R"(accessing
'DBLINK_REMOTE')

28rowsselected.
SQL>
--我们看到，这里已经没有了之前的WHERE:1="COL01",即使不带入到远程看执行计划，我们也可以猜到它是全表扫。
远程：
SQL>explainplanfor
2SELECT"COL01","COL25","COL26","COL27"FROM"T_REMOTE""R";
Explained.
SQL>select*fromtable(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Planhashvalue:4187688566
------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
------------------------------------------------------------------------------
|0|SELECTSTATEMENT||10000|615K|238(0)|00:00:01|
|1|TABLEACCESSFULL|T_REMOTE|10000|615K|238(0)|00:00:01|
------------------------------------------------------------------------------
8rowsselected.
SQL>

我们可以看到，对于远程表的执行计划，如果关联条件是远程表的第一个字段，第一个字段上的索引是被忽略的，执行计划是选择全表扫描的。

测试场景2：

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID5rwtbwcnv0tsm,childnumber1
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col01=r.col20
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||103(100)||||
|1|NESTEDLOOPS||50|6300|103(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|2(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL20","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL20"
(accessing'DBLINK_REMOTE')

23rowsselected.
SQL>
远程：
SQL>explainplanfor
2SELECT"COL20","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL20";
Explained.
SQL>select*fromtable(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Planhashvalue:3993494813
----------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|63|2(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWIDBATCHED|T_REMOTE|1|63|2(0)|00:00:01|
|*2|INDEXRANGESCAN|T_REMOTE_I20|1||1(0)|00:00:01|
----------------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2-access("COL20"=TO_NUMBER(:1))
14rowsselected.
SQL>

我们可以看到，对于远程表的执行计划，如果关联条件是远程表的第20个字段，这第20个字段上的索引是没有被忽略的，执行计划是走索引。

测试场景3：

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID81ctrx5huhfvq,childnumber1
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col02=r.col02
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||103(100)||||
|1|NESTEDLOOPS||50|6300|103(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|2(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL02","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL02"
(accessing'DBLINK_REMOTE')

23rowsselected.
SQL>
远程：
SQL>explainplanfor
2SELECT"COL02","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL02";
Explained.
SQL>select*fromtable(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Planhashvalue:2505594687
----------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|63|2(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWIDBATCHED|T_REMOTE|1|63|2(0)|00:00:01|
|*2|INDEXRANGESCAN|T_REMOTE_I02|1||1(0)|00:00:01|
----------------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2-access("COL02"=TO_NUMBER(:1))
14rowsselected.
SQL>

我们可以看到，对于远程表的执行计划，如果关联条件是远程表的第2个字段，这第2个字段上的索引是没有被忽略的，执行计划是走索引。

测试场景4：

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID407pxjh9mgbry,childnumber1
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col02=r.col20
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||103(100)||||
|1|NESTEDLOOPS||50|6300|103(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|2(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL20","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL20"
(accessing'DBLINK_REMOTE')

23rowsselected.
SQL>
远程：
SQL>explainplanfor
2SELECT"COL20","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL20";
Explained.
SQL>select*fromtable(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Planhashvalue:3993494813
----------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|63|2(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWIDBATCHED|T_REMOTE|1|63|2(0)|00:00:01|
|*2|INDEXRANGESCAN|T_REMOTE_I20|1||1(0)|00:00:01|
----------------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2-access("COL20"=TO_NUMBER(:1))
14rowsselected.
SQL>

我们可以看到，对于远程表的执行计划，如果关联条件是远程表的第20个字段，这第20个字段上的索引是没有被忽略的，执行计划是走索引。

我们目前可以总结到，当远程表第21个索引建立的时候，通过dblink关联本地表和远程表，如果关联条件是远程表的第1个建立的索引的字段，那么这个索引将被忽略，从而走全表扫描。如果关联条件是远程表的第2个建立索引的字段，则不受影响。

似乎是有效索引的窗口是20个，当新建第21个，那么第1个就被无视了。

（五）建立第22个索引，我们在来看看上述猜测是否符合。

createindext_remote_i22ont_remote(col22);
execdbms_stats.gather_table_stats(user,'T_REMOTE');

（六），目前远程表有22个索引，重复上面4个测试：

测试场景1：

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID04schqc3d9rgm,childnumber2
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col01=r.col01
Planhashvalue:830255788
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||156(100)||||
|*1|HASHJOIN||50|6300|156(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|10000|644K|153(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-access("L"."COL01"="R"."COL01")
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL01","COL25","COL26","COL27"FROM"T_REMOTE""R"(accessing
'DBLINK_REMOTE')

28rowsselected.
SQL>

测试场景2：

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID5rwtbwcnv0tsm,childnumber2
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col01=r.col20
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||103(100)||||
|1|NESTEDLOOPS||50|6300|103(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|2(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL20","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL20"
(accessing'DBLINK_REMOTE')
23rowsselected.
SQL>

测试场景3：

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID81ctrx5huhfvq,childnumber2
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col02=r.col02
Planhashvalue:830255788
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||156(100)||||
|*1|HASHJOIN||50|6300|156(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|10000|644K|153(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-access("L"."COL02"="R"."COL02")
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL02","COL25","COL26","COL27"FROM"T_REMOTE""R"(accessing
'DBLINK_REMOTE')

28rowsselected.
SQL>

测试场景4：

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID407pxjh9mgbry,childnumber2
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col02=r.col20
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||103(100)||||
|1|NESTEDLOOPS||50|6300|103(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|2(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL20","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL20"
(accessing'DBLINK_REMOTE')
23rowsselected.
SQL>

上述的测试，其实是可以验证我们的猜测的。oracle对于通过dblink关联访问远程表，只是会意识到最近创建的20个索引的字段。这个意识到索引的窗口是20个，一旦建立了一个新索引，那么最旧的一个索引会被无视。

（七）我们尝试rebuild索引，看看有没有效果：

rebuild第2个索引

alterindext_remote_i02rebuild;
execdbms_stats.gather_table_stats(user,'T_REMOTE');

（八）在第2个索引rebuild之后，重复上面4个测试：

--测试场景1：
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID04schqc3d9rgm,childnumber0
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col01=r.col01
Planhashvalue:830255788
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||156(100)||||
|*1|HASHJOIN||50|6300|156(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|10000|644K|153(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-access("L"."COL01"="R"."COL01")
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL01","COL25","COL26","COL27"FROM"T_REMOTE""R"(accessing
'DBLINK_REMOTE')
28rowsselected.
SQL>
--测试场景2：
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID5rwtbwcnv0tsm,childnumber0
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col01=r.col20
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||103(100)||||
|1|NESTEDLOOPS||50|6300|103(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|2(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL20","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL20"
(accessing'DBLINK_REMOTE')
23rowsselected.
SQL>
--测试场景3：
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID81ctrx5huhfvq,childnumber0
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col02=r.col02
Planhashvalue:830255788
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||156(100)||||
|*1|HASHJOIN||50|6300|156(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|10000|644K|153(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-access("L"."COL02"="R"."COL02")
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL02","COL25","COL26","COL27"FROM"T_REMOTE""R"(accessing
'DBLINK_REMOTE')
28rowsselected.
SQL>
--测试场景4：
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID407pxjh9mgbry,childnumber0
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col02=r.col20
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||103(100)||||
|1|NESTEDLOOPS||50|6300|103(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|2(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL20","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL20"
(accessing'DBLINK_REMOTE')
23rowsselected.
SQL>

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

（九）我们尝试dropandrecreate第2个索引。

dropindext_remote_i02;
createindext_remote_i02ont_remote(col02);

execdbms_stats.gather_table_stats(user,'T_REMOTE');

（十）重复上面的测试3和测试4：

测试3：
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID81ctrx5huhfvq,childnumber1
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col02=r.col02
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||103(100)||||
|1|NESTEDLOOPS||50|6300|103(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|2(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL02","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL02"
(accessing'DBLINK_REMOTE')
23rowsselected.
SQL>
测试4：
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID407pxjh9mgbry,childnumber1
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col02=r.col20
Planhashvalue:631452043
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||103(100)||||
|1|NESTEDLOOPS||50|6300|103(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|1|66|2(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL20","COL25","COL26","COL27"FROM"T_REMOTE""R"WHERE:1="COL20"
(accessing'DBLINK_REMOTE')
23rowsselected.
SQL>
此时，其实我们可以预测，远程表此时col03上的索引是用不到的，我们来测试验证一下：
测试5：
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_IDbhkczcfrhvsuw,childnumber0
-------------------------------------
selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25fromt_locall,
t_remote@dblink_remoterwherel.col03=r.col03
Planhashvalue:830255788
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||157(100)||||
|*1|HASHJOIN||500K|89M|157(1)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|5400|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|10000|781K|153(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-access("L"."COL03"="R"."COL03")
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL03","COL25","COL26","COL27"FROM"T_REMOTE""R"(accessing
'DBLINK_REMOTE')
28rowsselected.
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>selectl.col06,l.col05,l.col04,r.col27,r.col26,r.col25
2fromt_locall,t_remote@dblink_remoter
3wherel.col02=r.col02
4;
50rowsselected.
Elapsed:00:00:00.03
ExecutionPlan
----------------------------------------------------------
Planhashvalue:830255788
-----------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-----------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||50|6300|156(0)|00:00:01|||
|*1|HASHJOIN||50|6300|156(0)|00:00:01|||
|2|TABLEACCESSFULL|T_LOCAL|50|3000|3(0)|00:00:01|||
|3|REMOTE|T_REMOTE|10000|644K|153(0)|00:00:01|DBLIN~|R->S|
-----------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-access("L"."COL02"="R"."COL02")
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL02","COL25","COL26","COL27"FROM"T_REMOTE""R"(accessing
'DBLINK_REMOTE')
Statistics
----------------------------------------------------------
151recursivecalls
0dbblockgets
246consistentgets
26physicalreads
0redosize
2539bytessentviaSQL*Nettoclient
641bytesreceivedviaSQL*Netfromclient
5SQL*Netroundtripsto/fromclient
10sorts(memory)
0sorts(disk)
50rowsprocessed
SQL>
--可以看到远程表示走全表扫。

SQL>select/*+DRIVING_SITE(r)*/l.col06,l.col05,l.col04,r.col27,r.col26,r.col25
2fromt_locall,t_remote@dblink_remoter
3wherel.col02=r.col02
4;
50rowsselected.
Elapsed:00:00:00.03
ExecutionPlan
----------------------------------------------------------
Planhashvalue:1716516160
-------------------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Inst|IN-OUT|
-------------------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENTREMOTE||50|6450|103(0)|00:00:01|||
|1|NESTEDLOOPS||50|6450|103(0)|00:00:01|||
|2|NESTEDLOOPS||50|6450|103(0)|00:00:01|||
|3|REMOTE|T_LOCAL|50|3300|3(0)|00:00:01|!|R->S|
|*4|INDEXRANGESCAN|T_REMOTE_I02|1||1(0)|00:00:01|ORA12C||
|5|TABLEACCESSBYINDEXROWID|T_REMOTE|1|63|2(0)|00:00:01|ORA12C||
-------------------------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
4-access("A2"."COL02"="A1"."COL02")
RemoteSQLInformation(identifiedbyoperationid):
----------------------------------------------------
3-SELECT"COL02","COL04","COL05","COL06"FROM"T_LOCAL""A2"(accessing'!')
Note
-----
-fullyremotestatement
-thisisanadaptiveplan
Statistics
----------------------------------------------------------
137recursivecalls
0dbblockgets
213consistentgets
25physicalreads
0redosize
2940bytessentviaSQL*Nettoclient
641bytesreceivedviaSQL*Netfromclient
5SQL*Netroundtripsto/fromclient
10sorts(memory)
0sorts(disk)
50rowsprocessed
SQL>
--可以看到本地表是走全表扫，但是远程表使用了第2个字段的索引。

总结

以上就是本文关于远程数据库的表超过20个索引的影响详细解析的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站：SQL提取数据库表名及字段名等信息代码示例、MySQL数据库表分区注意事项大全【推荐】等，有什么问题可以直接留言，小编会及时回复大家的。感谢朋友们对本站的支持！

远程数据库的表超过20个索引的影响详细解析

热门推荐

随机推荐