Chinaunix首页 | 论坛 | 博客

dingjun123的blog

首页　| 　博文目录　| 　关于我

dingjun123

  • 博客访问： 1172356
  • 博文数量： 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）

我的朋友
最近访客
推荐博文
相关博文
快速从一个表里随机获取N行数据的方法

分类： Oracle

2024-05-06 15:29:59

  本文主要讲解如何从一个表里快速随机获取前N行的方法，用较少IO，利用索引的快速扫描，快速从表里获取随机的前N行数据。 
 
建表语句如下：

点击(此处)折叠或打开

  1. 建表语句如下：
  2.  drop table t;
  3.  create table t(id number,name varchar2(1000),ext varchar2(1000));
  4.  insert into t
  5.  select level,rpad('t',500,'x')||level,rpad('t',500,'x')||level
  6.  from dual
  7.  connect by level<=300000;
  8.  commit;
  9.  create index idx_t on t(id);
  10.  
  11.  exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'t',no_invalidate=>false);
  12. t表总共有30w行：
     select count(*) from t;


      COUNT(*)
    ----------
        300000
         

1.原始方法，全表扫描+排序，随机取10条记录

全表扫描，因为是随机的，所以要读完所有30w行再随机排序取10行，效率非常低
SQL如下：

点击(此处)折叠或打开

  1. select * from (
  2. select * from t order by dbms_random.value
  3. ) where rownum <= 10;
执行计划如下：

点击(此处)折叠或打开

  1. Plan hash value: 3299198703

  3. ------------------------------------------------------------------------------------------------------------------------------
  4. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
  5. ------------------------------------------------------------------------------------------------------------------------------
  6. | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.81 | 43171 | 22014 | | | |
  7. |* 1 | COUNT STOPKEY | | 1 | | 10 |00:00:00.81 | 43171 | 22014 | | | |
  8. | 2 | VIEW | | 1 | 300K| 10 |00:00:00.81 | 43171 | 22014 | | | |
  9. |* 3 | SORT ORDER BY STOPKEY| | 1 | 300K| 10 |00:00:00.81 | 43171 | 22014 | 106K| 106K|96256 (0)|
  10. | 4 | TABLE ACCESS FULL | T | 1 | 300K| 300K|00:00:00.18 | 43171 | 22014 | | | |
  11. ------------------------------------------------------------------------------------------------------------------------------

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

  16.    1 - filter(ROWNUM<=10)
  17.    3 - filter(ROWNUM<=10)
通过执行计划可以看到ID=4，读了表T的全部数据300K行，然后排序取10行。耗时0.81s,逻辑读43171，从执行计划看，主要耗时在排序上，
因为是对30w行随机排序，所以效率很低，后续优化要考虑避免扫描全量数据以及避免全量数据的随机排序。

2.改进方法，利用覆盖索引提高效率

因为id有索引，索引比表小，可以采用覆盖索引，先从索引里随机获取前10行，然后再到表里查。

可以从下面看到IDX_T索引只有6M，比表345M少很多：

点击(此处)折叠或打开

  1. OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_MB INIEXT MAXEXT
  2. ---------- ------------------------------ ------------------ ---------- ---------- ----------
  3. DINGJUN123 T TABLE 345 65536 1048576
  4. DINGJUN123 IDX_T INDEX 6 65536 1048576
为了能够走覆盖索引，将id改为not null:

点击(此处)折叠或打开

  1. alter table t modify id not null;
SQL如下：

点击(此处)折叠或打开

  1. select * from t where id in (
  2. select id
  3. from(
  4. select id from t order by dbms_random.value
  5. ) where rownum <= 10
  6. );
执行计划如下：

点击(此处)折叠或打开

  1. Plan hash value: 753993260

  3. ---------------------------------------------------------------------------------------------------------------------------------------
  4. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
  5. ---------------------------------------------------------------------------------------------------------------------------------------
  6. | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.41 | 699 | 5 | | | |
  7. | 1 | NESTED LOOPS | | 1 | | 10 |00:00:00.41 | 699 | 5 | | | |
  8. | 2 | NESTED LOOPS | | 1 | 10 | 10 |00:00:00.41 | 689 | 0 | | | |
  9. | 3 | VIEW | VW_NSO_1 | 1 | 10 | 10 |00:00:00.41 | 676 | 0 | | | |
  10. | 4 | HASH UNIQUE | | 1 | 10 | 10 |00:00:00.41 | 676 | 0 | 1397K| 1397K| 1259K (0)|
  11. |* 5 | COUNT STOPKEY | | 1 | | 10 |00:00:00.41 | 676 | 0 | | | |
  12. | 6 | VIEW | | 1 | 300K| 10 |00:00:00.41 | 676 | 0 | | | |
  13. |* 7 | SORT ORDER BY STOPKEY | | 1 | 300K| 10 |00:00:00.41 | 676 | 0 | 2048 | 2048 | 2048 (0)|
  14. | 8 | INDEX FAST FULL SCAN | IDX_T | 1 | 300K| 300K|00:00:00.03 | 676 | 0 | | | |
  15. |* 9 | INDEX RANGE SCAN | IDX_T | 10 | 1 | 10 |00:00:00.01 | 13 | 0 | | | |
  16. | 10 | TABLE ACCESS BY INDEX ROWID| T | 10 | 1 | 10 |00:00:00.01 | 10 | 5 | | | |
  17. ---------------------------------------------------------------------------------------------------------------------------------------

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

  22.    5 - filter(ROWNUM<=10)
  23.    7 - filter(ROWNUM<=10)
  24.    9 - access("ID"="ID")
通过覆盖索引，0.41s,逻辑读从4.3w降低到676，主要耗时还是在随机排序上（占95%以上）。

3.使用索引min/max快速扫描构造随机数提高效率

优化主要集中在下面两点：
1）怎么避免扫描全量数据
2）怎么避免对全量数据随机排序
避免扫描全量数据，必须构造一个ID值，从此ID处开始扫描，然后用ID排序，则可以消除以上两点。方法如下：

索引的min，max查询效率很高（ORACLE不能写在同一个SELECT里否则不能用快速扫描），只需要扫描左右侧索引NO.1叶子节点，可以实现快速扫描，
然后利用floor(tmp.min_id + (tmp.max_id - tmp.min_id + 1) * dbms_random.value) 构造随机id,然后与原表关联，利用索引列id排序，
因为是索引列，可以利用索引消除排序，然后取100条，为了防止断号（因为取10条的话，这个id是连续的，也不够连续，如果id本身不是连续的，
也可能取不到10条），先取100条，然后从100条里随机取10条，比全扫描效率高很多

SQL如下：

点击(此处)折叠或打开

  1. select *
  2.   from t
  3.  where id in
  4.        (select id
  5.           from (select id
  6.                   from (select id
  7.                           from (select id
  8.                                   from t
  9.                                  where t.id >=
  10.                                        (select floor(tmp.min_id +
  11.                                                      (tmp.max_id - tmp.min_id + 1) *
  12.                                                      dbms_random.value) id
  13.                                           from (select (select max(id) from t) max_id,
  14.                                                        (select min(id) from t) min_id
  15.                                                   from dual) tmp)
  16.                                  order by t.id)
  17.                          where rownum <= 100)
  18.                  order by dbms_random.value)
  19.          where rownum <= 10);
执行计划如下：

点击(此处)折叠或打开

  1. Plan hash value: 72274151

  3. -----------------------------------------------------------------------------------------------------------------------------------------
  4. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
  5. -----------------------------------------------------------------------------------------------------------------------------------------
  6. | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 30 | | | |
  7. | 1 | NESTED LOOPS | | 1 | | 10 |00:00:00.01 | 30 | | | |
  8. | 2 | NESTED LOOPS | | 1 | 10 | 10 |00:00:00.01 | 20 | | | |
  9. | 3 | VIEW | VW_NSO_1 | 1 | 10 | 10 |00:00:00.01 | 7 | | | |
  10. | 4 | HASH UNIQUE | | 1 | 10 | 10 |00:00:00.01 | 7 | 1397K| 1397K| 847K (0)|
  11. |* 5 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 7 | | | |
  12. | 6 | VIEW | | 1 | 100 | 10 |00:00:00.01 | 7 | | | |
  13. |* 7 | SORT ORDER BY STOPKEY | | 1 | 100 | 10 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
  14. | 8 | VIEW | | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
  15. |* 9 | COUNT STOPKEY | | 1 | | 100 |00:00:00.01 | 7 | | | |
  16. | 10 | VIEW | | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
  17. |* 11 | INDEX RANGE SCAN | IDX_T | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
  18. | 12 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
  19. | 13 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
  20. | 14 | INDEX FULL SCAN (MIN/MAX) | IDX_T | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
  21. | 15 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
  22. | 16 | INDEX FULL SCAN (MIN/MAX)| IDX_T | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
  23. |* 17 | INDEX RANGE SCAN | IDX_T | 10 | 1 | 10 |00:00:00.01 | 13 | | | |
  24. | 18 | TABLE ACCESS BY INDEX ROWID | T | 10 | 1 | 10 |00:00:00.01 | 10 | | | |
  25. -----------------------------------------------------------------------------------------------------------------------------------------

  27. Predicate Information (identified by operation id):
  28. ---------------------------------------------------

  30.    5 - filter(ROWNUM<=10)
  31.    7 - filter(ROWNUM<=10)
  32.    9 - filter(ROWNUM<=100)
  33.   11 - access("T"."ID">=)
  34.   17 - access("ID"="ID")
从执行计划看，改进的SQL逻辑读只需要30，执行时间0.01s。

4)使用索引min/max+递归with构造前N个随机数
和3类似，只不过这里使用递归with,先构造一个随机id,然后递归，取100条，然后随机排序后取10条。
如下所示：
SQL如下：

点击(此处)折叠或打开

  1. with tmp(rn,id) as
  2. (
  3. select 1 rn,floor(tmp.min_id +(tmp.max_id - tmp.min_id + 1) * dbms_random.value) id
  4.         from (select (select max(id) from t) max_id,
  5.                       (select min(id) from t) min_id
  6.                      from dual) tmp
  7. union all
  8. select rn+1,id
  9. from tmp
  10. where rn <100
  11. )
  12. select * from t where id in (
  13. select id from (
  14. select id+rn as id from tmp
  15. order by dbms_random.value
  16. ) where rownum <= 10
  17. );
执行计划如下：

点击(此处)折叠或打开

  1. Plan hash value: 2701499939

  3. --------------------------------------------------------------------------------------------------------------------------------------------------
  4. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
  5. --------------------------------------------------------------------------------------------------------------------------------------------------
  6. | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 22 | | | |
  7. | 1 | NESTED LOOPS | | 1 | | 10 |00:00:00.01 | 22 | | | |
  8. | 2 | NESTED LOOPS | | 1 | 1 | 10 |00:00:00.01 | 13 | | | |
  9. | 3 | VIEW | VW_NSO_1 | 1 | 2 | 10 |00:00:00.01 | 4 | | | |
  10. | 4 | HASH UNIQUE | | 1 | 1 | 10 |00:00:00.01 | 4 | 1397K| 1397K| 1092K (0)|
  11. |* 5 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 4 | | | |
  12. | 6 | VIEW | | 1 | 2 | 10 |00:00:00.01 | 4 | | | |
  13. |* 7 | SORT ORDER BY STOPKEY | | 1 | 2 | 10 |00:00:00.01 | 4 | 2048 | 2048 | 2048 (0)|
  14. | 8 | VIEW | | 1 | 2 | 100 |00:00:00.01 | 4 | | | |
  15. | 9 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | 1 | | 100 |00:00:00.01 | 4 | | | |
  16. | 10 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
  17. | 11 | INDEX FULL SCAN (MIN/MAX) | IDX_T | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
  18. | 12 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
  19. | 13 | INDEX FULL SCAN (MIN/MAX) | IDX_T | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
  20. | 14 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
  21. | 15 | RECURSIVE WITH PUMP | | 100 | | 99 |00:00:00.01 | 0 | | | |
  22. |* 16 | INDEX RANGE SCAN | IDX_T | 10 | 1 | 10 |00:00:00.01 | 9 | | | |
  23. | 17 | TABLE ACCESS BY INDEX ROWID | T | 10 | 1 | 10 |00:00:00.01 | 9 | | | |
  24. --------------------------------------------------------------------------------------------------------------------------------------------------

  26. Predicate Information (identified by operation id):
  27. ---------------------------------------------------

  29.    5 - filter(ROWNUM<=10)
  30.    7 - filter(ROWNUM<=10)
  31.   16 - access("ID"="ID")
逻辑读22，执行时间0.01s。

总结：
从一个表里随机取10条记录的关键在于：
1）避免扫描全部数据
2）避免对全部数据随机排序
所以关键在于构造一个随机ID，然后用这个ID为起始点去表里找，可以走索引查找，然后可以随机
取100条（为了避免ID不连续的断号问题），减少扫描和排序量，充分利用索引的高效扫描
提高效率。
构造随机ID,利用扫描索引取min,max可以快速扫描，然后利用min_id为起点+随机数，
这个随机数用(tmp.max_id - tmp.min_id + 1) * dbms_random.value
构造，然后用floor取整即可。




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

上一篇：绑定变量peeking关闭导致like走index range scan原因及分析

下一篇：ORACLE组合索引选择率计算及如何选择组合索引的列顺序_PART1

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6