高效的SQL（Function-based Indexes 函式、運算優化思路）

高效的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.

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-752260/，如需轉載，請註明出處，否則將追究法律責任。