create table page as select * from dba_objects; create index idx_page on page(object_id);create index idx_page_1 on page(owner,object_id);create index idx_page_2 on page(owner);create index idx_page_3 on page(object_id,owner);BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'PAGE', estimate_percent => 100, method_opt => 'for all columns size skewonly', no_invalidate => FALSE, degree => 4, cascade => TRUE);END;select * from (select * from (select a.*,rownum rn from page a where object_id >1000 and owner='SYS' order by object_id desc) where rownum<=20) where rn>=0;分页语句必须要有order by ,order by 哪个列 哪个列必须有索引只要 分页有 sort order by 绝对是错的肯定没走索引SQL> select * from (select * from (select a.*,rownum rn from page a where object_id >1000 and owner='SYS' order by object_id desc) where rownum<=20) where rn>=0; 2 3 4 5 6 7 8 9 10 20 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 824209635-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 20 | 4400 | | 910 (1)| 00:00:11 ||* 1 | VIEW | | 20 | 4400 | | 910 (1)| 00:00:11 ||* 2 | COUNT STOPKEY | | | | | | || 3 | VIEW | | 30424 | 6536K| | 910 (1)| 00:00:11 ||* 4 | SORT ORDER BY STOPKEY| | 30424 | 2881K| 4000K| 910 (1)| 00:00:11 || 5 | COUNT | | | | | | ||* 6 | TABLE ACCESS FULL | PAGE | 30424 | 2881K| | 234 (1)| 00:00:03 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=0) 2 - filter(ROWNUM<=20) 4 - filter(ROWNUM<=20) 6 - filter("OWNER"='SYS' AND "OBJECT_ID">1000)Statistics---------------------------------------------------------- 1153 recursive calls 0 db block gets 1336 consistent gets 1036 physical reads 0 redo size 3175 bytes sent via SQL*Net to client 431 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 66 sorts (memory) 0 sorts (disk) 20 rows processed分页语句:1.必须有orderby 2.order by 哪个列,那个列就必须要有索引,利用索引已排序的特点。3.分页有 sort order by 重新对数据进行排序select * from (select * from (select /*+ index(a) */ a.*,rownum rn from page a where object_id >1000 and owner='SYS' order by object_id desc) where rownum<=20) where rn>=0; SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 4tub4ucyfwk79, child number 0-------------------------------------select * from ( select * from ( select /*+ index(a) */ a.*,rownum rn from page a where object_id >1000 and owner='SYS' order by object_iddesc ) where rownum<=20 ) where rn>=0Plan hash value: 4010810952------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.83 | 865 | | | ||* 1 | VIEW | | 1 | 20 | 20 |00:00:00.83 | 865 | | | ||* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:00.83 | 865 | | | || 3 | VIEW | | 1 | 30424 | 20 |00:00:00.83 | 865 | | | ||* 4 | SORT ORDER BY STOPKEY | | 1 | 30424 | 20 |00:00:00.83 | 865 | 1824K| 650K| 1621K (0)|| 5 | COUNT | | 1 | | 29905 |00:00:00.65 | 865 | | | ||* 6 | TABLE ACCESS BY INDEX ROWID| PAGE | 1 | 30424 | 29905 |00:00:00.48 | 865 | | | ||* 7 | INDEX RANGE SCAN | IDX_PAGE_2 | 1 | 30835 | 30835 |00:00:00.10 | 66 | | | |------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=0) 2 - filter(ROWNUM<=20) 4 - filter(ROWNUM<=20) 6 - filter("OBJECT_ID">1000) 7 - access("OWNER"='SYS')30 rows selected.SORT ORDER BY STOPKEY 也是错的select * from (select * from (select /*+ index_desc(a idx_page_3) */ a.*,rownum rn from page a where object_id >1000 and owner='SYS' order by object_id desc) where rownum<=20) where rn>=0; SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 8vy3s6f4nh4ga, child number 0-------------------------------------select * from ( select * from ( select /*+ index_desc(a idx_page_3)*/ a.*,rownum rn from page a where object_id >1000 andowner='SYS' order by object_id desc ) where rownum<=20 ) where rn>=0Plan hash value: 3526010999------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.01 | 9 | 2 ||* 1 | VIEW | | 1 | 20 | 20 |00:00:00.01 | 9 | 2 ||* 2 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 9 | 2 || 3 | VIEW | | 1 | 30424 | 20 |00:00:00.01 | 9 | 2 || 4 | COUNT | | 1 | | 20 |00:00:00.01 | 9 | 2 || 5 | TABLE ACCESS BY INDEX ROWID | PAGE | 1 | 30424 | 20 |00:00:00.01 | 9 | 2 ||* 6 | INDEX RANGE SCAN DESCENDING| IDX_PAGE_3 | 1 | 30424 | 20 |00:00:00.01 | 4 | 2 |------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("RN">=0) 2 - filter(ROWNUM<=20) 6 - access("OBJECT_ID" IS NOT NULL AND "OBJECT_ID">1000 AND "OWNER"='SYS') filter("OWNER"='SYS')28 rows selected.多表关联后分页:select ... from a,b where a.id=b.id order by a.xx;a当驱动表 nl b分页SQL必须走NL循环 order by 谁 谁当驱动表1. 我们把它当成只 从a出数据2.值给a.xx建索引3.被驱动表 b b.id建索引 select ... from a,b where a.id=b.id order by a.xx，b.xxx;分页的 每页 取 20条这种SQL没法优化,order by 2个表没法优化select ... from a,b where a.id=b.id gropu by ...order by 分页的 每页 取 20条分页语句不能有group by ，distinct操作select ... from a left join b where ... order by b外链接 左链接 驱动表只能是a 那么order by a表 select ... from a where 过滤条件 order by 另外一个列按where 条件列+order by 列创建索引select ... from a where owner='SB' and xx like '%jj%' order by money这个时候建索引:money,owner,xx分页语句的优化技巧：1.分页SQL要想快最好走索引，根据order by asc/desc ，用hint index_asc/index_desc 强制它走索引INDEX FULL SCAN DESCENDING/INDEX RANGE SCAN DESCENDING2.不要让Oracle扫描整个index，确保只扫描一部分索引块，然后回表就取得数据，然后 就count stopkey3.一定要确保索引回表的时候不要在表上再去过滤，也就是说TABLE ACCESS BY INDEX ROWID 上面没*,不然就可能扫描整个索引块，根据where条件可以创建组合索引 (分区表要创建global index)4.看执行计划的时候不要被执行计划的基数给骗了----它可能显示是有误差的