Skip to Content
0
Jul 17, 2009 at 02:18 PM

Column(s) selectivity

146 Views

2 questions (not really sure if it's against the rules to pose two questions in one post, but here they go):

Question 1: We have in MSEG one index by CHARG (MSEGZ01) and another one by WERKS + MATNR (MSEGM)

(statistics 100%)

nr rows: 24,991,486
matnr:      225,480     
werks:           90
charg:    6,882,957

CHARG is in fact more selective than MATNR + WERKS,

even if nr distinct values charg < nr distinct values werks x nr distinct values matnr

(because in reality each material appears only in 1 or 2 plants)

The following query, then, uses MSEGM and not MSEGZ01 (which would be faster).

select *
from   mseg 
where  mandt = :a1
and    werks = :a2
and    matnr = :a3
and    charg = :a4

Apart from using hints (I don't want to force Z01 because the report has other optional parameters, and if filled other indexes should be selected), any ideas on how this is usually solved? For now the solution we have is to not use WERKS in the where condition (and then CHARG is more selective than MATNR, and Z01 is used).

Question 2: In our MSEG, the density for CHARG is not 1/distinct values (and that happens, I think, because we have MSEG with histograms - don't ask me why, but we do). Question is, how is this density calculated?

select table_name, column_name, num_distinct, 1/num_distinct, density, num_buckets
from   all_tab_columns
where  table_name = 'MSEG'
and   column_name = 'CHARG'

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT 1/NUM_DISTINCT    DENSITY NUM_BUCKETS
------------------------------ ------------------------------ ------------ -------------- ---------- -----------
MSEG                           CHARG                               6882957     1,4529E-07 ,000115903          61

I saw somewhere that the formula would be

sum(count^2) / ( sum(count)^2 )

but that doesn't seem to produce the expected density:

select sum(sq) / (sum(cnt) * sum(cnt))
from ( select charg, count(*)*count(*) sq, count(*) cnt from sapr3.mseg group by charg )

SUM(SQ)/(SUM(CNT)*SUM(CNT))
---------------------------
                 ,038637666