TABLE ACCESS BY INDEX ROWID
create table tq84_tab(
id integer,
tx varchar2(200),
constraint tq84_tab_pk primary key (id)
);
insert into tq84_tab
select
level,
rpad('x', 200)
from
dual connect by level <= 100000
;
begin
dbms_stats.gather_table_stats(user, 'tq84_tab');
end;
/
explain plan for
select
tx
from
tq84_tab
where
id = 12345;
select * from dbms_xplan.display(format=>'basic');
--
-- ---------------------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | TABLE ACCESS BY INDEX ROWID| TQ84_TAB |
-- | 2 | INDEX UNIQUE SCAN | TQ84_TAB_PK |
-- ---------------------------------------------------
drop table tq84_tab;
TABLE ACCESS BY INDEX ROWID BATCHED
create table tq84_tab(
nm number,
tx varchar2(200)
);
insert into tq84_tab
select
round(dbms_random.value(1, 1000), 1),
rpad('x', 200)
from
dual connect by level <= 100000
;
create index tq84_tab_ix on tq84_tab(nm);
begin
dbms_stats.gather_table_stats(user, 'tq84_tab');
end;
/
explain plan for
select
tx
from
tq84_tab
where
nm between 100 and 105;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- -----------------------------------------------------------
-- | Id | Operation | Name |
-- -----------------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TQ84_TAB |
-- | 2 | INDEX RANGE SCAN | TQ84_TAB_IX |
-- -----------------------------------------------------------
drop table tq84_tab;