安科网

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

zgxzowen

zgxzowen

2019-04-02

关注 关注

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

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

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

Several performance restrictions relate to access of remote objects:

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

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

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

（一）初始化测试表：

--创建远程表：
DROP TABLE t_remote;
 CREATE TABLE t_remote (
col01 NUMBER,
col02 NUMBER,
col03 VARCHAR2(50),
col04 NUMBER,
col05 NUMBER,
col06 VARCHAR2(50),
col07 NUMBER,
col08 NUMBER,
col09 VARCHAR2(50),
col10 NUMBER,
col11 NUMBER,
col12 VARCHAR2(50),
col13 NUMBER,
col14 NUMBER,
col15 VARCHAR2(50),
col16 NUMBER,
col17 NUMBER,
col18 VARCHAR2(50),
col19 NUMBER,
col20 NUMBER,
col21 VARCHAR2(50),
col22 NUMBER,
col23 NUMBER,
col24 VARCHAR2(50),
col25 NUMBER,
col26 NUMBER,
col27 VARCHAR2(50)
);
alter table t_remote modify (col01 not null);
INSERT INTO t_remote
SELECT
rownum, rownum, rpad('*',50,'*'),
rownum, rownum, rpad('*',50,'*'),
rownum, rownum, rpad('*',50,'*'),
rownum, rownum, rpad('*',50,'*'),
rownum, rownum, rpad('*',50,'*'),
rownum, rownum, rpad('*',50,'*'),
rownum, rownum, rpad('*',50,'*'),
rownum, rownum, rpad('*',50,'*'),
rownum, rownum, rpad('*',50,'*')
FROM dual
CONNECT BY level <= 10000;
commit; 
create unique index t_remote_i01_pk on t_remote (col01);
alter table t_remote add (constraint t_remote_i01_pk primary key (col01) using index t_remote_i01_pk);
create index t_remote_i02 on t_remote (col02);
create index t_remote_i03 on t_remote (col03);
create index t_remote_i04 on t_remote (col04);
create index t_remote_i05 on t_remote (col05);
create index t_remote_i06 on t_remote (col06);
create index t_remote_i07 on t_remote (col07);
create index t_remote_i08 on t_remote (col08);
create index t_remote_i09 on t_remote (col09);
create index t_remote_i10 on t_remote (col10);
create index t_remote_i11 on t_remote (col11);
create index t_remote_i12 on t_remote (col12);
create index t_remote_i13 on t_remote (col13);
create index t_remote_i14 on t_remote (col14);
create index t_remote_i15 on t_remote (col15);
create index t_remote_i16 on t_remote (col16);
create index t_remote_i17 on t_remote (col17);
create index t_remote_i18 on t_remote (col18);
create index t_remote_i19 on t_remote (col19);
create index t_remote_i20 on t_remote (col20);
 
exec dbms_stats.gather_table_stats(user,'T_REMOTE');
--创建本地表：
drop table t_local;
 
CREATE TABLE t_local (
col01 NUMBER,
col02 NUMBER,
col03 VARCHAR2(50),
col04 NUMBER,
col05 NUMBER,
col06 VARCHAR2(50)
);
 
INSERT INTO t_local
SELECT
rownum, rownum, rpad('*',50,'*'),
rownum, rownum, rpad('*',50,'*')
FROM dual
CONNECT BY level <= 50;
 
COMMIT;
 
create index t_local_i01 on t_local (col01);
create index t_local_i02 on t_local (col02);
create index t_local_i03 on t_local (col03);
create index t_local_i04 on t_local (col04);
create index t_local_i05 on t_local (col05);
create index t_local_i06 on t_local (col06);
 
exec dbms_stats.gather_table_stats(user,'t_local');
 
 
create database link dblink_remote CONNECT TO test IDENTIFIED BY test USING 'ora121';
 
 
SQL> select host_name from v$instance@dblink_remote;
 
HOST_NAME
----------------------------------------------------------------
testdb2
 
SQL> select host_name from v$instance;
 
HOST_NAME
----------------------------------------------------------------
testdb10
 
SQL>

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

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

测试场景1：

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

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

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

测试场景2：

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

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

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

测试场景3：

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

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

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

测试场景4：

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

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

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

（三）建立第21个索引：

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

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

测试场景1：

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

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

测试场景2：

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

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

测试场景3：

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

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

测试场景4：

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

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

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

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

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

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

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

测试场景1：

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

测试场景2：

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

测试场景3：

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

测试场景4：

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

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

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

rebuild第2个索引

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

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

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

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

（九）我们尝试 drop and recreate 第2个索引。

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

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

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

我们可以看到，通过drop之后再重建，是可以“唤醒”第二个索引的。这也证明了我们20个索引识别的移动窗口，是按照索引的创建时间来移动的。

综上：

1. 对于通过dblink关联本地表和远程表，如果远程表的索引个数少于20个，那么不受影响。
2. 对于通过dblink关联本地表和远程表，如果远程表的索引个数增加到21个或以上，那么oracle在执行远程操作的时候，将忽略最早创建的那个索引，但是会以20个为窗口移动，最新建立的索引会被意识到。此时如果查询的关联条件中，使用到最早创建的那个索引的字段，由于忽略了索引，会走全表扫描。
3. 要“唤醒”对原来索引的意识，rebuild索引无效，需要drop & create索引。
4. 在本地表数据量比较少，远程表的数据量很大，而索引数量超过20个，且关联条件的字段时最早索引的情况下，可以考虑使用DRIVING_SITE的hint，将本地表的数据全量到远程中，此时远程的关联查询可以意识到那个索引。可见文末的例子。是否使用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.03
Execution Plan
----------------------------------------------------------
Plan hash value: 830255788
-----------------------------------------------------------------------------------------------
| Id | Operation   | Name  | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |   | 50 | 6300 | 156 (0)| 00:00:01 |  |  |
|* 1 | HASH JOIN   |   | 50 | 6300 | 156 (0)| 00:00:01 |  |  |
| 2 | TABLE ACCESS FULL| T_LOCAL | 50 | 3000 |  3 (0)| 00:00:01 |  |  |
| 3 | REMOTE   | T_REMOTE | 10000 | 644K| 153 (0)| 00:00:01 | DBLIN~ | R->S |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - access("L"."COL02"="R"."COL02")
Remote SQL Information (identified by operation id):
----------------------------------------------------
 3 - SELECT "COL02","COL25","COL26","COL27" FROM "T_REMOTE" "R" (accessing
  'DBLINK_REMOTE' )
Statistics
----------------------------------------------------------
  151 recursive calls
   0 db block gets
  246 consistent gets
   26 physical reads
   0 redo size
  2539 bytes sent via SQL*Net to client
  641 bytes received via SQL*Net from client
   5 SQL*Net roundtrips to/from client
   10 sorts (memory)
   0 sorts (disk)
   50 rows processed
SQL>
--可以看到远程表示走全表扫。
SQL> select /*+DRIVING_SITE(r)*/ l.col06,l.col05,l.col04,r.col27, r.col26,r.col25
 2 from t_local l, t_remote@dblink_remote r
 3 where l.col02=r.col02
 4 ;
50 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1716516160
-------------------------------------------------------------------------------------------------------------
| Id | Operation     | Name   | Rows | Bytes | Cost (%CPU)| Time  | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE  |    | 50 | 6450 | 103 (0)| 00:00:01 |  |  |
| 1 | NESTED LOOPS    |    | 50 | 6450 | 103 (0)| 00:00:01 |  |  |
| 2 | NESTED LOOPS    |    | 50 | 6450 | 103 (0)| 00:00:01 |  |  |
| 3 | REMOTE     | T_LOCAL  | 50 | 3300 |  3 (0)| 00:00:01 |  ! | R->S |
|* 4 | INDEX RANGE SCAN   | T_REMOTE_I02 |  1 |  |  1 (0)| 00:00:01 | ORA12C |  |
| 5 | TABLE ACCESS BY INDEX ROWID| T_REMOTE  |  1 | 63 |  2 (0)| 00:00:01 | ORA12C |  |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 4 - access("A2"."COL02"="A1"."COL02")
Remote SQL Information (identified by operation id):
----------------------------------------------------
 3 - SELECT "COL02","COL04","COL05","COL06" FROM "T_LOCAL" "A2" (accessing '!' )
Note
-----
 - fully remote statement
 - this is an adaptive plan
Statistics
----------------------------------------------------------
  137 recursive calls
   0 db block gets
  213 consistent gets
   25 physical reads
   0 redo size
  2940 bytes sent via SQL*Net to client
  641 bytes received via SQL*Net from client
   5 SQL*Net roundtrips to/from client
   10 sorts (memory)
   0 sorts (disk)
   50 rows processed
SQL>
--可以看到本地表是走全表扫，但是远程表使用了第2个字段的索引。

总结

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

oracle索引

zgxzowen

zgxzowen

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

oracle数据库关于索引建立及使用的详细介绍

索引是与表相关的一个可选结构，在逻辑上和物理上都独立于表的数据，索引能优化查询，不能优化DML操作，Oracle自动维护索引，频繁的DML操作反而会引起大量的索引维护。如果该语句同时还要访问除索引列之外的列，那么，数据库会使用rowid来查找表中的行。通常

罗罗 2020-08-16

索引分裂：90-10分裂

数据库的索引基于B-树的结构，通过将数据插入到对应的叶子节点中实现数据新增的功能。更新值的操作是通过"删除"原有的值并在对应的叶子节点上添加修改后的值来实现的。举个例子，假设现在有一个索引键，并且假设一个数据块只能容纳该索引的4个索引值

oraclemch 2020-07-30

关于ORACLE索引的几种扫描方式

一条sql执行的效率因执行计划的差异而影响，经常说这条sql走索引了，那条sql 全表扫了。索引是怎么走的呢，说说我了解到的几种索引走的方式。具体来说，仅当查询谓词使用等号运算符引用唯一索引键中的所有列时，数据库才执行唯一扫描.数据库从索引条目中获取行标识

娜娜 2020-07-18

疑问？ Oracle普通的B树索引是升序还是降序？

Oracle普通的B树索引是升序还是降序？SQL> create table a as select * from dba_objects;SQL> select object_name from a where object_id in or

talkingDB 2020-07-05

Oracle索引或这类索引的分区处于不可用状态

select ‘alter index ‘||index_name||‘ rebuild online;‘ from user_indexes where status <> ‘VALID‘ and index_name not like‘%$

bianxq 2020-06-03

SQL优化

因为Oracle总是先解析SQL语句，把小写的字母转换成大写字母再执行。数据库在解析的过程中会将 * 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接，

talkingDB 2020-05-31

记一次Oracle分区表全局索引重建的过程

SELECT Upper "表空间名", D.TOT_GROOTTE_MB "表空间大小", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间"

zhangchaoming 2020-05-17

面试篇三：数据库MySQL、Oracle

SELECT * FROM admin_company ORDER BY id LIMIT 0,10;limit后参数代表什么意思？LIMIT[位置偏移量,]行数，第一个参数是从哪个序号加上，初始是0，第二个参数是查询多少行记录。MySQL在创建表时就会创

康慧欣 2020-05-02

oracle，哪些操作会导致索引失效？

如SUBSTR,DECODE,INSTR等，对索引列进行运算.需要建立函数索引就可以解决了。

Carlos 2020-05-01

&lt;&lt;SQL进阶教程&gt;&gt;([日]MICK/著 吴炎昌/译)之性能优化篇

对于联合索引，只有查询条件是联合索引的第一个字段，索引就是有效的。WHERE子句可以使用索引，HAVING子句是针对聚合后生成的视图进行筛选的，很多时候并无继承原表的索引结构。

dreamhua 2020-03-28

联合索引只用单字段时是否用到索引

这个规则在oracle和mysql数据库中均成立。如果a和b都要分别经常独立的被用作查询条件，那还是建立多个单列索引。

LuoXinLoves 2020-03-28

Oracle数据库索引相关

create [unique] index index_name on table_name tablespace tab_space;--创建索引

FightFourEggs 2020-02-18

115资源苹果手机怎么找

前面一篇文章从实例的角度进行数据库优化，通过配置一些参数让数据库性能达到最优。但是一些“不好”的SQL也会导致数据库查询变慢，影响业务流程。本文从SQL角度进行数据库优化，提升SQL运行效率。可以使用sar命令，top命令查看当前系统状态。也可以通过Pro

kuwoyinlehe 2020-01-29

Mysql数据库的索引和视图详解

数据库的索引与书籍中的目录类似在一本书中，无需阅读整本书，利用目录就可以快速查找所需信息书中的目录是一个词语列表，其中注明了包含各个词的页码数据库索引在数据库中，索引数据库程序无需对整个表进行扫描，就可以在其中找到所需数据数据库中的索引是某个表中一列或若干

bluetears 2020-01-10

01： MySQL简介

所以，现在我们使用关系型数据库管理系统。4．RDBMS即关系数据库管理系统的特点：。alter view good_price as select name,price from goods where price >300;

踩风火轮的乌龟 2020-01-08

全局索引和本地索引的区别

Oracle数据库中，有两种类型的分区索引，全局索引和本地索引，其中本地索引又可以分为本地前缀索引和本地非前缀索引。下面就分别看看每种类型的索引各自的特点。全局索引以整个表的数据为对象建立索引，索引分区中的索引条目既可能是基于相同的键值但是来自不同的分区，

maiktom 2019-12-31

Oracle 集合类型

a. 单行单列的数据，使用标量变量 。b. 单行多列数据，使用记录 [ 详细讲解请见：点击打开链接] c. 单列多行数据，使用集合。pl/sql集合类型包括关联数组Associative array、嵌套表、变长数组。Nested table与VARRY既

zbcaicai 2019-12-31

怎样保持Oracle数据优良性能的若干诀窍

如今，Oracle数据库以其高可靠性、安全性、可兼容性，得到越来越多的企业的青睐。如何使Oracle数据库保持优良性能，这是许多数据库治理员关心的问题，笔者建议不妨针对以下几个方面加以考虑。分区 根据实际经验，在一个大数据库中，数据库空间的绝大多数是被少量

shiaisun 2014-06-03

数据库-Oracle通过执行计划查看查询语句是否使用索引【转】

select * from t_call_records where t_bjhm=‘123456‘。如上图所示，index range scan为索引范围扫描;索引范围扫描，当优化器发现在UNIQUE列上使用了大于、小于、大于等于、小于等于以及BETWE

Streamoutput 2019-12-02

详解Oracle数据库的三大索引类型

概述今天主要介绍Oracle数据库的三大索引类型，仅供参考。外键上建立索引：不但可以提升查询效率，而且可以有效避免锁的竞争。普遍流行的观点：重复记录少的字段放在前面，重复记录多的放在后面，其实这样的结论并不准确。表上有过多索引主要会严重影响插入性能；cre

Streamoutput 2019-11-28
zgxzowen

zgxzowen

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号