查看: 12258|回复: 28

[精华] 利用递归WITH子查询进行优化的实例

[复制链接]
论坛徽章:
533
奥运会纪念徽章:垒球
日期: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
发表于 2011-6-16 02:25 | 显示全部楼层 |阅读模式
递归WITH子查询我已经用过不少了,不过都是当作玩具,没有在实践中用过。昨天碰到了一个实用例子。
在一个OLTP中有一张表,主键是随着创建时间递增的。每天产生大约1万条记录,全表大约有几百万,没有分区,创建时间没有索引。
现在要求取出最近两天的数据并且从中过滤出部分记录。过滤条件上也没有索引。
原来的查询为全表扫描,效率太低,有没有办法改善?前提:因为原表索引够多了,不能新增索引;原查询来自JAVA程序,修改之后必须还是一个SQL, 交由JAVA程序执行。

测试数据:
CREATE TABLE items AS
SELECT 3000000 - LEVEL    AS item_id
      ,SYSDATE-LEVEL/10000 AS created_date
      ,TRUNC(DBMS_RANDOM.VALUE(1,101)) AS item_type
      ,SYS_GUID()   AS DESCRIPTION
  FROM DUAL
CONNECT BY LEVEL<=1000000;

ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY (item_id) USING INDEX;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'ITEMS');

原表要复杂得多,现在简化为100万的一张表。查询大约相当于:

SELECT * FROM items WHERE created_date >= TRUNC(SYSDATE)-2 AND item_type=14;

294 rows selected.

Elapsed: 00:00:00.90

Execution Plan
----------------------------------------------------------
Plan hash value: 446380563

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   258 |  8772 |  1501   (1)| 00:00:19 |
|*  1 |  TABLE ACCESS FULL| ITEMS |   258 |  8772 |  1501   (1)| 00:00:19 |
---------------------------------------------------------------------------

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

   1 - filter("ITEM_TYPE"=14 AND "CREATED_DATE">=TRUNC(SYSDATE@!)-2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5429  consistent gets
       5406  physical reads
          0  redo size
      13636  bytes sent via SQL*Net to client
        625  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        294  rows processed

例子表简化了很多, 所以只花了0.9秒,5429个一致读。实际表要胖得多,查询需要几十秒。

改写:
因为主键是随着时间递增的,所以“取最近的数据”可转换为:取ID最大的N条数据,如果其中最早的一条还是两天之内就增大N, 直到日期落在两天之外停止查询。

WITH t (item_id,cnt) AS (
SELECT max(item_id),1 FROM items   ---先取最近的
UNION ALL
select (SELECT MIN(item_id) FROM (SELECT item_id FROM  items ORDER BY item_id DESC) WHERE ROWNUM<=t.cnt+2000) ---- 跳跃取2000行之后的ID
      ,cnt+2000 ----- 当日期还在区间内则递增取ID的范围。根据每天的数据两选取合适的步长,这里定为2000
FROM t
WHERE (SELECT MAX(created_date) FROM items WHERE item_id=t.item_id)>=TRUNC(SYSDATE)-2) ---- 当取到的ID落在区间外则停止递归
CYCLE item_id SET cycle_flag TO 'Y' DEFAULT 'N'  ---- 虽然ID都不重复但是ORACLE会报告有循环数据,所以在这里加上CYCLE语句
select *
FROM items
WHERE item_id>=(SELECT min(item_id) from t)  ----- 利用前面的搜索结果
      AND item_type=14
      AND created_date >= TRUNC(SYSDATE)-2;


294 rows selected.

Elapsed: 00:00:00.16

Execution Plan
----------------------------------------------------------
Plan hash value: 1844953721

---------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name     | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |          |    13 |   442 |    81   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID                 | ITEMS    |    13 |   442 |    72   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                           | ITEMS_PK |  9000 |       |    23   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE                            |          |     1 |    13 |            |       |
|   4 |     VIEW                                     |          |     2 |    26 |     9   (0)| 00:00:01 |
|   5 |      UNION ALL (RECURSIVE WITH) BREADTH FIRST|          |       |       |            |       |
|   6 |       SORT AGGREGATE                         |          |     1 |     6 |            |       |
|   7 |        INDEX FULL SCAN (MIN/MAX)             | ITEMS_PK |     1 |     6 |     3   (0)| 00:00:01 |
|   8 |       SORT AGGREGATE                         |          |     1 |    13 |            |       |
|*  9 |        COUNT STOPKEY                         |          |       |       |            |       |
|  10 |         VIEW                                 |          |  1000K|    12M|  2238   (1)| 00:00:27 |
|  11 |          INDEX FULL SCAN DESCENDING          | ITEMS_PK |  1000K|  5859K|  2238   (1)| 00:00:27 |
|* 12 |       FILTER                                 |          |       |       |            |       |
|  13 |        RECURSIVE WITH PUMP                   |          |       |       |            |       |
|  14 |        SORT AGGREGATE                        |          |     1 |    14 |            |       |
|  15 |         TABLE ACCESS BY INDEX ROWID          | ITEMS    |     1 |    14 |     3   (0)| 00:00:01 |
|* 16 |          INDEX UNIQUE SCAN                   | ITEMS_PK |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   1 - filter("ITEM_TYPE"=14 AND "CREATED_DATE">=TRUNC(SYSDATE@!)-2)
   2 - access("ITEM_ID">= (SELECT MIN("ITEM_ID") FROM "T" "T"))
   9 - filter(ROWNUM<=:B1+2000)
  12 - filter( (SELECT MAX("CREATED_DATE") FROM "ITEMS" "ITEMS" WHERE
              "ITEM_ID"=:B1)>=TRUNC(SYSDATE@!)-2)
  16 - access("ITEM_ID"=:B1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        722  consistent gets
          0  physical reads
          0  redo size
      13636  bytes sent via SQL*Net to client
        625  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
        294  rows processed
        
主键索引用得上了,一致读下降为722。
论坛徽章:
41
2010广州亚运会纪念徽章:橄榄球
日期:2011-01-11 06:17:26红孩儿
日期:2012-12-19 11:07:13玉石琵琶
日期:2012-12-19 11:07:13九尾狐狸
日期:2012-12-19 11:07:13嫦娥
日期:2012-12-19 11:07:13玉兔
日期:2012-12-19 11:07:13紫蜘蛛
日期:2012-12-19 11:07:13蓝色妖姬
日期:2012-12-19 11:07:13紫蛋头
日期:2013-01-23 09:04:49SQL大赛参与纪念
日期:2013-12-06 14:03:45
发表于 2011-6-16 04:20 | 显示全部楼层
I have done the similar "tricky" before in Oracle 10g.
What I have done was: creating a function to get the min_id based on the input date, and using "sorted binary tree" search algorithm, not as you did search every 2000 rows

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期: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
 楼主| 发表于 2011-6-16 04:56 | 显示全部楼层
原帖由 xqmei 于 2011-6-16 04:20 发表
I have done the similar "tricky" before in Oracle 10g.
What I have done was: creating a function to get the min_id based on the input date, and using "sorted binary tree" search algorithm, not as you did search every 2000 rows

那么你就是先猜测ID会在哪里,比如将MAX ID砍掉一半,取到那个日期看看接近多少,然后再不断调整......?
写程序的解决办法有不少,我这个递归SQL的办法还比较新鲜。

使用道具 举报

回复
论坛徽章:
10000
地主之星
日期:2015-07-20 17:15:36地主之星
日期:2015-09-01 14:14:25地主之星
日期:2015-09-01 17:59:09地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58
发表于 2011-6-16 08:31 | 显示全部楼层
标记学习下

使用道具 举报

回复
论坛徽章:
74
蓝锆石
日期:2011-12-29 15:35:34萤石
日期:2011-11-18 15:00:15祖母绿
日期:2011-12-29 15:26:07海蓝宝石
日期:2011-12-30 16:00:25紫水晶
日期:2011-12-29 15:26:07红宝石
日期:2011-12-29 15:26:07季节之章:冬
日期:2012-01-01 12:35:07季节之章:冬
日期:2012-01-01 12:35:07季节之章:夏
日期:2011-09-28 16:06:59季节之章:夏
日期:2011-09-28 16:06:59
发表于 2011-6-16 08:57 | 显示全部楼层
学习,我在sqlserver没有索引的表上查最近数据时也采用类似的方法

使用道具 举报

回复
论坛徽章:
10
2010新春纪念徽章
日期:2010-03-01 11:20:062014年新春福章
日期:2014-02-18 16:41:112013年新春福章
日期:2013-02-25 14:51:24奥运会纪念徽章:蹦床
日期:2012-10-12 14:07:54紫蛋头
日期:2012-06-05 23:31:082012新春纪念徽章
日期:2012-01-04 11:50:44ITPUB十周年纪念徽章
日期:2011-11-01 16:20:282011新春纪念徽章
日期:2011-02-18 11:43:35ITPUB9周年纪念徽章
日期:2010-10-08 09:32:26马上有车
日期:2014-02-18 16:41:11
发表于 2011-6-16 11:32 | 显示全部楼层
一直没理解newkid 大侠的思路,所以依葫芦画瓢照着步骤做了一遍!


SQL> WITH t(item_id,cnt) AS
(SELECT max(item_id), 1 FROM items
  UNION ALL
  select (SELECT MIN(item_id)
            FROM (SELECT item_id FROM items ORDER BY item_id DESC)
           WHERE ROWNUM <= t.cnt + 2000),
         cnt + 2000
    FROM t
   WHERE (SELECT MAX(created_date) FROM items WHERE item_id = t.item_id) >=
         TRUNC(SYSDATE) - 2)
CYCLE item_id SET cycle_flag TO
'Y' DEFAULT
'N'
select *
  FROM items
WHERE item_id >= (SELECT min(item_id) from t)
   AND item_type = 14
   AND created_date >= TRUNC(SYSDATE) - 2;
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   CYCLE item_id SET cycle_flag TO
                              *
第 11 行出现错误:
ORA-00937: 不是单组分组函数


琢磨了很久也没能解决这个问题,还请newkid 出手相助了!

使用道具 举报

回复
论坛徽章:
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-6-16 11:59 | 显示全部楼层
save and study~

使用道具 举报

回复
论坛徽章:
10000
地主之星
日期:2015-07-20 17:15:36地主之星
日期:2015-09-01 14:14:25地主之星
日期:2015-09-01 17:59:09地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58地主之星
日期:2015-08-31 16:17:58
发表于 2011-6-16 16:37 | 显示全部楼层
我也是这样报错:不是单分组函数

[ 本帖最后由 jboracle1981 于 2011-6-16 16:38 编辑 ]

使用道具 举报

回复
论坛徽章:
3
ITPUB官方微博粉丝徽章
日期:2011-06-29 09:48:25ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15秀才
日期:2016-02-18 09:23:46
发表于 2011-6-16 17:17 | 显示全部楼层
10g用户表示很无奈.

使用道具 举报

回复
论坛徽章:
74
蓝锆石
日期:2011-12-29 15:35:34萤石
日期:2011-11-18 15:00:15祖母绿
日期:2011-12-29 15:26:07海蓝宝石
日期:2011-12-30 16:00:25紫水晶
日期:2011-12-29 15:26:07红宝石
日期:2011-12-29 15:26:07季节之章:冬
日期:2012-01-01 12:35:07季节之章:冬
日期:2012-01-01 12:35:07季节之章:夏
日期:2011-09-28 16:06:59季节之章:夏
日期:2011-09-28 16:06:59
发表于 2011-6-16 17:21 | 显示全部楼层
这是一种思路,例如某个大表,只有主键索引,而且主键是序列递增,时间上没有索引,而用户需要提取最近两天创建的数据时就先查当前主键最大的数据的创建时间,然后设置一定的数值按主键索引往前推,避免全表扫描,呵呵

使用道具 举报

回复

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

本版积分规则 发表回复

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