Query execution

[sonaliak]

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 ?

[chrisrlong]

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

[sonaliak]

Thanks a lot.

[sonaliak]

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

[chrisrlong]

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