分页语句创建索引技巧

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(*)
----------
  12083584

SQL>  select count(*) from t100 where owner='SYSTEM';

  COUNT(*)
----------
     79232


SQL> 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; ---再运行SQL

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


创建索引1:

SQL>  select count(*) from t100 where owner='SYSTEM';

  COUNT(*)
----------
     79232

SQL>  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 client
	541  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 gets
	464  physical reads
	  0  redo size
       2543  bytes sent via SQL*Net to client
	541  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 client
	541  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 >= 1

SQL>  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 client
	541  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列