All Forums Database
15 Jul 2013
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
 
 

j355ga 100 posts Joined 12/05
15 Jul 2013

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.

Jeff

15 Jul 2013

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

j355ga 100 posts Joined 12/05
15 Jul 2013

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.
 

Jeff

j355ga 100 posts Joined 12/05
15 Jul 2013
You must sign in to leave a comment.