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.

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

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

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

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

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

Code:

---

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)

---

How about ...

Code:

---

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;

---

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

OH YES slimdave ! ! ! !

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

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

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

Quote:

---

Originally posted by DaPi
OH YES slimdave ! ! ! !

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

---

you know, I've only used them once in my real working life, as part of an ETL procedure. 99% of the time i use them in this forum.

Quote:

---

Originally posted by slimdave
you know, I've only used them once in my real working life, as part of an ETL procedure. 99% of the time i use them in this forum.

---

You mean to say that this isn't real life? :rolleyes:

Quote:

---

Originally posted by DaPi
You mean to say that this isn't real life? :rolleyes:

---

Not real work-ing life. Unless my dbasupport salary checks have been going to the wrong address.

I see that Oracle have "Senior Support engineer" jobs located just a few miles up the road from me. Sounds like the perfect vacation.

Try the below:

select *
from affected_cat_cust a
where (hurn,purn,cat_id) in
(select hurn,purn,cat_id
from affected_cat_cust
group by hurn,purn,cat_id
having count(*) = 1
)
not exists (SELECT 'x' from affected_catalogue_customer
WHERE hurn = a.hurn
and purn = a.purn
and cat_id = a.cat_id);


also add 2 indexes
1.) affected_cat_cust (hurn,purn,cat_id)
2.) affected_catalogue_customer (hurn,purn,cat_id)

MISSED "and" before the "not exists" in the last post

try this one --

select *
from affected_cat_cust a
where (hurn,purn,cat_id) in
(select hurn,purn,cat_id
from affected_cat_cust
group by hurn,purn,cat_id
having count(*) = 1
)
and not exists (SELECT 'x' from affected_catalogue_customer
WHERE hurn = a.hurn
and purn = a.purn
and cat_id = a.cat_id);


also add 2 indexes
1.) affected_cat_cust (hurn,purn,cat_id)
2.) affected_catalogue_customer (hurn,purn,cat_id)

Thanks Guys, changing the NOT IN to NOT EXISTS was enough to get the statement through in the required timescales. Thanks for all your other suggestions.

So cheers for that DaPi