Oracle ROWID vs ROWNUM io 比較

2/09/2022 04:00:00 上午

Lab測試 rowid vs rownum

SQL> create table product as select * from dba_objects;
SQL> create index myindex on product(object_type, owner);

SQL 1：

explain plan for
select * 
from (select a.*, rownum rn
         from (select *
                  from product a
                  where object_type = 'INDEX'
                  order by owner) a
         where rownum <= 20)
where rn > 10;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

SQL 2：
explain plan for
select b.*
  from (select *
           from (select a.*, rownum rn
                    from (select rowid rid, owner 
                             from product
                             where object_type = 'INDEX'
                             order by owner) a
                    where rownum <= 20)
          where rn > 10) a, product b
where a.rid = b.rowid;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());