INDEX RANGE SCAN not used when View is used

mcrn

unread,

Mar 16, 2004, 6:08:39 PM3/16/04

to

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

Sybrand Bakker

unread,

Mar 17, 2004, 12:40:14 AM3/17/04

to

On 16 Mar 2004 15:08:39 -0800, ready...@hotmail.com (mcrn) wrote:

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

So you seem to be stuck. Your post also doesn't include the necessary
details to result in any sensible answer.

--
Sybrand Bakker, Senior Oracle DBA

Daniel Morgan

unread,

Mar 17, 2004, 12:58:44 AM3/17/04

to

mcrn wrote:
> Hi all,
>
> I'm running oracle 8.1.6.0.0 and
> I got a situation here that doesn't make sense to me

Doesn't make sense to me either. Why would anyone be running on
an obsolete and unpatched version of a database version for which
the list of bugs is longer than the distance to Sedna?

Before thinking about anything else ... at least apply the appropriate
patches to 8.1.6 to get the optimizer working.

After that you can worry about tuning. Realistically, upgrading to
8.1.7.4 should be the minimum. But 8.1.6.0.0 is inexcusable.

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)

srivenu

unread,

Mar 17, 2004, 2:22:46 AM3/17/04

to

What is the value of the parameter _push_join_union_view ?
test again by setting it to TRUE.
regards
Srivenu

Bib Endum

unread,

Mar 17, 2004, 10:12:48 AM3/17/04

to

"mcrn" <ready...@hotmail.com> a écrit dans le message de
news:a727f79a.04031...@posting.google.com...

[snip]

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

Silly, but you did ANALYZE those tables, did you ?

mcrn

unread,

Mar 17, 2004, 6:04:59 PM3/17/04

to

Your suggestion worked. Incredible! I'm so glad I asked.

mcrn

sri...@hotmail.com (srivenu) wrote in message news:<1a68177.04031...@posting.google.com>...