Professional Web Applications Themes

index usage on view union, with subselect - Oracle Server

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

  1. #1

    Default index usage on view union, with subselect

    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.
    bung ho Guest

  2. #2

    Default Re: index usage on view union, with subselect

    "Jonathan Lewis" <jonathanjlcomp.demon.co.uk> wrote in message news:<atpg31$ckd$1$8300dec7news.demon.co.uk>...
    > 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.
    >
    you're right, of course. i guess i have come to expect magic from
    oracle.
    > 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.
    >
    apparently, it does. mine is set to false.
    > 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
    > ....
    >
    it's not so much that i want partition elimination, just a predicate
    push that will allow use of the indexes on the underlying tables. a
    range scan (+ table access by rowid) on both indexes would be fine. i
    was under the impression that this was achievable, even with a view of
    union (ALL or otherwise, but without identical tables). is there no
    way to get this to happen?

    thanks for your answer.
    >
    > --
    > Regards
    >
    > Jonathan Lewis
    > [url]http://www.jlcomp.demon.co.uk[/url]
    >
    > Coming soon a new one-day tutorial:
    > Cost Based Optimisation
    > (see [url]http://www.jlcomp.demon.co.uk/tutorial.html[/url] )
    >
    > Next Seminar dates:
    > (see [url]http://www.jlcomp.demon.co.uk/seminar.html[/url] )
    >
    > ____England______January 21/23
    >
    >
    > The Co-operative Oracle Users' FAQ
    > [url]http://www.jlcomp.demon.co.uk/faq/ind_faq.html[/url]
    >
    >
    >
    >
    >
    bung ho Guest

  3. #3

    Default Re: index usage on view union, with subselect

    thanks. _push_join_union_view was the one that did the trick, but
    only when it was UNION ALL. i still thought that using the underyling
    indexes was possible with UNION, but i guess i was wrong. in any
    case, is the desire for this kind of behavior so uncommon that (in v8
    at least) this parameter is false by default?

    thanks again.

    "Jonathan Lewis" <jonathanjlcomp.demon.co.uk> wrote in message news:<atqhid$p6d$1$8302bc10news.demon.co.uk>...
    > There are a couple of hints and hidden
    > parameters that may help - I can never
    > remember details of which specific Oracle
    > version they are relevant to:
    >
    > /*+ push_pred */
    >
    > pushes predicates into view.
    >
    > parameters:
    > _PUSH_JOIN_PREDICATE = true
    > _PUSH_JOIN_UNION_VIEW = true
    >
    > To set these with an alter session, you
    > need to surround the parameter with
    > double quote marks.
    >
    >
    > --
    > Regards
    >
    > Jonathan Lewis
    > [url]http://www.jlcomp.demon.co.uk[/url]
    >
    > Coming soon a new one-day tutorial:
    > Cost Based Optimisation
    > (see [url]http://www.jlcomp.demon.co.uk/tutorial.html[/url] )
    >
    > Next Seminar dates:
    > (see [url]http://www.jlcomp.demon.co.uk/seminar.html[/url] )
    >
    > ____England______January 21/23
    >
    >
    > The Co-operative Oracle Users' FAQ
    > [url]http://www.jlcomp.demon.co.uk/faq/ind_faq.html[/url]
    >
    >
    >
    >
    >
    > bung ho wrote in message
    > <567a1b1.0212180935.24989585posting.google.com>.. .
    > >"Jonathan Lewis" <jonathanjlcomp.demon.co.uk> wrote in message
    > news:<atpg31$ckd$1$8300dec7news.demon.co.uk>...
    > >> 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.
    > >>
    > >
    > >you're right, of course. i guess i have come to expect magic from
    > >oracle.
    > >
    bung ho Guest

  4. #4

    Default Re: index usage on view union, with subselect


    I believe the strategy is for parameters like that
    to be set to false in versions of Oracle where the
    code to implement the functionality is still
    considered to be beta. It isn't guaranteed to
    work properly in your version of Oracle, so
    you use it at your own risk.

    --
    Regards

    Jonathan Lewis
    [url]http://www.jlcomp.demon.co.uk[/url]

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

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

    ____England______January 21/23


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





    bung ho wrote in message
    <567a1b1.0212181807.bb5d8e6posting.google.com>. ..
    >thanks. _push_join_union_view was the one that did the trick, but
    >only when it was UNION ALL. i still thought that using the
    underyling
    >indexes was possible with UNION, but i guess i was wrong. in any
    >case, is the desire for this kind of behavior so uncommon that (in v8
    >at least) this parameter is false by default?
    >
    >thanks again.
    >


    Jonathan Lewis Guest

Similar Threads

  1. PL/PgSQL Index Usage with Trigger Variables
    By Thomas F.O'Connell in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 20th, 07:20 AM
  2. Index on a view??
    By Joost Kraaijeveld in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: January 5th, 11:07 PM
  3. Tab View Component index using addItem
    By KentDC in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: March 3rd, 07:48 PM
  4. Union View Optimizer Creates Temp Table
    By Brian Foster in forum Informix
    Replies: 2
    Last Post: September 26th, 10:00 PM
  5. Index Usage Question
    By Ryan in forum Oracle Server
    Replies: 2
    Last Post: June 24th, 02:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139