INDEX RANGE SCAN not used when View is used

Hi all,

I'm running oracle 8.1.6.0.0 and
I got a situation here that doesn't make sense to me
(the sql query to takes too long).

Here's a simplified version of the problem:

select
from A, B
where A.x = B.x
and A.time > February and A.time < February.

Both A and B are large tables (more than 1 million for A and more than 10 million for B).
I have an index on B's x.
I have an index on A's time.
B is a View defined as a union:
(select x from H UNION select x from J UNION select form K)
The tables H, J, K have exactly the same column description.

However, the query takes a long time to run and then when I run EXPLAIN PLAN,
it turns out that it's because the "B's x" index wasn't even used. The returned result was just about 150 rows (mostly filtered due to the A.time filter and done quickly thanks to the A.time index).

However, if I run the sql query with the View expanded like this:

select
from A, (select x from H UNION select x from J UNION select form K) B where A.x = B.x.

this query runs fast!
I check the explain plan, and it says that the B's x index was used this time.

So, how can I convince the database to always use that index espeically
since it uses it if I just simply expand the View?

However, there are two restricions that must be imposed to the solution:
1) I am not allowed to use db hints.
2) I cannot change the original sql query.

thanks.

mcrn Received on Tue Mar 16 2004 - 17:08:39 CST