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: amit poddar <amit.poddar_at_yale.edu>
Date: Thu, 02 Mar 2006 17:28:46 -0500
Message-Id: <5.1.0.14.2.20060302172820.036caf48@ap349.mail.yale.edu>


USE_CONCAT hint may be of help

At 04:19 PM 3/2/2006 -0600, 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:28:46 CST

Original text of this message

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