RE: Query performance question

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Thu, 2 Mar 2006 17:24:31 -0500
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC0A189F2E@ENYC11P32005.corpny.csfb.com>

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

Received on Thu Mar 02 2006 - 16:24:31 CST