Partial Key Selects

[Former Member]

Hi everyone

I am aware that when writing a select statement it is preferable to have all the PK fields in the where clause and failing that to have as many of the leftmost key fields as possible. However, I have a situation where I am only interested in finding records based on seperate parts of the key. For example, I have a table with PK fields PK1, PK2, PK3, PK4 and PK5. I am able to provide data in the where clause for PK1, PK2 and PK5, however, for PK3 <b>all</b> of the records contain 0 and while different records contain different data for PK4 I do not want to restrict the select on this field.

I have come up with the following options but do not know, which is the best in terms of performance:

1. Create a new index on the PK fields I an interested in

2. Provide the Where clause with:

2.1 - PK1, PK2 and PK5 only

2.2 - PK1, PK2, PK3 = 0

2.3 - PK1, PK2, PK3 = 0, (PK4 = X OR PK4 = Y etc), PK5

2.4 - PK1 and PK2 only

Can anybody help? Options 2.2 and 2.4 will obviously require some further processing on the itab to handle the PK5 field. I don't believe 2.4 will cause a full table scan but I'm not so sure about 2.1. Also, is it better to include a PK in the where clause even if all data for it is required (option 2.2 and 2.3). I would rather avoid creating a new index if possible.

Thanks in advance

Andy

[Former Member]

Since you allready have primiary feild, i would like to rule out the first option of creating an index only for this selection.

In fact I would suggest to use all the primary key fields if possible. if not the maximum numbers.

ie: in where as

<b> PK1, PK2, PK3 = 0, PK1 IN range_pk1 , PK5</b>

Here range_pk1 is a RANGE and will hold all the possible values needed for selection.

Or if this is difficult i prefer to use <b> PK1, PK2, PK3 = 0, PK5</b>

~thomas.

[Former Member]

Hi Andrew,

U r already aware wid rule of thumb of leftmost key fields.

Making new index with your required fields is best from the point of view of performance. But ask the question that is it really necessary, I mean is records exceeding 2 Lacks?

Systems are powerful and should take care of the various where clause u asked if the answer to above is no.

Regards,

Deva.

[Former Member]

Hi Andy,

using partial keys is not recommendable.

In your case pass the blank ranges in where condition for PK3 & PK4. This will make the index complete.

If u would have PK1 , Pk2 & Pk3 in where condition where pk4 & pk5 is not required then no need ot pass blank range in those fileds.

Note : please maintain the sequence of primary key same as in database structure.

Thanks

kapil

[Former Member]

Hi Andrew,

Guess the option

2.1 - PK1, PK2 and PK5 only

Looks fine from my point of view. But i would suggest you to use the EXPLAIN SQL STATEMENT option from ST05 tcode and try all your select options and check what is the best access type. This is the best and most accurate way to identify the most optimized selects.

Cheers

VJ

If it helped some points would be nice

[Former Member]

Thanks VJ

I have both used ST05 and done some run-time analysis of the program and it looks as if this is the best option. Using the full key takes about twice as long as the split, partial key option. This in turn is slower than just using PK1 and PK2 but not by much so I'm prepared to take the performance hit on that! I find it strange that using most of the key actually causes a deteriation in performance. I can only guess that it is to do with the selectivity of the data. Since there didn't seem to be many ditinct values in most of the key columns I guess the overhead of using the full key outweighed any search performance gains.

Thanks again to everyone for you advice.

Andy

[Former Member]

1. Create a new index on the PK fields I an interested in

--> Not needed as you already have them as PK.

2. Provide the Where clause with:

2.1 - PK1, PK2 and PK5 only

--> That should be fine as you have the required data on the above fields. Surely you can include PK3 = 0 in the where clause if you are certain that it is to remain 0.

2.2 - PK1, PK2, PK3 = 0

2.3 - PK1, PK2, PK3 = 0, (PK4 = X OR PK4 = Y etc), PK5

2.4 - PK1 and PK2 only

the rest of the options can be ignored.

Regards

Anurag