Composite Secondary Index-

Database
Enthusiast

Composite Secondary Index-

Hi Experts,

We have a query which summarise the data based on 2 column col_A and col_b of a table .. if we decide to create Secondary Indexes on col_a and col_B then

individual SI's on col_A  and col_b would be helpful or a composite SI on col_a and col_b would be helpful?

which one would cover the query?

cheers!

Nishant

Tags (1)
4 REPLIES
Teradata Employee

Re: Composite Secondary Index-

Generally, the individual indices would be helpful for access and the optimizer might be able to do a bit map with them if they are weakly selective alone.

The 1 or 2 col index would be used for a covered query depending on your SQL.

The question you should ask is: do you even need a SI?  Does performance suffer without the index?  Do you need a covered query index for performance?  The SI uses system resources so if you don't need it don't create it.

Enthusiast

Re: Composite Secondary Index-

thanks Jeff for the quick reply..

In case the col_a and col_b are highly selective then do we go for composite index on (col_A,co_b)?

also why do we call it a covered query? are there are some scenrios in which even if we create SI's then the

query is not covered ? can you please elaborate more on this..

thanks!

cheers!

Nishant

Teradata Employee

Re: Composite Secondary Index-

There is no way to guess which is better -  you will need to experiment. Try both single and two col index.  Review the EXPLAIN plan for both. Compare the performance of no SI, single SI and two col SI and decide which gives the best results.  There is no one definite answer when it comes to picking PI, SI etc.

 A covered query is one in which all of the columns in your SELECT are also in a SI or Join Index.

If you have col a, b, c in your sql but the SI only has col a, b then the optimizer will not consider the SI for a covered query. 

Read this post for more on covered query http://forums.teradata.com/forum/database/covered-query.

Teradata Employee

Re: Composite Secondary Index-