远程数据库的表超过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, '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数据库表分区注意事项大全【推荐】等，有什么问题可以直接留言，小编会及时回复大家的。感谢朋友们对本站的支持！
赞 (0)
相关推荐
-
oracle数据库索引失效
今天一个同事突然问我索引为什么失效.说实在的,失效的原因有多种: 但是如果是同样的sql如果在之前能够使用到索引,那么现在使用不到索引,以下几种主要情况: 1. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全表) 2. 统计信息失效 需要重新搜集统计信息 3. 索引本身失效 需要重建索引 下面是一些不会使用到索引的原因 索引失效 1) 没有查询条件,或者查询条件没有建立索引 2) 在查询条件上没有使用引导列 3) 查询
-
Oracle数据库中建立索引的基本方法讲解
怎样建立最佳索引? 1.明确地创建索引 create index index_name on table_name(field_name) tablespace tablespace_name pctfree 5 initrans 2 maxtrans 255 storage ( minextents 1 maxextents 16382 pctincrease 0 ); 2.创建基于函数的索引 常用与UPPER.LOWER.TO_CHAR(date)等函数分类上,例: create index
-
什么是数据库索引 有哪些类型和特点
有效优化VPS性能,提高VPS服务器运行速度,除了合理配置WEB服务器外,更多的是需要我们能够很好的优化网站程序及网站数据库,网站数据库的优化最为基础的优化措施就是建立数据库索引了,这里就介绍一下,什么是数据库索引?有哪些类型和特点? ⑴,什么是数据库索引? 数据库索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息.在数据库中,索引的含义与日常意义上的"索引"一词并无多大区别(想想小时候查字典),它是用于提高数据库表数据访问速度的数据库对象. ①
-
mysql数据库索引损坏及修复经验分享
mysql表索引被破坏的问题及解决 下午上班,惊闻我的dedecms的网站出问题了,访问一看,果然全屏报错,检查mysql日志,错误信息为: Table '.\dedecmsv4\dede_archives' is marked as crashed and should be repaired 提示说cms的文章表dede_archives被标记有问题,需要修复.于是赶快恢复历史数据,上网查找原因.最终将问题解决.解决方法如下: 找到mysql的安装目录的bin/myisamchk工具,在命令
-
pymongo为mongodb数据库添加索引的方法
本文实例讲述了pymongo为mongodb数据库添加索引的方法.分享给大家供大家参考.具体实现方法如下: from pymongo import ASCENDING, DESCENDING posts.create_index([("date", DESCENDING), ("author", ASCENDING)]) 返回: u'date_-1_author_1' 希望本文所述对大家的Python程序设计有所帮助.
-
远程数据库的表超过20个索引的影响详细解析
昨天同事参加了一个研讨会,有提到一个案例.一个通过dblink查询远端数据库,原来查询很快,但是远端数据库增加了一个索引之后,查询一下子变慢了. 经过分析,发现那个通过dblink的查询语句,查询远端数据库的时候,是走索引的,但是远端数据库添加索引之后,如果索引的个数超过20个,就会忽略第一个建立的索引,如果查询语句恰好用到了第一个建立的索引,被忽略之后,只能走Full Table Scan了. 听了这个案例,我查了一下,在oracle官方文档中,关于Managing a Distributed
-
MySQL索引机制的详细解析及原理
目录 一.索引的类型与常见的操作 二.常见的索引详解与创建 三.索引的原理 1.通过实验介绍B+tree 2.延伸 四.聚簇索引和非聚簇索引 1.使用聚簇索引的优势 2.什么情况下无法使用索引 总结 一.索引的类型与常见的操作 前缀索引 MySQL 前缀索引能有效减小索引文件的大小,提高索引的速度.但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index). 复合索引 集一个索引包含多个列(最左前
-
Mysql数据库表中为什么有索引却没有提高查询速度
背景 时间过得太快了,春节假期感觉光速般就结束了,转眼间就要继续搬砖上班了.紧接着很快就要进入金三银四的求职面试高峰期,程序猿小枫还没有找到令自己感到满意的工作.就算是在过年放假期间也在拼命的准备技术面试,这不他又梳理了下之前面试过程中面试官经常问到的关于数据库方面的一道面试题,我们来一起帮小枫看看有没有遗漏的地方吧. 面试题目--问题 面试官:看你的简历中有提到过曾经进行过索引优化的工作,那我就问问你,假设数据库表中有索引,但是进行SQL数据查询还是很慢,这种情况下应该怎么分析查询慢的原因?
-
MySQL快速复制数据库数据表的方法
某些时候,例如为了搭建一个测试环境,或者克隆一个网站,需要复制一个已存在的mysql数据库.使用以下方法,可以非常简单地实现. 假设已经存在的数据库名字叫db1,想要复制一份,命名为newdb.步骤如下: 1. 首先创建新的数据库newdb #mysql -u root -ppassword mysql>CREATE DATABASE `newdb` DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI; 2. 使用mysqldump及mysql的
-
JavaScript基于activexobject连接远程数据库SQL Server 2014的方法
本文实例讲述了JavaScript基于activexobject连接远程数据库SQL Server 2014的方法.分享给大家供大家参考,具体如下: 最近翻开一本10年前买的JavaScript的书,这本书买回来只看了前半部分,后面的一直没看,也一直没有从事这方的工作. 看到有一节写着js连access接数据库,于是照着书上的代码,写了一段在chrome浏览器中运行,发现完全没反应. 查了一下才发现,这个activexobject只有微软的IE支持,其他的浏览器都不支持,我想可能是由于连接数据库
-
PHP操作mysql数据库分表的方法
一般来说,当我们的数据库的数据超过了100w记录的时候就应该考虑分表或者分区了,这次我来详细说说分表的一些方法.首先,我们需要想好到底分多少个 表,前提当然是满足应用.这里我使用了一个比较简单的分表方法,就是根据自增id的尾数来分,也就是说分0-9一共10个表,其取值也很好做,就是对10 进行取模.另外,还可以根据某一字段的md5值取其中几位进行分表,这样的话,可以分的表就很多了. 好了,先来创建表吧,代码如下: CREATE TABLE `ttlsa_com`.`article_0` ( `i
-
mysql数据库单表最大存储依据详解
目录 引言 数据库单表行数最大多大? 索引的结构 页的结构 从页到索引 B+树承载的记录数量 x怎么算 y的计算 行总数计算 行数超一亿就慢了吗? B树承载的记录数量 总结 引言 故事从好多年前说起. 想必大家也听说过数据库单表建议最大2kw条数据这个说法.如果超过了,性能就会下降得比较厉害. 巧了. 我也听说过. 但我不接受它的建议,硬是单表装了1亿条数据. 这时候,我们组里新来的实习生看到了之后,天真无邪的问我:"单表不是建议最大两千万吗?为什么这个表都放了1个亿还不分库分表"?
-
SQLServer2008新实例远程数据库链接问题(sp_addlinkedserver)
先说说问题起因: 测试同学在自己电脑上测试不同的后台,但数据库使用的是相同的名称. 于是创建了新的SQL Server实例,而新实例需要指定不同端口才能启动,改端口后执行sp_addlinkedserver语句后,连接测试不成功. 执行的SQL语句如下: EXEC master.dbo.sp_addlinkedserver @server = N'UserDBLink', @srvproduct=N'UserDBLink', @provider=N'SQLNCLI', @datasrc=N'(L
-
SQL server 2005将远程数据库导入到本地的方法
远程数据库与本地数据库数据之间的导入导出,从来都是一个值得人关注的问题. 下面,就给你详细介绍SQL2005将远程数据库导入到本地的方法. 首先,将SQL2005打开.方法是:"开始"→"程序"→"Microsoft SQL Server 2005"→"SQL Server Management Studio",这样,就可以把本地的SQL2005打开了. 打开了本地的SQL2005之后,现在,如何把远程数据库中的数据库,导入到
-
sql server判断数据库、表、列、视图是否存在
1 判断数据库是否存在 if exists (select * from sys.databases where name = '数据库名') drop database [数据库名] 2 判断表是否存在 if exists (select * from sysobjects where id = object_id(N'[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [表名] 3 判断存储过程是否存在 if exist