NO_PUSH_PRED
The
no_push_pred hint prevents the pushing of a predicate from an outer
query block into the contained inner query block.
The following example tries to demonstrate this hint.
We need some test tables with test data:
create table tq84_O (val_i number(2), sel varchar2(6));
create table tq84_I (id number, pad varchar2(250));
insert into tq84_O
select
mod(level, 100),
to_char(level * 1234, 'fmxxxxxx')
from
dual connect by level <= 1000;
insert into tq84_I
select
mod(level * 5, 100),
rpad('*', 250, '*')
from
dual connect by level <= 1000;
create index tq84_I_ix on tq84_I(id);
begin
dbms_stats.gather_table_stats(user, 'tq84_O');
dbms_stats.gather_table_stats(user, 'tq84_I');
end;
/
Without using a hint in the following SQL statement, the
optimizer pushes the predicate
o.sel = '1c9ee' into the inner query block which uses the index (
INDEX RANGE SCAN) to access the relevant rows only:
explain plan for
select
o.val_i,
i.cnt
from
tq84_O o join
(select
id,
count(*) cnt
from
tq84_I
group by
id
) i on
o.val_i = i.id
where
o.sel = '1c9ee';
select * from table(dbms_xplan.display(format => 'basic'));
--
-- ---------------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | NESTED LOOPS | |
-- | 2 | TABLE ACCESS FULL | TQ84_O |
-- | 3 | VIEW PUSHED PREDICATE | |
-- | 4 | FILTER | |
-- | 5 | SORT AGGREGATE | |
-- | 6 | INDEX RANGE SCAN | TQ84_I_IX |
-- ---------------------------------------------
The following statement uses the no_push_pred hint in the outer query block and has to specify the name of the alias of the inner query block into which the predicate is not to be pushed.
The inner query block still uses the index, but mulitple times (NESTED LOOPS):
explain plan for
select /*+ no_push_pred(i) */
o.val_i,
i.cnt
from
tq84_O o join
(select
id,
count(*) cnt
from
tq84_I
group by
id
) i on
o.val_i = i.id
where
o.sel = '1c9ee';
select * from table(dbms_xplan.display(format => 'basic'));
--
-- -----------------------------------------
-- | Id | Operation | Name |
-- -----------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | HASH GROUP BY | |
-- | 2 | NESTED LOOPS | |
-- | 3 | TABLE ACCESS FULL| TQ84_O |
-- | 4 | INDEX RANGE SCAN | TQ84_I_IX |
-- -----------------------------------------
It's also possible to specify the hint in the inner query block, in this case without alias:
explain plan for
select
o.val_i,
i.cnt
from
tq84_O o join
(select /*+ no_push_pred */
id,
count(*) cnt
from
tq84_I
group by
id
) i on
o.val_i = i.id
where
o.sel = '1c9ee';
select * from table(dbms_xplan.display(format => 'basic'));
--
-- -----------------------------------------
-- | Id | Operation | Name |
-- -----------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | HASH GROUP BY | |
-- | 2 | NESTED LOOPS | |
-- | 3 | TABLE ACCESS FULL| TQ84_O |
-- | 4 | INDEX RANGE SCAN | TQ84_I_IX |
-- -----------------------------------------
Cleaning up
drop table tq84_O;
drop table tq84_I;