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

newkid

jihuyao 发表于 2021-7-23 12:39
I was thinking about the logical flow in procedure way for merge join.  Clearly if a_id is PK and b_ ...

普通索引已经建了，MERGE JOIN就是上面测过的要跑半分钟。

现在测试唯一索引：
drop index idx_range_b_0;
create unique index idx_range_b_0 on range_b(start_id,end_id);

A表原来是随机数，有重复，重新生成数据：
truncate table range_a;

insert into range_a(id)
select level*120 from dual connect by level<=200000;


drop index a_idx;
create unique index a_idx on range_a(id);


再来测试MERGE JOIN计划：

insert into range_result(id,type)
SELECT ID, TYPE
FROM range_a A,range_b B
WHERE A.ID>=B.START_ID AND A.ID<=B.END_ID;

计划和原来一样，时间更长：

117333 rows created.

Elapsed: 00:00:49.29

Plan hash value: 4188551763

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |    96M|  1740M|  1437  (70)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | RANGE_RESULT |       |       |            |          |
|   2 |   MERGE JOIN             |              |    96M|  1740M|  1437  (70)| 00:00:01 |
|   3 |    SORT JOIN             |              |   200K|  1171K|   443   (1)| 00:00:01 |
|   4 |     INDEX FULL SCAN      | A_IDX        |   200K|  1171K|   443   (1)| 00:00:01 |
|*  5 |    FILTER                |              |       |       |            |          |
|*  6 |     SORT JOIN            |              |  2000 | 26000 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL   | RANGE_B      |  2000 | 26000 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   5 - filter("A"."ID"<="B"."END_ID")
   6 - access(INTERNAL_FUNCTION("A"."ID")>=INTERNAL_FUNCTION("B"."START_ID"))
       filter(INTERNAL_FUNCTION("A"."ID")>=INTERNAL_FUNCTION("B"."START_ID"))
      

ZALBB

newkid 发表于 2021-7-23 21:21
测试数据是刘兄设计的，生成数据的脚本在20楼。

好的，谢谢，我也测试下，

jihuyao

The unique index on start and end can not ensure B IDs are not overlapped, eg 1, 10 and 2,, 20.  This results lines crossed when being joined even though after sorted.  This can be clearly seen given a traditional merge join, eg, t1 has rows 1,3,5 and t2 has rows 1,5,9 (two joined lines never cross between t1 and t2 and therefore inner loop needs not start from beginning again every time with outer loop going on in comparison with nested loop join).

newkid

jihuyao 发表于 2021-7-27 15:52
The unique index on start and end can not ensure B IDs are not overlapped, eg 1, 10 and 2,, 20.  Thi ...

没有任何一种约束可以实现这种范围的互不覆盖。所以你说的这个算法也不可能被SQL引擎采用。
但是既然数据有索引，已经排过序，反复查找也是很高效的，就像 nested loop 计划所示的那样。

newkid

继续玩，把 PL/SQL 函数改造成SQL MACRO(本来是20C功能，现在19.10也支持)
从原理上说，这是SQL TEXT的替换，不会有上下文切换的开销。

原来标量子查询的做法：
insert into range_result(id,type)
select *
from
(
select id,
    (select case when start_id<=a.id then type else null end as type
        from
        (select /*+ index(range_b idx_range_b_1) */ type,start_id
         from range_b
         where end_id>=a.id
          order by end_id
        ) where rownum=1
    ) as type
from range_a a
) where type is not null;


112086 rows created.

Elapsed: 00:00:01.30


改成SQL MACRO函数：
create or replace function f_get_type (p_id in number) return varchar2
  SQL_MACRO  ------ 关键在这里
as
  v_ret number;
begin
  return
  'select case when start_id<=p_id then type else null end as type
        from
        (select /*+ index(range_b idx_range_b_1) */ type,start_id
         from range_b
         where end_id>=p_id
          order by end_id
        ) where rownum=1';
end f_get_type;
/

----拿出去之后，主查询变得简洁了一些，而且代码也可以在别的查询里重用：
insert into range_result(id,type)
select id,type
from
(
select *
from range_a a
    ,lateral(select * from f_get_type(a.id))
) where type is not null
/

112086 rows created.

Elapsed: 00:00:01.14

jihuyao

没有任何一种约束可以实现这种范围的互不覆盖。所以你说的这个算法也不可能被SQL引擎采用。  That is true.  I am quite interested in a specific case, eg, t1 ID(1,1,2,5,...) t2 ID(1,1,3,5,1

jihuyao

没有任何一种约束可以实现这种范围的互不覆盖。所以你说的这个算法也不可能被SQL引擎采用。 That is true.  It has to be a specific test case other than present one in order to see if there are different paths implemented.

wolfop

我日，18年前的坟斗给挖了，出来。当年10GR2的优化器、执行引擎和现在19C的优化器、执行引擎没法比的。

newkid

冤有头债有主，这个坟可不是我挖的，是老刘没法回帖向我求助，我才陪他玩玩。
在我印象中，10g已经是很厉害的存在了，现在还有人用9i呢！我想起地下室里还有一台2003年的win2000笔记本，上面装的是10g, 于是立马挖出来玩。电池坏了，电源还在，难得还能开机，一看内存512M,(当时可是顶配)，于是把10g服务启动。但是所有浏览器都上不了现代化的网站了，新浏览器又装不上去，只好用优盘把SQL拷贝到本地执行，结果如下：
原SQL: 2分45秒
标量子查询：10.2.0.3 不支持多层次写法
我构造的hash join方法：1.5秒
加hint: use_nl(b a) 1.3秒

所以我说的没错，10g已经很厉害了，当年发生这个问题，肯定是哪里不对劲。

sql_tigerliu

我猜想应该是表大小与内存的原因, 我模拟出来的表占用空间不大,完全可以放到内存里. 如果现实中两个表比较大, 内存放不下, 那么merge join就比较慢了.