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