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

发布时间：2020-05-28 09:52:49 所属栏目：MsSql 来源：互联网

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

昨天同事参加了一个研讨会,有提到一个案例。一个通过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,'*'),'*')
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,col06 VARCHAR2(50)
);
 
INSERT INTO t_local
SELECT
rownum,'*')
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,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","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,t_remote@dblink_remote r
where l.col01=r.col20
;
select * from table( dbms_xplan.display_cursor(null,'typical LAST') );
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID 5rwtbwcnv0tsm,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","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：

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!