Re: Long running sub-query: bug or feature

Kelly,

For what it worth I tried your example on a version 7.3.2 database and using Sql*Lab for my explain plan. I see different plans than what you indicate. The subquery uses the col1 index in both cases (e.g. standalone and as a subquery). I see the col2 index used in conjunction with the col2 index both feeding into a "and-equal".

Ken F.

Kelly Young <young_at_maricopa.edu> wrote in article <34D9F9BD.4AD_at_maricopa.edu>...
> Any idea why a query will use the correct index when run standalone;
> but will use an incorrect index when executed as a sub-query?
> We have a query similar to this:
>
> select ...
> from table_a
> where col1 = 12345 and col2 in
> (select col2
> from table_a
> where col1 = 12345
> group by col2
> having sum(col3) != 0);
>
> Table_a has an index on col1 and an index on col2. When the above sql
> statement is run, the execution plan for the sub-query incorrectly
> uses the index on col2. When the sub-query is run standalone, it
> correctly uses the index on col1. Is this a bug or a feature? We are
> running rdbms 7.1.6 and use the rule based optimizer.
> --
> Kelly Young
> Database Administrator
> Maricopa Community Colleges
> young_at_maricopa.edu
>
  Received on Thu Feb 05 1998 - 00:00:00 CST