查看: 503|回复: 3

[每日一题] PL/SQL Challenge 每日一题:2020-4-8 索引

[复制链接]
论坛徽章:
526
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2020-5-20 01:45 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处:
http://www.plsqlchallenge.com/

作者:Chris Saxon

运行环境:SQLPLUS, SERVEROUTPUT已打开, 最低版本要求:11.2
注:本题给出答案时候要求给予简要说明才能得到奖品

你执行了这些语句:

create table qz_orders (
  order_id integer
    not null
    primary key,
  customer_id integer
    not null,
  order_datetime date
    not null
);

insert into qz_orders values ( 1, 1, sysdate - 2 );
insert into qz_orders values ( 2, 1, sysdate - 1 );
insert into qz_orders values ( 3, 1, sysdate );
commit;

exec dbms_stats.gather_table_stats ( null, 'qz_orders' ) ;

create index qz_order_i
  on qz_orders (
    ##REPLACE##
  );

select * from qz_orders
where  customer_id = 1
order  by order_datetime desc;

select *
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));
Currently the plan full scans QZ_ORDERS, then SORTS the results:

----------------------------------------                                    
| Id  | Operation          | Name      |                                    
----------------------------------------                                    
|   0 | SELECT STATEMENT   |           |                                    
|   1 |  SORT ORDER BY     |           |                                    
|   2 |   TABLE ACCESS FULL| QZ_ORDERS |                                    
----------------------------------------

哪些选项可以用来取代 ##REPLACE## 使得计划会使用索引并且剔除了SORT ORDER BY这个步骤?


(A)
customer_id

(B)
customer_id, order_datetime

(C)
customer_id, order_datetime desc

(D)
order_datetime desc, customer_id

(E)
order_datetime, order_id, customer_id
论坛徽章:
16
山治
日期:2017-01-11 16:13:26技术图书徽章
日期:2017-08-23 14:17:00技术图书徽章
日期:2017-08-23 14:17:00秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05
发表于 2020-5-20 09:32 | 显示全部楼层
答案:BCDE

A): customer_id列创建索引,但是是根据order_datetime来排序
    执行计划为全表扫描,且有sort order by   

B): customer_id, order_datetime列创建联合索引,索引是有序的,可以避免排序操作
    执行计划为INDEX RANGE SCAN DESCENDING

C): customer_id, order_datetime列创建联合索引,索引是有序的,可以避免排序操作
    索引列order_datetime desc与主查询的顺序一致
    执行计划为INDEX RANGE SCAN

D): order_datetime,customer_id列创建联合索引,索引是有序的,可以避免排序操作
    order_datetime desc, customer_id 与C的顺序进行调换
    执行计划为INDEX FULL SCAN
   
    假设DBA禁止了 索引跳跃全扫描
    (A,B)列创建联合索引,如果where后面谓词条件只有B,很多面试题目都说是走不了索引的
    其实还要考虑order by排序子句,这个走了索引全扫描
   
E): order_datetime, order_id, customer_id三列 创建联合索引,索引是有序的,可以避免排序操作
    执行计划为 INDEX FULL SCAN DESCENDING,而且避免了回表操作

使用道具 举报

回复
论坛徽章:
534
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2020-5-20 09:54 | 显示全部楼层

答案 BCDE
A: 会根据customer_id范围索引,但索引中并没包含order_datetime,所以执行计划中SORT ORDER BY还是会有。
B: 这个INDEX,主查询的过滤和order by 字段都包含了。索引中键值已经是排序的,表扫描带索引TABLE ACCESS BY INDEX ROWID,
   索引带降序INDEX RANGE SCAN DESCENDING
C: 键值之一order_datetime 指定按降序排序,执行计划与B同,推荐
D: 组合索引前导列的顺序换了一下,会根据customer_id范围索引,执行计划与B同
E: 这INDEX 把表的全部字段都包含了,只范围INDEX 就够了 INDEX FULL SCAN DESCENDING,不需要扫描表,但索引显得臃肿。

使用道具 举报

回复
论坛徽章:
526
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2020-5-20 22:41 | 显示全部楼层
答案BCDE, 2楼得奖。

A:
查询是以order_datetime排序。这个列不在索引中。所以数据库从表中读出数据之后必须对它进行排序。这个索引会产生这样的计划:

-----------------------------------------------------------
| Id  | Operation                            | Name       |
-----------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |
|   1 |  SORT ORDER BY                       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| QZ_ORDERS  |
|   3 |    INDEX RANGE SCAN                  | QZ_ORDER_I |
-----------------------------------------------------------

B:
这在索引中包含了order_datetime,但是条目是升序的。数据库仍然可以通过对索引使用降序扫描,在计划中避免排序,得到这样的计划:

---------------------------------------------------
| Id  | Operation                    | Name       |
---------------------------------------------------
|   0 | SELECT STATEMENT             |            |
|   1 |  TABLE ACCESS BY INDEX ROWID | QZ_ORDERS  |
|   2 |   INDEX RANGE SCAN DESCENDING| QZ_ORDER_I |
---------------------------------------------------

C:
这会在索引中以order_datetime降序的方式存储条目。所以数据库能够通过读取索引,以所需的顺序来返回数据行。

这个索引会给出这样的计划:

--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| QZ_ORDERS  |
|   2 |   INDEX RANGE SCAN          | QZ_ORDER_I |
--------------------------------------------------

D:
Customer_id 是这个索引中的第二个列。所以数据库没法对它进行范围扫描。但是条目是以order_datetime降序来排序的。所以它们和所需的排序顺序相匹配。

这使得数据库会对索引全扫描并且略过计划中的排序操作。
这个索引会给出这样的计划:

--------------------------------------------------
| Id  | Operation                   | Name       |
--------------------------------------------------
|   0 | SELECT STATEMENT            |            |
|   1 |  TABLE ACCESS BY INDEX ROWID| QZ_ORDERS  |
|   2 |   INDEX FULL SCAN           | QZ_ORDER_I |
--------------------------------------------------

E:
这个索引包含了查询中所有的列。所以数据库能够对它进行全扫描而不是读表。

条目是以order_datetime升序来排列的。所以它必须对索引执行降序扫描来避免排序。

得到的是这样的计划:

-------------------------------------------------
| Id  | Operation                  | Name       |
-------------------------------------------------
|   0 | SELECT STATEMENT           |            |
|   1 |  INDEX FULL SCAN DESCENDING| QZ_ORDER_I |
-------------------------------------------------


使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

DTCC2020中国数据库技术大会 限时8.5折

【架构革新 高效可控】2020年9月21日~23日第十一届中国数据库技术大会将在北京隆重召开。

大会设置2大主会场,20+技术专场,将邀请超百位行业专家,重点围绕数据架构、AI与大数据、传统企业数据库实践和国产开源数据库等内容展开分享和探讨,为广大数据领域从业人士提供一场年度盛会和交流平台。

http://dtcc.it168.com


大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表