Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query performance question

Re: Query performance question

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Thu, 02 Mar 2006 23:55:09 +0100
Message-ID: <440777CD.1090608@roughsea.com>


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-l
Received on Thu Mar 02 2006 - 16:55:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US