Secondary Index Usage

[Former Member]

Hi,

Can I use extra field with Index in where clause. e.g. for selecting from MSEG table there is one index available MSEG-M which has 5 fields, but i need one more field which is not in index. So now in my where clause there will be 6 fields, 5 from index and 1 field i added. In this case whether the Index will work OR not?

Any response will be highly appreciated.

Cheers,

Lokesh.

[Former Member]

It won't cause a problem.

Rob

[Former Member]

Hi,

the index will work but make sure that in the where claues you use the correct sequence of field, first use the 5 index fields in the order in the MSEG-M and then lastly the other field.By doing so , the index M will be your selecting condition and the field 6 will be your filtering condition. Try out like this and check for Trace in ST05 for better understanding .

Hope this helps you

Raj

Edited by: Raj on Dec 15, 2008 4:49 PM

[ThomasZloch]

The order of the fields in the WHERE-clause makes no difference. Find out yourself by trying to switch the order around and compare runtimes (always discard the first measurement though).

Thomas

[Former Member]

Hi,

it will add to the overall selectivity of your WHERE clause:

Index column selectivity: restricts data block visits for the table lookups

table column selectivity: restricts the retrieved data blocks further on the non-index column

The combined selectivity is given to the optimizer to generate an execution plan.

So it's not only possible but wanted if you can restrict further down on your data.

bye

yk

[former_member194613]

@Yukon Kid,

what is the selectivity on the table, usually statistics are only available of index fields. Table selectivity has effects on actual performance, but not on optimizer estimations. Or did I miss something?

Siegfried

[Former Member]

Hi Siegfried,

I wanted to explain that you have the part of VISITING the index entries, and the part of RETURNING the data. You can only know the amount of rows to return if you did the table lookup. To calculate the retuned amount you need to know how selective this non-indexed predicate is.

Hence , statistics calculation INCLUDES all columns of the table to feed the optimizer.

Example:

to keep it simple I only focus on the selectivities (to calculate the costs you have to consider other

additional parameters ) using ANDed predicates with = .

• Index column selectivity: -> handles the fraction of data that you are going to VISIT via the index

• table column selectivity: -> handles only the fraction of data that you are going to RETURN

i = indexed, ni = not indexed

SELECT i_x  i_y  i_z  ni_a from tab WHERE i_x = 'A' and i_y = 'B' 
and i_z = 'C' and ni_a = 1


-----------------------------------------------------------------------------------------------------------------------------------------------------------
filter           statistics column 
                 selectivity
-----------------------------------------------------------------------------------------------------------------------------------------------------------

i_x = 'A'       0.5
i_y = 'B'       0.5
i_z = 'C'       0.5
                  ----------------> index sel.   =  sel. (i_x) * sel(i_y) * sel. (i_z)
                                                      =   0.5 * 0.5   * 0.5
                                                      =   0.125
                                                      = 12,5% of index entries must be visited
-----------------------------------------------------------------------------------------------------------------------------------------------------------

ni_a = '1'      0.1
                  ------------------> table sel. =  0.125 * 0.1
                                                     =  0.0125
                                                     =  1.25 % of rows will be returned
-----------------------------------------------------------------------------------------------------------------------------------------------------------

BUT...

Selectivity has nothing to do, really, with whether a column should be indexed. The questions you ask and the data patterns dictate what columns make sense in an index:

ie.:

It may that the field is NOT very selective but added to the index can help to avoid an additional table lookup.

bye

yk

[Former Member]

Your index will work.

But make sure you using any option (using NE, using IN and corresponding select-option table is empty - this will be as good as you are not specifying that field in where clause) which may cause your optimizer to not use that particular index.

Regards,

Mohaiyuddin

[Former Member]

>

> Your index will work.
> 
> But make sure you using any option (using NE, using IN and corresponding select-option table is empty - this will be as good as you are not specifying that field in where clause) which may cause your optimizer to not use that particular index.
> 
> Regards,
> Mohaiyuddin

Hi Mohaiyuddin,

the use of IN will not block the index from being used (negations like NOT IN will).

bye

yk

[Former Member]

>

> the use of IN will not block the index from being used (negations like NOT IN will).

Not entirely true. If you do something like:

SELECT        * FROM  bseg
       WHERE  bukrs  = 'XXXX'
       AND    belnr  = '1234567890'
       AND    gjahr  = 2008
       AND    buzei  = 2.

It will be fast. And if you:

SELECT        * FROM  bseg
       WHERE  bukrs NE 'XXXX'
       AND    belnr  = '1234567890'
       AND    gjahr  = 2008
       AND    buzei  = 2.

It will be slow. But if you:

SELECT        * FROM  bseg
       WHERE  bukrs  = 'XXXX'
       AND    belnr  = '1234567890'
       AND    gjahr  = 2008
       AND    buzei  NE 2.

It will also be fast. It depends on the selectivity of the fields in the index before the <>.

Rob

[former_member194613]

@YukonKid,

the comment was o.k, he was referring to ranges which also appear as an IN. And empty range disappears in the interface and is no condition, so no index can be used.

Selectivity of table fields ... that is no standard setting, usually they are not available.

Siegfried