查看: 16896|回复: 58

[原创] 支付宝面试:select min(id),max(id) 优化问题

[复制链接]
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
发表于 2011-7-21 19:52 | 显示全部楼层 |阅读模式
2011,7/20去支付宝面试开发DBA,面试官问了这样一个题目 select min(id),max(id) from table; 他们说 是一个 很经典的案例 呵呵
我现在测试一把

SQL>  select * from v$version where rownum<2;  
  
BANNER  
--------------------------------------------------------------------------------  
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
  
SQL>  create table test as select * from dba_objects;  
  
Table created.  
  
SQL> create index i_object_id on test(object_id);  
  
Index created.  

SQL> select max(object_id),min(object_id) from test;  
  
MAX(OBJECT_ID) MIN(OBJECT_ID)  
-------------- --------------  
         74644              2  
  
Elapsed: 00:00:00.34  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 1751978921  
  
-------------------------------------------------------------------------------------  
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
-------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT      |             |     1 |     5 |    37   (0)| 00:00:01 |  
|   1 |  SORT AGGREGATE       |             |     1 |     5 |            |          |  
|   2 |   INDEX FAST FULL SCAN| I_OBJECT_ID | 72860 |   355K|    37   (0)| 00:00:01 |  
-------------------------------------------------------------------------------------  
  
  
Statistics  
----------------------------------------------------------  
          1  recursive calls  
          0  db block gets  
        167  consistent gets  
        145  physical reads  
          0  redo size  
        501  bytes sent via SQL*Net to client  
        420  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
          1  rows processed  

可以看到CBO选择了 INDEX FAST FULL SCAN,有145个物理读,167个逻辑读,现在改写SQL如下
SQL> select (select min(object_id) min_id from test a),(select max(object_id) max_id from test b) from dual;  
(SELECTMIN(OBJECT_ID)MIN_IDFROMTESTA) (SELECTMAX(OBJECT_ID)MAX_IDFROMTESTB)  
------------------------------------- -------------------------------------  
                                    2                                 74644  
                                      
Execution Plan  
----------------------------------------------------------  
Plan hash value: 4224666897  
  
------------------------------------------------------------------------------------------  
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT           |             |     1 |       |     2   (0)| 00:00:01 |  
|   1 |  SORT AGGREGATE            |             |     1 |     5 |            |          |  
|   2 |   INDEX FULL SCAN (MIN/MAX)| I_OBJECT_ID |     1 |     5 |     2   (0)| 00:00:01 |  
|   3 |  SORT AGGREGATE            |             |     1 |     5 |            |          |  
|   4 |   INDEX FULL SCAN (MIN/MAX)| I_OBJECT_ID |     1 |     5 |     2   (0)| 00:00:01 |  
|   5 |  FAST DUAL                 |             |     1 |       |     2   (0)| 00:00:01 |  
------------------------------------------------------------------------------------------  
  
  
Statistics  
----------------------------------------------------------  
          1  recursive calls  
          0  db block gets  
          4  consistent gets  
          0  physical reads  
          0  redo size  
        547  bytes sent via SQL*Net to client  
        420  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
          1  rows processed  
可以看到逻辑读降低为4,大家看看这个是不是最优化的方法?还有更进一步的方法优化吗?因为支付宝说这个案例很经典,能这么简单吗?
可能是我水平太低了,只能想出这个方法

----update-----

这样也可以,逻辑读也是4,大家想向是否还有其他方法?

SQL> select (select /*+ index_asc(test i_object_id) */ object_id from test where rownum=1) min
,(select /*+ index_desc(test i_object_id) */ object_id from test where rownum=1) max from dual;  2

       MIN        MAX
---------- ----------
         2      74644


Execution Plan
----------------------------------------------------------
Plan hash value: 91314419

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |       |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY              |             |       |       |            |          |
|   2 |   INDEX FULL SCAN           | I_OBJECT_ID |     1 |     5 |     2   (0)| 00:00:01 |
|*  3 |  COUNT STOPKEY              |             |       |       |            |          |
|   4 |   INDEX FULL SCAN DESCENDING| I_OBJECT_ID |     1 |     5 |     2   (0)| 00:00:01 |
|   5 |  FAST DUAL                  |             |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)
   3 - filter(ROWNUM=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        479  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


顺便打个广告,大量的SQL优化案例:
http://blog.csdn.net/robinson1988/article/category/621201

[ 本帖最后由 lbs1988 于 2011-7-21 20:31 编辑 ]
论坛徽章:
4
鲜花蛋
日期:2011-07-21 16:06:54双黄蛋
日期:2011-09-02 20:36:17ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29奥运会纪念徽章:射箭
日期:2012-07-26 19:04:19
发表于 2011-7-21 20:08 | 显示全部楼层
这道题很注重技巧啊。

LZ能否对其原理做一番阐述?

使用道具 举报

回复
论坛徽章:
18
紫蛋头
日期:2011-07-25 08:06:53咸鸭蛋
日期:2011-12-27 11:35:38鲜花蛋
日期:2012-01-11 11:08:36奥运会纪念徽章:射击
日期:2012-09-11 08:56:18奥运会纪念徽章:体操
日期:2012-10-25 09:07:51紫蛋头
日期:2012-12-10 13:46:51灰彻蛋
日期:2013-01-28 14:23:202013年新春福章
日期:2013-02-25 14:51:24
发表于 2011-7-21 20:10 | 显示全部楼层
可以原意是在考oracle對索掃描中的max,min索引掃描方法
INDEX FULL SCAN (MIN/MAX)

使用道具 举报

回复
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
 楼主| 发表于 2011-7-21 20:11 | 显示全部楼层

回复 #2 freas 的帖子

我还在找更优化的方法呢,因为他们说这个案例很经典。。。

使用道具 举报

回复
论坛徽章:
9
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:00ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51蛋疼蛋
日期:2011-08-09 14:26:55ITPUB十周年纪念徽章
日期:2011-11-01 16:24:51ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19奥迪
日期:2013-09-12 15:57:042014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2011-7-21 20:11 | 显示全部楼层
就这么简单。

使用道具 举报

回复
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
 楼主| 发表于 2011-7-21 20:13 | 显示全部楼层

回复 #5 tom_fans 的帖子

呵呵 那也太简单了

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2011-7-21 20:16 | 显示全部楼层
这道题目很简单,我研究过,呵呵

使用道具 举报

回复
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
 楼主| 发表于 2011-7-21 20:17 | 显示全部楼层

回复 #7 dingjun123 的帖子

哈哈 兔子哥别忙说答案 , 我也再研究一哈

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2011-7-21 20:17 | 显示全部楼层
其实已经不稀奇了,自从oracle cbo出来,然后国内相关优化书籍层出不穷,以及几大blog相继出现

一般的书上都有这个技巧,包括secooler的blog也说过这玩意,管理版也经常讨论这个10g的特性

使用道具 举报

回复
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
 楼主| 发表于 2011-7-21 20:19 | 显示全部楼层

回复 #9 dingjun123 的帖子

呵呵 几乎不上论坛

使用道具 举报

回复

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

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

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