topic Re: index statistic in Application Development and Automation Discussions

index statistic

Former Member — Tue, 12 Dec 2006 15:50:51 GMT

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

Re: index statistic

Former Member — Tue, 12 Dec 2006 18:23:12 GMT

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

Re: index statistic

LucianoBentiveg — Tue, 12 Dec 2006 18:54:56 GMT

Run report RSANAORA to regenerate index.

Regards.

Re: index statistic

christian_wohlfahrt — Wed, 13 Dec 2006 13:00:24 GMT

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

Re: index statistic

Former Member — Fri, 15 Dec 2006 15:55:42 GMT

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

Re: index statistic

Former Member — Fri, 15 Dec 2006 16:07:15 GMT

You might get some ideas from SAP note 588668.

Rob

Re: index statistic

Former Member — Mon, 18 Dec 2006 08:25:12 GMT

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!