[20191209]降序索引疑問.txt
[20191209]降序索引疑問.txt
--//今天最佳化一個專案,我發現許多表裡面有有隱含欄位,一般開發很少建立函式索引.我自己檢查發現裡面存在大量的降序索引.
--//我感覺有點奇怪,為什麼開發要建立大量降序索引有什麼好處呢?
--//關於降序索引我以前寫的:
http://blog.itpub.net/267265/viewspace-2221425/=>[20181123]關於降序索引問題.txt
http://blog.itpub.net/267265/viewspace-2221527/=>[20181124]關於降序索引問題2.txt
http://blog.itpub.net/267265/viewspace-2221529/=>[20181124]關於降序索引問題3.txt
http://blog.itpub.net/267265/viewspace-2221532/=>[20181124]關於降序索引問題4.txt
http://blog.itpub.net/267265/viewspace-1159181/=>[20140512]關於降序索引.txt
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------- -------------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> alter system set pga_aggregate_target=4G;
System altered.
SCOTT@book> create table t (id1 number,id2 number,vc varchar2(100));
Table created.
SCOTT@book> create index i_t_id1 on t(id1);
Index created.
SCOTT@book> create index i_t_id2 on t(id2 desc);
Index created.
SCOTT@book> insert into t select rownum,rownum,lpad('a',100,'a') from dual connect by level<=1e6;
1000000 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select index_name,index_type,blevel,leaf_blocks,distinct_keys from dba_indexes where owner=user and index_name like 'I_T_ID%';
INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- --------------------- ------ ----------- -------------
I_T_ID2 FUNCTION-BASED NORMAL 2 4283 1000000
I_T_ID1 NORMAL 2 1999 1000000
--//明顯可以發現降序索引I_T_ID2佔用塊許多.這個是因為我查詢的資料是有序的(與他們的情況類似),降序索引的分裂50-50%,而正常索引是90-10分裂.
--//所以在匯入資料時,降序索引佔用塊數多.而如果插入資料是增序的情況索引佔索引塊數少.
--//注意兩者的塊爭用都是一樣,只不過一個在索引樹的左邊(降序),一個在索引樹的右邊.
2.我仔細掃描執行語句,有點類似如下:
--//select * from (select * from t where id1<=1e6 order by id1 desc) where rownum<=100;
SCOTT@book> alter session set statistics_level = all;
Session altered.
SCOTT@book> select * from (select * from t where id1<=1e6 order by id1 desc) where rownum<=100;
...
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8ns53ghu2vkcz, child number 1
-------------------------------------
select * from (select * from t where id1<=1e6 order by id1 desc) where
rownum<=100
Plan hash value: 768900824
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 100 |00:00:00.01 | 8 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 100 |00:00:00.01 | 8 |
| 2 | VIEW | | 1 | 100 | 7800 | 5 (0)| 00:00:01 | 100 |00:00:00.01 | 8 |
| 3 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1000K| 105M| 5 (0)| 00:00:01 | 100 |00:00:00.01 | 8 |
|* 4 | INDEX RANGE SCAN DESCENDING| I_T_ID1 | 1 | 100 | | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 4 |
------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2 / T@SEL$2
4 - SEL$2 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
4 - access("ID1"<=1000000)
SCOTT@book> select * from (select * from t where id2<=1e6 order by id2 desc) where rownum<=100;
...
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0xc0uzzqsk6bp, child number 0
-------------------------------------
select * from (select * from t where id2<=1e6 order by id2 desc) where
rownum<=100
Plan hash value: 3930323544
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 100 |00:00:00.01 | 8 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 100 |00:00:00.01 | 8 |
| 2 | VIEW | | 1 | 100 | 7800 | 5 (0)| 00:00:01 | 100 |00:00:00.01 | 8 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1000K| 105M| 5 (0)| 00:00:01 | 100 |00:00:00.01 | 8 |
|* 4 | INDEX RANGE SCAN | I_T_ID2 | 1 | 100 | | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2 / T@SEL$2
4 - SEL$2 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=100)
4 - access("T"."SYS_NC00004$">=HEXTORAW('3BFDFF') )
filter(SYS_OP_UNDESCEND("T"."SYS_NC00004$")<=1000000)
--//可以發現兩者都能很好地使用對應索引.降序索引毫無優勢可言.取消where條件:
SCOTT@book> select * from (select * from t order by id1 desc) where rownum<=1;
ID1 ID2 VC
---------- ---------- ----------------------------------------------------------------------------------------------------
1000000 1000000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dyd579rap5r4q, child number 0
-------------------------------------
select * from (select * from t order by id1 desc) where rownum<=1
Plan hash value: 3299198703
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 29544 (100)| | 1 |00:00:00.42 | 16227 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 1 |00:00:00.42 | 16227 | | | |
| 2 | VIEW | | 1 | 1000K| 74M| | 29544 (1)| 00:05:55 | 1 |00:00:00.42 | 16227 | | | |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 1000K| 105M| 120M| 29544 (1)| 00:05:55 | 1 |00:00:00.42 | 16227 | 119M| 3708K| |
| 4 | TABLE ACCESS FULL | T | 1 | 1000K| 105M| | 4402 (1)| 00:00:53 | 1000K|00:00:00.12 | 16227 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT@book> select * from (select * from t order by id2 desc) where rownum<=1;
ID1 ID2 VC
---------- ---------- ----------------------------------------------------------------------------------------------------
1000000 1000000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 85pmrga0pr2jd, child number 0
-------------------------------------
select * from (select * from t order by id2 desc) where rownum<=1
Plan hash value: 3299198703
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 29544 (100)| | 1 |00:00:00.39 | 16227 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 1 |00:00:00.39 | 16227 | | | |
| 2 | VIEW | | 1 | 1000K| 74M| | 29544 (1)| 00:05:55 | 1 |00:00:00.39 | 16227 | | | |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 1000K| 105M| 120M| 29544 (1)| 00:05:55 | 1 |00:00:00.39 | 16227 | 119M| 3708K| |
| 4 | TABLE ACCESS FULL | T | 1 | 1000K| 105M| | 4402 (1)| 00:00:53 | 1000K|00:00:00.11 | 16227 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
--//兩者都走全表掃描.因為NULL的問題,select * from (select * from t order by id1 desc) where rownum<=1一定不會i_t_id1索引.
--//我以前做過類似測試,按照道理降序索引是包含NULL值的,可以降序索引一樣沒用.你可以做如下測試:
--//參考:http://blog.itpub.net/267265/viewspace-1159181/=>[20140512]關於降序索引.txt
SCOTT@book> select /*+ index(t i_t_id2) */ id2 from t where id2 is null ;
no rows selected
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5k10f4kkvknmr, child number 0
-------------------------------------
select /*+ index(t i_t_id2) */ id2 from t where id2 is null
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4402 (100)| | 0 |00:00:00.07 | 16227 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 5 | 4402 (1)| 00:00:53 | 0 |00:00:00.07 | 16227 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID2" IS NULL)
--//還是走全表掃描.實際上NULL在索引I_T_ID2裡面的.如果寫成如下:
SCOTT@book> select /*+ index(t i_t_id2) */ * from t where sys_op_descend(id2)=hextoraw('00') ;
no rows selected
--//這樣寫就是查詢id2 is NULL.
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8xc58b4jcqk3f, child number 0
-------------------------------------
select /*+ index(t i_t_id2) */ * from t where
sys_op_descend(id2)=hextoraw('00')
Plan hash value: 3974417878
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 0 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 117 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | I_T_ID2 | 1 | 1 | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"=HEXTORAW('00') )
--//可以這樣寫法是可以使用I_T_ID2索引的.我估計問題可能出在id2 is null這樣的寫法上.
3.修改為not NULL,繼續測試:
SCOTT@book> alter table t modify (id1 not null);
Table altered.
SCOTT@book> alter table t modify (id2 not null);
Table altered.
SCOTT@book> select * from (select * from t order by id1 desc) where rownum<=1;
ID1 ID2 VC
---------- ---------- ----------------------------------------------------------------------------------------------------
1000000 1000000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dyd579rap5r4q, child number 0
-------------------------------------
select * from (select * from t order by id1 desc) where rownum<=1
Plan hash value: 137725480
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 1 |00:00:00.01 | 4 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 4 |
| 2 | VIEW | | 1 | 1 | 78 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1000K| 105M| 4 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
| 4 | INDEX FULL SCAN DESCENDING| I_T_ID1 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2 / T@SEL$2
4 - SEL$2 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
SCOTT@book> select * from (select * from t order by id2 desc) where rownum<=1;
ID1 ID2 VC
---------- ---------- ----------------------------------------------------------------------------------------------------
1000000 1000000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 85pmrga0pr2jd, child number 0
-------------------------------------
select * from (select * from t order by id2 desc) where rownum<=1
Plan hash value: 177228429
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 1 |00:00:00.01 | 4 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 4 |
| 2 | VIEW | | 1 | 1 | 78 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1000K| 105M| 4 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
| 4 | INDEX FULL SCAN | I_T_ID2 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2 / T@SEL$2
4 - SEL$2 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
--//一樣都是可以使用對應索引.
--//很明顯開發生搬硬套,使用降序索引毫無優勢可言.
--//應該有很好地使用降序索引最佳化的例子,一時半會我自己沒有找到,那位給一些連結,我再看看....
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2667573/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20191209]降序索引疑問2.txt索引
- [20200303]降序索引疑問5.txt索引
- [20191210]降序索引疑問3.txt索引
- [20191218]降序索引疑問4.txt索引
- [20181123]關於降序索引問題.txt索引
- [20190910]關於降序索引問題5.txt索引
- [20181124]關於降序索引問題4.txt索引
- [20181124]關於降序索引問題3.txt索引
- [20181124]關於降序索引問題2.txt索引
- [20231116]降序索引取最大值.txt索引
- [20191219]降序索引與取最大值.txt索引
- 不能建立降序索引的問題的解決索引
- [20190411]linux stat 命令疑問.txtLinux
- [20190401]那個更快的疑問.txt
- [20191213]不完全恢復疑問.txt
- [20180509]函式索引問題.txt函式索引
- [20210926]並行執行計劃疑問.txt並行
- [20220308]查詢x$ksmmem遇到的疑問.txt
- [20190510]rman備份的疑問8.txt
- [20190510]rman備份的疑問7.txt
- [20190509]rman備份的疑問5.txt
- [20190423]那個更快的疑問3.txt
- [20221014]資料檔案2的小疑問.txt
- [20220304]測試library cache mutex遇到的疑問.txtMutex
- [20221010]使用toad管理索引改名問題.txt索引
- [20210520]關於主鍵索引問題.txt索引
- [20190918]關於函式索引問題.txt函式索引
- [20230308]versions偽列versions_starttime疑問2.txt
- [20220309]查詢x$ksmmem遇到的疑問補充.txt
- [20201207]關於ORACLE IMU的一些疑問.txtOracle
- [20220406]使用那個shared pool latch的疑問1.txt
- [20190312]關於增量檢查點的疑問(補充).txt
- [20180713]關於hash join 測試中一個疑問.txt
- [20220412]shared pool latch與使用sga heap的疑問2.txt
- [20220413]shared pool latch與使用sga heap的疑問3.txt
- 有些疑問
- [20190321]測試相同語句遇到導致cursor pin S的疑問.txt
- 我的疑問