Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Secondary Indexes of the database table while fetching data fom the view

former_member212005
Active Contributor
0 Kudos

Hi All,

I have a database table ZZZ which has 10 fields. This database table has one secondary index on 3 fields.

I also have another database table YYY which has 20 fields, This database table has 2 secondary indexes on some of the fields.

I have a VIEW defined on the above two tables.

Now, when I write a select statement on the view, I need following clarifications

1) Is the index of the corresponding database table considered when retrieving data from the view

2) If the index is considered, then what are the rules used for them. i.e. which database table index will be hit first.

3 REPLIES 3

Former Member
0 Kudos

Hi,

U can use the transaction ST05 to find which index it has used.

The fields in the select query and the fields in the index are matched(even the sequence of the fields should match).Which ever the optimizer finds appropriate it will select that particular index.

Thanks,

Nithya.

Edited by: Nithya Murugesan on Mar 9, 2009 11:21 AM

christine_evans
Active Contributor
0 Kudos

>

> Hi All,

>

> I have a database table ZZZ which has 10 fields. This database table has one secondary index on 3 fields.

> I also have another database table YYY which has 20 fields, This database table has 2 secondary indexes on some of the fields.

>

> I have a VIEW defined on the above two tables.

>

> Now, when I write a select statement on the view, I need following clarifications

>

> 1) Is the index of the corresponding database table considered when retrieving data from the view

> 2) If the index is considered, then what are the rules used for them. i.e. which database table index will be hit first.

In the good old days when relational databases used rule based optimizers, there was a set of rules that the optimiser followed that meant that you could usually (there are always exceptions) foretell which index it would use. You could probably stilll find a list of these rules and their order of priority on Google. Now, however, that we're all using cost based optimizers that base their choice of index mainly on table statistics, the optimizer can really do anything it likes and there are no hard and fast rules. Since we've handed this power over to the optimizer we should really try and trust what it does and usually (in my experience) it has been correct.

Write your query using indexed columns and test what it does using ST05. This will tell you which index the optimizer decides it is right to use in that system at that particular time. This is no guarantee that it will continue to use this particular index in a different system at a different time.

former_member212005
Active Contributor
0 Kudos

Thanks for the reply!