oracle分页(语句关键字)

oracle分页

—分页句子优化思路
一、单表分页句子优化思路
1.分页句子结构是否正确
过错的结构
select*
from(selectt.*,rownumrnfrom(需求分页的SQL)t)
wherern>=1
andrn<=10;
正确的分页结构
select*
from(select*
from(selecta.*,rownumrn
from(需求分页的SQL)a)
whererownum<=10)
wherern>=1;
**************************************
假如SQL没有过滤条件，只要排序，咱们能够使用现已排序的特性来优化分页句子的SQL;即消除执行计划中的SORTORDERBY
***假如拍序列答应为null，咱们创立索引的时分，需求给这个索引中增加一个常量(可所以0123或许英文字母),索引不能存储空值！增加常量能够让索引存储空值;
createindexidx_nameontest(onject_name,0);
**************************************
过错的分页结构导致功能变差的原因:
过错的分页结构没有COUNTSTOPKEY(whererownum<=…)功能，COUNTSTOPKEY便是当扫描到指定行数的数据之后,SQL就停止运行了
*************************************
假如SQL有等值过滤也有orderby,这时就需求将过滤列包含在索引中,创立组合索引;—等值列在前，过滤列在后,避免边扫描边过滤的情况
***假如过滤列能过滤掉大部分数据，不将拍序列包含在索引中也能够***
***在实际生产中，一般过滤列都是绑定变量，无法控制传入的参数究竟是那个值，不能确认回来数据的多少，主张最好将拍序列包含在索引中
************************************
假如拍序列有多个列，创立索引的时分，需求将一切的拍序列都包含在索引中！！！
注意：
拍序列先后次序(跟SQL句子中次序共同);
拍序列是升序仍是降序;
假如分页句子中拍序列只要一个，且是降序显现,创立索引的时分就没必要降序了，能够使用HINT:index_desc让索引降序扫描
***********************************
创立组合索引次序
1>等值列在前，过滤列在后
2>过滤列在前，非等值列在后
即:等值列>拍序列>非等值列
***假如分页句子没有排序，能够直接使用rownum的COUNTSTOPKEY特性优化SQL
二、多表相关优化思路
1.索引现已排序的特性
2.ROWNUM的COUNTSTOPKEY特性
3.嵌套循环传值特性(驱动表走的排序列的索引)/*+index(t2idx_name)leading(t2)use_nl(t2,t1)*/
***注意：假如走HASH,两表相关之后的成果，无法保证是有序的,需求相关后在排序(STORORDERBY),也不能走排序合并衔接
***嵌套循环是驱动表传值给被驱动表，假如驱动表回来的数据是有序的，相关之后的成果集也是有序的
**************************************
假如分页句子的拍序列来自多个表，这就需求等两表相关之后在进行排序，无法消除STORORDERBY,也就无法优化，只能走HASH衔接
想优化排序列来自多个表，需求开发和业务交流，去掉一个排序列
**************************************
假如两表相关是外衔接，当两表进行嵌套循环的时分，驱动表只能是主表;
假如嵌套循环的主表和排序列不是同一个表，则分页句子无法优化，只能走HASH;
想要优化，只能是让嵌套循环的驱动表的列作为排序列;
***************************************
分页句子中不能有distinct,groupby,max,avg,union,unionall等关键字;
假如有这些关键字，需求等表相关或许数据都跑完之后再来分页,但是功能很差;

oracle分页语句关键字

在Oracle中完结分页的办法大致分为两种，用ROWNUM关键字和用ROWID关键字，下面来详细介绍一下：
1、ROWNUM
其代码为：
SELECT*
FROM(SELECTROW_.*,ROWNUMROWNUM_
FROM(SELECT*
FROMTABLE1
WHERETABLE1_ID=XX
ORDERBYGMT_CREATEDESC)ROW_
WHEREROWNUM<=20)
WHEREROWNUM_>=10;
这应该是咱们大部分程序里所用到的版本，由于这个版本很容易完结复用，中间ROW_部分，便是咱们往常写到的sql句子，然后再将开始条数和终止条数作为专门的分页sql句子传入即可查询出咱们想要的结果。
从功率上看，上面的SQL句子在大多数情况具有较高的功率，首要体现在WHEREROWNUM<=20这句上，这样就操控了查询过程中的最大记载数，而在查询的最外层操控最小值。但最大值意味着假如查到了很大的规模（如百万等级的数据），查询就会从很大规模内往里削减，功率就会很低，因而，当面对大数据量时或许优化查询功率时，假如你用了ROWNUM，能够换第二种办法。
由以上的办法，又能够引申出3种方法：
A、结合BETWEENAND
代码如下：
SELECT*
FROM(SELECTA.*,ROWNUMRN
FROM(SELECT*
FROMTABLE1
WHERETABLE1_ID=XX
ORDERBYGMT_CREATEDESC)A)
WHERERNBETWEEN10AND20;
这个便是换汤不换药了，而且查询功率更低，由于：
Oracle能够将外层的查询条件推到内层查询中，以进步内层查询的执行功率，但不能跨越多层。
由于查询条件BETWEEN10AND20是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即便推到最内层也没有意义，由于最内层查询不知道RN代表什么）。因而，这个查询句子，Oracle最内层回来给中间层的是所有满意条件的数据，而中间层回来给最外层的也是所有数据。数据的过滤在最外层完结，明显这个功率要比原始的查询低得多。
B、结合MINUS
SELECT*
FROMTABLE1
WHEREROWNUM<=20
MINUS
SELECT*FROMTABLE1WHEREROWNUM<=10;
查询了两次，功率上更差了一些。
C、ROW_NUMBER()OVER(ORDERBYORDER_DATEDESC)
这个和ROWNUM关键字类似，生成的次序和rownum的句子相同，功率也相同（关于同样有ORDERBY的ROWNUM句子来说），所以在这种情况下两种用法是相同的。
而关于分组后查询做分页的话，则是ROWNUM无法完结的，这时只要ROW_NUMBER能够完结，ROW_NUMBER()OVER(PARTITIONBY分组字段ORDERBY排序字段)就能完结分组后编号，其代码为：
SELECT*
FROM(SELECTa.*,
ROW_NUMBER()OVER(PARTITIONBYTRUNC(order_date)ORDERBYorder_dateDESC)rn
FROMTABLE1a)
WHERErn<=10;
2、ROWID
ROWID依旧需求ROWNUM，但方法不同，因而我将其归为另一大类，其代码为：
SELECT*
FROM(SELECTRID
FROM(SELECTR.RID,ROWNUMLINENUM
FROM(SELECTROWIDRID
FROMTABLE1
WHERETABLE1_ID=XX
ORDERBYorder_dateDESC)R
WHEREROWNUM<=20)
WHERELINENUM>=10)T1,
TABLE1T2
WHERET1.RID=T2.ROWID;
从句子上看，共有4层Select嵌套查询，最内层为可替换的不分页原始SQL句子，但是他查询的字段只要ROWID，而没有任何待查询的实践表字段，详细查询实践字段值是在最外层完结的；
这种方法的原理大致为：
首要经过ROWNUM查询到分页之后的10条实践回来记载的ROWID，最后经过ROWID将最终回来字段值查询出来并回来；
和前面ROWNUM完结方法比较，该SQL的完结方法更加繁琐，通用性也不是非常好，由于要将原始的查询句子分成两部分（查询字段在最外层，表及其查询条件在最内层），想要复用就很困难了；
但这种完结在特定场景下仍是有优势的：比方咱们常常要翻页到很后边，比方10000条记载中咱们常常需求查9000-9100及其以后的数据；此时该计划功率可能要比前面的高；
由于前面的计划中是经过ROWNUM<=9100来操控的，这样就需求查询出9100条数据，然后取最后9000-9100之间的数据，而这个计划直接经过ROWID取需求的那100条数据；
从不断向后翻页这个视点来看，第一种完结计划的成本会越来越高，基本上是线性增加，而第三种计划的成本则不会像前者那样快速，他的增加只体现在经过查询条件读取ROWID的部分；
因而，在咱们实践项目中，基本分页都是能够单靠ROWNUM就能够完结，而在数据量只要几十万的情况下，功率也是够的，假如一定要优化，则能够考虑ROWID。