这样的语句改成表连接,怎么改
select
cp.pair_id,substr(trim(cp.pair_id),10,3),'S004'
from rimbase.cbl_pair cp
where cp.opr_state_id = 170003
and cp.delete_state = 0
and not exists
(
select * from
rimbase.asn_res_link arl
where
arl.res_id = cp.pair_id
and arl.delete_state = 0
)
select
cp.pair_id,substr(trim(cp.pair_id),10,3),'S004'
from rimbase.cbl_pair cp
where cp.opr_state_id = 170003
and cp.delete_state = 0
and not exists
(
select * from
rimbase.asn_res_link arl
where
arl.res_id = cp.pair_id
and arl.delete_state = 0
)
我听说优化语句上面说可以不用not exists,改成表链接,速度更快,不会改
另外能不能解释下“表链接”的具体含义?
cp.pair_id,substr(trim(cp.pair_id),10,3),'S004'
from rimbase.cbl_pair cp,rimbase.asn_res_link arl
where
(arl.res_id <> cp.pair_id
or arl.delete_state <> 0 )
and cp.opr_state_id = 170003
and cp.delete_state = 0
FROM RIMBASE.CBL_PAIR CP, RIMBASE.ASN_RES_LINK ARL
WHERE CP.OPR_STATE_ID = 170003
AND CP.DELETE_STATE = 0
AND ARL.DELETE_STATE = 0
AND ARL.RES_ID(+) = CP.PAIR_ID
AND ARL.RES_ID IS NULL;
create table cbl_pair
(pair_id char(20),
opr_state_id number,
delete_state number
)
insert into cbl_pair values('01234567890123456789', 170003,0);
insert into cbl_pair values('01234567890123456788', 170003,0);
insert into cbl_pair values('01234567890123456786', 170003,1);
insert into cbl_pair values('01234567890123456787', 170004,0);create table asn_res_link
(res_id char(20),
delete_state number
)
insert into asn_res_link values('01234567890123456789', 1);
insert into asn_res_link values('01234567890123456788', 0);
insert into asn_res_link values('01234567890123456786', 1);
insert into asn_res_link values('01234567890123456787', 0);
select
cp.pair_id,substr(trim(cp.pair_id),10,3),'S004'
from cbl_pair cp
where cp.opr_state_id = 170003
and cp.delete_state = 0
and not exists
(
select * from
asn_res_link arl
where
arl.res_id = cp.pair_id
and arl.delete_state = 0
)
--结果:
'01234567890123456789', '901' , 'S004'
select distinct
cp.pair_id,substr(trim(cp.pair_id),10,3),'S004'
from cbl_pair cp,asn_res_link arl
where
(arl.res_id <> cp.pair_id
or arl.delete_state <> 0 )
and cp.opr_state_id = 170003
and cp.delete_state = 0--结果:
'01234567890123456788', '901' , 'S004'
'01234567890123456789', '901' , 'S004'
SELECT CP.PAIR_ID, SUBSTR(TRIM(CP.PAIR_ID), 10, 3), 'S004'
FROM CBL_PAIR CP,ASN_RES_LINK ARL
WHERE CP.OPR_STATE_ID = 170003
AND CP.DELETE_STATE = 0
AND ARL.DELETE_STATE = 0
AND ARL.RES_ID(+) = CP.PAIR_ID
AND ARL.RES_ID IS NULL;--结果:
NULL
SELECT CP.PAIR_ID, SUBSTR(TRIM(CP.PAIR_ID), 10, 3), 'S004'
FROM CBL_PAIR CP,ASN_RES_LINK ARL
WHERE CP.OPR_STATE_ID = 170003
AND CP.DELETE_STATE = 0
AND ARL.DELETE_STATE = 0
AND ARL.RES_ID(+) = CP.PAIR_ID
--结果:
'01234567890123456788', '901' , 'S004'结论:5楼、7楼的语句似乎都不是很正确。
SELECT CP.PAIR_ID, SUBSTR(TRIM(CP.PAIR_ID), 10, 3), 'S004'
FROM CBL_PAIR CP,ASN_RES_LINK ARL
WHERE CP.OPR_STATE_ID = 170003
AND CP.DELETE_STATE = 0
AND ARL.DELETE_STATE(+) = 0
AND ARL.RES_ID(+) = CP.PAIR_ID
AND ARL.RES_ID IS NULL;