Typical problem that you can encounter during tuning is how to change explain plan inside complex view or inside a subquery for your sql statements. QB_NAME hints helps to do it. It can define a name for a query block. This name can then be used in another query block to hint tables appearing in the named query block.
Let’s create sample data model to show how QB_NAME hint works. I have created two table MASTER_TBL and CHILD_TBL and view MASTER_CHILD_TBL_VW in my private schema TOMASZ.The most important is the view that will be used later in examples for QB_NAME hint.
CREATE TABLE master_tbl ( master_id NUMBER PRIMARY KEY, master_name VARCHAR2(30) ); CREATE TABLE child_tbl ( child_id NUMBER PRIMARY KEY, child_name VARCHAR2(30), child_master_id NUMBER ); ALTER TABLE child_tbl ADD CONSTRAINT child_tbl_fk FOREIGN KEY(child_master_id) REFERENCES master_tbl(master_id); CREATE INDEX child_tbl_fk_idx ON child_tbl(child_master_id); CREATE OR REPLACE VIEW master_child_tbl_vw AS SELECT m1.*, c1.* FROM master_tbl m1, child_tbl c1 WHERE m1.master_id=c1.child_master_id UNION SELECT m2.*, c2.* FROM master_tbl m2, child_tbl c2 WHERE m2.master_id=c2.child_master_id;
Let’s load some sample data
BEGIN FOR i IN 1..10 LOOP INSERT INTO master_tbl VALUES(i, 'name_'||i); END LOOP; COMMIT; END; / BEGIN FOR i IN 1..10 LOOP FOR j IN 1..10 LOOP INSERT INTO child_tbl VALUES((i-1)*10+j, 'name_'||((i-1)*10+j), i); END LOOP; END LOOP; COMMIT; END; / BEGIN dbms_stats.gather_table_stats(USER, 'MASTER_TBL', CASCADE=>TRUE); dbms_stats.gather_table_stats(USER, 'CHILD_TBL', CASCADE=>TRUE); END; / SELECT * FROM master_tbl; MASTER_ID MASTER_NAME ---------- ------------------------------ 1 name_1 2 name_2 ... 9 name_9 10 name_10 10 rows selected SELECT child_master_id, count(*) FROM child_tbl GROUP BY child_master_id ORDER BY 1; CHILD_MASTER_ID COUNT(*) ---------------- ---------- 1 10 2 10 3 10 4 10 5 10 6 10 7 10 8 10 9 10 10 10 10 rows selected
Explain plan generation for simple query selecting data from view MASTER_CHILD_TBL_VW show that Oracle gives automatically names for each subquery block and each table inside a subquery block “Query Block Name / Object Alias”:
----------------------------------------------------------------- -- don't execute it I just want to show --"query block names / object alias" -- assigned during explain plan generation below ----------------------------------------------------------------- CREATE OR REPLACE VIEW master_child_tbl_vw "SET$1" AS "SEL$2" SELECT m1.*, c1.* FROM master_tbl m1 "M1@SEL2", child_tbl c1 "C1@SEL$2" WHERE m1.master_id=c1.child_master_id UNION "SEL$3" SELECT m2.*, c2.* FROM master_tbl m2 "M2@SEL$3", child_tbl c2 "C2@SEL$3" WHERE m2.master_id=c2.child_master_id;
- SET$1 – name for view MASTER_CHILD_TBL_VW
- SEL$2 – first subquery name in MASTER_CHILD_TBL_VW
- SEL$3- second subquery name in MASTER_CHILD_TBL_VW
- M1@SEL$2 – first table in first subquery
- C1@SEL$2 – second table in first subquery
- M2@SEL$2 – first table in first subquery
- C2@SEL$2 – second table in first subquery
EXPLAIN PLAN FOR SELECT * FROM master_child_tbl_vw WHERE master_id=1; SELECT * FROM TABLE(dbms_xplan.display( format=>'ALL -ROWS -BYTES -COST -PROJECTION -PREDICATE -NOTE')); Plan hash value: 1781916737 --------------------------------------------------------------------- | Id | Operation | Name --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | VIEW | MASTER_CHILD_TBL_VW | 2 | SORT UNIQUE | | 3 | UNION-ALL | | 4 | NESTED LOOPS | | 5 | TABLE ACCESS BY INDEX ROWID | MASTER_TBL | 6 | INDEX UNIQUE SCAN | SYS_C0010501 | 7 | TABLE ACCESS BY INDEX ROWID BATCHED| CHILD_TBL | 8 | INDEX RANGE SCAN | CHILD_TBL_FK_IDX | 9 | NESTED LOOPS | | 10 | TABLE ACCESS BY INDEX ROWID | MASTER_TBL | 11 | INDEX UNIQUE SCAN | SYS_C0010501 | 12 | TABLE ACCESS BY INDEX ROWID BATCHED| CHILD_TBL | 13 | INDEX RANGE SCAN | CHILD_TBL_FK_IDX --------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$1 / MASTER_CHILD_TBL_VW@SEL$1 2 - SET$1 4 - SEL$2 5 - SEL$2 / M1@SEL$2 6 - SEL$2 / M1@SEL$2 7 - SEL$2 / C1@SEL$2 8 - SEL$2 / C1@SEL$2 9 - SEL$3 10 - SEL$3 / M2@SEL$3 11 - SEL$3 / M2@SEL$3 12 - SEL$3 / C2@SEL$3 13 - SEL$3 / C2@SEL$3
Query block names and object alias let us to use dedicated hints
/*+ hint(@query_block_name @object_name) */
In following example I have forced full scan on table MASTER_TBL in first subquery
EXPLAIN PLAN FOR SELECT /*+ full(@sel$2 m1@sel$2) */ * FROM master_child_tbl_vw WHERE master_id=1; SELECT * FROM TABLE(dbms_xplan.display( format=>'ALL -ROWS -BYTES -COST -PROJECTION -PREDICATE -NOTE')); Plan hash value: 2818659847 --------------------------------------------------------------------- | Id | Operation | Name --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | VIEW | MASTER_CHILD_TBL_VW | 2 | SORT UNIQUE | | 3 | UNION-ALL | | 4 | NESTED LOOPS | | 5 | NESTED LOOPS | | 6 | TABLE ACCESS FULL | MASTER_TBL | 7 | INDEX RANGE SCAN | CHILD_TBL_FK_IDX | 8 | TABLE ACCESS BY INDEX ROWID | CHILD_TBL | 9 | NESTED LOOPS | | 10 | TABLE ACCESS BY INDEX ROWID | MASTER_TBL | 11 | INDEX UNIQUE SCAN | SYS_C0010501 | 12 | TABLE ACCESS BY INDEX ROWID BATCHED| CHILD_TBL | 13 | INDEX RANGE SCAN | CHILD_TBL_FK_IDX --------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$1 / MASTER_CHILD_TBL_VW@SEL$1 2 - SET$1 4 - SEL$2 6 - SEL$2 / M1@SEL$2 7 - SEL$2 / C1@SEL$2 8 - SEL$2 / C1@SEL$2 9 - SEL$3 10 - SEL$3 / M2@SEL$3 11 - SEL$3 / M2@SEL$3 12 - SEL$3 / C2@SEL$3 13 - SEL$3 / C2@SEL$3
QB_NAME hint enables to give manual names for our subqueries
CREATE OR REPLACE VIEW master_child_tbl_vw AS SELECT /*+ QB_NAME(qb_sub1) */ m1.*, c1.* FROM master_tbl m1, child_tbl c1 WHERE m1.master_id=c1.child_master_id UNION SELECT /*+ QB_NAME(qb_sub2) */ m2.*, c2.* FROM master_tbl m2, child_tbl c2 WHERE m2.master_id=c2.child_master_id;
So reference to objects is easier. Forcing full on CHILD_TBL in second subquery QB_SUB2
EXPLAIN PLAN FOR SELECT /*+ full(@qb_sub2 c2@qb_sub2) */ * FROM master_child_tbl_vw WHERE master_id=1; SELECT * FROM TABLE(dbms_xplan.display( format=>'ALL -ROWS -BYTES -COST -PROJECTION -PREDICATE -NOTE')); Plan hash value: 1754361206 --------------------------------------------------------------------- | Id | Operation | Name --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | VIEW | MASTER_CHILD_TBL_VW | 2 | SORT UNIQUE | | 3 | UNION-ALL | | 4 | NESTED LOOPS | | 5 | TABLE ACCESS BY INDEX ROWID | MASTER_TBL | 6 | INDEX UNIQUE SCAN | SYS_C0010501 | 7 | TABLE ACCESS BY INDEX ROWID BATCHED| CHILD_TBL | 8 | INDEX RANGE SCAN | CHILD_TBL_FK_IDX | 9 | NESTED LOOPS | | 10 | TABLE ACCESS BY INDEX ROWID | MASTER_TBL | 11 | INDEX UNIQUE SCAN | SYS_C0010501 | 12 | TABLE ACCESS FULL | CHILD_TBL --------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$1 / MASTER_CHILD_TBL_VW@SEL$1 2 - SET$1 4 - QB_SUB1 5 - QB_SUB1 / M1@QB_SUB1 6 - QB_SUB1 / M1@QB_SUB1 7 - QB_SUB1 / C1@QB_SUB1 8 - QB_SUB1 / C1@QB_SUB1 9 - QB_SUB2 10 - QB_SUB2 / M2@QB_SUB2 11 - QB_SUB2 / M2@QB_SUB2 12 - QB_SUB2 / C2@QB_SUB2
Another simple examples
--forcing full yes I know I could do full(a) :) SELECT /*+ QB_NAME(qb) FULL(@qb a@qb) */ * FROM master_tbl A WHERE A.master_id=1; --more useful example :) SELECT /*+ FULL(@qb c1@qb) */ * FROM ( SELECT /*+ QB_NAME(qb) */ m1.*, c1.* FROM master_tbl m1, child_tbl c1 WHERE m1.master_id=c1.child_master_id ) WHERE master_id=1;
Have a fun 🙂
Tomasz