高效的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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 慢Sql優化思路SQL優化
- NumPy 通用函式(ufunc):高效能陣列運算的利器函式陣列
- SQL優化案例-自定義函式索引(五)SQL優化函式索引
- NumPy常用的位運算函式函式
- 記錄一次SQL函式和優化的問題SQL函式優化
- SQL優化之把運算放在列的另一端SQL優化
- Excel 優化函式Excel優化函式
- React函式式元件的效能優化React函式元件優化
- 函式計算進行自動化運維專題函式運維
- 翻譯(九)——Clustered Indexes: Stairway to SQL Server Indexes Level 3IndexAISQLServer
- 目標函式存在累加的非線性優化問題的求解思路zzzzzzzzzzzzzzzzzzzz(1)函式優化
- python-算數運算子對應的函式Python函式
- Sql 中的 left 函式、right 函式SQL函式
- 函式呼叫的代價與優化函式優化
- Oracle 高效能SQL引擎剖析--SQL優化與調優機制詳解OracleSQL優化
- MySQL函式索引及優化MySql函式索引優化
- 【SQL】19 SQL函式SQL函式
- sql函式SQL函式
- SQL-函式 - 聚合函式SQL函式
- 奇思妙想的SQL|去重Cube計算最佳化新思路SQL
- SQL 調優一般思路SQL
- JavaScript 立即執行函式、逗號運算JavaScript函式
- 函式計算自動化運維實戰1 -- 定時任務函式運維
- SQL中的cast()函式SQLAST函式
- 如何使用函式來優化效能函式優化
- 深度學習運算元優化-FFT深度學習優化FFT
- Serverless工作流如何整合http的fc函式運算?ServerHTTP函式
- 算極化率的格林函式演算法函式演算法
- 「分散式技術專題」常用的 SQL 運算元介紹分散式SQL
- mysql注入方法邏輯運算及常用函式MySql函式
- [記錄] 通用封裝函式——四則運算封裝函式
- 【IDL】幾何圖形數學運算函式函式
- 運籌優化(十四)--離散優化的啟發式演算法優化演算法
- T-SQL——函式——字串操作函式SQL函式字串
- 高效能運算-粒子狀態模擬計算最佳化
- 理解並優化函式節流Throttle優化函式
- 效能優化之節流函式---throttle優化函式
- [JS效能優化]函式去抖(debounce)與函式節流(throttle)JS優化函式
- SQL Server最佳化標量函式改寫內聯表值函式SQLServer函式