您现在的位置是：网站首页> 编程资料编程资料

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

2023-05-27 310人已围观

简介远程数据库的表超过20个索引的影响详细解析_数据库其它_

昨天同事参加了一个研讨会,有提到一个案例。一个通过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

上一篇：SQL注入原理与解决方法代码示例_数据库其它_

下一篇：90%程序员面试会遇到的索引优化问题_数据库其它_

您现在的位置是：网站首页> 编程资料编程资料

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

相关内容

点击排行

本栏推荐

猜你喜欢