Applying Hints to Objects inside Database Views

Occasionally, I need to apply a hint to a table within a view to achieve a particular execution plan. I can't put the hint into the view because it is not appropriate for all queries that use the view. However, I can give the query block an explicit name using the QB_NAME hint, and then refer to the named query block.

When I do this, I give the query block the same name as the record in PeopleSoft. This example uses record VAT_TX_AP_I_VW that corresponds to a database view PS_VAT_TX_AP_I_VW. Therefore, I named the query block VAT_TX_AP_I_VW. This naming convention is helpful if the view name is dynamically generated in an Application Engine step (see previous blog posting on Hinting Dynamically Generated SQL in Application Engine).

CREATE VIEW PS_VAT_TX_AP_I_VW ... AS
SELECT /*+QB_NAME(VAT_TX_AP_I_VW)*/ B.BUSINESS_UNIT
,B.VOUCHER_ID
,B.UNPOST_SEQ
,B.APPL_JRNL_ID
...
FROM PS_VOUCHER A
,PS_VCHR_ACCTG_LINE B
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.VOUCHER_ID = B.VOUCHER_ID
AND B.VAT_DISTRIB_STATUS IN (' ', 'N', 'R', 'M')
AND B.DST_ACCT_TYPE IN ('VIR', 'VIWR', 'VIIR', 'VICR')
AND B.PRIMARY_LEDGER = 'Y' 

I need to use a hint to force this use of this index on the table PS_VCHR_ACCTG_LINE.

CREATE INDEX PSHVCHR_ACCTG_LINE ON PS_VCHR_ACCTG_LINE
(VAT_DISTRIB_STATUS, BUSINESS_UNIT, VOUCHER_ID); 

Now I can add a hint to the query that calls the view and apply the hint to a table within the view.

SELECT /*+INDEX(@VAT_TX_AP_I_VW B@VAT_TX_AP_I_VW (PS_VCHR_ACCTG_LINE.VAT_DISTRIB_STATUS))*/ C.VAT_ENTITY,
A.COUNTRY_VAT_RPTG, 0, 0, A.BUSINESS_UNIT, A.VOUCHER_ID, A.UNPOST_SEQ, A.APPL_JRNL_ID,
A.POSTING_PROCESS, A.PYMNT_CNT, A.VOUCHER_LINE_NUM, A.DISTRIB_LINE_NUM, A.DST_ACCT_TYPE,
A.CF_BAL_LINE_NUM, A.LEDGER, A.TAX_AUTHORITY_CD, 50717653, 'N'
FROM PS_VAT_TX_AP_I_VW A,
PS_VAT_UPD_BU_TAO B
, PS_VAT_ENT_BU_GL C
WHERE B.PROCESS_INSTANCE = 50717653
AND B.REQUEST_NBR = 1
AND B.VAT_SOURCE_DEFN = 'AP_VOUCHER'
AND A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.BUSINESS_UNIT_GL = C.BUSINESS_UNIT

The execution plan shows that the index dictated by the hint was used. There are additional benefits. I can also see the query block name in various places in the extended execution plan, and it helps interpretation.

-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     1 |   157 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                    |     1 |   157 |     5   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                 |                    |     1 |   138 |     4   (0)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN        |                    |     1 |    66 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN           | PS_VAT_UPD_BU_TAO  |     1 |    52 |     0   (0)| 00:00:01 |
|   5 |     BUFFER SORT                |                    |     9 |   126 |     1   (0)| 00:00:01 |
|   6 |      INDEX FULL SCAN           | PS_VAT_ENT_BU_GL   |     9 |   126 |     1   (0)| 00:00:01 |
|   7 |    INLIST ITERATOR             |                    |       |       |            |          |
|*  8 |     TABLE ACCESS BY INDEX ROWID| PS_VCHR_ACCTG_LINE |     1 |    72 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | PSHVCHR_ACCTG_LINE |   117 |       |     3   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID  | PS_VOUCHER         |     1 |    19 |     1   (0)| 00:00:01 |
|* 11 |    INDEX UNIQUE SCAN           | PS_VOUCHER         |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

It is now clear which objects in the execution plan come from the view. This can be helpful when the same table appears in more than one place.

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$71CBF28F
4 - SEL$71CBF28F / B@SEL$1
6 - SEL$71CBF28F / C@SEL$1
8 - SEL$71CBF28F / B@VAT_TX_AP_I_VW
9 - SEL$71CBF28F / B@VAT_TX_AP_I_VW
10 - SEL$71CBF28F / A@VAT_TX_AP_I_VW
11 - SEL$71CBF28F / A@VAT_TX_AP_I_VW

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$71CBF28F" "A"@"VAT_TX_AP_I_VW")
USE_NL(@"SEL$71CBF28F" "B"@"VAT_TX_AP_I_VW")
USE_MERGE_CARTESIAN(@"SEL$71CBF28F" "C"@"SEL$1")
LEADING(@"SEL$71CBF28F" "B"@"SEL$1" "C"@"SEL$1" "B"@"VAT_TX_AP_I_VW" "A"@"VAT_TX_AP_I_VW")
INDEX_RS_ASC(@"SEL$71CBF28F" "A"@"VAT_TX_AP_I_VW" "PS_VOUCHER")
INDEX_RS_ASC(@"SEL$71CBF28F" "B"@"VAT_TX_AP_I_VW" ("PS_VCHR_ACCTG_LINE"."VAT_DISTRIB_STATUS"
 "PS_VCHR_ACCTG_LINE"."BUSINESS_UNIT" "PS_VCHR_ACCTG_LINE"."VOUCHER_ID"))
INDEX(@"SEL$71CBF28F" "C"@"SEL$1" ("PS_VAT_ENT_BU_GL"."VAT_ENTITY"
 "PS_VAT_ENT_BU_GL"."BUSINESS_UNIT"))
INDEX(@"SEL$71CBF28F" "B"@"SEL$1" ("PS_VAT_UPD_BU_TAO"."PROCESS_INSTANCE"
 "PS_VAT_UPD_BU_TAO"."REQUEST_NBR" "PS_VAT_UPD_BU_TAO"."VAT_SOURCE_DEFN"
 "PS_VAT_UPD_BU_TAO"."BUSINESS_UNIT"))

OUTLINE(@"VAT_TX_AP_I_VW")
OUTLINE(@"SEL$1")
MERGE(@"VAT_TX_AP_I_VW")
OUTLINE_LEAF(@"SEL$71CBF28F")
ALL_ROWS
OPT_PARAM('_optimizer_cost_based_transformation' 'off')
OPT_PARAM('_unnest_subquery' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

This technique is not infallible. I have had cases where query transformation rendered the QB_NAME invalid.