topic Re: Concepts SQL in Application Development and Automation Discussions

Concepts SQL

former_member425121 — Wed, 24 Jan 2007 19:06:11 GMT

Hi everybody

Can somebody help me with this two concepts:

If i have a Table with two key Fields:

FieldA key

FieldB Key

a) If i make a Select using both key fields, but the first key field is not resctricted:

SELECT... WHERE

FieldA IN SelOptsA AND <-- empty range

FieldB IN SelOptsB <-- range with one value

The select uses the Index even the first key is not filled ?

Even if the select really is not resctricting any value for the first key field , but is taking all values for that FieldA.

b) If i make a Select using first another Field wich is not key, but it's not restricted:

SELECT....WHERE

FieldX IN SelOptsX AND <-- empty range

FieldA IN SelOptsA AND <-- range with one value

FieldB IN SelOptsB <-- range with one value

The select is 'inteligent' for use the Index ?

That is, the select can detect that first field (FieldX) has not really a restriction (it's taking all values) and see that the next fields restricted are in a Index ?

(I do not know if a Select first check what fields are really restricted and what are restricted to ALL values; for get the fields restricted and search an Index wich compliance the key)

Thanks

Frank

Re: Concepts SQL

RichHeilman — Wed, 24 Jan 2007 19:24:20 GMT

Hi Frank, it appears that you may be questioning the optimizer's methods. I will not comment on what I think the optimizer is doing, because I simply don't know and it may be different from system to system and database to database. So all I can offer is a little reading material. I will say that the optimizer is looking for the best access path and it is looking at the avaiable indexes as well as the restrictions in your WHERE clause.

http://help.sap.com/saphelp_nw04s/helpdata/en/fb/36fe3b34334a7be10000000a114084/frameset.htm

http://help.sap.com/saphelp_nw04s/helpdata/en/2d/0ef63ebdee3a4c958f51cfee32ad3a/frameset.htm

Regards,

Rich Heilman

Re: Concepts SQL

Former Member — Wed, 24 Jan 2007 19:38:21 GMT

Check this:

/people/rob.burbank/blog/2006/09/13/using-an-index-when-you-dont-have-all-of-the-fields

What I'm saying there is that if you are missing say the first field of a key, it's worth you while to take some extra measures to get that field (at least in Oracle and DB2).

Rob

Message was edited by:

Rob Burbank

Re: Concepts SQL

Former Member — Wed, 24 Jan 2007 20:15:31 GMT

I agree with Rob. If you do not have the first key field, your performance would be adversely impacted.

Re: Concepts SQL

former_member425121 — Fri, 26 Jan 2007 17:12:49 GMT

Thanks..