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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

----------------------------------------------------------

1recursive calls

0 dbblock gets

1533 consistent gets

0physical reads

0 redosize

1851 bytes sent via sql*Net toclient

551 bytes received via sql*Net from client

2sql*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1 rowsprocessed

走的全表扫描，没有走索引。去掉UPPER函数执行如下

sql> select * from t where object_name='T';

Execution Plan

----------------------------------------------------------

Plan hash value: 603483963

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------

1recursive calls

0 dbblock gets

5consistent gets

2physical reads

0 redosize

1855 bytes sent via sql*Net toclient

551 bytes received via sql*Net from client

2sql*Net roundtrips to/from client

0sorts (memory)

0sorts (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

----------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------

5recursive calls

0 dbblock gets

7consistent gets

2physical reads

0 redosize

1851 bytes sent via sql*Net toclient

551 bytes received via sql*Net from client

2sql*Net roundtrips to/from client

0sorts (memory)

0sorts (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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

----------------------------------------------------------

1recursive calls

0 dbblock gets

1535 consistent gets

0physical reads

0 redosize

3725 bytes sent via sql*Net toclient

573 bytes received via sql*Net from client

4sql*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

39 rowsprocessed

2.2实验2

sql> select * from t where object_id<=40;

39 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1296629646

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------

1recursive calls

0 dbblock gets

9consistent gets

1physical reads

0 redosize

5890 bytes sent via sql*Net toclient

573 bytes received via sql*Net from client

4sql*Net roundtrips to/from client

0sorts (memory)

0sorts (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

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------

2recursive calls

0 dbblock gets

11consistent gets

1physical reads

0 redosize

3513 bytes sent via sql*Net toclient

573 bytes received via sql*Net from client

4sql*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

39 rowsprocessed

3. 避免列运算2

sql> select * from t where substr(object_name,1,4)='CLUS';

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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",4)='CLUS')

Note

-----

-dynamic statistics used: dynamic sampling (level=2)

-1 sql Plan Directive used for this statement

Statistics

----------------------------------------------------------

18recursive calls

0 dbblock gets

2308 consistent gets

0physical reads

0 redosize

2049 bytes sent via sql*Net toclient

551 bytes received via sql*Net from client

2sql*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

3 rowsprocessed

除非建立一个SUBSTR相关函数的索引，否则用不上索引。

不过还可以使用如下命令进行避免

sql> select * from t where object_name like 'CLUS%';

Execution Plan

----------------------------------------------------------

Plan hash value: 603483963

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 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

8physical reads

0 redosize

2136 bytes sent via sql*Net toclient

551 bytes received via sql*Net from client

2sql*Net roundtrips to/from client

67sorts (memory)

0sorts (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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

----------------------------------------------------------

11recursive 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

1sql*Net roundtrips to/from client

0sorts (memory)

0sorts (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

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 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

----------------------------------------------------------

11recursive calls

0 dbblock gets

6consistent gets

2physical reads

0 redosize

1572 bytes sent via sql*Net toclient

540 bytes received via sql*Net from client

1sql*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

0 rowsprocessed

执行就走索引了。

28.读书笔记收获不止Oracle之 函数索引

1. 函数索引

2. 避免列运算1

2.1实验1

2.2实验2

2.3实验3

3. 避免列运算2

4. 避免列运算3

猜你在找的Oracle相关文章

28.读书笔记收获不止Oracle之函数索引