分页语句创建索引技巧

来源：互联网发布：做淘宝运营对数据分析编辑：程序博客网时间：2024/05/18 16:18
SQL> select *  from (select rownum as rn, a.*          from (select *                  from t100 a                 where object_id > 1500                   and owner = 'SYSTEM'                 order by object_id desc) a         where rownum <= 40) a where rn >= 1;SQL> select count(*) from t100;  COUNT(*)----------  12083584SQL>  select count(*) from t100 where owner='SYSTEM';  COUNT(*)----------     79232SQL> select count(*)                  from t100 a                 where                  owner = 'SYSTEM'                 and object_name like '%LOG%'  2    3    4    5  ;  COUNT(*)----------     39168---特殊执行计划:11G：set linesize 200;set pagesize 200;alter session set statistics_level=all; ---再运行SQLSQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));创建索引1:SQL>  select count(*) from t100 where owner='SYSTEM';  COUNT(*)----------     79232SQL>  create index t100_idx1 on t100(owner,object_id);select *  from (select rownum as rn, a.*          from (select *                  from t100 a                 where                     owner = 'SYSTEM'                    and object_name like '%LOG%'                 order by object_id desc) a         where rownum <= 40) a where rn >= 1执行计划----------------------------------------------------------Plan hash value: 2240177993---------------------------------------------------------------------------------------------| Id  | Operation| Name    | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT|    | 40 |  8800 | 45   (0)| 00:00:01 ||*  1 |  VIEW|    | 40 |  8800 | 45   (0)| 00:00:01 ||*  2 |   COUNT STOPKEY |    |    |    | |    ||   3 |    VIEW |    | 41 |  8487 | 45   (0)| 00:00:01 ||   4 |     TABLE ACCESS BY INDEX ROWID | T100    |425K| 39M| 45   (0)| 00:00:01 ||*  5 |      INDEX RANGE SCAN DESCENDING| T100_IDX1 | 41 |    |  3   (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("RN">=1)   2 - filter(ROWNUM<=40)   5 - access("OWNER"='SYSTEM' AND "OBJECT_ID">1500 AND "OBJECT_ID" IS NOT NULL)统计信息----------------------------------------------------------  1  recursive calls  0  db block gets 46  consistent gets  0  physical reads  0  redo size       2529  bytes sent via SQL*Net to client541  bytes received via SQL*Net from client  4  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk) 40  rows processed创建索引2:SQL> create index t100_idx2 on t100(owner,object_name,object_id);执行计划----------------------------------------------------------Plan hash value: 3889701471-----------------------------------------------------------------------------------------------------| Id  | Operation| Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT|    | 40 |  8800 |    | 24854   (1)| 00:04:59 ||*  1 |  VIEW|    | 40 |  8800 |    | 24854   (1)| 00:04:59 ||*  2 |   COUNT STOPKEY |    |    |    |    | |    ||   3 |    VIEW |    | 21578 |  4361K|    | 24854   (1)| 00:04:59 ||*  4 |     SORT ORDER BY STOPKEY|    | 21578 |  2044K|  2840K| 24854   (1)| 00:04:59 ||   5 |      TABLE ACCESS BY INDEX ROWID| T100    | 21578 |  2044K|    | 24372   (1)| 00:04:53 ||*  6 |       INDEX RANGE SCAN| T100_IDX2 | 21578 |    |    |  2790   (1)| 00:00:34 |-----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("RN">=1)   2 - filter(ROWNUM<=40)   4 - filter(ROWNUM<=40)   6 - access("OWNER"='SYSTEM')       filter("OBJECT_NAME" LIKE '%LOG%' AND "OBJECT_NAME" IS NOT NULL)统计信息---------------------------------------------------------- 28  recursive calls  0  db block gets      39661  consistent gets464  physical reads  0  redo size       2543  bytes sent via SQL*Net to client541  bytes received via SQL*Net from client  4  SQL*Net roundtrips to/from client  7  sorts (memory)  0  sorts (disk) 40  rows processed创建索引3:SQL>  create index t100_idx3 on t100(object_id,owner);索引已创建。执行计划----------------------------------------------------------Plan hash value: 1672976351--------------------------------------------------------------------------------------------| Id  | Operation       | Name   | Rows  | Bytes | Cost (%CPU)| Time   |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT       |   |40 |  8800 |   872   (0)| 00:00:11 ||*  1 |  VIEW       |   |40 |  8800 |   872   (0)| 00:00:11 ||*  2 |   COUNT STOPKEY        |   |   |   ||   ||   3 |    VIEW        |   |40 |  8280 |   872   (0)| 00:00:11 ||*  4 |     TABLE ACCESS BY INDEX ROWID| T100   | 21578 |  2044K|   872   (0)| 00:00:11 ||*  5 |      INDEX FULL SCAN DESCENDING| T100_IDX3 |   800 |   |71   (0)| 00:00:01 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("RN">=1)   2 - filter(ROWNUM<=40)   4 - filter("OBJECT_NAME" LIKE '%LOG%' AND "OBJECT_NAME" IS NOT NULL)   5 - access("OWNER"='SYSTEM')       filter("OWNER"='SYSTEM')统计信息----------------------------------------------------------  1  recursive calls  0  db block gets      33730  consistent gets      32022  physical reads  0  redo size       2543  bytes sent via SQL*Net to client541  bytes received via SQL*Net from client  4  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk) 40  rows processed创建索引4:select *  from (select rownum as rn, a.*          from (select *                  from t100 a                 where                     owner = 'SYSTEM'                    and object_name like '%LOG%'                 order by object_id desc) a         where rownum <= 40) a where rn >= 1SQL>  create index t100_idx4 on t100(object_id,owner,object_name);执行计划----------------------------------------------------------Plan hash value: 1439634448--------------------------------------------------------------------------------------------| Id  | Operation       | Name   | Rows  | Bytes | Cost (%CPU)| Time   |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT       |   |40 |  8800 |   189   (0)| 00:00:03 ||*  1 |  VIEW       |   |40 |  8800 |   189   (0)| 00:00:03 ||*  2 |   COUNT STOPKEY        |   |   |   ||   ||   3 |    VIEW        |   |40 |  8280 |   189   (0)| 00:00:03 ||   4 |     TABLE ACCESS BY INDEX ROWID| T100   | 21578 |  2044K|   189   (0)| 00:00:03 ||*  5 |      INDEX FULL SCAN DESCENDING| T100_IDX4 |40 |   |   148   (0)| 00:00:02 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("RN">=1)   2 - filter(ROWNUM<=40)   5 - access("OWNER"='SYSTEM')       filter("OWNER"='SYSTEM' AND "OBJECT_NAME" LIKE '%LOG%' AND "OBJECT_NAME" IS      NOT NULL)统计信息----------------------------------------------------------  1  recursive calls  0  db block gets      68683  consistent gets      68639  physical reads  0  redo size       2543  bytes sent via SQL*Net to client541  bytes received via SQL*Net from client  4  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk) 40  rows processed结论: 分页SQL 创建索引 where 列+ order by列
0 0