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.
Posts
