Optimiser query

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/