Use index_desc
常見的例子,表中記錄按照creation_date作purge,且該欄位上有個索引。
如果不加index_desc hint,purge job執行時間長了可能會越來越慢。如下consistent read明顯要比current read多很多。是因為Index range scan從index tree的最左面開始掃描,掃描了很多空塊。
SQL> DELETE FROM vltb_data_0 where creation_date < (SYSDATE - 3) and rownum < 1000;
999 rows deleted.———————————————————-
| Id | Operation | Name |
———————————————————-
| 0 | DELETE STATEMENT | |
| 1 | DELETE | RULE_OBJECT_ATTR_DATA_0 |
|* 2 | COUNT STOPKEY | |
|* 3 | INDEX RANGE SCAN| RULE_OBJECT_ATTR_DATA_0_IX3 |
———————————————————-
Predicate Information (identified by operation id):
—————————————————
2 - filter(ROWNUM<1000)
3 - access(”CREATION_DATE”Statistics
———————————————————-
1 recursive calls
21167 db block gets
427848 consistent gets
0 physical reads
1341824 redo size
1 sorts (memory)
0 sorts (disk)
999 rows processed
通過新增index_desc, Index range descending scan從index tree的中間或者右邊進入掃描,更快定位到存在紀錄的block.
SQL> DELETE /*+ index_desc(rule_object_attr_data_0) */ FROM vltb_data_0 where creation_date < (SYSDATE - 3) and rownum < 1000;
999 rows deleted.Statistics
———————————————————-
1 recursive calls
21177 db block gets
59 consistent gets
288 physical reads
1368592 redo size
814 bytes sent via SQL*Net to client
833 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
999 rows processed
從而避免了無用功。這樣的例子時常在工作中遇到。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/20577218/viewspace-700738/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- sql優化一例(index_desc)SQL優化Index
- 索引反向使用案例,加index_desc hint索引Index
- 【轉載】Kano Model — Ways to use it and NOT use it
- the "in" use in mysqlMySql
- oracle hint_use_concat_use_nl_with_indexOracleIndex
- plsql use skillsSQL
- GPFS use with OracleOracle
- how to use typeset?
- USE EXPLAIN PLANAI
- index 和 index_desc hints的一點有意思的區別Index
- Use PHP7PHP
- IPFS_basic_use
- how to use coffee script
- why use dynamic SQL?SQL
- In Oracle,How to use dumpOracle
- Oracle Hint:USE_NL、USE_MERGE、UESE_HASH(原理)Oracle
- the NTP socket is in use, exiting
- 嚴格模式use strict模式
- use "jsdelivr" to host fileJSVR
- use_nl,use_hash,use_merge的3種連線方式驅動與被驅動關係
- oracle hint之full,index,index_asc,index_desc,index_combile示例OracleIndex
- Vue.use 原始碼分析Vue原始碼
- Vue.use(plugin)詳解VuePlugin
- vue.use()做了什麼Vue
- 翻譯|How to Use the useReducer HookuseReducerHook
- use_sim_time-ROS系列ROS
- Podfile中的 use_frameworks!Framework
- AppBuilder(一)【Use彙總】APPUI
- [Libcurl]Build&Use ManualUI
- Use Excel Pivot Table as a BI toolExcel
- REST is not enabled. use -rest to turn onREST
- Use windows batch script to create menuWindowsBAT
- how to use ghd hair straightenerAI
- Use the statspack to generate the accurate explain planAI
- use GIT with eclipse plugin egitGitEclipsePlugin
- How to use hints in Oracle sql for performanceOracleSQLORM
- oracle hints ? use_hash ? ordered ?Oracle
- use vs require in Perl5UI