Anyone good at re-writing sql ??
Okay - I've already said I'm not very good at this coding lark....
explain plan for
select *
from affected_cat_cust
where (hurn,purn,cat_id) in
(select hurn,purn,cat_id
from
(select hurn,purn,cat_id,count(*)
from affected_cat_cust
group by hurn,purn,cat_id
having count(*) = 1
)
)
and (hurn,purn,cat_id) not in(select hurn,purn,cat_id from affected_catalogue_customer)
-------------------------------------------------------
OPERATION OPTIONS OBJECT_NAME OPTIMIZER COST CARDINALITY
----------------------------------- ---------------- -------------------- ---------- --------- -----
SELECT STATEMENT CHOOSE 618222 5681
FILTER
HASH JOIN SEMI 4674 5681
TABLE ACCESS FULL AFFECTED_CAT_CUST ANALYZED 2018 142014
VIEW VW_NSO_1 2656 28403
FILTER
SORT GROUP BY 2656 28403
TABLE ACCESS FULL AFFECTED_CAT_CUST ANALYZED 2018 2840283
TABLE ACCESS FULL AFFECTED_CATALOGUE_C ANALYZED 108 11
------------------------------------------------------
I know the 'having count(*) = 1' is giving me a filter, and the 'not in' clause is giving me the other.
Can anyone re-write this to maintian the integrity of the statement, but remove the flamin' filters ???
Dapi - how about you ??
much obliged.
Horace.
Re: Anyone good at re-writing sql ??
Code:
select *
from affected_cat_cust a1
.
.
.
.
and not exists
(select * from affected_catalogue_customer c
where c.hurn = a1.hurn
and c.purn = a1.purn
and c.cat_id = a1.cat_id)
You do have analyzed indexes on hurn, purn and /or cat_id ? (best would be one index on all three). You can do the same kind of thing for the IN as well - but there must be a better way . .. haven't seen it yet . . .