Re: index usage on view union, with subselect

I like the 'nothing unusual there' line.

Oracle has unnested a subquery, and
recognised a partition view - that's a
pretty good feat of optimisation.

Do you have parameter

    partition_views_enabled = true ?
Very recent versions of Oracle recognise partition views even when this parameter is set to false - I didn't think the behaviour went back to 8.1.7.0.

Oracle will automatically try to push joins into UNION ALL views and do partition
elimination provided it recognises the UNION ALL view to be a partition view - which means that all the tables in the UNION ALL have exactly the same structural definitions. This means that all the tables must have the same columns, and types, in the same order, and all indexes must match across all tables - and the view must be equivalent to:

    select * from table1
    union all
    select * from table2
    ....

The feature was introduced in 7.3, but deprecated with the arrival of partition tables in 8.0. However the feature still works, and in many ways is no longer a 'special feature' in 9.2, simply a particular effect of one of the things that the optimizer just does.

For more details, there are a couple of (7.3 - 8.0) articles on my website about PVs and PTs - but you may otherwise have to go back to 7.3 manuals to get further details about what makes PVs work.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





bung ho wrote in message
<567a1b1.0212172031.c487c95_at_posting.google.com>...


>using 8.1.7.0 on linux.

>

>i run the following script:

>

>---------------

>drop table t1;

>drop table t2;

>

>create table t1 (a int, b char(20));

>create table t2 (a int, b char(20));

>

>create index t1_pk on t1(a);

>create index t2_pk on t2(a);

>

>exec dbms_stats.set_table_stats( user, 'T1', numRows => 10000000,

>numBlks => 5000000 );

>exec dbms_stats.set_table_stats( user, 'T2', numRows => 10000000,

>numBlks => 5000000 );

>

>select a, b from

>(

>select a, b from t1

>union all

>select a, b from t2

>)

>where a in (select 1 from dual union select 2 from dual);

>---------------

>

>the query plan for that last query is:

>

>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1399 Card=16400000

B


>          ytes=459200000)

>

>   1    0   NESTED LOOPS (Cost=1399 Card=16400000 Bytes=459200000)

>   2    1     VIEW OF 'VW_NSO_1' (Cost=6 Card=82 Bytes=246)

>   3    2       SORT (UNIQUE) (Cost=6 Card=82)

>   4    3         UNION-ALL

>   5    4           TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=41)

>   6    4           TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=41)

>   7    1     VIEW

>   8    7       UNION-ALL (PARTITION)

>   9    8         TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2

Card=1


>          00000 Bytes=3500000)

>

>  10    9           INDEX (RANGE SCAN) OF 'T1_PK' (NON-UNIQUE)

(Cost=1


>           Card=100000)

>

>  11    8         TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2

Card=1


>          00000 Bytes=3500000)

>

>  12   11           INDEX (RANGE SCAN) OF 'T2_PK' (NON-UNIQUE)

(Cost=1


>           Card=100000)

>

>nothing surprising there.

>

>however, if i change the definition of table T1 to

>

>create table t1 (a int, b char(20), c char(20)); -- one more column

>

>and re-run the script, i suddenly get two FTS's:

>

>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1527847

Card=1640000


>          0 Bytes=459200000)

>

>   1    0   HASH JOIN (Cost=1527847 Card=16400000 Bytes=459200000)

>   2    1     VIEW OF 'VW_NSO_1' (Cost=6 Card=82 Bytes=246)

>   3    2       SORT (UNIQUE) (Cost=6 Card=82)

>   4    3         UNION-ALL

>   5    4           TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=41)

>   6    4           TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=41)

>   7    1     VIEW (Cost=1517738 Card=20000000 Bytes=500000000)

>   8    7       UNION-ALL

>   9    8         TABLE ACCESS (FULL) OF 'T1' (Cost=758869

Card=100000


>          00 Bytes=250000000)

>

>  10    8         TABLE ACCESS (FULL) OF 'T2' (Cost=758869

Card=100000


>          00 Bytes=350000000)

>

>

>am i not understanding something really obvious here? am i using

>dbms_stats.set_table_stats incorrectly? if not, why the big

difference


>in plans? thanks in advance.