ORACLE递归with实现group by快速查找min/max值

980阅读　0评论2024-05-21　dingjun123
分类：Oracle

对于分组查找min/max，在MySQL里有index loose scan扫描，针对每个分组值，查找索引的leftmost或righmost行，
这样只需要扫描索引区间的端点值即可，实现快速索引扫描（这里指的是min/max查找，非INDEX FAST FULL SCAN)。


本文研究ORACLE快速索引扫描的方法以及如何实现类似MySQL的index loose scan让分组能够走索引快速扫描。

建表语句如下：

点击(此处)折叠或打开

  1. drop table group_tab;
  2. create table group_tab as select * from dba_objects;

  3.
  4. --多执行几次
  5. insert into group_tab select * from group_tab;
  6. commit;

  8. --建立索引：
  9. create index idx_group_tab on group_tab(owner,object_id);

  11. --为了演示，将owner改为not null
  12. alter table group_tab modify owner not null;

  14. --收集统计信息：
  15. exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'group_tab',no_invalidate=>false);

  17. 环境：
  18. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
  19. Version 19.3.0.0.0
总共有27个owner,总行数1164944，owner基数很小：

点击(此处)折叠或打开

  1. select count(distinct owner),count(*)
  2. from group_tab;

  4. COUNT(DISTINCTOWNER) COUNT(*)
  5. -------------------- ----------
  6.                   27 1164944

1.ORACLE里索引快速扫描(MIN/MAX)

1）在ORACLE里如果无条件，可以实现快速索引扫描。

SQL：

点击(此处)折叠或打开

  1. select min(owner)
  2. from group_tab;
无条件，只能针对前导列min/max，执行计划走INDEX FULL SCAN (MIN/MAX)

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 760541921

  5. --------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. --------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
  9. | 1 | SORT AGGREGATE | | 1 | 5 | | |
  10. | 2 | INDEX FULL SCAN (MIN/MAX)| IDX_GROUP_TAB | 1 | 5 | 3 (0)| 00:00:01 |
  11. --------------------------------------------------------------------------------------------
  12. Statistics
  13. ----------------------------------------------------------
  14.           1 recursive calls
  15.           0 db block gets
  16.           3 consistent gets
  17.           0 physical reads
  18.           0 redo size
  19.         559 bytes sent via SQL*Net to client
  20.         394 bytes received via SQL*Net from client
  21.           2 SQL*Net roundtrips to/from client
  22.           0 sorts (memory)
  23.           0 sorts (disk)
  24.           1 rows processed

2）ORACLE里索引前导列等值，可以对次列min/max快速扫描


SQL如下：

点击(此处)折叠或打开

  1. select min(object_id)
  2. from group_tab
  3. where owner = 'SYS';

  5. Elapsed: 00:00:00.01
可以看到执行计划走INDEX RANGE SCAN (MIN/MAX)，然后取FIRST ROW

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 3732729641

  5. ----------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. ----------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 |
  9. | 1 | SORT AGGREGATE | | 1 | 10 | | |
  10. | 2 | FIRST ROW | | 1 | 10 | 3 (0)| 00:00:01 |
  11. |* 3 | INDEX RANGE SCAN (MIN/MAX)| IDX_GROUP_TAB | 1 | 10 | 3 (0)| 00:00:01 |
  12. ----------------------------------------------------------------------------------------------

  14. Predicate Information (identified by operation id):
  15. ---------------------------------------------------

  17.    3 - access("OWNER"='SYS')

  19. Statistics
  20. ----------------------------------------------------------
  21.           1 recursive calls
  22.           0 db block gets
  23.           3 consistent gets
  24.           0 physical reads
  25.           0 redo size
  26.         556 bytes sent via SQL*Net to client
  27.         418 bytes received via SQL*Net from client
  28.           2 SQL*Net roundtrips to/from client
  29.           0 sorts (memory)
  30.           0 sorts (disk)
  31.           1 rows processed

Oracle数据库比较智能，比如三个列索引：

点击(此处)折叠或打开

  1. create index idx1_group_tab on group_tab(owner,object_id,object_name);

SQL如下：

点击(此处)折叠或打开

  1. select min(object_id)
  2. from group_tab
  3. where owner = 'SYS' and object_name like 'AB%';

看SQL条件：where owner = 'SYS' and object_name like 'AB%',索引中间列没有条件，前导列
owner有等值条件，也可以实现INDEX RANGE SCAN (MIN/MAX)，
看谓词是access("OWNER"='SYS')，filter("OBJECT_NAME" LIKE 'AB%')。


点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 4109515879

  5. -----------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. -----------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 |
  9. | 1 | SORT AGGREGATE | | 1 | 45 | | |
  10. | 2 | FIRST ROW | | 1 | 45 | 3 (0)| 00:00:01 |
  11. |* 3 | INDEX RANGE SCAN (MIN/MAX)| IDX1_GROUP_TAB | 1 | 45 | 3 (0)| 00:00:01 |
  12. -----------------------------------------------------------------------------------------------

  14. Predicate Information (identified by operation id):
  15. ---------------------------------------------------

  17.    3 - access("OWNER"='SYS')
  18.        filter("OBJECT_NAME" LIKE 'AB%')

  20. Statistics
  21. ----------------------------------------------------------
  22.           1 recursive calls
  23.           0 db block gets
  24.        6653 consistent gets
  25.        6694 physical reads
  26.           0 redo size
  27.         554 bytes sent via SQL*Net to client
  28.         445 bytes received via SQL*Net from client
  29.           2 SQL*Net roundtrips to/from client
  30.           0 sorts (memory)
  31.           0 sorts (disk)
  32.           1 rows processed
删除这个临时索引：

点击(此处)折叠或打开

  1. drop index idx1_group_tab;

3)在ORACLE里注意，不能同时查找min、max,在MySQL里可以同时查找

走不了索引快速扫描:


SQL如下：

点击(此处)折叠或打开

  1. select min(object_id),max(object_id)
  2. from group_tab
  3. where owner = 'SYS';
  4. Elapsed: 00:00:00.18
执行计划走 INDEX RANGE SCAN，不能快速扫描端点值，Oracle不能将min/max写在同一层，
这样不能实现min/max index scan：

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 2957380139

  5. -----------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. -----------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 1 | 10 | 141 (1)| 00:00:01 |
  9. | 1 | SORT AGGREGATE | | 1 | 10 | | |
  10. |* 2 | INDEX RANGE SCAN| IDX_GROUP_TAB | 43146 | 421K| 141 (1)| 00:00:01 |
  11. -----------------------------------------------------------------------------------

  13. Predicate Information (identified by operation id):
  14. ---------------------------------------------------
  15.    2 - access("OWNER"='SYS')
  16. Statistics
  17. ----------------------------------------------------------
  18.           0 recursive calls
  19.           0 db block gets
  20.        2356 consistent gets
  21.           0 physical reads
  22.           0 redo size
  23.         645 bytes sent via SQL*Net to client
  24.         433 bytes received via SQL*Net from client
  25.           2 SQL*Net roundtrips to/from client
  26.           0 sorts (memory)
  27.           0 sorts (disk)
  28.           1 rows processed

如果要同时查找，实现快速扫描端点值，可以用dual表+标量子查询：


点击(此处)折叠或打开

  1. select
  2. (select min(object_id)
  3. from group_tab
  4. where owner = 'SYS') min_object_id,
  5. (select max(object_id)
  6. from group_tab
  7. where owner = 'SYS') max_object_id
  8. from dual;

执行计划如下：

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 3276013224

  5. ----------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. ----------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 1 | | 8 (0)| 00:00:01 |
  9. | 1 | SORT AGGREGATE | | 1 | 10 | | |
  10. | 2 | FIRST ROW | | 1 | 10 | 3 (0)| 00:00:01 |
  11. |* 3 | INDEX RANGE SCAN (MIN/MAX)| IDX_GROUP_TAB | 1 | 10 | 3 (0)| 00:00:01 |
  12. | 4 | SORT AGGREGATE | | 1 | 10 | | |
  13. | 5 | FIRST ROW | | 1 | 10 | 3 (0)| 00:00:01 |
  14. |* 6 | INDEX RANGE SCAN (MIN/MAX)| IDX_GROUP_TAB | 1 | 10 | 3 (0)| 00:00:01 |
  15. | 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
  16. ----------------------------------------------------------------------------------------------

  18. Predicate Information (identified by operation id):
  19. ---------------------------------------------------
  20.    3 - access("OWNER"='SYS')
  21.    6 - access("OWNER"='SYS')
  22. Statistics
  23. ----------------------------------------------------------
  24.           0 recursive calls
  25.           0 db block gets
  26.           6 consistent gets
  27.           0 physical reads
  28.           0 redo size
  29.         643 bytes sent via SQL*Net to client
  30.         525 bytes received via SQL*Net from client
  31.           2 SQL*Net roundtrips to/from client
  32.           0 sorts (memory)
  33.           0 sorts (disk)
  34.           1 rows processed

在MySQL里没有问题：
语句如下：

点击(此处)折叠或打开

  1. select gender,min(emp_no),max(emp_no)
  2. from emp1
  3. group by gender;

Extra:Using index for group-by ,执行计划如下：


点击(此处)折叠或打开

  1. +----+-------------+-------+------------+-------+-----------------------------------------------------------------------+-----------+---------+------+------+----------+--------------------------+
  2. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  3. +----+-------------+-------+------------+-------+-----------------------------------------------------------------------+-----------+---------+------+------+----------+--------------------------+
  4. | 1 | SIMPLE | emp1 | NULL | range | idx2_emp1,idx_emp1_skip,idx4_emp1_loose,idx_emp1_date2,idx_emp1_date1 | idx2_emp1 | 1 | NULL | 3 | 100.00 | Using index for group-by |
  5. +----+-------------+-------+------------+-------+-----------------------------------------------------------------------+-----------+---------+------+------+----------+--------------------------+
  6. 1 row in set, 1 warning (0.01 sec)

树形执行计划：Covering index skip scan for grouping


点击(此处)折叠或打开

  1. *************************** 1. row ***************************
  2. EXPLAIN: -> Covering index skip scan for grouping on emp1 using idx2_emp1 (cost=4.90 rows=3) (actual time=3.152..3.167 rows=2 loops=1)



2.ORACLE里分组查找min/max,不能实现MySQL loose scan


SQL如下：

点击(此处)折叠或打开

  1. select owner,max(object_id)
  2. from group_tab
  3. group by owner;

在ORACLE里，直接分组查询，走不了快速索引扫描INDEX RANGE SCAN (MIN/MAX)，执行计划
走INDEX FAST FULL SCAN，逻辑读3785，索引扫描行数1164K行。

点击(此处)折叠或打开

  1. PLAN_TABLE_OUTPUT
  2. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  3. SQL_ID 0a47gjns4huux, child number 2
  4. -------------------------------------
  5. select owner,max(object_id) from group_tab group by owner

  7. Plan hash value: 1341240122

  9. ----------------------------------------------------------------------------------------------------------------------------
  10. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
  11. ----------------------------------------------------------------------------------------------------------------------------
  12. | 0 | SELECT STATEMENT | | 1 | | 27 |00:00:00.21 | 3785 | | | |
  13. | 1 | HASH GROUP BY | | 1 | 27 | 27 |00:00:00.21 | 3785 | 1010K| 1010K| 2561K (0)|
  14. | 2 | INDEX FAST FULL SCAN| IDX_GROUP_TAB | 1 | 1164K| 1164K|00:00:00.09 | 3785 | | | |
  15. ----------------------------------------------------------------------------------------------------------------------------
强制skip scan走 INDEX FULL SCAN，也是一样，走不了快速索引扫描。       

点击(此处)折叠或打开

  1. select/*+index_ss(a)*/ owner,max(object_id)
  2. from group_tab a
  3. group by owner;
  4. 27 rows selected.

  6. PLAN_TABLE_OUTPUT
  7. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  8. SQL_ID 7n24nwtgzssdb, child number 2
  9. -------------------------------------
  10. select/*+index_ss(a)*/ owner,max(object_id) from group_tab a group by
  11. owner

  13. Plan hash value: 202550610

  15. ------------------------------------------------------------------------------------------------
  16. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  17. ------------------------------------------------------------------------------------------------
  18. | 0 | SELECT STATEMENT | | 1 | | 27 |00:00:00.50 | 3733 |
  19. | 1 | SORT GROUP BY NOSORT| | 1 | 27 | 27 |00:00:00.50 | 3733 |
  20. | 2 | INDEX SKIP SCAN | IDX_GROUP_TAB | 1 | 1164K| 1164K|00:00:00.32 | 3733 |
  21. ------------------------------------------------------------------------------------------------

MySQL对分组可以实现index loose scan,如下所示：
可以看到执行计划Extra:Using index for group-by,树形计划显示Covering index skip scan for
grouping （ORACLE没有实现这种）
而且MySQL可以将min.max写在一层（ORACLE不可以）。
都是覆盖索引扫描才可以：

点击(此处)折叠或打开

  1. explain select gender,max(first_name),min(first_name)
  2.     -> from emp1
  3.     -> group by gender
  4.     -> ;
  5. +----+-------------+-------+------------+-------+-----------------------------------------------------------------------+-----------------+---------+------+------+----------+--------------------------+
  6. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  7. +----+-------------+-------+------------+-------+-----------------------------------------------------------------------+-----------------+---------+------+------+----------+--------------------------+
  8. | 1 | SIMPLE | emp1 | NULL | range | idx2_emp1,idx_emp1_skip,idx4_emp1_loose,idx_emp1_date2,idx_emp1_date1 | idx4_emp1_loose | 1 | NULL | 3 | 100.00 | Using index for group-by |
  9. +----+-------------+-------+------------+-------+-----------------------------------------------------------------------+-----------------+---------+------+------+----------+--------------------------+
  10. 1 row in set, 1 warning (0.01 sec)


点击(此处)折叠或打开

  1. explain analyze
  2.     -> select gender,max(first_name),min(first_name)
  3.     -> from emp1
  4.     -> group by gender
  5.     -> \G
  6. *************************** 1. row ***************************
  7. EXPLAIN: -> Covering index skip scan for grouping on emp1 using idx4_emp1_loose (cost=5.20 rows=3) (actual time=3.376..3.399 rows=2 loops=1)

3.ORACLE使用递归with实现MySQL loose scan快速分组

查找min/max 


GROUP_TAB有只有27个owner，基数很小：


点击(此处)折叠或打开

  1. select count(distinct owner),count(*) from group_tab;

  3. COUNT(DISTINCTOWNER) COUNT(*)
  4. -------------------- ----------
  5.                   27 1164944

思考：如果能将27个owner,逐条取出来并且按照owner = :owner赋值，然后查找min/max,
这样就可以实现min/max索引快速查找。


1）首先要找出这个27个owner,不能直接distinct,那样也不能快速查找，很显然owner列是索引
前导列，可以用递归的方法，锚点选择min(owner),然后递归查找比前面所有owner大的min(
owner),这样类似loose scan,快速找到27个owner。

2）临时表现在是27个owner行，可以逐行传递给表group_tab,查找min/max,可以利用索引
的min/max扫描， 一般情况下，这种用标量子查询效率较高，因为owner的基数小，
标量子查询循环次数少。
   
递归with快速找到27个不同的owner值，注意owner IS NOT NULL
使用递归with如下：

点击(此处)折叠或打开

  1. WITH owner_cte(owner) AS (
  2.   SELECT MIN(owner) FROM group_tab
  3.   UNION ALL
  4.   SELECT (SELECT MIN(owner) FROM group_tab WHERE owner > c.owner)
  5.   FROM owner_cte c
  6.   WHERE c.owner IS NOT NULL
  7. )
  8. --然后使用标量子查询快速查找max值
  9. SELECT c.owner,
  10.    (SELECT MAX(g.object_id) FROM group_tab g WHERE g.owner = c.owner) max_object_id
  11. from owner_cte c
  12. where c.owner IS NOT NULL;

执行计划貌似不符合预期，标量子查询竟然与递归with的结果做了HASH JOIN，标量子查询没有
走类似FILTER的计划，这样和直接分组没有啥区别，逻辑读3847：

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 1639639195

  5. ------------------------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. ------------------------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 2 | 290 | 1127 (9)| 00:00:01 |
  9. |* 1 | HASH JOIN OUTER | | 2 | 290 | 1127 (9)| 00:00:01 |
  10. |* 2 | VIEW | | 2 | 132 | 27 (0)| 00:00:01 |
  11. | 3 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
  12. | 4 | SORT AGGREGATE | | 1 | 5 | | |
  13. | 5 | INDEX FULL SCAN (MIN/MAX) | IDX_GROUP_TAB | 1 | 5 | 3 (0)| 00:00:01 |
  14. | 6 | SORT AGGREGATE | | 1 | 5 | | |
  15. | 7 | FIRST ROW | | 1 | 5 | 3 (0)| 00:00:01 |
  16. |* 8 | INDEX RANGE SCAN (MIN/MAX) | IDX_GROUP_TAB | 1 | 5 | 3 (0)| 00:00:01 |
  17. |* 9 | RECURSIVE WITH PUMP | | | | | |
  18. | 10 | VIEW | VW_SSQ_1 | 27 | 2133 | 1100 (9)| 00:00:01 |
  19. | 11 | HASH GROUP BY | | 27 | 270 | 1100 (9)| 00:00:01 |
  20. | 12 | INDEX FAST FULL SCAN | IDX_GROUP_TAB | 1164K| 11M| 1019 (2)| 00:00:01 |
  21. ------------------------------------------------------------------------------------------------------------

  23. Predicate Information (identified by operation id):
  24. ---------------------------------------------------

  26.    1 - access("ITEM_0"(+)="C"."OWNER")
  27.    2 - filter("C"."OWNER" IS NOT NULL)
  28.    8 - access("OWNER">:B1)
  29.    9 - filter("C"."OWNER" IS NOT NULL)


  32. Statistics
  33. ----------------------------------------------------------
  34.           0 recursive calls
  35.           0 db block gets
  36.        3847 consistent gets
  37.           0 physical reads
  38.           0 redo size
  39.        1319 bytes sent via SQL*Net to client
  40.         713 bytes received via SQL*Net from client
  41.           3 SQL*Net roundtrips to/from client
  42.          29 sorts (memory)
  43.           0 sorts (disk)
  44.          27 rows processed
很显然，在19C里标量子查询这种写法，直接unnest了，这是12C开始的新特性，貌似也不管
COST，这里COST=1127，下面不让unnest cost其实更小，只有30。

知道了是查询转换有问题，那么不查询转换即可，对标量子查询使用no_unnest hints：


点击(此处)折叠或打开

  1. WITH owner_cte(owner) AS (
  2.   SELECT MIN(owner) FROM group_tab
  3.   UNION ALL
  4.   SELECT (SELECT MIN(owner) FROM group_tab WHERE owner > c.owner)
  5.   FROM owner_cte c
  6.   WHERE c.owner IS NOT NULL
  7. )
  8. --标量子查询使用no_unnest hints
  9. SELECT c.owner,
  10.    (SELECT/*+no_unnest*/ MAX(g.object_id) FROM group_tab g WHERE g.owner = c.owner) max_object_id
  11. from owner_cte c
  12. where c.owner IS NOT NULL;

  14. Elapsed: 00:00:00.00
现在的执行计划符合预期,走类似FILTER的执行计划，标量子查询在上面（这是和一般执行计划
显示顺序不一样，标量子查询执行计划比较特殊，在上面，同等级，但是是被下面的结果驱动），

这样标量子查询类似循环，外面行每传递一个owner,执行标量子查询，这样可以转为27个等值
条件分组，实现快速min/max查找


逻辑读从3847减少到120，Cost也只有30，执行计划里都是利用索引MIN/MAX快速查找：

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 135935541

  5. -----------------------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. -----------------------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 2 | 132 | 30 (0)| 00:00:01 |
  9. | 1 | SORT AGGREGATE | | 1 | 10 | | |
  10. | 2 | FIRST ROW | | 1 | 10 | 3 (0)| 00:00:01 |
  11. |* 3 | INDEX RANGE SCAN (MIN/MAX) | IDX_GROUP_TAB | 1 | 10 | 3 (0)| 00:00:01 |
  12. |* 4 | VIEW | | 2 | 132 | 27 (0)| 00:00:01 |
  13. | 5 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
  14. | 6 | SORT AGGREGATE | | 1 | 5 | | |
  15. | 7 | INDEX FULL SCAN (MIN/MAX) | IDX_GROUP_TAB | 1 | 5 | 3 (0)| 00:00:01 |
  16. | 8 | SORT AGGREGATE | | 1 | 5 | | |
  17. | 9 | FIRST ROW | | 1 | 5 | 3 (0)| 00:00:01 |
  18. |* 10 | INDEX RANGE SCAN (MIN/MAX) | IDX_GROUP_TAB | 1 | 5 | 3 (0)| 00:00:01 |
  19. |* 11 | RECURSIVE WITH PUMP | | | | | |
  20. -----------------------------------------------------------------------------------------------------------

  22. Predicate Information (identified by operation id):
  23. ---------------------------------------------------

  25.    3 - access("G"."OWNER"=:B1)
  26.    4 - filter("C"."OWNER" IS NOT NULL)
  27.   10 - access("OWNER">:B1)
  28.   11 - filter("C"."OWNER" IS NOT NULL)


  31. Statistics
  32. ----------------------------------------------------------
  33.           0 recursive calls
  34.           0 db block gets
  35.         120 consistent gets
  36.           0 physical reads
  37.           0 redo size
  38.        1319 bytes sent via SQL*Net to client
  39.         727 bytes received via SQL*Net from client
  40.           3 SQL*Net roundtrips to/from client
  41.          29 sorts (memory)
  42.           0 sorts (disk)
  43.          27 rows processed

如果写成JOIN也实现不了快速查找：


点击(此处)折叠或打开

  1. WITH owner_cte(owner) AS (
  2.   SELECT MIN(owner) FROM group_tab
  3.   UNION ALL
  4.   SELECT (SELECT MIN(owner) FROM group_tab WHERE owner > c.owner)
  5.   FROM owner_cte c
  6.   WHERE c.owner IS NOT NULL
  7. )
  8. SELECT g.owner, MAX(g.object_id) AS max_object_id
  9. FROM owner_cte c
  10. JOIN group_tab g ON c.owner = g.owner
  11. where g.owner is not null
  12. GROUP BY g.owner;
  13. Elapsed: 00:00:00.37
走NESTED LOOPS，按照原来的想法应该是走INDEX RANGE SCAN (MIN/MAX)，结果走的是
INDEX RANGE SCAN，不符合预期：

点击(此处)折叠或打开

  1. Execution Plan
  2. ----------------------------------------------------------
  3. Plan hash value: 2043639768

  5. -------------------------------------------------------------------------------------------------------------
  6. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  7. -------------------------------------------------------------------------------------------------------------
  8. | 0 | SELECT STATEMENT | | 27 | 2052 | 312 (3)| 00:00:01 |
  9. | 1 | HASH GROUP BY | | 27 | 2052 | 312 (3)| 00:00:01 |
  10. | 2 | NESTED LOOPS | | 86292 | 6404K| 306 (1)| 00:00:01 |
  11. | 3 | VIEW | | 2 | 132 | 27 (0)| 00:00:01 |
  12. | 4 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
  13. | 5 | SORT AGGREGATE | | 1 | 5 | | |
  14. | 6 | INDEX FULL SCAN (MIN/MAX) | IDX_GROUP_TAB | 1 | 5 | 3 (0)| 00:00:01 |
  15. | 7 | SORT AGGREGATE | | 1 | 5 | | |
  16. | 8 | FIRST ROW | | 1 | 5 | 3 (0)| 00:00:01 |
  17. |* 9 | INDEX RANGE SCAN (MIN/MAX) | IDX_GROUP_TAB | 1 | 5 | 3 (0)| 00:00:01 |
  18. |* 10 | RECURSIVE WITH PUMP | | | | | |
  19. |* 11 | INDEX RANGE SCAN | IDX_GROUP_TAB | 43146 | 421K| 140 (1)| 00:00:01 |
  20. -------------------------------------------------------------------------------------------------------------

  22. Predicate Information (identified by operation id):
  23. ---------------------------------------------------

  25.    9 - access("OWNER">:B1)
  26.   10 - filter("C"."OWNER" IS NOT NULL)
  27.   11 - access("C"."OWNER"="G"."OWNER")


  30. Statistics
  31. ----------------------------------------------------------
  32.           0 recursive calls
  33.           0 db block gets
  34.        3829 consistent gets
  35.           0 physical reads
  36.           0 redo size
  37.        1319 bytes sent via SQL*Net to client
  38.         718 bytes received via SQL*Net from client
  39.           3 SQL*Net roundtrips to/from client
  40.          29 sorts (memory)
  41.           0 sorts (disk)
  42.          27 rows processed

总结：
本文主要研究ORACLE里的索引快速扫描min/max的方法，以及使用递归with查找前导列
distinct值+标量子查询（12以上要有no_unnest hints)实现MySQL group by 走
index loose scan的效果。



上一篇：ORACLE CBO 默认选择率及其问题解决方案_PART4
下一篇：MySQL Subquery Cache vs Oracle Subquery Cache子查询缓存_MySQL部分_PART1