Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query performance question
Mike,
I guess that the optimizer is unable to estimate properly the cardinalities.
I would try something such as
/select count(*)
from (//select rowid
from fred.table_a A where A.col_1 in (select col_3 from fred.table_b B where B.col_4 = '662') union select rowid from // fred.table_a A/ / where A.col_2 in (select col_3 from fas.table_b B where B.col_4 = '662'))/
It should make indexes irresistible IMHO. I prefer this type of writing to hints.
Stéphane Faroult
Mike Schmitt wrote:
>
> Hi All,
>
> I was hoping someone could help me figure out a way to get better
> performance from the following query. This is in a 10.2.0.1 instance
> with updated statistics
>
> This following query takes 6 minutes ~27million consistent gets:
>
> /select count(*) from fred.table_a A
> where A.col_1 in (select col_3 from fred.table_b B where B.col_4 = '662')
> or A.col_2 in (select col_3 from fas.table_b B where B.col_4 = '662')
>
> /If I make the above statement into two separate queries, each one
> takes approximately 1 second.
>
> for example:
> 1 second ~1400 consistent gets
> /select count(*) from fred.table_a A
> where A.col_1 in (select col_3 from fred.table_b B where B.col_4 = '662')
> /..............................
>
> I have tried using various hints, however my tracing keeps showing
> that the statement with the 'or' continues to want to access table_A
> (which is ~7million rows) with a full table scan. While the
> individual queries access table_A by way of indexes on col_1 and col_2.
>
> Any ideas on how I can get the optimizer to handle this query
> differently, and get the timing more in line with the individual
> queries.
>
> Thanks in advance
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 02 2006 - 16:55:09 CST
![]() |
![]() |