RE: Query performance question

try something like
 
select cnt from (
select count(*) cnt  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 count(*) cnt from fred.table_a A
where  A.col_2 in (select col_3 from fas.table_b B where  B.col_4 = '662');
 
Assume you have index on A.col_1, A.col_2, B.col_3, B.col_4
 
Guang

-----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

Other related posts: