ORACLE分析函数

：我有个SQL，你能帮我用分析函数改写下吗？乙：相关列有索引吗？甲：owner有索引，选择性不错，我想用分析函数改写看看？乙：哦，知道了，这是典型的top-n查询。SELECTowner,object_typeFROMdemo2WHEREowner='DINGJUN123'ANDtrunc(created,'dd')=(SELECTMAX(trunc(created,'dd'))FROMdemo2WHEREowner='DINGJUN123')SELECTowner,object_typeFROM(SELECTowner,object_type,dense_rank()over(ORDERBYtrunc(created,'dd')DESC)rnFROMdemo2WHEREowner='DINGJUN123')WHERErn=14子查询方法总行数：667827返回9行原始SQL:逻辑读848，COST：1103优点：最容易想到缺点：多次访问表或索引分析函数方法分析SQL:逻辑读423，COST：693优点：减少表或索引的访问次数，逻辑读和COST是常规方法的一半，SQL简单缺点：需要排序操作------------------------------------------------------------|Id|Operation|Name|Rows|------------------------------------------------------------|0|SELECTSTATEMENT||202||*1|TABLEACCESSBYINDEXROWID|DEMO2|202||*2|INDEXRANGESCAN|IDX_DEMO2|20237||3|SORTAGGREGATE||1||4|TABLEACCESSBYINDEXROWID|DEMO2|20237||*5|INDEXRANGESCAN|IDX_DEMO2|20237|PredicateInformation(identifiedbyoperationid):-------------------------------------------------------------1-filter(TRUNC(INTERNAL_FUNCTION(CREATED),'fmdd')=(SELECTMAX(TRUNC(INTERNAL_FUNCTION(CREATED),'fmdd'))FROMDEMO2DEMO2WHEREOWNER='DINGJUN123'))2-access(OWNER='DINGJUN123')5-access(OWNER='DINGJUN123')------------------------------------------------------------|Id|Operation|Name|Rows|------------------------------------------------------------|0|SELECTSTATEMENT||20237||*1|VIEW||20237||*2|WINDOWSORTPUSHEDRANK||20237||3|TABLEACCESSBYINDEXROWID|DEMO2|20237||*4|INDEXRANGESCAN|IDX_DEMO2|20237|------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter(RN=1)2-filter(DENSE_RANK()OVER(ORDERBYTRUNC(INTERNAL_FUNCTION(CREATED),'fmdd')DESC)=1)4-access(OWNER='DINGJUN123')---------------------------------------------------------|Id|Operation|Name|Rows|---------------------------------------------------------|0|SELECTSTATEMENT||213||1|TABLEACCESSBYINDEXROWID|DEMO2|213||*2|INDEXRANGESCAN|IDX_DEMO2|1||3|SORTAGGREGATE||1||*4|INDEXRANGESCAN|IDX_DEMO2|21334|---------------------------------------------------------SELECTowner,object_typeFROMdemo2WHEREowner='DINGJUN123'ANDtrunc(created,'dd')=(SELECTMAX(trunc(created,'dd'))FROMdemo2WHEREowner='DINGJUN123')SELECTowner,object_typeFROM(SELECTowner,object_type,dense_rank()over(ORDERBYtrunc(created,'dd')DESC)rnFROMdemo2WHEREowner='DINGJUN123')WHERErn=1-----------------------------------------------------------|Id|Operation|Name|Rows|-----------------------------------------------------------|0|SELECTSTATEMENT||21334||*1|VIEW||21334||*2|WINDOWNOSORTSTOPKEY||21334||3|TABLEACCESSBYINDEXROWID|DEMO2|21334||*4|INDEXRANGESCAN|IDX_DEMO2|21334|-----------------------------------------------------------SELECTempno,sal,deptno,SUM(sal)over(PARTITIONBYdeptnoORDERBYempno)sum_currentFROMempEMPNOSALDEPTNOSUM_CURRENT-------------------------------------778224501024507839500010745079341300108750。。。SELECTa.ID,a.sal,a.extFROMt1a,(SELECTID,MAX(sal)max_salFROMt1GROUPBYID)bWHEREa.sal=b.max_salANDa.ID=b.IDSELECTID,sal,extFROM(SELECTID,sal,ext,rank()over(PARTITIONBYIDORDERBYsalDESC)rnFROMt1)WHERErn=1---------------------------------------------------------|SELECTSTATEMENT||1|65|||HASHJOIN||1|65|35M||VIEW||990K|24M|||HASHGROUPBY||990K|24M|||TABLEACCESSFULL|T1|990K|24M|||TABLEACCESSFULL|T1|990K|36M||---------------------------------------------------------Elapsed:00:00:01.49-------------------------------------------------------------|SELECTSTATEMENT||990K|49M|||VIEW||990K|49M|||WINDOWSORTPUSHEDRANK||990K|36M|49M||TABLEACCESSFULL|T1|990K|36M||-------------------------------------------------------------Elapsed:00:00:04.38CREATETABLEt1ASSELECTmod(LEVEL,1000)ID,LEVEL+1000sal,MOD(LEVEL,10)extFROMdualCONNECTBYLEVEL1000000PARTITIONBYORDERBYROWSVsRANGEAnalyticFunctionUNBOUNDEDPRECEDINGFOLLOWINGCURRENTROW通过partitionby子句，将相同的行聚合到一起成为一组，之后当前行的分析函数计算就是在这行对应的partition里。每个分析函数都可以使用partitionby子句。每行在对应的窗口内，应用分析函数，然后计算得到当前行对应的分析函数值。partitionby子句可以没有，如果也没有orderby子句，那么表示当前行对应的窗口范围是所有行。11223SELECTdeptno,empno,sal,SUM(sal)over(PARTITIONBYdeptno)sum_dept,SUM(sal)over()sum_allFROMempDEPTNOEMPNOSALSUM_DEPTSUM_ALL-------------------------------------------------107782245087503202510793413008750320251078395000875032025207902300010875320252075662975108753202520787611001087532025207369800108753202520778830001087532025orderby当前行默认窗口是当前行所属的partition第1行到当前行（根据orderby顺序指定），无orderby就是对应所属partition所有行。orderby默认是range窗口，对应逻辑窗口，保证分析函数值的唯一性，但是对排名分析函数特殊,因为排名函数不能带window。orderby如果有多个排序键且是range窗口，则必须要求对应的窗口是当前partition所有行、第1行到当前行、当前行到当前partition最后一行或当前行到当前行123SELECTdeptno,empno,sal,SUM(sal)over(PARTITIONBYdeptnoORDERBYsal)dept_current,SUM(sal)over(PARTITIONBYdeptnoORDERBYsalRANGEBETWEENunboundedprecedingANDCURRENTROW)dept_current1FROMempDEPTNOEMPNOSALDEPT_CURRENTDEPT_CURRENT1---------------------------------------------------1079341300130013001077822450375037501078395000875087502073698008008002078761100190019002075662975487548752077883000108751087520790230001087510875307900950950