-
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
-
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 . . .
Last edited by DaPi; 08-22-2003 at 05:47 AM.
-
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?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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)..................
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|