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表的数据量很大,看看能否再该进下?
解决方案 »
- 帮忙看下这个语句哪里错了?
- 查询系统内部的视图也报错了。。。请高手执教!!谢谢
- 我将两个函数定义在一个*.sql文件中,在编译是,却不能编译通过,怎么回事?分开后编译,正常通过.
- 存储过程返回OUT参数的游标问题!!!急,望牛人帮忙
- 请问如何复制一个表
- 用户与表空间及表名称问题
- [急!!!] 关于Oracle占用CPU. 高手进来看看!
- oracle中关于clob读取
- 如何解决Oracle 8i 的SQLPlus Worksheet中简体中文显示为乱码的问题?
- oracle中查询时日期时间作为条件
- RAC安装时碰到"failed to initialieze ocrconfig",后来我修改了问题,并重新运行root.sh成功,还能接着后面的配置助手吗?
- 序列会用完吗?
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)
一样的吧
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
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(+)
能解释下为什么错了吗? 我也认为connect by是没有必要的
能解释下为什么错了吗? 我也认为connect by是没有必要的
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='李天'恩 有理 采用视图解决 就用这个了