Weird behaviour in query execution plan

Hi all,

We are using 9.2.0.6 on Tru64 5.1a

we have a query joining two views on tables a (approx. 60M rows) and b
(approx 10K recs)

a_vw:

select num_col1, num_col2,
num_col3, varchar_col1, varchar_col2, num_col4 from a
union all
select num_col1, num_col2,
num_col3, varchar_col1, 'total' varchar_col2, num_col4 from a;

b_vw

select distinct varchar_col1, num_col1, varchar_col2 from
(select varchar_col1, num_col1, varchar_col2
from b
union all
select varchar_col1, num_col1, 'total' varchar_col2 from b)

the query:

SELECT VARCHAR_COL1, NUM_COL1, COUNT(1) ROW_COUNT, SUM(NUM_COL4) TOT_NUM_COL4, VARCHAR_COL2 FROM A_VW
WHERE (VARCHAR_COL1, NUM_COL1, VARCHAR_COL2) IN
(SELECT VARCHAR_COL1, NUM_COL1, VARCHAR_COL2
FROM B_VW)
GROUP BY VARCHAR_COL1, NUM_COL1, VARCHAR_COL2 having count(1) >= case

    when varchar_col2 = 'total' then 250     else 100
    end
or sum(num_col4) >= case

    when varchar_col2 = 'total' then 10000     else sum(num_col4) + 1
end;

The highlighted part is giving us hard times. Table a is indexed on varchar_col1 and is partitioned by range on num_col1, with only one distinct value of num_col1 inside each of the 3 partitions. The 60M records are evenly distributed over the partitions.

We'd expect CBO to use the index since it's outputting about 2% of table a's total rows. But it doesn't (even if hinted) and the full scan takes ages.

We tried to reproduce the problem on another machine with same versions of tru64 and oracle, same data, same file system, same init parameters ecc. and, surprise! the index gets used! the query takes less than a minute.

Any ideas? Where should we start looking from?

Thanks a lot

Stefano Received on Fri Oct 07 2005 - 04:02:57 CDT