利用递归WITH子查询进行优化的实例

xsaier

不过怎么说这个创建日期上不加索引有点说不过去.newkid的SQL确实炉火纯情。

perzer

赞一个，用得炉火纯清啊！

[ 本帖最后由 perzer 于 2011-6-16 21:42 编辑 ]

newkid

原帖由 爱乐 于 2011-6-16 11:32 发表
一直没理解newkid 大侠的思路，所以依葫芦画瓢照着步骤做了一遍!




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

11201的SQL解析有BUG, 11202就好了。

必须这么改：
WITH t (item_id,cnt) AS (
SELECT max(item_id),1 FROM items   ---先取最近的
GROUP BY 1  --------- 强行加个GROUP BY
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 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;

------------------
WITH t (item_id,cnt) AS (
SELECT (SELECT max(item_id) FROM items),1 FROM DUAL   ---改为标量子查询
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 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(SY

lastwinner

我这里有张上亿的日志表，9i
时间上没做索引，我打算用ID范围来圈定数据的时间区间，这其实比在日期上建索引要节省空间，而且我多是做统计，所以性能不会差

先期的测试已证明此方面非常高效

newkid

原帖由 lastwinner 于 2011-6-16 23:45 发表
我这里有张上亿的日志表，9i
时间上没做索引，我打算用ID范围来圈定数据的时间区间，这其实比在日期上建索引要节省空间，而且我多是做统计，所以性能不会差

先期的测试已证明此方面非常高效

9i能用一个SQL搞出来么？我试了CONNECT BY不行。

爱乐

谢谢 newkid 在凌晨都上来细心回答问题！  敬佩！    


测试了结果好像有点出入。
   
---------------------------------------

SQL> WITH t (item_id,cnt) AS (
SELECT (SELECT max(item_id) FROM items),1 FROM DUAL   ---改为标量子查询
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 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 ;  

已选择20行。
------------------------------------------------


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

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

已选择140行。
-------------------------------------------------

结果集不一样，想修改。无奈还未能完全理解。

lastwinner

原帖由 newkid 于 11-6-17 00:30 发表


9i能用一个SQL搞出来么？我试了CONNECT BY不行。

我的思路与目标是fewer disk space exchange good performance
不建日期索引，因为那样太占用空间了，现在一天的数据大约100万，若建日期索引少说占得8M空间（还没算rowid呢……）
而其实这里只要不到100个字节就可以存储差不多等价的信息，这里需要新建一张表——日期Id 表
表中就三列trunc(date), startid, endid
startid是时间为trunc(date)的最小的主键， endid是时间为trunc(date)+1-1/86400的最大的主键

查最近两天的数据怎么办？简单，先用 where date between trunc(sysdate)-2 and trunc(sysdate)-1/86400即可从日期ID表中查知startid和endid
然后以此作为主键的查询区间，就能高效定位这区间的数据了

在初始化数据完成后，后续的日期ID可以在前一日的日期ID的基础上获得，这同样是高效的
有些固定的东西，就找个地方将之固定存放下来，无需每次都去海量数据中寻找，这样不就达到快速查找的目的了么？


你的sql太长，我没看完，只大致看了你的情景描述，不知对不对？

nyfor

NewKid的解法很新颖
野花的方法也很不错

newkid

原帖由 爱乐 于 2011-6-17 09:59 发表
谢谢 newkid 在凌晨都上来细心回答问题！  敬佩！    


测试了结果好像有点出入。
   
---------------------------------------

已选择20行。
------------------------------------------------


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

已选择140行。
-------------------------------------------------

结果集不一样，想修改。无奈还未能完全理解。

我这里无法重现这个错误。单独运行一下这个，看看item_id是多少，是否落在区间之外？已经过去一天，你可能要改为 TRUNC(SYSDATE)-3

WITH t (item_id,cnt) AS (
SELECT (SELECT max(item_id) FROM items),1 FROM DUAL   ---改为标量子查询
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 created_date FROM items WHERE item_id=t.item_id)>=TRUNC(SYSDATE)-2) ---- 当取到的ID落在区间外则停止递归
CYCLE item_id SET cycle_flag TO 'Y' DEFAULT 'N'
SELECT MIN(item_id) FROM t;

newkid

原帖由 lastwinner 于 2011-6-17 15:22 发表



我的思路与目标是fewer disk space exchange good performance
不建日期索引，因为那样太占用空间了，现在一天的数据大约100万，若建日期索引少说占得8M空间（还没算rowid呢……）
而其实这里只要不到100个字节就可以存储差不多等价的信息，这里需要新建一张表——日期Id 表
表中就三列trunc(date), startid, endid
startid是时间为trunc(date)的最小的主键， endid是时间为trunc(date)+1-1/86400的最大的主键

查最近两天的数据怎么办？简单，先用 where date between trunc(sysdate)-2 and trunc(sysdate)-1/86400即可从日期ID表中查知startid和endid
然后以此作为主键的查询区间，就能高效定位这区间的数据了

在初始化数据完成后，后续的日期ID可以在前一日的日期ID的基础上获得，这同样是高效的
有些固定的东西，就找个地方将之固定存放下来，无需每次都去海量数据中寻找，这样不就达到快速查找的目的了么？


你的sql太长，我没看完，只大致看了你的情景描述，不知对不对？

不对，我的SQL相当于：
DECLARE
   lv_min_id NUMBER;
   lv_cnt    NUMBER :=1;
   lv_date   DATE;
BEGIN
   SELECT max(item_id) INTO lv_min_id FROM items;
   LOOP
       SELECT created_date INTO lv_date FROM items WHERE item_id=lv_min_id;
       EXIT WHEN lv_date<TRUNC(SYSDATE)-2;
       SELECT MIN(item_id) INTO lv_min_id
         FROM (SELECT item_id FROM  items ORDER BY item_id DESC)
        WHERE ROWNUM<=lv_cnt+2000;
       lv_cnt := lv_cnt+2000;
   END LOOP;
   
END;
/

你这方法相当于自己发明了一个索引，但是我不知道你打算如何维护，是放到事务？那么就有并发问题；每天维护一次？那就不能实时提供数据。