Re: Optimiser query

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/07/26
Message-ID: <33da7b23.7033680@www.sigov.si>#1/1

On Thu, 24 Jul 1997 13:42:35 GMT, Mike Gahan <ccaamrg_at_ucl.ac.uk> wrote:

>First, I create two indexed tables:
> create table t1 (xx varchar2(5) primary key) enable primary key;
> create table t2 (xx varchar2(5) primary key) enable primary key;
>And now, a union view of the two:
> create view tall as select xx from t1 union select xx from t2;
>And a third, unindexed table:
> create table t3 (xx varchar2(5) );
>
>Now look at the execution plans for the following selects:
>1) plan "select * from tall where xx = 's'"
>....[SNIPPED EXECUTION PLAN USING INDEX SCANS].....
>2) plan "select * from tall a, t3 b where a.xx = b.xx"
>....[SNIPPED EXECUTION PLAN USING FULL TABLE SCANS].....
>In (1), the individual union components are accessed by index.
>In (2), the optimiser decides to construct the whole union using
>full scans, before joining with T3.
>
>Is there any way I can persuade the optimiser to use the indexes
>in case (2)? In my real application, T1 is very large.

Yes, there is a way. You must explicitly "limit" the result set from a view itself, not only with a joining condition. For example:

select * from tall a, t3 b where a.xx = b.xx and a.xx > ''

will scan the t1's and t2s indexes instead of their coresponding tables. I don't know if there is a way you can force this query to use indexes with hints - I tried with different sorts of hints (USE_NL(t3), INDEX(t1) INDEX(t2), NO_MERGE) without success.

>Observe also the following:
>
>3) plan "select * from t1 a, t3 b where a.xx = b.xx union
> select * from t2 a, t3 b where a.xx = b.xx"
>....[SNIPPED EXECUTION PLAN USING INDEX SCANS].....
>
>This is the sort of plan I want, but I would like to do it by
>uniting t1 and t2 BEFORE I join to T3, and have the optimiser
>'sort it out'.

In this case optimizer can use nested loops because it can directly see the indexes of t1 and t2. But when you yoin a t3 to the "tall" view then I guess optimizer can not directly "see" the indexes unless you limit the view's result set itself.

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Sat Jul 26 1997 - 00:00:00 CDT