RE: Query performance question
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "'mschmitt@xxxxxxxxxxxx'" <mschmitt@xxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 2 Mar 2006 17:24:31 -0500
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@xxxxxxxxxxxxxxxxx
212.538.1143
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Mike Schmitt
Sent: Thursday, March 02, 2006 5:20 PM
To: oracle-l@xxxxxxxxxxxxx
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
==============================================================================
Other related posts:
