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

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

                  执行就走索引了。