Re: Query performance question
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: mschmitt@xxxxxxxxxxxx
- Date: Thu, 02 Mar 2006 23:55:09 +0100
Mike,
I guess that the optimizer is unable to estimate properly the
cardinalities.
I would try something such as
/select count(*)
from (//select rowid
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 rowid
from // fred.table_a A/
/ where A.col_2 in (select col_3
from fas.table_b B
where B.col_4 = '662'))/
It should make indexes irresistible IMHO. I prefer this type of writing
to hints.
Stéphane Faroult
Mike Schmitt wrote:
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
--
//www.freelists.org/webpage/oracle-l
Other related posts: