09-29-2006 9:13 AM
Dear All,
I created a single field index on one of my Ztables but my basis administrator receives an error message saying something about 'Missing Optimizer Statistics' in the index.
Let me tell you that my Ztable had 3 primary keys and I created the index on one of these. The index was created and activated successfully and I had received the message saying 'No Incosistencies Found'.
There is a possibility that I might have missed something.
Can anyone advice on the standard process of creating the index also.
Regards,
Alok.
09-29-2006 9:32 AM
Hi,
just a pair of thing about index that it's usefull to know.
1.When u create a table with 3 key field (for example A , B , C), a standard index called "~0" is created based on key fields.
2.If u have MANDT field in your table in index u have necessary MANDT as first field of the index, otherwise that index will never be used.
3.To use an index, the WHERE clausole of the SELECT statement must be written, following the order of the index field.
So if u create and index called 'Z1' based on C B and A field, the where clausole (to use that index) must be :
select * from ztab where C = '...' and B = '....' and A='....'.
4.If in your selection if u doesen't provide all the fields of the index, the custom index might no be used (this depends by the optimizer of the databaes based on the index usages). For example if the SELECT before is like :
select * from ztab where C = '...' and A='...'
Generally, it's better provide all fields possible to use and index, also to reduce the SELECT time (this is the main pourpose of the index creation)
Hope this helps
Bye
Andrea
Pls reward if it helps