QB_NAME – control execution plans in views

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.

qb_name_1

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

Leave a Reply

Your email address will not be published. Required fields are marked *