[20191218]降序索引疑问4.txt
[20191218]降序索引疑问4.txt
--//前几天优化一个项目,我发现许多表里面有有隐含字段,一般开发很少建立函数索引.我自己检查发现里面存在大量的降序索引.
--//我感觉有点奇怪,为什么开发要建立大量降序索引有什么好处呢?
--//我在链接http://www.itpub.net/thread-2122088-1-1.html里面问这个问题,sqysl的解答给了我很好的提示,我通过例子说明使用降
--//序索引的一点点好处,通过例子说明:
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(50));
Table created.
create index i_t_id1_id2 on t (id1,id2);
create index i_t_id1desc_id2desc on t (id1 desc,id2 desc);
create index i_t_id1desc_id2 on t (id1 desc,id2 );
create index i_t_id1_id2desc on t (id1 ,id2 desc );
SCOTT@book> insert into t select rownum,rownum,lpad('a',50,'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 table_name='T';
INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS DISTINCT_KEYS
------------------------------ --------------------------- ---------- ----------- -------------
I_T_ID1_ID2 NORMAL 2 2623 1000000
I_T_ID1DESC_ID2DESC FUNCTION-BASED NORMAL 2 5877 1000000
I_T_ID1DESC_ID2 FUNCTION-BASED NORMAL 2 5618 1000000
I_T_ID1_ID2DESC FUNCTION-BASED NORMAL 2 2753 1000000
--//无论那种倒序索引,索引都比正序索引大,特别是第1个字段选择desc的情况.因为我插入的字段是自增字段.
--//倒序索引分裂都是50-50分裂.
2.测试例子:
SCOTT@book> alter session set statistics_level = all;
Session altered.
SCOTT@book> select * from (select * from t where id1<=1e6 order by id1 desc,id2 ) where rownum<=10;
ID1 ID2 VC
---------- ---------- --------------------------------------------------
1000000 1000000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
999999 999999 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
999998 999998 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
999997 999997 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
999996 999996 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
999995 999995 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
999994 999994 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
999993 999993 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
999992 999992 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
999991 999991 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
10 rows selected.
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 20hb1uyyqx4tf, child number 0
-------------------------------------
select * from (select * from t where id1<=1e6 order by id1 desc,id2 )
where rownum<=10
Plan hash value: 404407004
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 10 |00:00:00.01 | 6 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 10 |00:00:00.01 | 6 |
| 2 | VIEW | | 1 | 10 | 530 | 4 (0)| 00:00:01 | 10 |00:00:00.01 | 6 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1000K| 58M| 4 (0)| 00:00:01 | 10 |00:00:00.01 | 6 |
|* 4 | INDEX RANGE SCAN | I_T_ID1DESC_ID2 | 1 | 10 | | 3 (0)| 00:00:01 | 10 |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<=10)
4 - access("T"."SYS_NC00004$">=HEXTORAW('3BFDFF') AND "T"."SYS_NC00004$" IS NOT NULL)
filter(SYS_OP_UNDESCEND("T"."SYS_NC00004$")<=1000000)
32 rows selected.
--//逻辑读仅仅6个.而其它2个索引都无法达到这样的效果.
select * from (select /*+ index(t I_T_ID1DESC_ID2DESC) */ * from t where id1<=1e6 order by id1 desc,id2 ) where rownum<=10;
select * from (select /*+ index_desc(t I_T_ID1DESC_ID2DESC) */ * from t where id1<=1e6 order by id1 desc,id2 ) where rownum<=10;
select * from (select /*+ index(t I_T_ID1_ID2) */ * from t where id1<=1e6 order by id1 desc,id2 ) where rownum<=10;
select * from (select /*+ index_desc(t I_T_ID1_ID2) */ * from t where id1<=1e6 order by id1 desc,id2 ) where rownum<=10;
--//结果不在贴出.大家可以自行测试.
3.继续测试:
SCOTT@book> alter index I_T_ID1DESC_ID2 invisible;
Index altered.
SCOTT@book> select * from (select * from t where id1<=1e6 order by id1 desc,id2 ) where rownum<=1;
ID1 ID2 VC
---------- ---------- --------------------------------------------------
1000000 1000000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2r7j4a5gdhpnj, child number 0
-------------------------------------
select * from (select * from t where id1<=1e6 order by id1 desc,id2 )
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 | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 17500 (100)| | 1 |00:00:00.38 | 9285 | 74 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 1 |00:00:00.38 | 9285 | 74 | | | |
| 2 | VIEW | | 1 | 1000K| 50M| | 17500 (1)| 00:03:30 | 1 |00:00:00.38 | 9285 | 74 | | | |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 1000K| 58M| 69M| 17500 (1)| 00:03:30 | 1 |00:00:00.38 | 9285 | 74 | 65M| 2806K| |
|* 4 | TABLE ACCESS FULL | T | 1 | 1000K| 58M| | 2744 (1)| 00:00:33 | 1000K|00:00:00.12 | 9285 | 74 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2
4 - SEL$2 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
3 - filter(ROWNUM<=1)
4 - filter("ID1"<=1000000)
32 rows selected.
--//oracle选择全表扫描.
SCOTT@book> alter index I_T_ID1DESC_ID2 visible;
Index altered.
4.再继续测试:
--//测试第1个字段正序,第2个字段倒序的情况.
SCOTT@book> select * from (select * from t where id1<=1e6 order by id1 ,id2 desc ) where rownum<=5;
ID1 ID2 VC
---------- ---------- --------------------------------------------------
1 1 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
2 2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
3 3 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
4 4 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
5 5 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8f5yj8c3frpnc, child number 0
-------------------------------------
select * from (select * from t where id1<=1e6 order by id1 ,id2 desc )
where rownum<=5
Plan hash value: 2787951352
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 5 |00:00:00.01 | 6 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.01 | 6 |
| 2 | VIEW | | 1 | 5 | 265 | 4 (0)| 00:00:01 | 5 |00:00:00.01 | 6 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1000K| 58M| 4 (0)| 00:00:01 | 5 |00:00:00.01 | 6 |
|* 4 | INDEX RANGE SCAN | I_T_ID1_ID2DESC | 1 | 5 | | 3 (0)| 00:00:01 | 5 |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<=5)
4 - access("ID1"<=1000000)
31 rows selected.
--//可以发现很好第使用I_T_ID1_ID2DESC索引.逻辑读也很小,仅仅6.
SCOTT@book> alter index I_T_ID1_ID2DESC invisible;
Index altered.
select * from (select * from t where id1<=1e6 order by id1 ,id2 desc ) where rownum<=1;
SCOTT@book> select * from (select * from t where id1<=1e6 order by id1 ,id2 desc ) where rownum<=5;
ID1 ID2 VC
---------- ---------- --------------------------------------------------
1 1 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
2 2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
3 3 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
4 4 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
5 5 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8f5yj8c3frpnc, child number 0
-------------------------------------
select * from (select * from t where id1<=1e6 order by id1 ,id2 desc )
where rownum<=5
Plan hash value: 2145689175
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 5 |00:00:00.01 | 6 | 1 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.01 | 6 | 1 |
| 2 | VIEW | | 1 | 5 | 265 | 4 (0)| 00:00:01 | 5 |00:00:00.01 | 6 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1000K| 58M| 4 (0)| 00:00:01 | 5 |00:00:00.01 | 6 | 1 |
|* 4 | INDEX RANGE SCAN DESCENDING| I_T_ID1DESC_ID2 | 1 | 5 | | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 | 1 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
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<=5)
4 - access("T"."SYS_NC00004$" IS NOT NULL AND "T"."SYS_NC00004$">=HEXTORAW('3BFDFF') )
filter(SYS_OP_UNDESCEND("T"."SYS_NC00004$")<=1000000)
32 rows selected.
--//你会发现一个奇特现象,oracle会使用"倒过来"的一个索引,也就是id1 desc,id2 asc的索引.
SCOTT@book> alter index I_T_ID1DESC_ID2 invisible;
Index altered.
SCOTT@book> select * from (select * from t where id1<=1e6 order by id1 ,id2 desc ) where rownum<=5;
ID1 ID2 VC
---------- ---------- --------------------------------------------------
1 1 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
2 2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
3 3 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
4 4 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
5 5 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8f5yj8c3frpnc, child number 0
-------------------------------------
select * from (select * from t where id1<=1e6 order by id1 ,id2 desc )
where rownum<=5
Plan hash value: 3299198703
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 17500 (100)| | 5 |00:00:00.41 | 9279 | 9269 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | | 5 |00:00:00.41 | 9279 | 9269 | | | |
| 2 | VIEW | | 1 | 1000K| 50M| | 17500 (1)| 00:03:30 | 5 |00:00:00.41 | 9279 | 9269 | | | |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 1000K| 58M| 69M| 17500 (1)| 00:03:30 | 5 |00:00:00.41 | 9279 | 9269 | 2048 | 2048 | 2048 (0)|
|* 4 | TABLE ACCESS FULL | T | 1 | 1000K| 58M| | 2744 (1)| 00:00:33 | 1000K|00:00:00.15 | 9279 | 9269 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2
4 - SEL$2 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
4 - filter("ID1"<=1000000)
32 rows selected.
--//选择的是全表扫描.
SCOTT@book> alter index i_t_id1desc_id2desc invisible;
Index altered.
SCOTT@book> select * from (select * from t where id1<=1e6 order by id1 desc ,id2 desc ) where rownum<=1;
ID1 ID2 VC
---------- ---------- --------------------------------------------------
1000000 1000000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 66z6w3qc77jwp, child number 0
-------------------------------------
select * from (select * from t where id1<=1e6 order by id1 desc ,id2
desc ) where rownum<=1
Plan hash value: 3873686303
----------------------------------------------------------------------------------------------------------------------------------------
| 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 | 53 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
| 3 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1000K| 58M| 4 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 4 | INDEX RANGE SCAN DESCENDING| I_T_ID1_ID2 | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------------------------------
--//也能很好的选择正序索引.
5.总结:
--//通过以上测试,仅仅一种特殊的情况才需要建立倒序索引.存在id1 desc,id2 asc的情况下,建议完全可反过来建立(特别对于自增序列字段),
--//就是反过来建立索引id1 ,id2 desc,这样的索引占用磁盘空间更小.
--//其它情况我看不到降序索引的优势.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2017-12-18 [20171218]varchar2(4000)如何保存.txt