Anyone good at re-writing sql ??
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Anyone good at re-writing sql ??

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    My first would be to replace (NOT) IN with (NOT) EXISTS . . .

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    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 06:47 AM.

  5. #5
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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)

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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;
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    OH YES slimdave ! ! ! !

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

  10. #10
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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
  •  


Click Here to Expand Forum to Full Width