composite index

Reply
Contributor
Posts: 30

composite index

Hi

Pls help..!

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?

thks..

Super Contributor
Posts: 644

Re: composite index

I believe that if your condition references c1 and c2 the index will be used but not if you specify c1 and c3 or c2 and c4.

Richard

Super Contributor
Posts: 644

Re: composite index

There are other conditions that must be met before a composite index can be used

SAS(R) 9.3 Language Reference: Concepts, Second Edition

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.

Richard

Super Contributor
Posts: 282

Re: composite index

Hi,

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:

options msglevel=i;

See the documentation for more information:

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Regards,

Amir.

Contributor
Posts: 25

Re: composite index

Run your proc print with the following option.

options msglevel=i;

proc print data=your_data_set(idxname=than_name_of_your_composite_index);

     where ......;

run;

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.

Contributor
Posts: 30

Re: composite index

I tried

options msglevel=i;

proc print data=datasetname(idxname=inname);

     where ....

run;

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 Smiley Happy

Contributor
Posts: 25

Re: composite index

Smiley Happy

Ask a Question
Discussion stats
  • 6 replies
  • 306 views
  • 0 likes
  • 4 in conversation