分页SQL 的各种索引对比

来源：互联网发布：windows defender 打开编辑：程序博客网时间：2024/06/04 19:22
SQL> select *  from (select rownum as rn, a.*          from (select *                  from t100 a                 where object_id > 1500                   and owner = 'SYS'                 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='SYS';  COUNT(*)----------   4838784SQL> select count(*)                  from t100 a                 where                  owner = 'SYS'                 and object_name like '%LOG%'  2    3    4    5  ;  COUNT(*)----------     37632创建索引1：  CREATE INDEX "SYSTEM"."T100_IDX1" ON "SYSTEM"."T100" ("OWNER", "OBJECT_ID") SQL> select *  from (select rownum as rn, a.*          from (select /*+ index(a t100_idx1)*/ *                  from t100 a                 where                     owner = 'SYS'                    and object_name like 'LOG%'                 order by object_id desc) a         where rownum <= 40) a where rn >= 1;  2    3    4    5    6    7    8    9   10  已选择40行。执行计划----------------------------------------------------------Plan hash value: 3995274525---------------------------------------------------------------------------------------------| Id  | Operation| Name    | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT|    |  7 |  1540 |432K  (1)| 01:26:36 ||*  1 |  VIEW|    |  7 |  1540 |432K  (1)| 01:26:36 ||*  2 |   COUNT STOPKEY |    |    |    | |    ||   3 |    VIEW |    |  7 |  1449 |432K  (1)| 01:26:36 ||*  4 |     SORT ORDER BY STOPKEY|    |  7 |679 |432K  (1)| 01:26:36 ||*  5 |      TABLE ACCESS BY INDEX ROWID| T100    |  7 |679 |432K  (1)| 01:26:36 ||*  6 |       INDEX RANGE SCAN| T100_IDX1 |431K|    |  1318   (1)| 00:00:16 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("RN">=1)   2 - filter(ROWNUM<=40)   4 - filter(ROWNUM<=40)   5 - filter("OBJECT_NAME" LIKE 'LOG%')   6 - access("OWNER"='SYS')统计信息----------------------------------------------------------  1  recursive calls  0  db block gets    4852145  consistent gets     116880  physical reads  0  redo size       2537  bytes sent via SQL*Net to client542  bytes received via SQL*Net from client  4  SQL*Net roundtrips to/from client  1  sorts (memory)  0  sorts (disk) 40  rows processed逻辑读 4852145创建索引2：  CREATE INDEX "SYSTEM"."T100_IDX2" ON "SYSTEM"."T100" ("OWNER", "OBJECT_NAME", "OBJECT_ID") 执行计划----------------------------------------------------------Plan hash value: 3889701471---------------------------------------------------------------------------------------------| Id  | Operation| Name    | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT|    |  7 |  1540 | 13   (8)| 00:00:01 ||*  1 |  VIEW|    |  7 |  1540 | 13   (8)| 00:00:01 ||*  2 |   COUNT STOPKEY |    |    |    | |    ||   3 |    VIEW |    |  7 |  1449 | 13   (8)| 00:00:01 ||*  4 |     SORT ORDER BY STOPKEY|    |  7 |679 | 13   (8)| 00:00:01 ||   5 |      TABLE ACCESS BY INDEX ROWID| T100    |  7 |679 | 12   (0)| 00:00:01 ||*  6 |       INDEX RANGE SCAN| T100_IDX2 |  7 |    |  4   (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("RN">=1)   2 - filter(ROWNUM<=40)   4 - filter(ROWNUM<=40)   6 - access("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'LOG%')       filter("OBJECT_NAME" LIKE 'LOG%')统计信息----------------------------------------------------------  1  recursive calls  0  db block gets      10685  consistent gets  0  physical reads  0  redo size       2537  bytes sent via SQL*Net to client542  bytes received via SQL*Net from client  4  SQL*Net roundtrips to/from client  1  sorts (memory)  0  sorts (disk) 40  rows processed逻辑读 10685创建索引3：  CREATE INDEX "SYSTEM"."T100_IDX3" ON "SYSTEM"."T100" ("OBJECT_ID", "OWNER") 执行计划----------------------------------------------------------Plan hash value: 1239817574---------------------------------------------------------------------------------------------| Id  | Operation| Name    | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT|    |  7 |  1540 |468K  (1)| 01:33:43 ||*  1 |  VIEW|    |  7 |  1540 |468K  (1)| 01:33:43 ||*  2 |   COUNT STOPKEY |    |    |    | |    ||   3 |    VIEW |    |  7 |  1449 |468K  (1)| 01:33:43 ||*  4 |     SORT ORDER BY STOPKEY|    |  7 |679 |468K  (1)| 01:33:43 ||*  5 |      TABLE ACCESS BY INDEX ROWID| T100    |  7 |679 |468K  (1)| 01:33:43 ||*  6 |       INDEX FULL SCAN| T100_IDX3 |431K|    | 36886   (1)| 00:07:23 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("RN">=1)   2 - filter(ROWNUM<=40)   4 - filter(ROWNUM<=40)   5 - filter("OBJECT_NAME" LIKE 'LOG%')   6 - access("OWNER"='SYS')       filter("OWNER"='SYS')统计信息----------------------------------------------------------  1  recursive calls  0  db block gets    4875771  consistent gets     153771  physical reads  0  redo size       2537  bytes sent via SQL*Net to client542  bytes received via SQL*Net from client  4  SQL*Net roundtrips to/from client  1  sorts (memory)  0  sorts (disk) 40  rows processed逻辑读 4875771创建索引4：  CREATE INDEX "SYSTEM"."T100_IDX4" ON "SYSTEM"."T100" ("OBJECT_ID", "OWNER", "OBJECT_NAME") 执行计划----------------------------------------------------------Plan hash value: 1079028630---------------------------------------------------------------------------------------------| Id  | Operation| Name    | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT|    |  7 |  1540 | 77998   (1)| 00:15:36 ||*  1 |  VIEW|    |  7 |  1540 | 77998   (1)| 00:15:36 ||*  2 |   COUNT STOPKEY |    |    |    | |    ||   3 |    VIEW |    |  7 |  1449 | 77998   (1)| 00:15:36 ||*  4 |     SORT ORDER BY STOPKEY|    |  7 |679 | 77998   (1)| 00:15:36 ||   5 |      TABLE ACCESS BY INDEX ROWID| T100    |  7 |679 | 77997   (1)| 00:15:36 ||*  6 |       INDEX FULL SCAN| T100_IDX4 |  7 |    | 77989   (1)| 00:15:36 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("RN">=1)   2 - filter(ROWNUM<=40)   4 - filter(ROWNUM<=40)   6 - access("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'LOG%')       filter("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'LOG%')统计信息----------------------------------------------------------  1  recursive calls  0  db block gets      89401  consistent gets       2935  physical reads  0  redo size       2537  bytes sent via SQL*Net to client542  bytes received via SQL*Net from client  4  SQL*Net roundtrips to/from client  1  sorts (memory)  0  sorts (disk) 40  rows processed逻辑读: 89401创建索引5：  CREATE INDEX "SYSTEM"."T100_IDX5" ON "SYSTEM"."T100" ("OBJECT_NAME", "OWNER", "OBJECT_ID") 执行计划----------------------------------------------------------Plan hash value: 3702588553---------------------------------------------------------------------------------------------| Id  | Operation| Name    | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT|    |  7 |  1540 | 14   (8)| 00:00:01 ||*  1 |  VIEW|    |  7 |  1540 | 14   (8)| 00:00:01 ||*  2 |   COUNT STOPKEY |    |    |    | |    ||   3 |    VIEW |    |  7 |  1449 | 14   (8)| 00:00:01 ||*  4 |     SORT ORDER BY STOPKEY|    |  7 |679 | 14   (8)| 00:00:01 ||   5 |      TABLE ACCESS BY INDEX ROWID| T100    |  7 |679 | 13   (0)| 00:00:01 ||*  6 |       INDEX RANGE SCAN| T100_IDX5 |  7 |    |  5   (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("RN">=1)   2 - filter(ROWNUM<=40)   4 - filter(ROWNUM<=40)   6 - access("OBJECT_NAME" LIKE 'LOG%' AND "OWNER"='SYS')       filter("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'LOG%')统计信息----------------------------------------------------------  0  recursive calls  0  db block gets      10898  consistent gets  0  physical reads  0  redo size       2537  bytes sent via SQL*Net to client542  bytes received via SQL*Net from client  4  SQL*Net roundtrips to/from client  1  sorts (memory)  0  sorts (disk) 40  rows processed
0 0