还是查询优化的问题，可能有点怪

newkid · 发表于 2021-7-18 03:32

不用LATERAL, 用传统的CONNECT BY写法：

insert into range_result2
with b as (
select b.*,trunc(start_id/10000)+level-1 as compare_id
  from range_b b
connect by start_id=prior start_id and end_id=prior end_id
   and level<=trunc(end_id/10000)-trunc(start_id/10000)+1
   and prior sys_guid() is not null
)
select a.id,b.type from range_a a,b where trunc(a.id/10000)=b.compare_id and a.id between b.start_id and b.end_id
;

newkid · 发表于 2021-7-18 07:08

此外，关于外层表的列值只能向标量子查询传递一层这个限制，在10g的某个版本曾经是解除了的，但是后续的版本又再加上了，直到12c才放开。我曾经在asktom看到有人提问，TOM说这是ANSI标准，如果放开了就是一个bug。也不知道为什么ANSI标准要加这个限制。

jihuyao · 发表于 2021-7-18 08:09

Loop A and then loop B it is about 0.4*10**9 in total. Loop B and then using index range scan for A it is about 2000 plus some index search in total. So I would bid hint ordered, from B, A.

newkid · 发表于 2021-7-18 08:11

jihuyao 发表于 2021-7-18 08:09
Loop A and then loop B it is about 0.4*10**9 in total. Loop B and then using index range scan for A ...

写一个来试试看?

newkid · 发表于 2021-7-18 08:28

ASKTOM上关于相关子查询嵌套层数的讨论贴：
https://asktom.oracle.com/pls/ap ... 1859169400346361423

2013年TOM还说这是一个bug, 到2016年Connor McDonald终于承认这是一个improvement

sql_tigerliu · 发表于 2021-7-18 10:49

苏大师这SQL功力，实在是厉害！佩服佩服！

newkid · 发表于 2021-7-18 20:35

jihuyao 发表于 2021-7-18 08:09
Loop A and then loop B it is about 0.4*10**9 in total. Loop B and then using index range scan for A ...

你的想法非常好！我那些复杂做法原来都是想太多了，哈哈。

create index a_idx on range_a(id );

insert into range_result
SELECT /*+ use_nl(b a)*/ ID, TYPE
FROM range_a A,range_b B
WHERE A.ID>=B.START_ID AND A.ID<=B.END_ID;

111824 rows created.

Elapsed: 00:00:00.08

ZALBB · 发表于 2021-7-20 09:30

没数据，没版本信息，很难印证楼主的想法，

ZALBB · 发表于 2021-7-20 09:33

以B表的范围分区来作驱动，去对比A表的ID，不知效果如何？

sql_tigerliu · 发表于 2021-7-20 19:55

newkid 发表于 2021-7-18 20:35
你的想法非常好！我那些复杂做法原来都是想太多了，哈哈。create index a_idx on range_a(id );insert into ...

这个数据量用这个写法确实非常简单高效, 如果range_b表的数据量达到几百万(比如IP地址库), 这个时候这个写法的效率是不能接受的.