遠(yuǎn)程數(shù)據(jù)庫(kù)的表超過(guò)20個(gè)索引的影響詳細(xì)解析
更新時(shí)間：2017年10月24日 09:50:36 投稿：mengwei
這篇文章主要介紹了遠(yuǎn)程數(shù)據(jù)庫(kù)的表超過(guò)20個(gè)索引的影響詳細(xì)解析，具有一定參考價(jià)值，需要的朋友可以了解下。
昨天同事參加了一個(gè)研討會(huì),有提到一個(gè)案例。一個(gè)通過(guò)dblink查詢(xún)遠(yuǎn)端數(shù)據(jù)庫(kù)，原來(lái)查詢(xún)很快，但是遠(yuǎn)端數(shù)據(jù)庫(kù)增加了一個(gè)索引之后，查詢(xún)一下子變慢了。
經(jīng)過(guò)分析，發(fā)現(xiàn)那個(gè)通過(guò)dblink的查詢(xún)語(yǔ)句，查詢(xún)遠(yuǎn)端數(shù)據(jù)庫(kù)的時(shí)候，是走索引的，但是遠(yuǎn)端數(shù)據(jù)庫(kù)添加索引之后，如果索引的個(gè)數(shù)超過(guò)20個(gè)，就會(huì)忽略第一個(gè)建立的索引，如果查詢(xún)語(yǔ)句恰好用到了第一個(gè)建立的索引，被忽略之后，只能走Full Table Scan了。
聽(tīng)了這個(gè)案例，我查了一下，在oracle官方文檔中，關(guān)于Managing a Distributed Database有一段話(huà)：
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.
說(shuō)到，如果遠(yuǎn)程數(shù)據(jù)庫(kù)使用超過(guò)20個(gè)索引，這些索引將不被考慮。這段話(huà)，在oracle 9i起的文檔中就已經(jīng)存在，一直到12.2還有。
那么，超過(guò)20個(gè)索引，是新的索引被忽略了？還是老索引被忽略了？如何讓被忽略的索引讓oracle意識(shí)到?我們來(lái)測(cè)試一下。
（本文基于12.1.0.2的遠(yuǎn)程庫(kù)和12.2.0.1的本地庫(kù)進(jìn)行測(cè)試，如果對(duì)測(cè)試過(guò)程沒(méi)興趣的，可以直接拉到文末看“綜上”部分）
（一）初始化測(cè)試表：
--創(chuàng)建遠(yuǎn)程表：
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');
--創(chuàng)建本地表：
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>
可以看到，遠(yuǎn)程表有27個(gè)字段，目前還只是在前20個(gè)字段建立了索引，且第一個(gè)字段是主鍵。本地表，有6個(gè)字段，6個(gè)字段都建索引。
（二）第一輪測(cè)試，遠(yuǎn)程表上有20個(gè)索引。
測(cè)試場(chǎng)景1：
在遠(yuǎn)程表20索引的情況下，本地表和遠(yuǎn)程表關(guān)聯(lián)，用本地表的第一個(gè)字段關(guān)聯(lián)遠(yuǎn)程表的第一個(gè)字段：
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"的存在，正是因?yàn)檫@個(gè)條件，所以在遠(yuǎn)程是走了主鍵而不是全表掃。我們把這個(gè)語(yǔ)句帶入到遠(yuǎn)程執(zhí)行。
遠(yuǎn)程：
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.
我們可以看到，對(duì)于遠(yuǎn)程表的執(zhí)行計(jì)劃，這是走主鍵的。
測(cè)試場(chǎng)景2：
在遠(yuǎn)程表20索引的情況下，本地表和遠(yuǎn)程表關(guān)聯(lián)，用本地表的第一個(gè)字段關(guān)聯(lián)遠(yuǎn)程表的第20個(gè)字段：
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>
遠(yuǎn)程：
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>
我們可以看到，對(duì)于遠(yuǎn)程表的執(zhí)行計(jì)劃，這是走索引范圍掃描的。
測(cè)試場(chǎng)景3：
在遠(yuǎn)程表20索引的情況下，本地表和遠(yuǎn)程表關(guān)聯(lián)，用本地表的第2個(gè)字段關(guān)聯(lián)遠(yuǎn)程表的第2個(gè)字段：
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>
遠(yuǎn)程：
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>
我們可以看到，對(duì)于遠(yuǎn)程表的執(zhí)行計(jì)劃，這是走索引范圍掃描的。
測(cè)試場(chǎng)景4：
在遠(yuǎn)程表20索引的情況下，本地表和遠(yuǎn)程表關(guān)聯(lián)，用本地表的第2個(gè)字段關(guān)聯(lián)遠(yuǎn)程表的第20個(gè)字段：
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>
遠(yuǎn)程：
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>
我們可以看到，對(duì)于遠(yuǎn)程表的執(zhí)行計(jì)劃，這是走索引范圍掃描的。
（三）建立第21個(gè)索引：
create index t_remote_i21 on t_remote (col21); exec dbms_stats.gather_table_stats(user,'T_REMOTE');
（四）遠(yuǎn)程表上現(xiàn)在有21個(gè)索引，重復(fù)上面4個(gè)測(cè)試：
測(cè)試場(chǎng)景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>
--我們看到，這里已經(jīng)沒(méi)有了之前的 WHERE :1="COL01",即使不帶入到遠(yuǎn)程看執(zhí)行計(jì)劃，我們也可以猜到它是全表掃。
遠(yuǎn)程：
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>
我們可以看到，對(duì)于遠(yuǎn)程表的執(zhí)行計(jì)劃，如果關(guān)聯(lián)條件是遠(yuǎn)程表的第一個(gè)字段，第一個(gè)字段上的索引是被忽略的，執(zhí)行計(jì)劃是選擇全表掃描的。
測(cè)試場(chǎng)景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>
遠(yuǎn)程：
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>
我們可以看到，對(duì)于遠(yuǎn)程表的執(zhí)行計(jì)劃，如果關(guān)聯(lián)條件是遠(yuǎn)程表的第20個(gè)字段，這第20個(gè)字段上的索引是沒(méi)有被忽略的，執(zhí)行計(jì)劃是走索引。
測(cè)試場(chǎng)景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>
遠(yuǎn)程：
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>
我們可以看到，對(duì)于遠(yuǎn)程表的執(zhí)行計(jì)劃，如果關(guān)聯(lián)條件是遠(yuǎn)程表的第2個(gè)字段，這第2個(gè)字段上的索引是沒(méi)有被忽略的，執(zhí)行計(jì)劃是走索引。
測(cè)試場(chǎng)景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>
遠(yuǎn)程：
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>
我們可以看到，對(duì)于遠(yuǎn)程表的執(zhí)行計(jì)劃，如果關(guān)聯(lián)條件是遠(yuǎn)程表的第20個(gè)字段，這第20個(gè)字段上的索引是沒(méi)有被忽略的，執(zhí)行計(jì)劃是走索引。
我們目前可以總結(jié)到，當(dāng)遠(yuǎn)程表第21個(gè)索引建立的時(shí)候，通過(guò)dblink關(guān)聯(lián)本地表和遠(yuǎn)程表，如果關(guān)聯(lián)條件是遠(yuǎn)程表的第1個(gè)建立的索引的字段，那么這個(gè)索引將被忽略，從而走全表掃描。如果關(guān)聯(lián)條件是遠(yuǎn)程表的第2個(gè)建立索引的字段，則不受影響。
似乎是有效索引的窗口是20個(gè)，當(dāng)新建第21個(gè)，那么第1個(gè)就被無(wú)視了。
（五）建立第22個(gè)索引，我們?cè)趤?lái)看看上述猜測(cè)是否符合。
create index t_remote_i22 on t_remote (col22); exec dbms_stats.gather_table_stats(user,'T_REMOTE');
（六），目前遠(yuǎn)程表有22個(gè)索引，重復(fù)上面4個(gè)測(cè)試：
測(cè)試場(chǎng)景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>
測(cè)試場(chǎng)景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>
測(cè)試場(chǎng)景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>
測(cè)試場(chǎng)景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>
上述的測(cè)試，其實(shí)是可以驗(yàn)證我們的猜測(cè)的。oracle對(duì)于通過(guò)dblink關(guān)聯(lián)訪(fǎng)問(wèn)遠(yuǎn)程表，只是會(huì)意識(shí)到最近創(chuàng)建的20個(gè)索引的字段。這個(gè)意識(shí)到索引的窗口是20個(gè)，一旦建立了一個(gè)新索引，那么最舊的一個(gè)索引會(huì)被無(wú)視。
（七）我們嘗試rebuild索引，看看有沒(méi)有效果：
rebuild第2個(gè)索引
alter index t_remote_i02 rebuild; exec dbms_stats.gather_table_stats(user,'T_REMOTE');
（八）在第2個(gè)索引rebuild之后，重復(fù)上面4個(gè)測(cè)試：
--測(cè)試場(chǎng)景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>
--測(cè)試場(chǎng)景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>
--測(cè)試場(chǎng)景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>
--測(cè)試場(chǎng)景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個(gè)索引。
drop index t_remote_i02; create index t_remote_i02 on t_remote (col02); exec dbms_stats.gather_table_stats(user,'T_REMOTE');
（十）重復(fù)上面的測(cè)試3和測(cè)試4：
測(cè)試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>
測(cè)試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>
此時(shí)，其實(shí)我們可以預(yù)測(cè)，遠(yuǎn)程表此時(shí)col03上的索引是用不到的，我們來(lái)測(cè)試驗(yàn)證一下：
測(cè)試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>
我們可以看到，通過(guò)drop之后再重建，是可以“喚醒”第二個(gè)索引的。這也證明了我們20個(gè)索引識(shí)別的移動(dòng)窗口，是按照索引的創(chuàng)建時(shí)間來(lái)移動(dòng)的。
綜上：
1. 對(duì)于通過(guò)dblink關(guān)聯(lián)本地表和遠(yuǎn)程表，如果遠(yuǎn)程表的索引個(gè)數(shù)少于20個(gè)，那么不受影響。
2. 對(duì)于通過(guò)dblink關(guān)聯(lián)本地表和遠(yuǎn)程表，如果遠(yuǎn)程表的索引個(gè)數(shù)增加到21個(gè)或以上，那么oracle在執(zhí)行遠(yuǎn)程操作的時(shí)候，將忽略最早創(chuàng)建的那個(gè)索引，但是會(huì)以20個(gè)為窗口移動(dòng)，最新建立的索引會(huì)被意識(shí)到。此時(shí)如果查詢(xún)的關(guān)聯(lián)條件中，使用到最早創(chuàng)建的那個(gè)索引的字段，由于忽略了索引，會(huì)走全表掃描。
3. 要“喚醒”對(duì)原來(lái)索引的意識(shí)，rebuild索引無(wú)效，需要drop & create索引。
4. 在本地表數(shù)據(jù)量比較少，遠(yuǎn)程表的數(shù)據(jù)量很大，而索引數(shù)量超過(guò)20個(gè)，且關(guān)聯(lián)條件的字段時(shí)最早索引的情況下，可以考慮使用DRIVING_SITE的hint，將本地表的數(shù)據(jù)全量到遠(yuǎn)程中，此時(shí)遠(yuǎn)程的關(guān)聯(lián)查詢(xún)可以意識(shí)到那個(gè)索引?？梢?jiàn)文末的例子。是否使用hint，需要評(píng)估本地表數(shù)據(jù)全量推送到遠(yuǎn)程的成本，和遠(yuǎn)程表使用全表掃的成本。
附：在22個(gè)索引的情況下，嘗試采用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>
--可以看到遠(yuǎn)程表示走全表掃。
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>
--可以看到本地表是走全表掃，但是遠(yuǎn)程表使用了第2個(gè)字段的索引。
總結(jié)
以上就是本文關(guān)于遠(yuǎn)程數(shù)據(jù)庫(kù)的表超過(guò)20個(gè)索引的影響詳細(xì)解析的全部?jī)?nèi)容，希望對(duì)大家有所幫助。感興趣的朋友可以繼續(xù)參閱本站：SQL提取數(shù)據(jù)庫(kù)表名及字段名等信息代碼示例、MySQL數(shù)據(jù)庫(kù)表分區(qū)注意事項(xiàng)大全【推薦】等，有什么問(wèn)題可以直接留言，小編會(huì)及時(shí)回復(fù)大家的。感謝朋友們對(duì)本站的支持！
相關(guān)文章
數(shù)據(jù)庫(kù)正規(guī)化和設(shè)計(jì)技巧
數(shù)據(jù)庫(kù)正規(guī)化和設(shè)計(jì)技巧...2007-06-06
用計(jì)算列實(shí)現(xiàn)移動(dòng)加權(quán)平均算法
昨天有人讓我?guī)兔?xiě)個(gè)算移動(dòng)加權(quán)平均的SQL語(yǔ)句，我想了半天終于寫(xiě)出來(lái)正確的了。現(xiàn)在發(fā)出來(lái)供大家參考、討論。2009-09-09
Apache?Doris?Colocate?Join?原理實(shí)踐教程
這篇文章主要為大家介紹了Apache?Doris?Colocate?Join?原理實(shí)踐教程，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2022-10-10
explain慢查詢(xún)SQL調(diào)優(yōu)exists的實(shí)戰(zhàn)
這篇文章主要介紹了explain慢查詢(xún)SQL調(diào)優(yōu)exists的實(shí)戰(zhàn),經(jīng)過(guò)兩次優(yōu)化SQL語(yǔ)句之后,慢SQL的性能顯著提升了,耗時(shí)從8s優(yōu)化到了0.7s,現(xiàn)在拿出來(lái)給大家分享一下,希望對(duì)你會(huì)有所幫助2023-12-12
SQL知識(shí)點(diǎn)之列轉(zhuǎn)行Unpivot函數(shù)
這篇文章主要給大家介紹了關(guān)于SQL知識(shí)點(diǎn)之列轉(zhuǎn)行Unpivot函數(shù)的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用SQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09
Access轉(zhuǎn)換成SQL Server需要注意事項(xiàng)整理
很多朋友想用SQL2000數(shù)據(jù)庫(kù)的編程方法，但是卻又苦于自己是學(xué)ACCESS的，對(duì)SQL只是一點(diǎn)點(diǎn)的了解而已，這里我給大家提供以下參考---將ACCESS轉(zhuǎn)化成SQL2000的方法和注意事項(xiàng)2008-04-04
開(kāi)源數(shù)據(jù)庫(kù)設(shè)計(jì)神器chiner的安裝及初體驗(yàn)介紹
最近在造輪子，從?0?到?1?的那種，就差前臺(tái)的界面了，大家可以耐心耐心耐心期待一下。其中需要設(shè)計(jì)一些數(shù)據(jù)庫(kù)表，可以通過(guò)?Navicat?這種圖形化管理工具直接開(kāi)搞，也可以通過(guò)一些數(shù)據(jù)庫(kù)設(shè)計(jì)工具來(lái)搞，比如說(shuō)?PowerDesigner，更專(zhuān)業(yè)一點(diǎn)2022-02-02
數(shù)據(jù)設(shè)計(jì)之權(quán)限的實(shí)現(xiàn)
這篇文章主要介紹了數(shù)據(jù)設(shè)計(jì)之權(quán)限的實(shí)現(xiàn)，文章圍繞主題展開(kāi)詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的朋友可以參考一下2022-08-08
SQL實(shí)現(xiàn)分頁(yè)查詢(xún)方法總結(jié)
這篇文章介紹了SQL實(shí)現(xiàn)分頁(yè)查詢(xún)方法總結(jié)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-12-12
使用Dbeaver遠(yuǎn)程連接Hive的詳細(xì)方法
這篇文章主要介紹了使用Dbeaver遠(yuǎn)程鏈接Hive的詳細(xì)方法,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03