28.读书笔记收获不止Oracle之 函数索引
先来看个例子：
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> create index idx_object_id on t(object_id);
Index created.
SQL> create index idx_object_name on t(object_name);
Index created.
SQL> create index idx_createed on t(created);
Index created.
SQL> select count(*) from t;
COUNT(*)
----------
90945
SQL> set autotrace traceonly
SQL> set linesize 1000
SQL> select * from t whereupper(object_name)='T';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 909 | 102K| 426 (1)| 00:00:01 |
|* 1| TABLE ACCESS FULL| T | 909 | 102K| 426 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter(UPPER("OBJECT_NAME")='T')
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
1533 consistent gets
0 physical reads
0 redosize
1851 bytes sent via SQL*Net toclient
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rowsprocessed
走的全表扫描，没有走索引。去掉UPPER函数执行如下
SQL> select * from t where object_name='T';
Execution Plan
----------------------------------------------------------
Plan hash value: 603483963
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 2 | 230 | 4 (0)|00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED| T | 2 | 230 | 4 (0)|00:00:01 |
|* 2| INDEX RANGESCAN | IDX_OBJECT_NAME | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- access("OBJECT_NAME"='T')
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
5 consistent gets
2 physical reads
0 redosize
1855 bytes sent via SQL*Net toclient
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rowsprocessed
发现，因为UPPER函数，导致无法使用索引，这个是因为对所有列做运算导致索引无法使用。
如果OBJECT_NAME列不存在小写字母，则SELECT *FROM T WHERE OBJECT_NAME=’T’ 和SELECT *FROM T WHEREUPPER(OBJECT_NAME)=’T’ 是完全等价的。如果还写UPPER就是多此一举又影响性能。
1. 函数索引
如果OBJECT_NAME列的取值真的有大有小，需要UPPER函数来执行，就需要函数索引了。
函数索引的方法很简单，和普通索引的方法类似，区别在于用函数运算替代列名。具体看如下例子：
SQL> create index idx_upper_obj_name on t (upper(object_name));
Index created.
SQL> select * from t where upper(object_name)='T';
Execution Plan
----------------------------------------------------------
Plan hash value: 2908766729
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 909 | 102K| 193 (0)| 00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED| T | 909| 102K| 193 (0)| 00:00:01 |
|* 2| INDEX RANGE SCAN | IDX_UPPER_OBJ_NAME | 364 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- access(UPPER("OBJECT_NAME")='T')
Statistics
----------------------------------------------------------
5 recursive calls
0 dbblock gets
7 consistent gets
2 physical reads
0 redosize
1851 bytes sent via SQL*Net toclient
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rowsprocessed
使用函数索引了，代价是193，B树索引代价是4，全表扫描代价是426.
来查看索引类型如下：
SQL> selectindex_name,index_type from user_indexes where table_name='T';
INDEX_NAME INDEX_TYPE
--------------- ---------------------------
IDX_OBJECT_ID NORMAL
IDX_OBJECT_NAME NORMAL
IDX_CREATEED NORMAL
IDX_UPPER_OBJ_N FUNCTION-BASED NORMAL
AME
在大多数情况下，对列进行函数运算的SQL写法都是可以转换成对列不做运算的不同写法。
2. 避免列运算1
函数索引在很多情况下，是对列进行运算。函数索引性能介于普通索引和全表扫描之间，能用普通索引就尽量用普通索引。
2.1 实验1
SQL> set autotrace traceonly
SQL> set linesize 1000
SQL> select * from t where object_id-10<=30;
39 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 4547 | 510K| 426 (1)| 00:00:01 |
|* 1| TABLE ACCESS FULL| T | 4547 | 510K| 426 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter("OBJECT_ID"-10<=30)
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
1535 consistent gets
0 physical reads
0 redosize
3725 bytes sent via SQL*Net toclient
573 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39 rowsprocessed
2.2 实验2
SQL> select * from t where object_id<=40;
39 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1296629646
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 39 | 4485 | 4 (0)| 00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED| T | 39 | 4485 | 4 (0)| 00:00:01 |
|* 2| INDEX RANGE SCAN | IDX_OBJECT_ID | 39 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- access("OBJECT_ID"<=40)
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
9 consistent gets
1 physical reads
0 redosize
5890 bytes sent via SQL*Net toclient
573 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39 rowsprocessed
同样的结果，不同的写法导致性能差异。
建立索引试试
2.3 实验3
Create index idx_object_id_2 on t(object_id -10);
SQL> select * from t where object_id-10<=30;
39 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 865720425
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 4547 | 510K| 26 (0)|00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED| T | 4547 | 510K| 26 (0)|00:00:01 |
|* 2| INDEX RANGE SCAN | IDX_OBJECT_ID_2 | 819 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- access("OBJECT_ID"-10<=30)
Statistics
----------------------------------------------------------
2 recursive calls
0 dbblock gets
11 consistent gets
1 physical reads
0 redosize
3513 bytes sent via SQL*Net toclient
573 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39 rowsprocessed
3. 避免列运算2
SQL> select * from t where substr(object_name,1,4)='CLUS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 6 | 690 | 426 (1)| 00:00:01 |
|* 1| TABLE ACCESS FULL| T | 6 | 690 | 426 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter(SUBSTR("OBJECT_NAME",1,4)='CLUS')
Note
-----
-dynamic statistics used: dynamic sampling (level=2)
-1 Sql Plan Directive used for this statement
Statistics
----------------------------------------------------------
18 recursive calls
0 dbblock gets
2308 consistent gets
0 physical reads
0 redosize
2049 bytes sent via SQL*Net toclient
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rowsprocessed
除非建立一个SUBSTR相关函数的索引，否则用不上索引。
不过还可以使用如下命令进行避免
SQL> select * from t where object_name like 'CLUS%';
Execution Plan
----------------------------------------------------------
Plan hash value: 603483963
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 2 | 290 | 4 (0)|00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED| T | 2 | 290 | 4 (0)|00:00:01 |
|* 2| INDEX RANGE SCAN | IDX_OBJECT_NAME | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- access("OBJECT_NAME" LIKE 'CLUS%')
filter("OBJECT_NAME" LIKE 'CLUS%')
Statistics
----------------------------------------------------------
1831 recursive calls
0 dbblock gets
1676 consistent gets
8 physical reads
0 redosize
2136 bytes sent via SQL*Net toclient
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
67 sorts (memory)
0 sorts (disk)
3 rowsprocessed
4. 避免列运算3
看如下脚本
SQL> select * from t where trunc(created)>=TO_DATE('2012-10-02','YYYY-MM-DD') and trunc(created) <=TO_DATE('2012-10-03','YYYY-MM-DD');
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 227 | 32915 | 428 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 227 | 32915 | 428 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter(TRUNC(INTERNAL_FUNCTION("CREATED"))>=TO_DATE(' 2012-10-02
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
TRUNC(INTERNAL_FUNCTION("CREATED"))<=TO_DATE(' 2012-10-0300:00:00',
'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
11 recursive calls
0 dbblock gets
1536 consistent gets
117 physical reads
0 redosize
1572 bytes sent via SQL*Net toclient
540 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rowsprocessed
没有走索引，不过可以使用相同的办法实现相同的功能如下：
SQL> select * from t wherecreated>= TO_DATE('2012-10-02','YYYY-MM-DD') and created < TO_DATE('2012-10-03','YYYY-MM-DD')+1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3369967073
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 143 | 20735 | 5 (0)| 00:00:01 |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED| T | 143 | 20735 | 5 (0)| 00:00:01 |
|* 2| INDEX RANGE SCAN | IDX_CREATEED | 143 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
2- access("CREATED">=TO_DATE(' 2012-10-02 00:00:00', 'syyyy-mm-ddhh24:mi:ss') AND
"CREATED"<TO_DATE('2012-10-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
11 recursive calls
0 dbblock gets
6 consistent gets
2 physical reads
0 redosize
1572 bytes sent via SQL*Net toclient
540 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rowsprocessed
执行就走索引了。
扫一扫
3212
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
