07-25-2013 06:40 AM
I have a table T1 with around 40 columns and it has one composite index defined on 4 columns c1,c2,c3,c4 and simple index on some of the other columns.
Now while performing a query on the table T1 if all the 4 columns(c1,c2,c3,c4) are used, then composite index comes into picture ...
But if I use only two columns (c1,c2) in the where clause then will the composite index(the one defined on C1,c2,c3,c4 in the given order) be used?
07-25-2013 07:12 AM
There are other conditions that must be met before a composite index can be used
It seems that c1 and c3 might be used. The first variable in the composite index must be used in the query for the query to use the index.
07-25-2013 07:39 AM
You might also want to consider using the msglevel option which amongst other things can provide information in your log about what indexes are being used:
See the documentation for more information:
07-25-2013 08:03 AM
Run your proc print with the following option.
proc print data=your_data_set(idxname=than_name_of_your_composite_index);
If you get a error in the log then it means that your WHERE statement does not follow SAS rules in order to pick up the composite index.
Some instances a composite index might be used:
Check SAS(R) 9.2 Language Reference: Concepts, Second Edition the section Compound Optimization there is a sentance For compound optimization to occur, all of the following must be true.
07-25-2013 11:13 AM
proc print data=datasetname(idxname=inname);
if the first columnname (of the composite index) is present anywhere in the where clause then the composite index is used else not...
that is it says --> INFO: Index indnam selected for WHERE clause optimization. ELSE no such message is displayed...
Thank you all