我的编程空间，编程开发者的网络收藏夹
学习永远不晚
位置：首页-资讯-数据库

[20191218]降序索引疑问4.txt

2017-06-19 16:08

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号
获取短信验证
立即预约

请填写图片验证码后获取短信验证码

看不清楚，换张图片

免费获取短信验证码

[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 * from t where id1<=1e6 order by id1 desc,id2 ) where rownum<=10;
select * from (select * from t where id1<=1e6 order by id1 desc,id2 ) where rownum<=10;

select * from (select * from t where id1<=1e6 order by id1 desc,id2 ) where rownum<=10;
select * from (select * 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,这样的索引占用磁盘空间更小.
--//其它情况我看不到降序索引的优势.

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

[20191218]降序索引疑问4.txt
阅读原文内容投诉

[20191218]降序索引疑问4.txt

下载Word文档到电脑，方便收藏和打印～

下载Word文档

相关文章

猜你喜欢

[20191218]降序索引疑问4.txt

[20191218]降序索引疑问4.txt--//前几天优化一个项目,我发现许多表里面有有隐含字段,一般开发很少建立函数索引.我自己检查发现里面存在大量的降序索引.--//我感觉有点奇怪,为什么开发要建立大量降序索引有什么好处呢?--//我在链接http://
[20191218]降序索引疑问4.txt
2017-06-19

热门标签

Linux(148)PHP(127)Java(102)正则表达式(101)JavaScript(69)最佳实践(67)jQuery(44)MySQL(39)Docker(37)C语言(36)性能优化(34)Python(34)XML(28)string(27)第三方库(23)回调函数(23)ZIP(22)数组(22)可扩展性(22)字符串比较(21)find(20)RPM(20)Go(20)grep(19)ASP.NETCore(19)XML解析器(19)事件(19)事件处理程序(19)StringBuilder(18)Nginx(18)

编程热搜

查看更多

编程资源站

  • 资料下载
  • 历年试题
2021年下半年软考高级信息系统项目管理师高频考点精选资料
2021下半年软考高级信息系统技术知识点记忆口诀精选资料
2021下半年软考《信息系统项目管理师》考试真题及答案精选资料
2021下半年软考高级考试备考攻略精选资料
2021年软考高级《信息系统项目管理师》巩固练习题汇总精选资料
2021下半年软考高级信息系统项目管理师30个易考知识点汇总精选资料
2021下半年软考高级知识点这样记，还担心记不住吗精选资料
2021年下半年软考高级考试重点汇总精选资料
2021下半年软考高级信息系统项目管理师计算公式汇总精选资料
2021年下半年软考高级《信息系统项目管理师》模拟试题精选资料
查看更多
信息系统项目管理师选择题每日一练（2024）历年试题
2023年下半年信息系统项目管理师综合知识真题演练历年试题
查看更多

目录

本网页已闲置超过3分钟，按键盘任意键或点击空白处，即可回到网页
最新资讯更多
    友情链接编程网

    反馈

    我要
    反馈