108-一个SQL, 两种优化方法, 效果都还可以

sql_tigerliu

newkid 发表于 2023-8-14 22:02
我把标量子查询换了一个索引列，简化了一下，效果也不错，都是1.X秒：create index idx_T_DEMO_648_Wind_W_ ...

苏老师, 你这个写法我测试执行耗时130秒, 我不知道你的1.x秒是什么来的. 这个写法的最大问题是, 如果给一个较大的h.beg_date, 它要从索引的最小值开始匹配, 然后发现不满足<w.end_date被丢弃,  h.beg_date传入的值越大, 执行的越慢.  这种区间检索的问题, 我研究的比较多, 我那种两层的写法的效率还是比较高的. 如果用beg_date, 要用降序扫描才行(你的写法加index_desc hint,  耗时1.22秒).  你这种一层的写法, 如果遇到匹配不到的情况, 耗时也会增加, 如果能确保都匹配到, 也可以这样写, 用beg_data就要降序, 用end_date就不需要降序.

newkid

如果要让 beg_date 上的索引起作用，就得让它降序：
create index idx_T_DEMO_648_Wind_W_2 on T_DEMO_648_Wind_W(i_code,beg_date desc);

这样的话九楼的写法就能奏效了。

sql_tigerliu

newkid 发表于 2023-8-14 22:57
如果要让 beg_date 上的索引起作用，就得让它降序：create index idx_T_DEMO_648_Wind_W_2 on T_DEMO_648_W ...

https://mp.weixin.qq.com/s/oRcLfXKz2k-pCrTV7f9Aww

〇〇

duckdb运行newkid的写法也是9秒，看来它不能用这种优化

newkid

sql_tigerliu 发表于 2023-8-14 22:56
苏老师, 你这个写法我测试执行耗时130秒, 我不知道你的1.x秒是什么来的. 这个写法的最大问题是, 如果给一个 ...

我刚才发帖的时候你的回帖还没显示出来，我也确实发现beg_date要降序才可以，这是由区间匹配的天然属性所决定的。我试图用新语法 order by W.beg_date desc FETCH NEXT 1 ROW ONLY 来实现只写一层，但是这个新语法还是很慢。

newkid

〇〇 发表于 2023-8-14 23:10
duckdb运行newkid的写法也是9秒，看来它不能用这种优化

像我12楼那样把索引建成降序的，会怎么样?

〇〇

还是一样，它的执行计划没有用到索引

sql_tigerliu

newkid 发表于 2023-8-14 23:56
我刚才发帖的时候你的回帖还没显示出来，我也确实发现beg_date要降序才可以，这是由区间匹配的天然属性所决 ...

12c的offset  fetch写法, 内部是用row_number分析实现的, 把标量子查询部分单独拿出来执行, 执行计划可以走"WINDOW NOSORT STOPKEY",这个效率是可以的; 但是在标量子查询里面, 执行计划就变成了"WINDOW BUFFER PUSHED RANK", 这个效率是较差的. 即使使用这种写法, 也是要两层, 才能解决匹配不到时效率差的问题.  这个案例返回的是一个字段, 如果返回的是多个字段, 我还尝试过outer apply的写法, 也是无法实现标量子查询写法的效率.

〇〇

不知下面写法是否等价

select h.*,w.dp_close as w_dp_close
from   H
LEFT JOIN  W
     ON   H.I_CODE = W.I_CODE
      AND W.BEG_DATE <= H.BEG_DATE
      AND W.END_DATE >=  H.END_DATE;

chengccy2010

拉链表不适合百万级以上的。 如果一个表到了百万级了还是冗余成日频表更好用，分区、索引都方便