Shulou(Shulou.com)05/31 Report--

This article mainly introduces the impact of more than 20 indexes of tables in the remote database, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let's take a look at it.

After analysis, it is found that the query statement through dblink, when querying the remote database, uses the index, but after the remote database adds the index, if the number of indexes exceeds 20, the first index will be ignored. If the query statement happens to use the first index, it can only go Full Table Scan after being ignored.

After listening to this case, I looked it up. In the official oracle document, there is a passage about 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.

It is said that if the remote database uses more than 20 indexes, these indexes will not be considered. This paragraph has existed in the documents since oracle 9i until 12.2.

So, with more than 20 indexes, are the new indexes ignored? Or is the old index ignored? How to make ignored indexes make oracle aware? Let's test it.

(this article is based on the remote library of 12.1.0.2 and the local library of 12.2.0.1. If you are not interested in the testing process, you can go directly to the "summary" section at the end of the article.)

(1) initialize the test table:

-- create remote tables: DROP TABLE tweak 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_remoteSELECTrownum, rownum, rpad, rownum, rownum Rpad ('*', 50-hundred-year-old), rownum, rownum, rpad ('*', 50-month-old) FROM dualCONNECT BY level select host_name from v$instance HOST_NAME----testdb10 SQL >

As you can see, the remote table has 27 fields, and so far only the first 20 fields have been indexed, and the first field is the primary key. There are 6 fields in this table, and all 6 fields are indexed.

(2) in the first round of tests, there are 20 indexes on the remote table.

Test scenario 1:

In the case of remote table 20 index, the local table is associated with the remote table, and the first field of the remote table is associated with the first field of the local table:

Select l.col06 l.col05authorl.col04authorr.col27, r.col26authorr.col25 from t_local l, t_remote@dblink_remote rwhere l.col01sancr.col01scarf select * from table (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.col01Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 53 | NESTED LOOPS | | 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 >-- Let's pay attention here. WHERE: 1 = "COL01" exists because of this condition, so the primary key is used remotely instead of a full table scan. We bring this statement to remote execution. Remote: 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.

We can see that for the execution plan of the remote table, this is the primary key.

Test scenario 2:

In the case of remote table 20 index, the local table is associated with the remote table, and the 20th field of the remote table is associated with the first field of the local table:

Select l.col06 l.col05authorl.col04authorr.col27, r.col26authorr.col25 from t_local l, t_remote@dblink_remote rwhere l.col01sancr.col20scarf select * from table (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.col20Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 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 > remote: 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 >

We can see that for the execution plan of the remote table, this is scanned by the index range.

Test scenario 3:

In the case of remote table 20 index, the local table is associated with the remote table, and the second field of the remote table is associated with the second field of the local table:

Select l.col06 l.col05authorl.col04authorr.col27r.col26authorr.col25from t_local l, t_remote@dblink_remote rwhere l.col02sancr.col02scarfselect * from table (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.col02Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 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 > remote: 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 >

We can see that for the execution plan of the remote table, this is scanned by the index range.

Test scenario 4:

In the case of remote table 20 index, the local table is associated with the remote table, and the 20th field of the remote table is associated with the second field of the local table:

Select l.col06 l.col05authorl.col04authorr.col27, r.col26authorr.col25 from t_local l, t_remote@dblink_remote rwhere l.col02sancr.col20scarf select * from table (dbms_xplan.display_cursor (null, null, 'col20)) 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.col20Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 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 > remote: 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 >

We can see that for the execution plan of the remote table, this is scanned by the index range.

(III) Establishment of the 21st index:

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

(4) there are now 21 indexes on the remote table. Repeat the above four tests:

Test scenario 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.col01Plan hash value: 830255788-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 156100 | | * 1 | HASH JOIN | | 50 | 6300 | 3000 | 00:00:01 | 2 | TABLE ACCESS FULL | T_LOCAL | 50 | 3000 | 3 (0) | 00:00:01 | 3 | REMOTE | T_REMOTE | 10000 | 644k | 153k | 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 >-- we see Without the previous WHERE: 1 = "COL01" here, we can guess that it is a full table scan even if we don't bring it to the remote view of the execution plan. Remote: 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 >

We can see that for the execution plan of the remote table, if the association condition is the first field of the remote table, the index on the first field is ignored, and the execution plan selects a full table scan.

Test scenario 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.col20Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 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 > remote: 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 >

We can see that for the execution plan of the remote table, if the association condition is the 20th field of the remote table, the index on the 20th field is not ignored, and the execution plan is to follow the index.

Test scenario 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.col02Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 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 > remote: 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 >

We can see that for the execution plan of the remote table, if the association condition is the second field of the remote table, the index on this second field is not ignored, and the execution plan is to take the index.

Test scenario 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.col20Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 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 > remote: 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 >

We can see that for the execution plan of the remote table, if the association condition is the 20th field of the remote table, the index on the 20th field is not ignored, and the execution plan is to follow the index.

We can now conclude that when the 21st index of the remote table is established, the local table and the remote table are associated through dblink. If the association condition is the field of the first index of the remote table, then the index will be ignored and the full table scan will be performed. If the association condition is the second indexed field of the remote table, it is not affected.

The window that seems to be a valid index is 20, and when the 21st is created, the first is ignored.

(5) to establish the 22nd index, we are going to see if the above guess is in line with it.

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

(6) at present, there are 22 indexes in the remote table. Repeat the above four tests:

Test scenario 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.col01Plan hash value: 830255788-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 156100 | | * 1 | HASH JOIN | | 50 | 6300 | 3000 | 00:00:01 | 2 | TABLE ACCESS FULL | T_LOCAL | 50 | 3000 | 3 (0) | 00:00:01 | 3 | REMOTE | T_REMOTE | 10000 | 644k | 153k | 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 >

Test scenario 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.col20Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 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 >

Test scenario 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.col02Plan hash value: 830255788-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 156100 | | * 1 | HASH JOIN | | 50 | 6300 | 3000 | 00:00:01 | 2 | TABLE ACCESS FULL | T_LOCAL | 50 | 3000 | 3 (0) | 00:00:01 | 3 | REMOTE | T_REMOTE | 10000 | 644k | 153k | 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 >

Test scenario 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.col20Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 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 >

The above tests can actually verify our conjecture. Oracle is only aware of the fields of the most recently created 20 indexes for accessing remote tables through dblink associations. This realizes that there are 20 windows for the index, and once a new index is created, the oldest index will be ignored.

(7) Let's try the rebuild index to see if it works:

Second index of rebuild

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

(8) repeat the above four tests after the second index rebuild:

-- Test scenario 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.col01Plan hash value: 830255788-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 156100 | | * 1 | HASH JOIN | | 50 | 6300 | 3000 | 00:00:01 | 2 | TABLE ACCESS FULL | T_LOCAL | 50 | 3000 | 3 (0) | 00:00:01 | 3 | REMOTE | T_REMOTE | 10000 | 644k | 153k | 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 >-- Test scenario 2 -- 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.col20Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 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 >-- Test scenario 3 -- 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.col02Plan hash value: 830255788-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 156100 | | * 1 | HASH JOIN | | 50 | 6300 | 3000 | 00:00:01 | 2 | TABLE ACCESS FULL | T_LOCAL | 50 | 3000 | 3 (0) | 00:00:01 | 3 | REMOTE | T_REMOTE | 10000 | 644k | 153k | 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 >-- Test scenario 4 -- 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.col20Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 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 >

So we can see that the index rebuild cannot play the role of "waking up" the index again.

(9) We try to drop and recreate the second index.

Drop index tweak remotekeeper i02 * * create index t_remote_i02 on t_remote (col02); exec dbms_stats.gather_table_stats (user,'T_REMOTE')

(X) repeat tests 3 and 4 above:

Test 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.col02Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 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 > Test 4 -- 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.col20Plan hash value: 631452043-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 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 > at this time In fact, we can predict that the index on the col03 of the remote table is not needed at this time. Let's test and verify: test 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.col03Plan hash value: 830255788-| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |-| 0 | SELECT STATEMENT | | | | 157100 | | * 1 | HASH JOIN | | 500K | 89m | 157m | 00:00:01 | 2 | TABLE ACCESS FULL | T_LOCAL | 50 | 5400 | 3 (0) | 00:00:01 | 3 | REMOTE | T_REMOTE | 10000 | 781k | 153k (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 >

We can see that the second index can be "awakened" by rebuilding after drop. This also proves that the mobile windows identified by our 20 indexes are moved according to the creation time of the index.

To sum up:

1. For associating local tables and remote tables through dblink, if the number of indexes of the remote table is less than 20, then it is not affected.

two。 For associating local tables and remote tables through dblink, if the number of indexes of remote tables increases to 21 or more, then oracle will ignore the earliest created index when performing remote operations, but will move in a window of 20, and the newly created index will be recognized. At this point, if the field of the earliest created index is used in the association condition of the query, the full table scan will be performed because the index is ignored.

3. To "awaken" awareness of the original index, the rebuild index is invalid and requires a drop & create index.

4. We can see the example at the end of the article. Whether or not to use hint, you need to assess the cost of fully pushing local surface data to the remote, and the cost of using full table sweeps for remote tables.

Attachment: in the case of 22 indexes, try to use DRIVING_SITE 's hint:

SQL > select l.col06 from t_local l.col05 where l.col02=r.col02 l.col04 from t_local r.col27, r.col26 from t_local l, col25 2 l, col05 3 t_remote@dblink_remote 4 50 rows selected.Elapsed: 00:00:00.03Execution Plan---Plan hash value: 830255788 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Inst | IN-OUT |- -- | 0 | SELECT STATEMENT | | 50 | 6300 | 156 | 00:00:01 | * 1 | HASH JOIN | | 50 | 6300 | 6300 | 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 processedSQL >-you can see the remote means to go full table scan. SQL > select / * + DRIVING_SITE (r) * / l.col06 where l.col02=r.col02 l.col05 where l.col02=r.col02 l.col04 from t_local r.col27, r.col26Powerr.col25 2 where l.col02=r.col02 l, col05 3 Col06 4 50 rows selected.Elapsed: 00:00:00.03Execution Plan---Plan hash value: 1716516160Murray- -| 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 planStatistics--- 137 recursive calls 0 db block gets 213 consistent gets 25 physical reads 0 redo size 2940 bytes sent via SQL*Net to client 641bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 50 rows processedSQL >-- you can see that the local surface is fully scanned. But the remote table uses the index of the second field. Thank you for reading this article carefully. I hope the article "what is the impact of more than 20 indexes of tables in a remote database" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.