Re: Optimiser query

Hi Mike,

a good way for me to tell my selects to use all available indexes is:

select /*+ first_rows */ ...

You may wanr to try this too:

alter session optimizer_goal = first_rows;

This command is discontinued in further releases > 7.3 and not documented. (Not shure about this).

Kind regards,

Toni

In article <33D75BCA.3F54_at_ucl.ac.uk>, Mike Gahan <ccaamrg_at_ucl.ac.uk> wrote:

> I am puzzled by the behaviour of the optimiser under the
> following circumstances. The scenario below represents the
> simplest manifestation of my problem.
>
> 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'"
>
> {SELECT STATEMENT } {}
> { VIEW TALL } {}
> { PROJECTION } {}
> { SORT UNIQUE } {}
> { UNION-ALL } {}
> { INDEX UNIQUE SCAN SYS_C0020136 } {}
> { INDEX UNIQUE SCAN SYS_C0020137 } {}
>
>
> 2)
>
> plan "select * from tall a, t3 b where a.xx = b.xx"
>
> {SELECT STATEMENT } {}
> { MERGE JOIN } {}
> { SORT JOIN } {}
> { TABLE ACCESS FULL T3 } {}
> { SORT JOIN } {}
> { VIEW TALL } {}
> { PROJECTION } {}
> { SORT UNIQUE } {}
> { UNION-ALL } {}
> { TABLE ACCESS FULL T1 } {}
> { TABLE ACCESS FULL T2 } {}
>
> 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.
>
> 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"
>
> {SELECT STATEMENT } {}
> { PROJECTION } {}
> { SORT UNIQUE } {}
> { UNION-ALL } {}
> { NESTED LOOPS } {}
> { TABLE ACCESS FULL T3 } {}
> { INDEX UNIQUE SCAN SYS_C0020136 } {}
> { NESTED LOOPS } {}
> { TABLE ACCESS FULL T3 } {}
> { INDEX UNIQUE SCAN SYS_C0020137 } {}
>
> 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'.
>
> --
> Mike Gahan
> Information Systems Division
> University College London
>
> http://www.ucl.ac.uk/~ccaamrg/
 

-- 
A. Dischner, SGI&AIX sysadmin, Oracle DBA       | Don't let friends
Institut fuer Klinische Chemie                  | use WinDose
Klinikum Grosshadern                            | Just say NO. 
Ludwig Maximilians Universitaet Muenchen,  GER  | Please remove            
Marchioninistr.15 81366 Muenchen 49-89-70953202 | _NO_SPAM for email.