sql语句再优化，看看能否帮助提高效率，

chyy001 2009-11-12 09:25:38



 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 , AREA A 

  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.AREA_ID = A.AREA_ID

    AND PARTY.PARTY_STATUS_CD = 1

    AND PARTY.NAME = '李天'

查询字段不能少，关于表连接是否能再优化下，party表的数据量很大，看看能否再该进下？

...全文

150 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

chyy001 2009-11-14

打赏
举报

[Quote=引用 7 楼 wildwave 的回复:]
改成这样试试
SQL codeSELECT PARTY.PARTY_IDAS PARTYID,
PARTY.AREA_IDAS AREAID,
PARTY.NAMEAS PARTYNAME,
PARTY.DEFAULT_ID_TYPEAS DEFAULTIDTYPE,
I.IDENTITY_NUMAS IDENTITYNUM,
I.IDENTIDIES_TYPE_CDAS IDENTIDIESTYPECD,
A.NAMEAS AREANAMEFROM PARTY PARTY, PARTY_IDENTITY I,
(SELECT NAME,AREA_IDFROM AREA A
STARTWITH A.AREA_ID=21
CONNECTBY PRIOR A.AREA_ID= A.PARENT_AREA) AWHERE PARTY.AREA_ID= A.AREA_IDAND PARTY.PARTY_STATUS_CD=1AND PARTY.NAME='李天'and PARTY.PARTY_ID= I.PARTY_ID(+)
[/Quote]



codeSELECT PARTY.PARTY_IDAS PARTYID,

        PARTY.AREA_IDAS AREAID,

        PARTY.NAMEAS PARTYNAME,

        PARTY.DEFAULT_ID_TYPEAS DEFAULTIDTYPE,

        I.IDENTITY_NUMAS IDENTITYNUM,

        I.IDENTIDIES_TYPE_CDAS IDENTIDIESTYPECD,

        A.NAMEAS AREANAMEFROM PARTY PARTY, PARTY_IDENTITY I, 

        (SELECT NAME,AREA_IDFROM AREA A

          STARTWITH A.AREA_ID=21

         CONNECTBY PRIOR A.AREA_ID= A.PARENT_AREA) AWHERE PARTY.AREA_ID= A.AREA_ID and PARTY.PARTY_ID= I.PARTY_ID(+) AND PARTY.PARTY_STATUS_CD=1 AND PARTY.NAME='李天'

恩有理采用视图解决就用这个了

duanzhi1984 2009-11-14

打赏
举报

增加对应列的索引

inthirties 2009-11-13

打赏
举报

索引情况是怎样的。各表数据量多少。

小灰狼W 2009-11-13

打赏
举报

改成这样试试

 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, PARTY_IDENTITY I, 

        (SELECT NAME,AREA_ID

           FROM AREA A

          START WITH A.AREA_ID = 21

         CONNECT BY PRIOR A.AREA_ID = A.PARENT_AREA) A 

  WHERE PARTY.AREA_ID = A.AREA_ID

    AND PARTY.PARTY_STATUS_CD = 1

    AND PARTY.NAME = '李天'

    and PARTY.PARTY_ID = I.PARTY_ID(+)

chyy001 2009-11-13

打赏
举报



SELECT STATEMENT,                     GOAL = CHOOSE		     	                             Cost=16	Cardinality=1	Bytes=63

 COUNT STOPKEY					

  FILTER					

   NESTED LOOPS OUTER			                                                                Cost=14	Cardinality=1	Bytes=63

    NESTED LOOPS OUTER		                      	                                        Cost=10	Cardinality=1	Bytes=35

     TABLE ACCESS BY INDEX ROWID     	Object owner=CRM	Object name=PARTY                 Cost=9	Cardinality=1	Bytes=26

      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	

   FILTER					

    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

小灰狼W 2009-11-13

打赏
举报

[Quote=引用 4 楼 wildwave 的回复:]
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)
这段有什么用？如果为true的话，比如存在一条A.AREA_ID = 21，如果这条存在，就成立，如果不存在，就不成立。所以connect by是没有必要的吧？

改成where exists(select 1 from area a where PARTY.AREA_ID = A.AREA_ID and A.AREA_ID = 21)
一样的吧
[/Quote]
是我搞错了..

小灰狼W 2009-11-13

打赏
举报

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)
这段有什么用？如果为true的话，比如存在一条A.AREA_ID = 21，如果这条存在，就成立，如果不存在，就不成立。所以connect by是没有必要的吧？

改成where exists(select 1 from area a where PARTY.AREA_ID = A.AREA_ID and A.AREA_ID = 21)
一样的吧

cosio 2009-11-13

打赏
举报

把执行计划的结果贴上来看看

chyy001 2009-11-13

打赏
举报

tianyazlf 2009-11-13

打赏
举报

[Quote=引用 5 楼 wildwave 的回复:]
引用 4 楼 wildwave 的回复:
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)
这段有什么用？如果为true的话，比如存在一条A.AREA_ID = 21，如果这条存在，就成立，如果不存在，就不成立。所以connect by是没有必要的吧？

改成where exists(select 1 from area a where PARTY.AREA_ID = A.AREA_ID and A.AREA_ID = 21)
一样的吧

是我搞错了..
[/Quote]

能解释下为什么错了吗？我也认为connect by是没有必要的

tianyazlf 2009-11-13