This patch implements support for implicit query block (QB) names in
optimizer hints, allowing hints to reference query blocks and tables
within derived tables, views and CTEs without requiring explicit
QB_NAME hints.
Examples.
-- Addressing a table inside a derived table using implicit QB name
select /*+ no_index(t1@dt) */ *
from (select * from t1 where a > 10) as DT;
-- this is an equivalent to:
select /*+ no_index(t1@dt) */ * from
(select /*+ qb_name(dt)*/ * from t1 where a > 10) as DT;
-- Addressing a query block corresponding to the derived table
select /*+ no_bnl(@dt) */ *
from (select * from t1, t2 where t.1.a > t2.a) as DT;
-- View
create view v1 as select * from t1 where a > 10 and b > 100;
-- referencing a table inside a view by implicit QB name:
select /*+ index_merge(t1@v1 idx_a, idx_b) */ *
from v1, t2 where v1.a = t2.a;
-- equivalent to:
create view v1 as select /*+ qb_name(qb_v1) */ *
from t1 where a > 10 and b > 100;
select /*+ index_merge(t1@qb_v1 idx_a, idx_b) */ *
from v1, t2 where v1.a = t2.a;
-- CTE
with aless100 as (select a from t1 where b <100)
select /*+ index(t1@aless100) */ * from aless100;
-- equivalent to:
with aless100 as (select /*+ qb_name(aless100) */ a from t1 where b <100)
select /*+ index(t1@aless100) */ * from aless100;
Limitations:
- Only SELECT statements support implicit QB names. DML operations
(UPDATE, DELETE, INSERT) only support explicit QB names
