1、复合索引的使用
在T(x,y)上有一个索引,如果查询中不涉及列x(如where y=5),则不使用索引,但索引跳跃式扫描除外。
JEL@JEL >create table t as select decode(mod(rownum,2),0,'M','F') gender,all_objects.* from all_objects;
Table created.
JEL@JEL >create index i_t on t(gender,object_id);
Index created.
JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T',cascade=>true);
PL/SQL procedure successfully completed.
JEL@JEL >set autotrace traceonly explain
JEL@JEL >select * from t where object_id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 3072826391
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 86 | 4 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 86 | 4 (0)| 00:00:
01 |
|* 2 | INDEX SKIP SCAN | I_T | 1 | | 3 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=42)
filter("OBJECT_ID"=42)
如上使用了索引,是因为gender列只有很少的几个不同值,而且优化器了解这一点。
JEL@JEL >update t set gender=chr(mod(rownum,256));
9381 rows updated.
JEL@JEL >select * from t where object_id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 3072826391
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 86 | 4 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 86 | 4 (0)| 00:00:
01 |
|* 2 | INDEX SKIP SCAN | I_T | 1 | | 3 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=42)
filter("OBJECT_ID"=42)
JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T',cascade=>true);
PL/SQL procedure successfully completed.
JEL@JEL >select * from t where object_id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 36 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 86 | 36 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=42)
注意,在执行统计信息后,查询执行的全表扫描。
当第一列的取值比较多,查询中没有用到第一列,且优化器知道信息的情况下,执行查询会进行全表扫描
2、索引字段含有null值
JEL@JEL >create table test (x int ,y int);
Table created.
JEL@JEL >create index i_test on test (x);
Index created.
JEL@JEL >insert into test values (1,1);
1 row created.
JEL@JEL >insert into test values (null,1);
1 row created.
JEL@JEL >insert into test values (null,2);
1 row created.
JEL@JEL >insert into test values (null,1);
1 row created.
JEL@JEL >insert into test values (null,2);
1 row created.
JEL@JEL >commit;
Commit complete.
JEL@JEL >select * from test;
X Y
---------- ----------
1 1
1
2
1
2
JEL@JEL >select count(*) from test;
COUNT(*)
----------
5
JEL@JEL >select * from test where x=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 17 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 26 | 17 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=1)
Note
-----
- dynamic sampling used for this statement
JEL@JEL >select /*+index(test i_test) */* from test where x=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3945961961
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 26 | 161 (0)| 00:0
0:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 26 | 161 (0)| 00:0
0:02 |
|* 2 | INDEX RANGE SCAN | I_TEST | 1 | | 1 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=1)
Note
-----
- dynamic sampling used for this statement
索引字段含有null值,查询不走索引。对于索引键完全为null的行不会建立相应的索引条目,所以索引中的行数并不是表中的行数。
3、因为有函数,所以没用索引
JEL@JEL >create table t (x char(1),y char(1));
Table created.
JEL@JEL >create index i_t on t(x);
Index created.
JEL@JEL >insert into t values ('a','a');
1 row created.
JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T',cascade=>true);
PL/SQL procedure successfully completed.
JEL@JEL >select x,y from t where x=lower('A');
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 4 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"='a')
JEL@JEL >select x,y from t where upper(x)='A';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 17 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 4 | 17 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("X")='A')
4、隐式转换
继续上面的例子
JEL@JEL >insert into t values (1,1);
1 row created.
JEL@JEL >delete from t where x='a';
1 row deleted.
JEL@JEL >select * from t where x=1;
X Y
- -
1 1
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 17 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 4 | 17 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
456 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
如上,没有使用索引,且进行了隐式转换( 1 - filter(TO_NUMBER("X")=1))
JEL@JEL >select * from t where x='1';
X Y
- -
1 1
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 4 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"='1')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
460 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
如上,使用了索引。
结论,SQL中尽量在等号(=)右边进行转换
5、使用索引反而更慢
JEL@JEL >create table t1 (x,y,primary key(x)) as select rownum x,object_name from all_objects;
Table created.
JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T1',cascade=>true);
PL/SQL procedure successfully completed.
JEL@JEL >select x,y from t1 where x<50;
Execution Plan
----------------------------------------------------------
Plan hash value: 547136816
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 49 | 1029 | 3 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 49 | 1029 | 3 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_C002878 | 49 | | 2 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"<50)
JEL@JEL >select x,y from t1 where x<5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 102K| 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 5000 | 102K| 13 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"<5000)
通过索引获取的行数超过一定得阈值,则不使用索引而进行全表扫描
在T(x,y)上有一个索引,如果查询中不涉及列x(如where y=5),则不使用索引,但索引跳跃式扫描除外。
JEL@JEL >create table t as select decode(mod(rownum,2),0,'M','F') gender,all_objects.* from all_objects;
Table created.
JEL@JEL >create index i_t on t(gender,object_id);
Index created.
JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T',cascade=>true);
PL/SQL procedure successfully completed.
JEL@JEL >set autotrace traceonly explain
JEL@JEL >select * from t where object_id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 3072826391
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 86 | 4 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 86 | 4 (0)| 00:00:
01 |
|* 2 | INDEX SKIP SCAN | I_T | 1 | | 3 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=42)
filter("OBJECT_ID"=42)
如上使用了索引,是因为gender列只有很少的几个不同值,而且优化器了解这一点。
JEL@JEL >update t set gender=chr(mod(rownum,256));
9381 rows updated.
JEL@JEL >select * from t where object_id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 3072826391
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 86 | 4 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 86 | 4 (0)| 00:00:
01 |
|* 2 | INDEX SKIP SCAN | I_T | 1 | | 3 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=42)
filter("OBJECT_ID"=42)
JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T',cascade=>true);
PL/SQL procedure successfully completed.
JEL@JEL >select * from t where object_id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 36 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 86 | 36 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=42)
注意,在执行统计信息后,查询执行的全表扫描。
当第一列的取值比较多,查询中没有用到第一列,且优化器知道信息的情况下,执行查询会进行全表扫描
2、索引字段含有null值
JEL@JEL >create table test (x int ,y int);
Table created.
JEL@JEL >create index i_test on test (x);
Index created.
JEL@JEL >insert into test values (1,1);
1 row created.
JEL@JEL >insert into test values (null,1);
1 row created.
JEL@JEL >insert into test values (null,2);
1 row created.
JEL@JEL >insert into test values (null,1);
1 row created.
JEL@JEL >insert into test values (null,2);
1 row created.
JEL@JEL >commit;
Commit complete.
JEL@JEL >select * from test;
X Y
---------- ----------
1 1
1
2
1
2
JEL@JEL >select count(*) from test;
COUNT(*)
----------
5
JEL@JEL >select * from test where x=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 17 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 26 | 17 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=1)
Note
-----
- dynamic sampling used for this statement
JEL@JEL >select /*+index(test i_test) */* from test where x=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3945961961
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 26 | 161 (0)| 00:0
0:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 26 | 161 (0)| 00:0
0:02 |
|* 2 | INDEX RANGE SCAN | I_TEST | 1 | | 1 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=1)
Note
-----
- dynamic sampling used for this statement
索引字段含有null值,查询不走索引。对于索引键完全为null的行不会建立相应的索引条目,所以索引中的行数并不是表中的行数。
3、因为有函数,所以没用索引
JEL@JEL >create table t (x char(1),y char(1));
Table created.
JEL@JEL >create index i_t on t(x);
Index created.
JEL@JEL >insert into t values ('a','a');
1 row created.
JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T',cascade=>true);
PL/SQL procedure successfully completed.
JEL@JEL >select x,y from t where x=lower('A');
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 4 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"='a')
JEL@JEL >select x,y from t where upper(x)='A';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 17 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 4 | 17 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("X")='A')
4、隐式转换
继续上面的例子
JEL@JEL >insert into t values (1,1);
1 row created.
JEL@JEL >delete from t where x='a';
1 row deleted.
JEL@JEL >select * from t where x=1;
X Y
- -
1 1
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 17 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 4 | 17 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
456 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
如上,没有使用索引,且进行了隐式转换( 1 - filter(TO_NUMBER("X")=1))
JEL@JEL >select * from t where x='1';
X Y
- -
1 1
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 4 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"='1')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
460 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
如上,使用了索引。
结论,SQL中尽量在等号(=)右边进行转换
5、使用索引反而更慢
JEL@JEL >create table t1 (x,y,primary key(x)) as select rownum x,object_name from all_objects;
Table created.
JEL@JEL >exec dbms_stats.gather_table_stats('JEL','T1',cascade=>true);
PL/SQL procedure successfully completed.
JEL@JEL >select x,y from t1 where x<50;
Execution Plan
----------------------------------------------------------
Plan hash value: 547136816
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 49 | 1029 | 3 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 49 | 1029 | 3 (0)|
00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_C002878 | 49 | | 2 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"<50)
JEL@JEL >select x,y from t1 where x<5000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 102K| 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 5000 | 102K| 13 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"<5000)
通过索引获取的行数超过一定得阈值,则不使用索引而进行全表扫描
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29337971/viewspace-1063629/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29337971/viewspace-1063629/