Primary & Secondary Index in ABAP open SQL


 

Step1. Go to SE11 open the table VBAK and click on the Indexes button. Here it shows 4 secondary index. Let’s check the property of each one. Double click on each one .

Step2. This is active and the index VBAK~AUD is created in the DB.

Step3. This is  active and the index VBAK~ERD is not  created in the DB.

Step4.  This is  active and the index VBAK~KUN is not  created in the DB.

Step5.  This is NOT  active and the index VBAK~OI0 is not  created in the DB.

Step6. Go to Tcode- DB02.

Step7. From the left side tree, double click on single table analysis.

Step8. In the right part of the screen provide the table name as VBAK and hot enter to load its details.

Step9. as we saws in above we have 4 secondary index but out of then one is active with creation in DB. So here we have two indexes one is primary and another is secondary. There are lot of numbers to the right side of each index showing how many times these are hited by sql queries and others. 

Step10. We have the below program to show what is the time taken when primary index, secondary index & when no index is used to fetch the data..

Step11. Run with primary index mode.

Step12. Run for the secondary index mode.

Step13. Run with no index mode. Actually whether primary or secondary or no index will be used determined by the condition provided in the select query. So there is subsequent time optimization when primary index is used.


Step14. Note the number in the primary index of vbak table in DB02 transaction. Run the program.

Step15. We have the O/P. Now refresh the screen in DB02 tcode. it is observed that the number under USER SEEKS increased by one against the primary index. VBAK~0.



Step16. Just a before and after comparison of the DB02 transaction before and after the report is run.

Step17. Similarly run the program so that it will hit the secondary index.

Step18. After the program executed, refresh the DB02 transaction and we have the below figures. 


 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s