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