查看: 4932|回复: 8

sql优化之stopkey

[复制链接]
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2008-9-12 11:40 | 显示全部楼层 |阅读模式
4.stopkey的优化

案例1:和分页有关的例子

stopkey经常在含有rownum的语句中出现,也就是说分页的时候经常看到count stopkey的执行计划

我构造个表,test,这个表的中只有id为1的行很多,id为其它值的都很少

测试1:


SQL>select * from (select * from test where id=1 and name is not null order by name) where rownum<11;


已选择10行。


执行计划
----------------------------------------------------------
Plan hash value: 544630612

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

| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)|
Time     |

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

|   0 | SELECT STATEMENT              |          |    10 |   790 |     7   (0)|
00:00:01 |

|*  1 |  COUNT STOPKEY                |          |       |       |            |
         |

|   2 |   VIEW                        |          |    12 |   948 |     7   (0)|
00:00:01 |

|*  3 |    TABLE ACCESS BY INDEX ROWID| TEST     | 49591 |  1307K|     7   (0)|
00:00:01 |

|*  4 |     INDEX FULL SCAN           | I_TEST_1 |    14 |       |     2   (0)|
00:00:01 |

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


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

   1 - filter(ROWNUM<11)
   3 - filter("ID"=1)
   4 - filter("NAME" IS NOT NULL)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        679  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed  

这时候很快,因为id=1占了几乎所有的记录,因此id=1的这个条件很容易满足,由于stopkey机制的存在,
原来的语句基本等价于

select * from (select * from test where  name is not null order by name) where rownum<11;
因为name上的索引保证了有序,因此读10行记录是很快的


b.把id=1换成一个别的值id=2

select * from (select * from test where id=2 and name is not null order by name) where rownum<11;

已选择10行。


执行计划
----------------------------------------------------------
Plan hash value: 544630612

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

| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)|
Time     |

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

|   0 | SELECT STATEMENT              |          |    10 |   790 |    17   (0)|
00:00:01 |

|*  1 |  COUNT STOPKEY                |          |       |       |            |
         |

|   2 |   VIEW                        |          |    10 |   790 |    17   (0)|
00:00:01 |

|*  3 |    TABLE ACCESS BY INDEX ROWID| TEST     | 99968 |  2538K|    17   (0)|
00:00:01 |

|*  4 |     INDEX FULL SCAN           | I_TEST_1 |    20 |       |     3   (0)|
00:00:01 |

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


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

   1 - filter(ROWNUM<11)
   3 - filter("ID"=2)
   4 - filter("NAME" IS NOT NULL)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      59354  consistent gets
          0  physical reads
          0  redo size
        561  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

这里的逻辑读5w多,恐怖啊

id为2的行很少,id=2这个条件不容易满足,oracle基本扫描整个索引才能得到这个记录,因此这里的stopkey意义不大了

看看id为1和id为2的分别多少条

  1  select sum(case when id=1 then 1 else 0 end) id1,sum(case when id=2 then 1 else 0 end) id2,
  2* count(*) from test
SQL> /

       ID1        ID2   COUNT(*)
---------- ---------- ----------
    199536        400     199936

id=1和id=2的记录悬殊,当id=2的时候就适合走id上的索引,创建id上的索引并收集直方图

SQL> create index i_test_2 on test(id);

索引已创建。

SQL> analyze table test compute statistics for columns id;

表已分析。

现在看看2个语句的执行计划,是不是都能走对

SQL> set autot trace
SQL> select * from (select * from test where id=1 and name is not null order by name) where rownum<1
1;

已选择10行。


执行计划
----------------------------------------------------------
Plan hash value: 544630612

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

| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)|
Time     |

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

|   0 | SELECT STATEMENT              |          |    10 |   790 |    10   (0)|
00:00:01 |

|*  1 |  COUNT STOPKEY                |          |       |       |            |
         |

|   2 |   VIEW                        |          |    11 |   869 |    10   (0)|
00:00:01 |

|*  3 |    TABLE ACCESS BY INDEX ROWID| TEST     |   199K|  5066K|    10   (0)|
00:00:01 |

|*  4 |     INDEX FULL SCAN           | I_TEST_1 |    11 |       |     3   (0)|
00:00:01 |

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


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

   1 - filter(ROWNUM<11)
   3 - filter("ID"=1)
   4 - filter("NAME" IS NOT NULL)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        586  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> select * from (select * from test where id=2 and name is not null order by name) where rownum<1
1;

已选择10行。


执行计划
----------------------------------------------------------
Plan hash value: 2469756000

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

| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)|
Time     |

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

|   0 | SELECT STATEMENT               |          |    10 |   790 |     4  (25)|
00:00:01 |

|*  1 |  COUNT STOPKEY                 |          |       |       |            |
          |

|   2 |   VIEW                         |          |   400 | 31600 |     4  (25)|
00:00:01 |

|*  3 |    SORT ORDER BY STOPKEY       |          |   400 | 10400 |     4  (25)|
00:00:01 |

|*  4 |     TABLE ACCESS BY INDEX ROWID| TEST     |   400 | 10400 |     3   (0)|
00:00:01 |

|*  5 |      INDEX RANGE SCAN          | I_TEST_2 |   400 |       |     1   (0)|
00:00:01 |

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


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

   1 - filter(ROWNUM<11)
   3 - filter(ROWNUM<11)
   4 - filter("NAME" IS NOT NULL)
   5 - access("ID"=2)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          2  physical reads
          0  redo size
        561  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed  

这时候执行计划都能走对了,当id=1的时候走了i_test_1上的索引,这时候是正确的,当id=2的时候走了i_test_2上的索引,
这时候达到了目标了


类似案例
http://www.itpub.net/thread-1052633-1-11.html

前后的逻辑读对比

--------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |    20 |  3180 |       |  9942   (1)| 00:02:00 |
|*  1 |  COUNT STOPKEY          |          |       |       |       |            |          |
|   2 |   VIEW                  |          |   487K|    73M|       |  9942   (1)| 00:02:00 |
|*  3 |    SORT ORDER BY STOPKEY|          |   487K|    26M|    82M|  9942   (1)| 00:02:00 |
|*  4 |     TABLE ACCESS FULL   | AWFACTOR |   487K|    26M|       |  3095   (2)| 00:00:38 |
--------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13932  consistent gets
          0  physical reads
          0  redo size
       1666  bytes sent via SQL*Net to client
        357  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         20  rows processed





-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |    20 |  3180 |    23   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |                 |       |       |            |          |
|   2 |   VIEW                        |                 |    20 |  3180 |    23   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| AWFACTOR        |    20 |  1160 |    23   (0)| 00:00:01 |
|*  4 |     INDEX FULL SCAN           | I_AWFACTOR_NAME |   487K|       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         25  consistent gets
          0  physical reads
          0  redo size
       1658  bytes sent via SQL*Net to client
        357  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed
招聘 : 数据库管理员
论坛徽章:
25
生肖徽章2007版:龙
日期:2008-05-06 11:07:48咸鸭蛋
日期:2011-10-19 10:09:12ITPUB十周年纪念徽章
日期:2011-11-01 16:20:282012新春纪念徽章
日期:2012-01-04 11:49:542013年新春福章
日期:2013-02-25 14:51:24
发表于 2008-9-12 11:41 | 显示全部楼层
棉花今天吃药了。

使用道具 举报

回复
论坛徽章:
55
马上加薪
日期:2014-02-19 11:55:142010广州亚运会纪念徽章:排球
日期:2011-04-27 13:27:19SQL大赛参与纪念
日期:2011-04-13 12:08:172011新春纪念徽章
日期:2011-02-18 11:43:332011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:502011新春纪念徽章
日期:2011-01-25 15:41:01生肖徽章2007版:兔
日期:2011-01-20 12:58:49
发表于 2008-9-12 11:52 | 显示全部楼层
原帖由 foxmile 于 2008-9-12 11:41 发表
棉花今天吃药了。


只能说这个斑竹比较负责!!认真,塌实!!

使用道具 举报

回复
论坛徽章:
66
皇马
日期:2009-02-13 09:38:532011新春纪念徽章
日期:2011-02-18 11:43:342010广州亚运会纪念徽章:柔道
日期:2011-04-08 23:11:212010广州亚运会纪念徽章:排球
日期:2011-04-18 22:00:58鲜花蛋
日期:2011-05-30 21:23:49ITPUB十周年纪念徽章
日期:2011-11-01 16:24:04ITPUB 11周年纪念徽章
日期:2012-10-09 18:08:15迷宫蛋
日期:2012-12-18 23:39:42问答徽章
日期:2013-09-25 16:14:23优秀写手
日期:2015-02-12 06:00:13
发表于 2008-9-12 12:13 | 显示全部楼层

使用道具 举报

回复
发表于 2008-9-12 12:41 | 显示全部楼层
今天吃了棉花糖

使用道具 举报

回复
论坛徽章:
2
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44奥运会纪念徽章:跳水
日期:2008-10-24 13:09:39
发表于 2008-9-12 13:28 | 显示全部楼层
棉花索引i_test_1是建在哪些列上的?

使用道具 举报

回复
论坛徽章:
2
祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:23
发表于 2008-9-12 13:29 | 显示全部楼层

请问棉花糖,请教你的Statistics是怎么弄来的?谢谢

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
 楼主| 发表于 2008-9-12 13:36 | 显示全部楼层
原帖由 yelang9809 于 2008-9-12 13:28 发表
棉花索引i_test_1是建在哪些列上的?


order by 的字段上的

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
 楼主| 发表于 2008-9-12 13:37 | 显示全部楼层
原帖由 fourahabit 于 2008-9-12 13:29 发表

请问棉花糖,请教你的Statistics是怎么弄来的?谢谢


set autot trace得到的啊

使用道具 举报

回复

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

本版积分规则 发表回复

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