topic Re: Indexing in Application Development and Automation Discussions

Indexing

Former Member — Fri, 18 Nov 2005 21:03:47 GMT

Hi,

For a table like BSIS where there are 8 Primary keys( Excluading Client ) , and its having three Indexs.

And i am using 5 Primary Keys with order

BUKRS,AUGDT,GJAHR,BELNR,HKONT . When i am getting data here using one index does the order of Projection is accoring to the data base field postion.

Even though we are changing the order of the keys its not improving the performance and is using the same index.

Is it suggested to go for another index . And asked Basis guy for creating a new index and they say there are already 4 Index's here and cant create any.

Good Luck.

Jing.

Re: Indexing

RichHeilman — Fri, 18 Nov 2005 21:12:43 GMT

You can create as many indexes as you want, the system will allow it. Be forewarned that when creating indexes there could be a decrease in performance when updating or inserting records. This is the only bad thing. Creating indexes helps a lot when accessing data. If there is not an index that matches your WHERE clause of the select statement, I would first try to adjust your WHERE clause in such a way where it does use all of the key fields of the index, also make sure that the fields are in the correct order of the index. If you can not, then I would create an index, but monitor carefully after implementation.

Regards,

Rich Heilman

Re: Indexing

Former Member — Fri, 18 Nov 2005 21:14:00 GMT

If you know BUKRS,GJAHR and BELNR Why not just go to BSEG? Once there take the item with the HKONT you need.

Rob

Re: Indexing

Former Member — Fri, 18 Nov 2005 21:16:28 GMT

We can create more than 4 indexes on a transparent table. In my server I can see 9 indexes on BKPF table.

But as we create more indexes on a table performance while posting data to these table will come down, may be that is the basis people worry.

Cheers,

Satya

Re: Indexing

Former Member — Fri, 18 Nov 2005 21:17:21 GMT

Check if statistics is run on this table. If statistics is not updated the optimizer may not pick right index.

If there is no other option you can try using hints for index

%_HINTS ORACLE 'INDEX("tablename" "tablename~indexname")'

This hint syntax is for ORACLE.

Regards

Kalpana

Re: Indexing

Former Member — Fri, 18 Nov 2005 21:17:59 GMT

Can you put the 5 fields in the order of the index & for the fields you don't know, can you still specify them in the SELECT with wildcards ?

e.g. ZUONR LIKE '%' AND AUGBL LIKE '%', this will make the system look at the primary index..

Re: Indexing

Former Member — Fri, 18 Nov 2005 21:35:45 GMT

Hi,

If your are using one of the index make sure you pass all field of the index in where conditions & in order, even if some fields of the index u r not using in that case define that fields in range

For example u r using index say Z01

having bukrs

hkont

audat

belnr

but you only have bukrs hkont & audat value but not belnr in that case create a dummy range say

DATA: BEGIN OF r_belnr OCCURS 0,

SIGN(1),

OPTION(2),

LOW LIKE f,

HIGH LIKE f,

END OF r_belnr.

select * from BSIS where

bukrs in s_bukrs and

hkont in s_hkont and

audat in s_audat and

belnr in R_belnr.

it will improve your performance.

Hope this will help you.

Thanks

Rajeev

Re: Indexing

Former Member — Fri, 18 Nov 2005 22:02:30 GMT

Jing,

The best alternative for you is use BSEG table as suggested by Rob Burbank. Since you have the entire key you will get best performance.

Also creating new indexes on these tables is a costly one, it needs more memory and also decreases the performance if you have toomany indexes.

Thanks

Giridhar

Message was edited by: Giridhar Nayudu

Re: Indexing

Former Member — Mon, 21 Nov 2005 16:07:19 GMT

Hi Jing,

First of all would like to know what is the version of Oracle DB that you are using , Previously when you are selecting data from the data base you need to pass the Primary keys in the way it is there in the table .

this is also one performance issue. In the latest Oracle DB you can pass in any way.

If you cannot create indexes then the best way is to pass the Primary keys in the Asecnding order and check the SQL Trace about the performance . And if you can create a Index then that is the best alternative , but that will get down the performance when you start deleting or inserting the records in that.

Make sure that the keys are in the ascending order , as this will make sure that it picks the index in that way.You can check this even in the SQL Trace as of what index it is fetching.

If you are using the Latest Oracle DB then you can pass the keys in any way as this not make a difference.

Good Luck.

Thanks.

Naidu.

Re: Indexing

Former Member — Fri, 25 Nov 2005 19:37:34 GMT

Jing - if your question is answered, please close the thread.

Rob