DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: Optimizer & rownum & view

Threaded View

  1. #1
    Join Date
    Nov 2010
    Posts
    8

    Optimizer & rownum & view

    Hello,

    Field1, part of table3, is indexed.
    Any clue why the optimizers chooses a full tablescan when the rownum is part of the view, and an index scan when not?
    It looks like it is precalculating all the rownums

    Myview AS

    select rownum, field1,field2,field3
    from (
    select field1,field2,field3 from table1 t1 join table2 t2 on t1.id=t2.t1_id join table3 on t3.t2_id=t2.id where Field1 not like 'X%'
    union select field1,field2,field3
    from table1 t4 join table2 t2 on t4.id=t2.t4_id join table3 on t3.t2_id=t2.id join table1 t1 on t1.id=t4.id_base )

    select rownum,field1,field2,field3 from myview where field3='Z' ->Cost 32.000 due full tablescans

    *recreate the view without rownum in the definition
    select rownum,field1,field2,field3 from myview where field3='Z' ->Cost 20
    Scan on Field3

    *After reading the workings of rownum
    http://asktom.oracle.com/pls/asktom/...D:948366252775
    it makes sense (fetch all records and apply rownumbering)
    rownum = 1
    for x in ( select * from A )
    loop
    if ( x satisifies the predicate )
    then
    OUTPUT the row
    rownum = rownum + 1
    end if;
    end loop;



    rownum is assigned during the predicate evaluation - but before any sorting/aggregating is performed. That is why:


    select * from t where rownum <= 10 order by x;

    is very very very different from

    select * from (select * from t order by x) where rownum <= 10;
    Last edited by Flyby; 11-24-2010 at 09:38 AM.

Posting Permissions

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


Click Here to Expand Forum to Full Width