Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1200726
  • 博文数量: 166
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3765
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc

文章分类

全部博文(166)

文章存档

2024年(21)

2023年(28)

2022年(43)

2020年(62)

2014年(3)

2013年(9)

分类: Oracle

2024-05-21 09:17:12

对于分组查找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. insert into group_tab select * from group_tab;
  5. commit;

  6. --建立索引:
  7. create index idx_group_tab on group_tab(owner,object_id);

  8. --为了演示,将owner改为not null
  9. alter table group_tab modify owner not null;

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

  12. 环境:
  13. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
  14. Version 19.3.0.0.0
总共有27个owner,总行数1164944,owner基数很小:

点击(此处)折叠或打开

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

  3. COUNT(DISTINCTOWNER) COUNT(*)
  4. -------------------- ----------
  5.                   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

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

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


SQL如下:

点击(此处)折叠或打开

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

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

点击(此处)折叠或打开

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

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

  12. Predicate Information (identified by operation id):
  13. ---------------------------------------------------

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

  15. Statistics
  16. ----------------------------------------------------------
  17.           1 recursive calls
  18.           0 db block gets
  19.           3 consistent gets
  20.           0 physical reads
  21.           0 redo size
  22.         556 bytes sent via SQL*Net to client
  23.         418 bytes received via SQL*Net from client
  24.           2 SQL*Net roundtrips to/from client
  25.           0 sorts (memory)
  26.           0 sorts (disk)
  27.           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

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

  12. Predicate Information (identified by operation id):
  13. ---------------------------------------------------

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

  16. Statistics
  17. ----------------------------------------------------------
  18.           1 recursive calls
  19.           0 db block gets
  20.        6653 consistent gets
  21.        6694 physical reads
  22.           0 redo size
  23.         554 bytes sent via SQL*Net to client
  24.         445 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
删除这个临时索引:

点击(此处)折叠或打开

  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

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

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

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

  16. Predicate Information (identified by operation id):
  17. ---------------------------------------------------
  18.    3 - access("OWNER"='SYS')
  19.    6 - access("OWNER"='SYS')
  20. Statistics
  21. ----------------------------------------------------------
  22.           0 recursive calls
  23.           0 db block gets
  24.           6 consistent gets
  25.           0 physical reads
  26.           0 redo size
  27.         643 bytes sent via SQL*Net to client
  28.         525 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

在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

  6. Plan hash value: 1341240122

  7. ----------------------------------------------------------------------------------------------------------------------------
  8. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
  9. ----------------------------------------------------------------------------------------------------------------------------
  10. | 0 | SELECT STATEMENT | | 1 | | 27 |00:00:00.21 | 3785 | | | |
  11. | 1 | HASH GROUP BY | | 1 | 27 | 27 |00:00:00.21 | 3785 | 1010K| 1010K| 2561K (0)|
  12. | 2 | INDEX FAST FULL SCAN| IDX_GROUP_TAB | 1 | 1164K| 1164K|00:00:00.09 | 3785 | | | |
  13. ----------------------------------------------------------------------------------------------------------------------------
强制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.

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

  11. Plan hash value: 202550610

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

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;

  2. COUNT(DISTINCTOWNER) COUNT(*)
  3. -------------------- ----------
  4.                   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

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

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

  23.    1 - access("ITEM_0"(+)="C"."OWNER")
  24.    2 - filter("C"."OWNER" IS NOT NULL)
  25.    8 - access("OWNER">:B1)
  26.    9 - filter("C"."OWNER" IS NOT NULL)


  27. Statistics
  28. ----------------------------------------------------------
  29.           0 recursive calls
  30.           0 db block gets
  31.        3847 consistent gets
  32.           0 physical reads
  33.           0 redo size
  34.        1319 bytes sent via SQL*Net to client
  35.         713 bytes received via SQL*Net from client
  36.           3 SQL*Net roundtrips to/from client
  37.          29 sorts (memory)
  38.           0 sorts (disk)
  39.          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;

  13. 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

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

  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------

  22.    3 - access("G"."OWNER"=:B1)
  23.    4 - filter("C"."OWNER" IS NOT NULL)
  24.   10 - access("OWNER">:B1)
  25.   11 - filter("C"."OWNER" IS NOT NULL)


  26. Statistics
  27. ----------------------------------------------------------
  28.           0 recursive calls
  29.           0 db block gets
  30.         120 consistent gets
  31.           0 physical reads
  32.           0 redo size
  33.        1319 bytes sent via SQL*Net to client
  34.         727 bytes received via SQL*Net from client
  35.           3 SQL*Net roundtrips to/from client
  36.          29 sorts (memory)
  37.           0 sorts (disk)
  38.          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

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

  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------

  22.    9 - access("OWNER">:B1)
  23.   10 - filter("C"."OWNER" IS NOT NULL)
  24.   11 - access("C"."OWNER"="G"."OWNER")


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

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



阅读(373) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~