Covered Query

Database
Enthusiast

Covered Query

Hi All,

I would like to know about Covered query , what it is and how does it improve the performance ?

When do we decide to go for a covered query

Regards
R.Rajeev
4 REPLIES
Senior Apprentice

Re: Covered Query

Hi Rajeev,
a Covering Index means that all of the columns needed to process a query are included in an existing index.
The optimizer can use this index instead of scanning the base table, which usually results in less IOs -> faster query.

It might be a NUSI (simple case, only a single table is used in query) or a Join Index (complex case, multiple tables plus aggregation possible). Creating an index specifically for query covering is mainly useful if you got a set of predefined queries with a large number of executions.

Dieter
Enthusiast

Re: Covered Query

Hi Dieter ,

Thanks for the quick turnaround

I would like to extent my understanding with a simple scenario

Now for example i have a set of 5 columns which are frequently queried and a particular query utilises only these 5
if i were to define a compound SI using these 5 then the next time i use any of these 5 columns in either select or in the where clause
then the optimizer would make a choice to read the subtable instead of accessing the larger base table

however if the query uses only say 2-3 columns then it may not choose to use the subtable

Also in case of SI is there a necessity that all the index columns be used in the where clause of the query

looking for your inputs !

Any documetation would be helpful
Senior Apprentice

Re: Covered Query

*All* columns you use in your query (SELECT or WHERE clause) must be within the index, of course this might be less than all columns defined in the SI.

Additonaly details are in the Database Design manual
Chapter 10: Secondary Indexes
NUSIs and Query Covering

Dieter
Enthusiast

Re: Covered Query

Hi Dieter,

Thanks for the inputs

Regards

R.Rajeev