index statistic

[Former Member]

Hi,

i use SAP on Oracle an i got a problem with an index.

My Table looks like that:

MANDT		CLNT(3)   Primary Key
Gsber		CHAR(4)   Primary Key
ItemID 		CHAR(38)  Primary Key
OrderID		NUMC(38)
Timestamp    	NUMC(14)
...

Now I have a few indexes:

Index1 - ( Mandt Gsber OrderID )

Index2 - ( Mandt Gsber Timestamp )

...

Now whenever i do a select like this:

SELECT * FROM mytable WHERE gsber = xx AND orderid = yy AND timestamp = zz

the index2 is taken, ignoring that index1 is much faster beacuse for any orderid there are max 4 entrys but for for any timestamp there are really lot of...

So i had a look for the statistics and found this:

    NONUNIQUE  Index1          
    Column Name                     #Distinct       
    MANDT                                          2
    GSBER                                         29
    ORDERID                                        3
                                                                                NONUNIQUE  Index2                
    Column Name                     #Distinct       
    MANDT                                          2
    GSBER                                         29
    TIMESTAMP                                 39.394

I understand distinct means diffrent rows...?!

So first index statistics are wrong, because i have much more distinct entrys.

I started table anlyse but same values still...

I dont want to use HINT in my select statements!

Does anyone have an idea why statistics are wrong?

Maybe problem with numc(38) in index?

Thanks for replies and ideas!

Benjamin Garn

[Former Member]

Hi,

i use SAP on Oracle an i got a problem with an index.

My Table looks like that:

MANDT		CLNT(3)   Primary Key
Gsber		CHAR(4)   Primary Key
ItemID 		CHAR(38)  Primary Key
OrderID		NUMC(38)
Timestamp    	NUMC(14)
...

Now I have a few indexes:

Index1 - ( Mandt Gsber OrderID )

Index2 - ( Mandt Gsber Timestamp )

...

Now whenever i do a select like this:

SELECT * FROM mytable WHERE gsber = xx AND orderid = yy AND timestamp = zz

the index2 is taken, ignoring that index1 is much faster beacuse for any orderid there are max 4 entrys but for for any timestamp there are really lot of...

So i had a look for the statistics and found this:

    NONUNIQUE  Index1          
    Column Name                     #Distinct       
    MANDT                                          2
    GSBER                                         29
    ORDERID                                        3
                                                                                NONUNIQUE  Index2                
    Column Name                     #Distinct       
    MANDT                                          2
    GSBER                                         29
    TIMESTAMP                                 39.394

I understand distinct means diffrent rows...?!

So first index statistics are wrong, because i have much more distinct entrys.

I started table anlyse but same values still...

I dont want to use HINT in my select statements!

Does anyone have an idea why statistics are wrong?

Maybe problem with numc(38) in index?

Thanks for replies and ideas!

Benjamin Garn

[Former Member]

Index is not generated automatically in SAP. If you have created the Index you need to generate/populate them. Try populating the Index and it will pick up the first index for you.

- Guru

Reward points for helpful answers

[LucianoBentiveg]

Run report RSANAORA to regenerate index.

Regards.

[christian_wohlfahrt]

Hi,

there is transaction DB20, where you can see last run of statistics update (for a specific table) and trigger a new run.

Regards,

Christian

[Former Member]

Thank you for answers, but as i told u: Rebuild of statistics dont help. There is still the same wrong value in it.

[Former Member]

You might get some ideas from SAP note 588668.

Rob

[Former Member]

Hi Rob,

this note contained a link to note 365480 which pointed out my problem.

That's what i needed!

My Col was N(38) --> Varchar2(38) on Oracle.

Only first 32 characters are used for statistic.

Thank you!