Anyone good at re-writing sql ??

[Horace]

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.

[DaPi]

My first would be to replace (NOT) IN with (NOT) EXISTS . . .

[Horace]

DaPi - I knew you were out there
How do I code the NOT Exists ??
I'll have another go, whilst I wait for your wisdom........

and (hurn,purn,cat_id) not exists (select hurn,purn,cat_id from affected_catalogue_customer)
*
ERROR at line 13:
ORA-00920: invalid relational operator

[DaPi]

 
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 . . .

[Horace]

Ruddy Marvellous...........
That nasty filter has disappeared and been replaced with an anti hash join.

How many pints is that now DaPi ??



OPERATION OPTIONS OBJECT_NAME OPTIMIZER COST CARDINALITY
----------------------------------- ---------------- -------------------- ---------- --------- -----
SELECT STATEMENT CHOOSE 4822 1
HASH JOIN ANTI 4822 1
HASH JOIN 4714 1
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_CAT_CUST ANALYZED 2018 2840283
TABLE ACCESS FULL AFFECTED_CATALOGUE_C ANALYZED 108 90126
USTOMER

[DaPi]

You're still doing FTS - is that inevitable? The NOT logic (as we have discussed recently) might be able to use a FIS instead . . .
The IN becoming EXISTS might be more effective: I wondered about recoding the first bit

select *
from affected_cat_cust a1
where exists 
(select a2.hurn,a2.purn,a2.cat_id,count(*)
 from affected_cat_cust a2
 where a2.hurn = a1.hurn
 and   a2.purn = a1.purn
 and   a2.cat_id = a1.cat_id
 group by a2.hurn,a2.purn,a2.cat_id
 having count(*) = 1)

[slimdave]

How about ...

Select
   hurn,
   purn,
   cat_id,
   .... etc ...
From
   (
   Select
   hurn,
   purn,
   cat_id,
   .... etc ...
   count(*) Over 
      (partition by
         hurn,
         purn,
         cat_id) c_star
   From
      affected_cat_cust
   )
Where c_star = 1;

[abhaysk]

Do you have index on the table, if yes on what cols?

[DaPi]

OH YES slimdave ! ! ! !

I obviously need to go on an Analytic Functions Summer Camp - I don't have the right reflexes yet

[Horace]

Oh Bummer, gonna have to get me head round analytical functions again......

(Deep Breath)..................