RE: Query performance question

Hi Paul,

autotrace was indicating that the query was being rewritten with the exists statement. However, just to make sure I rewrote the query like you listed to test it out, and I still get the same results.

Thanks,
Mike

At 04:24 PM 3/2/2006, Baumgartel, Paul wrote:
>Mike,
>
>Have you tried rewriting the query to use EXISTS instead of IN, e.g.
>
>select count(*) from fred.table_a A
>where exists (select 1 from fred.table_b B where B.col_4 = '662' and
>B.col_3 = A.col_1)
>or exists (select 1 from fred.table_b B where B.col_4 = '662' and B.col_3
>= A.col_2)
>?
>
>worth a try...
>
>Paul Baumgartel
>paul.baumgartel_at_credit-suisse.com
>212.538.1143
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mike Schmitt
>Sent: Thursday, March 02, 2006 5:20 PM
>To: oracle-l_at_freelists.org
>Subject: Query performance question
>
>
>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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>==============================================================================
>Please access the attached hyperlink for an important electronic
>communications disclaimer:
>
>http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
>==============================================================================

--
http://www.freelists.org/webpage/oracle-l