Query execution
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Query execution

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    I need help in deciding subquery is better or a join ???
    Select Res_ID
    from mwebRes
    where Res_Status <> 20 AND Res_Entity_Type = 3
    AND (Res_ID IN (Select AuthAttr_Res_ID
    From MwebAuthAttrib
    AND (AuthAttr_Work_ID IN
    (Select Work_ID
    From MwebWork
    Where DECODE(inWorkLevel,2,Work_Par2,3,Work_Par3,4,Work_Par4,5,
    Work_Par5,6,Work_par6,7,Work_par7 ,8 ,work_par8,Work_Par9 ) = inProject
    AND Work_Entity_Type = 4 AND Work_Status <> 40))
    AND AuthAttr_Role_ID = inRole));
    ------------------------------------------------

    Select a.Res_ID
    from mwebRes a, AuthAttr_Res_ID b, mwebwork c
    where a.Res_Status <> 20 AND a.Res_Entity_Type = 3
    AND a.Res_ID =b.AuthAttr_Res_ID
    AND b.AuthAttr_Work_ID =c.Work_ID
    and DECODE(inWorkLevel,2,c.Work_Par2,3,c.Work_Par3,4,c.Work_Par4,5,
    c.Work_Par5,6,c.Work_par6,7,c.Work_par7 ,8 ,c.work_par8,c.Work_Par9 ) = inProject
    AND c.Work_Entity_Type = 4 AND c.Work_Status <> 40
    AND b.AuthAttr_Role_ID = inRole;

    Which one is faster ??
    What is the sequence of execution ?
    Sonali

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Sub-selects should always be avoided. They limit the choices for the optimizer. Mind you, this query is not terribly complicated so the optimizer would probably get to the same place anyway. However, as a general rule - avoid sub-selects when a join will do!

    Therefore, the join is the better choice.

    As for the sequence of events, you are much better equipped to tell that than I. Check the EXPLAIN PLAN and see what it is doing.

    However, I thought I'd give it a shot:

    Select
    ===a.Res_ID
    from
    ===mwebRes a,
    ===AuthAttr_Res_ID b,
    ===mwebwork c
    where
    ===a.Res_Status <> 20 AND
    ===a.Res_Entity_Type = 3 AND
    ===b.AuthAttr_Res_ID = a.Res_ID AND
    ===b.AuthAttr_Role_ID = inRole AND
    ===c.Work_ID = b.AuthAttr_Work_ID AND
    ===c.Work_Entity_Type = 4 AND
    ===c.Work_Status <> 40 AND
    ===DECODE(
    ======inWorkLevel,
    ======2, c.Work_Par2,
    ======3, c.Work_Par3,
    ======4, c.Work_Par4,
    ======5, c.Work_Par5,
    ======6, c.Work_par6,
    ======7, c.Work_par7,
    ======8, c.work_par8,
    =========c.Work_Par9) = inProject

    After cleaning things up a bit (nasty habit of mine :) ), I noticed some things.

    3 tables, a,b,c. There is a relationship between a and b and between b and c, but nothing between a and c. Therefore, the optimizer will not go CAB or ACB. It will most likely go ABC or CBA, but BCA and BAC are still possible. Since all 3 tables have additional constraints on them, I can't tell which table it will start with. Sorry, but you'll have to test this one on your own.

    That DECODE needs to be commented on however. There is obviously some kind of many-1 relationship here that has been modelled into a single table - *always* a bad idea. It locks you into a 1..<constant> relationship, wastes space, and forces you to do silly DECODES in your SELECT statements meaning no index, etc. There are just a ton of reasons *not* to do that. Just an FYI, but I'm sure you knew that :)


    HTH,

    - Chris


  3. #3
    Join Date
    Jan 2001
    Posts
    318
    Thanks a lot.
    Sonali

  4. #4
    Join Date
    Jan 2001
    Posts
    318

    Angry

    Yes, you are right we have lots of one to many relationships in our tables(fields in table). Well, there is not much I can do to change the design.. .............
    Is there any document which will explain repercations of using one- many relationships in a table (for my reference).
    Is it true that indexes won't be used if I have DECODE in where clause ??

    Thanks again
    Sonali
    Sonali

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I don't have any references for you off-hand, but trust me - you do *not* want to model things that way. There are just sooooo many reasons not to do that.

    To wit:

    - What if you need a 10th value for someone? Now you need to add a new field to your table and change all those SQL statements with the little DECODEs in them

    - How do you do maintenance on them? If someone goes from "a b c d" to "a b c d e f" to "a b d f"? Are the third and fifth entries blank? Do you compress them down? If you compress them, what if they add c back in? Do you re-sort them?

    - Indexes. First of all, you would have to index all 9 fields. This is 9 times more indexes than you need. Also, you can only search in one of two ways - either with a DECODE or a lot of ORs In neither case will an index be used.

    ======================
    An index will *only* ever be used on a column when that column appears COMPLETELY ALONE on its side of the logical operator in the WHERE clause. WHERE x > y + 5, for example. An index on X could be used, but *not* one on Y.
    ======================

    - What if you want to add a corrseponding attribute? For example, say you were modelling a car. Your model would be
    CAR_T(
    CAR_ID
    ...
    TIRELF_ID,
    TIRERF_ID,
    TIRELR_ID,
    TIRERR_ID)

    Now what if you want to record the wear on each tire? Now you need 4 more fields. It is easy with a proper model:

    CAR_T(
    CAR_ID,
    ...)

    CARTIRE_T(
    CAR_ID,
    TIRE_ID,
    LOCATION ((LF,RF,LR,RR)),
    WEAR_CD)

    Note the flexibility I just gained. I can add things like miles for each tire. I can expand my location codes to handle 18-wheelers. I can index any of these fields and they will all be used in queries. I can easily find any tires on any cars that have extremely heavy wear.

    This is the entire purpose of proper data modelling techniques - to reduce redundancy and enhance flexibility. I could go on but I'm running out of steam. Just accept the bad situation you are in and learn from it.

    ... and never, never, never model like this in the future :)

    - Chris


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