Demonstration
We need two table to demonstrate the effect of
first_rows to an
execution plan:
create table tq84_A (
id integer not null,
num number(1) not null
);
create table tq84_B (
id integer,
txt varchar2(10)
);
Each table is populated with one million records and statistics are gathered for the optimizer:
begin
insert into tq84_A
select
level,
dbms_random.value*9
from
dual connect by level <= 1000000;
insert into tq84_B
select
level,
dbms_random.string('a', 10)
from
dual connect by level <= 1000000;
dbms_stats.gather_table_stats(user, 'tq84_A');
dbms_stats.gather_table_stats(user, 'tq84_B');
end;
/
An index is also created:
create unique index tq84_a_ix on tq84_A(id);
The execution plan without using a hint (assuming
optimizer_mode is set to the default value
all_rows):
explain plan for
select
*
from
tq84_A a join
tq84_B b on a.id = b.id
where
a.num = 5;
select * from dbms_xplan.display(format=>'basic');
--
-- -------------------------------------
-- | Id | Operation | Name |
-- -------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | HASH JOIN | |
-- | 2 | TABLE ACCESS FULL| TQ84_A |
-- | 3 | TABLE ACCESS FULL| TQ84_B |
-- -------------------------------------
Same query, but this time with the first_rows hint:
explain plan for
select /*+ first_rows */
*
from
tq84_A a join
tq84_B b on a.id = b.id
where
a.num = 5;
select * from dbms_xplan.display(format=>'basic');
--
-- --------------------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | NESTED LOOPS | |
-- | 2 | NESTED LOOPS | |
-- | 3 | TABLE ACCESS FULL | TQ84_B |
-- | 4 | INDEX UNIQUE SCAN | TQ84_A_IX |
-- | 5 | TABLE ACCESS BY INDEX ROWID| TQ84_A |
-- --------------------------------------------------