oracle递归with实现group by快速查找min/max值-凯发app官方网站

凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心

dingjun123的blog

凯发app官方网站首页　| 　博文目录　| 　关于我

dingjun123

  • 博客访问： 1156259
  • 博文数量： 166
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 3760
  • 用 户 组： 普通用户
  • 注册时间： 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递归with实现group by快速查找min/max值

分类： 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. --多执行几次
  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的效果。



阅读(313) | 评论(0) | 转发(0) |
0

上一篇：oracle cbo 默认选择率及其问题凯发app官方网站的解决方案_part4

下一篇：没有了

给主人留下些什么吧！~~
| | | | |

凯发app官方网站 copyright 2001-2010 chinaunix.net all rights reserved 北京皓辰网域网络信息技术有限公司. 凯发k8官网下载客户端中心的版权所有

感谢所有关心和支持过chinaunix的朋友们
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图