Composite Secondary Index

Database
Highlighted

Composite Secondary Index

Hi All,

 

I had a doubt regarding Composite secondary index.

When we use two column combination as secondary index and when we query on the table with where condition as only one of the column will it go into full table scan ? 

2 REPLIES
Senior Apprentice

Re: Composite Secondary Index

Hi,

 

In truth the answer is "it depends", but often such an index will not be used.

  • Whether an index is used or not is an optimizer decision.
  • Whether not using an index means a full table scan is based on other factors.

By default, you need conditions against all columns in an index for it to usually be used.

 

The most common exception to this is if you define your index such that only one column is used for HASH or VALUE ordering. See Create Index for examples and syntax.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Composite Secondary Index

By default, you need conditions against all columns in an index for it to usually be used.


You have the case of covering index, if you select one column and filter on the other, you can still use the index instead of the base table.