高效的 SQL （ Function-based Indexes 函数、运算优化思路）

① Function-based Indexes situations

1、   UPPER function   http://www.oracle-base.com/articles/8i/function-based-indexes.php

2、   col1+col2

② experiment following

1 、【 create table t and function-based indexes idx-t (col1 and col2) 】 AND 【 create table t1 and indexes idx_t1_col1(col1) 、 idx_t2_col2(col2) 】

doudou@TEST> desc t

 Name                                                   Null?     Type

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

 COL1                                                            NUMBER

 COL2                                                            NUMBER

doudou@TEST> select count(*) from t;

  COUNT(*)

----------

    100000

doudou@TEST> create index idx_t on t (col1+col2);

Index created.

doudou@TEST> select index_type from user_indexes where table_name='T';

INDEX_TYPE

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

FUNCTION-BASED NORMAL

doudou@TEST> create table t1 as select * from t;

Table created.

doudou@TEST> create index idx_t1_col1 on t1 (col1);

Index created.

doudou@TEST> create index idx_t1_col2 on t1(col2);

Index created.

doudou@TEST>   select index_type from user_indexes where table_name='T1';

INDEX_TYPE

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

NORMAL

NORMAL

2 、 funciton-based indexes and indexes differents

doudou@TEST> select col1+col2 from t where col1+col2<2000;

997 rows selected.

Execution Plan

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

Plan hash value: 2296882198

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

| Id   | Operation         | Name   | Rows   | Bytes | Cost (%CPU)| Time      |

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

|    0 | SELECT STATEMENT |        |      3 |     78 |      4    (0)| 00:00:01 |

|*   1 |   INDEX RANGE SCAN| IDX_T |      3 |     78 |      4    (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

    1 - access("COL1"+"COL2"<2000)

Note

-----

   - dynamic sampling used for this statement

Statistics

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

          4   recursive calls

          0   db block gets

        139   consistent gets

          0   physical reads

          0   redo size

      14017   bytes sent via SQL*Net to client

       1126   bytes received via SQL*Net from client

         68   SQL*Net roundtrips to/from client

          0   sorts (memory)

          0   sorts (disk)

        997   rows processed

【 function-based indexes 中 where 谓语中使用了 函数 也会使用索引 】

doudou@TEST> select col1+col2 from   t1 where col1+col2 <2000;

997 rows selected.

Execution Plan

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

Plan hash value: 3617692013

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

| Id   | Operation          | Name | Rows   | Bytes | Cost (%CPU)| Time      |

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

|    0 | SELECT STATEMENT   |       |     38 |    988 |     49    (0)| 00:00:01 |

|*   1 |  TABLE ACCESS FULL| T1    |     38 |    988 |     49    (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

    1 - filter("COL1"+"COL2"<2000)

Note

-----

   - dynamic sampling used for this statement

Statistics

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

         28   recursive calls

          0   db block gets

        326   consistent gets

          0   physical reads

          0   redo size

      14017   bytes sent via SQL*Net to client

       1126   bytes received via SQL*Net from client

         68   SQL*Net roundtrips to/from client

          0   sorts (memory)

          0   sorts (disk)

        997   rows processed

【 normal indexes 中 where 谓语中使用了 函数不走索引】

③ 总结：

基于函数的索引：

1、   可以有针对函数运算、函数进行优化。

2、   了解不同索引为优化提供多方位的方式。

附表

Using Function-based Indexes for Performance

http://docs.oracle.com/cd/E11882_01/server.112/e16638/data_acc.htm#PFGRF94785

A function-based index includes columns that are either transformed by a function, such as the UPPER function, or included in an expression, such as col1 + col2. With a function-based index, you can store computation-intensive expressions in the index.

Function-Based Indexes

http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm#autoId15

You can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index computes the value of a function or expression involving one or more columns and stores it in the index. A function-based index can be either a B-tree or a bitmap index.