index problem RESB

[nicola_blasi]

Hello

i have the following query :

SELECT

"POSTP" , "PSPEL" , "GPREIS" , "BDMNG" , "WAERS" , "PEINH" , "RSPOS" , "RSNUM"

FROM

"RESB"

WHERE

"MANDT" = :A0 AND "PSPEL" = :A1 AND "POSTP" IN ( :A2 , :A3 ) AND "XLOEK" = :A4#

I have created an index

NONUNIQUE Index RESB~Z04

Column Name #Distinct

MANDT 1

PSPEL 1

POSTP 5

XLOEK 2

Last statistics date 12.07.2008

Analyze Method mple 20.967.515 Rows

Levels of B-Tree 3

I think that something is wrong above all regarding the high analyze method ...the table goes in table access full and it doesn't use the index created. What do you suggest ?

Thanks

Regards

Nicola

[lbreddemann]

Hi,

Stefan is actually right - without more information we could only guess. But let's guess a bit...

The column-statistics you provided show that only the fields POSTP (5) and XLOEK (2) can actually reduce the number of rows to be read.

XLOEK is usually VERY skew as most rows should have an ' ' in it.

That leaves POSTP as a selection criteria.

The IN-Condition is assumed to reduce the number of rows down to 2/5 of the whole table.

So for about 8387006 rows the index blocks have to be read and then the coressponding table block needs to be read.

And that is only the case if POSTP is evenly distributed.

Depending on what you actually select there (perhabs you're looking for rows that have 'seldom' values in XLOEK and POSTP?) you may consider to create Histograms on these columns and use Literals in the Query (ABAP -> use_literals hint!) so that Oracle 'knows' that the index is better for this query.

If you actually select the values that fit to most of the rows, than the full table scan would be the best choice here.

Regards,

Lars

[stefan_koehler]

Hello Nicola,

please post the statistics of the table and the columns - the index statistic is not enough.

Regards

Stefan

[nicola_blasi]

Hello

sorry but i have this problem since around 12 of july. I did the statistics that days but the table went always in table access full.

Regards

Nicola