Multiple Index Access

Michael Hannan

Multiple Index Access
(in response to Brian Zamborski)
Brian,

Very simply, the optimizer is not as smart as you are in this situation.
Its intellegence is limited by need to be fast at BIND time.
Use OPTIMIZE FOR n ROWS to discourage Multi-index processing, or redesign
your indexes.
Note however if 1st index returns <= 32 RIDs, 2nd index will be skipped, for
Intersection type MI processing. This avoids the wastage sometimes.

From: Michael Hannan

>From: Brian Zamborski <[login to unmask email]>
>Subject: Multiple Index Access
>To: [login to unmask email]
>
>Hi all!! I've asked a similar question before, but the same problem keeps
>cropping up. If a table has 2 indexes defined on it, with one having 3
>columns and the other 6 columns, with the 3 columns all being encompassed
>by the 6 column index as well, and a program needs the other 3 columns in
>the 6 column index, why is a multiple index access path being selected? The
>data will be identical since the exact same columns that are in the smaller
>index are in the larger one. Doesn't it seem to be a time consuming waste
>to do a multiple index access? This is driving us mad....Thanks for any
>info!!!
>
>
>


>



Brian Zamborski

Multiple Index Access
Hi all,
Thanks for all of your help on the RID pool MAX question. To eliminate the
RID pool max problem, we have created another index (and used the optimize
for 1 row clause in the select).This now causes the optimizer to use a
single index (even prior to creating the new index, the optimize for 1 row
clause caused the use of a single index rather than the multiple index
access), However, we are confused as to why the optimizer selected a
multiple index access path in the first place, based on what the 2 indexes
in question consisted of....
Index 1 had the following columns in this order..(I eliminated col names
for simplicity)
Column 1 (in predicate)
Column 2 (in predicate)
Column A (not predicate)
Column B (not predicate)
Column C (not predicate)

Index 2 had the following columns in this order
Column D (in predicate)
Column E (in predicate)
Column F (in predicate)
Column 1 (in predicate (same as column 1 in index 1)
Column 2 (in predicate (same as column 2 in index 1)
Column G (in predicate)
Column H (in predicate)
Column I (in predicate)
Column J (in predicate)

Noting that Column 1 and Column 2 are the same in both indexes, the explain
showed that there was a multiple index access
with a matchcols of 2 on index 1 and a matchcols of 9 on index 2. This
being the case, it seems that each index 1 would return the same results as
index 2. Why would the optimizer then choose multiple index access rather
than just a single index access on index 2 which had the matchcols of 9??

Thanks for your help and suggestions....good luck to all with Y2K!!!

Regards,
Brian Z
[login to unmask email]



Venkat (PCA) Pillay

Re: Multiple Index Access
(in response to Brian Zamborski)
What is fullkeycardf & clusterratios for both the indexes ? If you could
mention colcardf for all 9 columns, even better.

> -----Original Message-----
> From: Brian Zamborski [SMTP:[login to unmask email]
> Sent: Thursday, December 09, 1999 10:21 AM
> To: [login to unmask email]
> Subject: Multiple Index Access
>
> Hi all,
> Thanks for all of your help on the RID pool MAX question. To eliminate the
> RID pool max problem, we have created another index (and used the optimize
> for 1 row clause in the select).This now causes the optimizer to use a
> single index (even prior to creating the new index, the optimize for 1 row
> clause caused the use of a single index rather than the multiple index
> access), However, we are confused as to why the optimizer selected a
> multiple index access path in the first place, based on what the 2 indexes
> in question consisted of....
> Index 1 had the following columns in this order..(I eliminated col names
> for simplicity)
> Column 1 (in predicate)
> Column 2 (in predicate)
> Column A (not predicate)
> Column B (not predicate)
> Column C (not predicate)
>
> Index 2 had the following columns in this order
> Column D (in predicate)
> Column E (in predicate)
> Column F (in predicate)
> Column 1 (in predicate (same as column 1 in index 1)
> Column 2 (in predicate (same as column 2 in index 1)
> Column G (in predicate)
> Column H (in predicate)
> Column I (in predicate)
> Column J (in predicate)
>
> Noting that Column 1 and Column 2 are the same in both indexes, the
> explain
> showed that there was a multiple index access
> with a matchcols of 2 on index 1 and a matchcols of 9 on index 2. This
> being the case, it seems that each index 1 would return the same results
> as
> index 2. Why would the optimizer then choose multiple index access rather
> than just a single index access on index 2 which had the matchcols of 9??
>
> Thanks for your help and suggestions....good luck to all with Y2K!!!
>
> Regards,
> Brian Z
> [login to unmask email]
>
>
>
>
>



Brian Zamborski

Multiple Index Access
(in response to Venkat (PCA) Pillay)
Hi all!! I've asked a similar question before, but the same problem keeps
cropping up. If a table has 2 indexes defined on it, with one having 3
columns and the other 6 columns, with the 3 columns all being encompassed
by the 6 column index as well, and a program needs the other 3 columns in
the 6 column index, why is a multiple index access path being selected? The
data will be identical since the exact same columns that are in the smaller
index are in the larger one. Doesn't it seem to be a time consuming waste
to do a multiple index access? This is driving us mad....Thanks for any
info!!!