改进sql的查询效率，付f5结果，请达人帮忙

Question

SELECT PARTY.PARTY_ID AS PARTYID,
       PARTY.NAME AS PARTYNAME,
       PARTY.AREA_ID AS AREAID,
       PARTY.DEFAULT_ID_TYPE AS DEFAULTIDTYPE,
       PARTY.PARTY_TYPE_CD AS PARTYTYPECD,
       PARTY.ADDRESS_ID AS ADDRESSID,
       PARTY.ADDRESS_STR AS ADDRESSSTR,
       PARTY.SIMPLE_SPELL AS SIMPLESPELL,
       PARTY.BUSINESS_PASSWORD AS BUSINESSPASSWORD,
       PARTY.CREATE_DT AS CREATEDT,
       PARTY.VERSION AS PARTYVERSION,
       PARTY.INDUSTRY_CLASS_CD AS INDUSTRYCLASSCD,
       PARTY.QUERY_PASSWORD AS QUERYPASSWORD,
       PARTY.PARTY_STATUS_CD AS PARTYSTATUSCD,
       PARTY.CREATOR,
       I.IDENTIDIES_TYPE_CD AS IDENTIDIESTYPECD,
       I.IDENTITY_NUM AS IDENTITYNUM,
       A.NAME AS AREANAME
  FROM PARTY PARTY
  LEFT OUTER JOIN PARTY_IDENTITY I ON PARTY.PARTY_ID = I.PARTY_ID
  LEFT OUTER JOIN AREA A ON PARTY.AREA_ID = A.AREA_ID
 WHERE PARTY.AREA_ID IN
       (SELECT A.AREA_ID
          FROM AREA A
         START WITH A.AREA_ID = 21
        CONNECT BY PRIOR A.AREA_ID = A.PARENT_AREA)
   AND PARTY.PARTY_STATUS_CD = 1
   AND PARTY.NAME = '吴向东'
   AND ROWNUM < 10按下f5后执行结果
SELECT STATEMENT, GOAL = CHOOSE Cost=22 Cardinality=4 Bytes=544
 COUNT STOPKEY
  HASH JOIN SEMI Cost=22 Cardinality=4 Bytes=544
   NESTED LOOPS OUTER Cost=19 Cardinality=4 Bytes=492
    NESTED LOOPS OUTER Cost=11 Cardinality=2 Bytes=190
     TABLE ACCESS BY INDEX ROWID Object owner=CRM Object name=PARTY Cost=9 Cardinality=2 Bytes=172
      INDEX RANGE SCAN Object owner=CRM Object name=IDX_PTY_NAME Cost=3 Cardinality=6
     TABLE ACCESS BY INDEX ROWID Object owner=BSS_SYS Object name=AREA Cost=1 Cardinality=1 Bytes=9
      INDEX UNIQUE SCAN Object owner=BSS_SYS Object name=PK_AREA Cardinality=1
    TABLE ACCESS BY INDEX ROWID Object owner=CRM Object name=PARTY_IDENTITY Cost=4 Cardinality=2 Bytes=56
     INDEX RANGE SCAN Object owner=CRM Object name=PK_PARTY_IDENTITY Cost=2 Cardinality=2
   VIEW Object owner=SYS Object name=VW_NSO_1 Cost=2 Cardinality=91 Bytes=1183
    CONNECT BY WITH FILTERING
     NESTED LOOPS
      INDEX UNIQUE SCAN Object owner=BSS_SYS Object name=PK_AREA Cardinality=1 Bytes=3
      TABLE ACCESS BY USER ROWID Object owner=BSS_SYS Object name=AREA
     HASH JOIN
      CONNECT BY PUMP
      TABLE ACCESS FULL Object owner=BSS_SYS Object name=AREA Cost=2 Cardinality=91 Bytes=546请达人帮忙？？

Answer 1

如果还需要什么数据 请给我提示，我附上，请给出sql效率慢的原因，

Answer 2

WHERE PARTY.AREA_ID IN
       (SELECT A.AREA_ID
          FROM AREA A
         START WITH A.AREA_ID = 21
        CONNECT BY PRIOR A.AREA_ID = A.PARENT_AREA)
area表大吗
要改的话就是这里了，area全表扫描

Answer 3

执行计划好乱呀.. 楼主重贴一下吧..SQLPLUS 里执行 EXPLAIN PLAN 
SQL>EXPLAIN PLAN FOR 你的sql语句; 
如 
SQL>EXPLAIN PLAN FOR SELECT * FROM EMP WHERE EMPNO=7369; 
然后
如果格式乱：就加个：set wrap off 
SQL>SELECT plan_table_output 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE')); 
用EXISTS替代IN、用NOT EXISTS替代NOT IN：
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
例子：
(高效)SELECT * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  EXISTS (SELECT ‘X'  FROM DEPT  WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = ‘MELB')
(低效)SELECT  * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB')Oracle SQL的优化
http://blog.csdn.net/tianlesoftware/archive/2009/10/14/4672023.aspx

Answer 4

  LEFT OUTER JOIN AREA A ON PARTY.AREA_ID = A.AREA_ID
 WHERE PARTY.AREA_ID IN
       (SELECT A.AREA_ID
          FROM AREA A
         START WITH A.AREA_ID = 21
        CONNECT BY PRIOR A.AREA_ID = A.PARENT_AREA)
把上面改为:  LEFT OUTER JOIN (SELECT A.AREA_ID
          FROM AREA A
         START WITH A.AREA_ID = 21
        CONNECT BY PRIOR A.AREA_ID = A.PARENT_AREA) A ON PARTY.AREA_ID = A.AREA_ID

Answer 5

如果只是查询ROWNUM < 10的大致数据
  LEFT OUTER JOIN PARTY_IDENTITY I ON PARTY.PARTY_ID = I.PARTY_ID
  LEFT OUTER JOIN AREA A ON PARTY.AREA_ID = A.AREA_ID
这里明显可以不用的，因为where中没有用到I和A表。(SELECT A.AREA_ID
          FROM AREA A
         START WITH A.AREA_ID = 21
        CONNECT BY PRIOR A.AREA_ID = A.PARENT_AREA)
这个执行出来的数据量有多大，如果比较大哪怕只有几十条。建议改为JOIN，毕竟IN的效率太低。
  FROM PARTY PARTY
  JOIN (SELECT A.AREA_ID
          FROM AREA A
         START WITH A.AREA_ID = 21
        CONNECT BY PRIOR A.AREA_ID = A.PARENT_AREA) A ON PARTY.AREA_ID=A.AREA_ID
;
       

Answer 6

各位老师：
   把in改为exist确实效率高多了，不过上面的对语句的分析比较贴切，让我汗颜，多学习，
  如果只是查询ROWNUM < 10的大致数据 
  LEFT OUTER JOIN PARTY_IDENTITY I ON PARTY.PARTY_ID = I.PARTY_ID 
  LEFT OUTER JOIN AREA A ON PARTY.AREA_ID = A.AREA_ID 
  这里明显可以不用的，因为where中没有用到I和A表。 
这个是基于什么理论，是不用，但是确实需要I表的信息

Answer 7

 SELECT PARTY.PARTY_ID        AS PARTYID,
        PARTY.AREA_ID         AS AREAID,
        PARTY.NAME            AS PARTYNAME,
        PARTY.DEFAULT_ID_TYPE AS DEFAULTIDTYPE,
        I.IDENTITY_NUM        AS IDENTITYNUM,
        I.IDENTIDIES_TYPE_CD  AS IDENTIDIESTYPECD,
        A.NAME                AS AREANAME
   FROM PARTY PARTY
   LEFT OUTER JOIN PARTY_IDENTITY I ON PARTY.PARTY_ID = I.PARTY_ID
   LEFT OUTER JOIN AREA A ON PARTY.AREA_ID = A.AREA_ID
  WHERE EXISTS (SELECT 1
           FROM AREA A
          WHERE PARTY.AREA_ID = A.AREA_ID
          START WITH A.AREA_ID = 21
         CONNECT BY PRIOR A.AREA_ID = A.PARENT_AREA)
    AND PARTY.PARTY_STATUS_CD = 1
    AND PARTY.NAME = '李天'
    AND ROWNUM < 10这样写快了两三倍，问是否嫩在改进下，谢谢？强烈感觉到exist的效率高于In