oracle那个效率更高呢

Question

select orgName,planId,
(select plan_name from DRGS_INDEX_ESTIMATE_PLAN where plan_id=planId and rownum=1) as planName,
rank() over (ORDER BY total desc) as totalRanking,
 total,
          aAll,
          eAll,
          cAll,
          qAll,
          a002IndexScore,
          a002IndexWeight,
          a002IndexValue,
          e002IndexScore,
          e002IndexWeight, 
          e002IndexValue, 
          e001IndexScore,
          e001IndexWeight, 
          e001IndexValue, 
          q001IndexScore,
          q001IndexWeight, 
          q001IndexValue 
          from (
          select 
          ORG_NAME as orgName,
          PLAN_ID as planId,
          ROUND(avg(TOTAL),2) as total,
          ROUND(avg(A_ALL),2) as aAll,
          ROUND(avg(E_ALL),2) as eAll,
          ROUND(avg(C_ALL),2) as cAll,
          ROUND(avg(Q_ALL),2) as qAll,
          ROUND(avg(A002_INDEX_SCORE),2)  as a002IndexScore,
          ROUND(avg(A002_INDEX_WEIGHT),2) as a002IndexWeight,
          ROUND(avg(A002_INDEX_VALUE),2)  as a002IndexValue,
          ROUND(avg(E002_INDEX_SCORE),2)  as e002IndexScore,
          ROUND(avg(E002_INDEX_WEIGHT),2) as e002IndexWeight,
          ROUND(avg(E002_INDEX_VALUE),2)  as e002IndexValue,
          ROUND(avg(E001_INDEX_SCORE),2)  as e001IndexScore,
          ROUND(avg(E001_INDEX_WEIGHT),2) as e001IndexWeight,
          ROUND(avg(E001_INDEX_VALUE),2)  as e001IndexValue,
          ROUND(avg(Q001_INDEX_SCORE),2)  as q001IndexScore,
          ROUND(avg(Q001_INDEX_WEIGHT),2) as q001IndexWeight,
          ROUND(avg(Q001_INDEX_VALUE),2)  as q001IndexValue
          from (
          select 
          (select area_name from DRGS_BUILT_AREA where AREA_CODE =
          (select AREA_CODE from UP_ORG_UNIT where ID=ORG_ID)
           and a_level = '2' and rownum = 1) as ORG_NAME,
          PLAN_ID,
          TOTAL,
          A_ALL,
          E_ALL,
          C_ALL,
          Q_ALL,
          A002_INDEX_SCORE,
          A002_INDEX_WEIGHT,
          A002_INDEX_VALUE,
          E002_INDEX_SCORE,
          E002_INDEX_WEIGHT,
          E002_INDEX_VALUE,
          E001_INDEX_SCORE,
          E001_INDEX_WEIGHT,
          E001_INDEX_VALUE,
          Q001_INDEX_SCORE,
          Q001_INDEX_WEIGHT,
          Q001_INDEX_VALUE 
          from v_index_esti_score_detail where plan_id='4028468162e1221b0162e1b19ad100d0' and ORG_DIM = '10')
          where ORG_NAME is not null
          group by ORG_NAME,PLAN_ID)select orgName,planId,
(select plan_name from DRGS_INDEX_ESTIMATE_PLAN where plan_id=planId and rownum=1) as planName,
rank() over (ORDER BY total desc) as totalRanking,
 total,
          aAll,
          eAll,
          cAll,
          qAll,
          a002IndexScore,
          a002IndexWeight,
          a002IndexValue,
          e002IndexScore,
          e002IndexWeight, 
          e002IndexValue, 
          e001IndexScore,
          e001IndexWeight, 
          e001IndexValue, 
          q001IndexScore,
          q001IndexWeight, 
          q001IndexValue 
          from (
          select 
          ORG_NAME as orgName,
          PLAN_ID as planId,
          ROUND(avg(TOTAL),2) as total,
          ROUND(avg(A_ALL),2) as aAll,
          ROUND(avg(E_ALL),2) as eAll,
          ROUND(avg(C_ALL),2) as cAll,
          ROUND(avg(Q_ALL),2) as qAll,
          ROUND(avg(A002_INDEX_SCORE),2)  as a002IndexScore,
          ROUND(avg(A002_INDEX_WEIGHT),2) as a002IndexWeight,
          ROUND(avg(A002_INDEX_VALUE),2)  as a002IndexValue,
          ROUND(avg(E002_INDEX_SCORE),2)  as e002IndexScore,
          ROUND(avg(E002_INDEX_WEIGHT),2) as e002IndexWeight,
          ROUND(avg(E002_INDEX_VALUE),2)  as e002IndexValue,
          ROUND(avg(E001_INDEX_SCORE),2)  as e001IndexScore,
          ROUND(avg(E001_INDEX_WEIGHT),2) as e001IndexWeight,
          ROUND(avg(E001_INDEX_VALUE),2)  as e001IndexValue,
          ROUND(avg(Q001_INDEX_SCORE),2)  as q001IndexScore,
          ROUND(avg(Q001_INDEX_WEIGHT),2) as q001IndexWeight,
          ROUND(avg(Q001_INDEX_VALUE),2)  as q001IndexValue
          from (
          select 
          c.area_name as ORG_NAME,
          PLAN_ID,
          TOTAL,
          A_ALL,
          E_ALL,
          C_ALL,
          Q_ALL,
          A002_INDEX_SCORE,
          A002_INDEX_WEIGHT,
          A002_INDEX_VALUE,
          E002_INDEX_SCORE,
          E002_INDEX_WEIGHT,
          E002_INDEX_VALUE,
          E001_INDEX_SCORE,
          E001_INDEX_WEIGHT,
          E001_INDEX_VALUE,
          Q001_INDEX_SCORE,
          Q001_INDEX_WEIGHT,
          Q001_INDEX_VALUE 
          from v_index_esti_score_detail a,UP_ORG_UNIT b,DRGS_BUILT_AREA c where a.ORG_ID=b.id and b.AREA_CODE=c.AREA_CODE and c.a_level='2' and  plan_id='4028468162e1221b0162e1b19ad100d0' and ORG_DIM = '10')
          where ORG_NAME is not null
          group by ORG_NAME,PLAN_ID)

Answer 1

第1个SQL效率高些~

Answer 2

如果能执行出来的话，建议使用dbms_xplan.display_cursor比较下逻辑读（当然这个用sqlplus的autotrace也可以获取到），并看下是否还存在一些瓶颈，是否还能被优化？仅仅一个F5的信息太少了，重要的谓词信息都没有