H.1.3 Sample Queries
Several examples follow of DML statements that the allocate engine will issue to generate debits and credits.
These examples illustrate where the four kinds of supported hints are inserted into the queries. Note that while the examples all demonstrate hints that suggest parallelism to the database optimizer, you may utilize any valid form of a hint.
Filter on Hint
MERGE INTO LEDGER_STAT TARGET
USING (
SELECT C.*
FROM (
SELECT 11002.000000 identity_code, 1994 year_s,'D'
accum_type_cd,100 consolidation_cd, 101 balance_type_cd, 'USD'
ISO_CURRENCY_CD, 457.000000 FINANCIAL_ELEM_ID, ORG_UNIT_ID,
GL_ACCOUNT_ID, 821.000000 COMMON_COA_ID, PRODUCT_ID, (
SOURCE_AMOUNT + leaf_amount )*1 BALANCE_AMOUNT
from (SELECT /*+ parallel(ledger_stat, 8) */row_number() over (partition by a.PRODUCT_ID,
a.GL_ACCOUNT_ID, a.ORG_UNIT_ID order by a.PRODUC T_ID, a.GL_ACCOUNT_ID, a.ORG_UNIT_ID)rw , SUM(DECODE(COMMON_COA_ID, 101, 0,
DECODE(a.year_s, 1994, a.month_03, 0)) ) over (partition by
a.PRODUCT_ID, a.GL_ACCOUNT_ID, a.ORG_UNIT_ID) SOURCE_AMOUNT,
SUM(DECODE(COMMON_COA_ID, 101, DECODE(a.year_s, 1994, a.month_03,
0), 0)) over ( partition by a.PRODUCT_ID, a.GL_ACCOUNT_ID,
a.ORG_UNIT_ID ) leaf_amount , a.PRODUCT_ID, a.GL_ACCOUNT_ID,
a.ORG_UNIT_ID
from LEDGER_STAT a
where(a.COMMON_COA_ID=820
OR a.COMMON_COA_ID=101)
and (((a.IDENTITY_CODE = 0)
or (a.IDENTITY_CODE = 1)
or (a.IDENTITY_CODE = 700)
or (a.IDENTITY_CODE = 779)))
and (a.identity_code) IN (select distinct a.parent_identity_code
from FSI_M_DATA_IDENTITY_DETAIL a
where a.identity_code in (11002)
and a.as_of_date = '03/ 31/ 1994'
and a.src_drv_type in ('S' , 'D') )
and a.year_s IN (1994) )
where rw = 1 ) C ) SOURCE ON (TARGET.FINANCIAL_ELEM_ID =
457.000000
AND TARGET.ORG_UNIT_ID = SOURCE.ORG_UNIT_ID
AND TARGET.GL_ACCOUNT_ID = SOURCE.GL_ACCOUNT_ID
AND TARGET.COMMON_COA_ID = 821.000000
AND TARGET.PRODUCT_ID = SOURCE.PRODUCT_ID
AND TARGET.YEAR_S = 1994
AND TARGET.IDENTITY_CODE = 11002.000000
AND TARGET.ISO_CURRENCY_CD = SOURCE.ISO_CURRENCY_CD
AND TARGET.CONSOLIDATION_CD = 100
AND TARGET.ACCUM_TYPE_CD = 'D' ) WHEN MATCHED THEN
UPDATE
SET TARGET.MONTH_03 = TARGET.MONTH_03 + SOURCE.BALANCE_AMOUNT,
BALANCE_TYPE_CD = 101, ytd_03 = ytd_02 + SOURCE.BALANCE_AMOUNT , ytd_04 = ytd_03 + month_04 , ytd_05 = ytd_04 + month_05 , ytd_06
= ytd_05 + month_06 , ytd_07 = ytd_06 + month_07 , ytd_08 =
ytd_07 + month_08 , ytd_09 = ytd_08 + month_09 , ytd_10 = ytd_09
+ month_10 , ytd_11 = ytd_10 + month_11 , ytd_12 = ytd_11 +
month_12 WHEN NOT MATCHED THEN
INSERT (FINANCIAL_ELEM_ID, ORG_UNIT_ID, GL_ACCOUNT_ID,
COMMON_COA_ID, PRODUCT_ID, ACCUM_TYPE_CD, BALANCE_TYPE_CD,
CONSOLIDATION_CD, CURRENCY_TYPE_CD, ISO_CURRENCY_CD,
IDENTITY_CODE, MONTH_01, MONTH_02, MONTH_03, MONTH_04, MONTH_05,
MONTH_06, MONTH_07, MONTH_08, MONTH_09, MONTH_10, MONTH_11,
MONTH_12, YTD_01, YTD_02, YTD_03, YTD_04, YTD_05, YTD_06, YTD_07,
YTD_08, YTD_09, YTD_10, YTD_11, YTD_12, YEAR_S )
VALUES ( 457.000000, SOURCE.ORG_UNIT_ID, SOURCE.GL_ACCOUNT_ID,
821.000000, SOURCE.PRODUCT_ID, 'D' , 101 , 100 , 0 ,
SOURCE.ISO_CURRENCY_CD, 11002.000000 , 0.0, 0.0,
SOURCE.BALANCE_AMOUNT, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0,
0.0, 0, 0, SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT , 1994)
Tree Filter Hint
MERGE INTO LEDGER_STAT TARGET
USING (
SELECT 11040.000000 identity_code, 1994 year_s, 'D'
accum_type_cd , 100 consolidation_cd , 101 balance_type_cd,'USD'
ISO_CURRENCY_CD, 10900.000000 FINANCIAL_ELEM_ID, ORG_UNIT_ID,
GL_ACCOUNT_ID, COMMON_COA_ID, PRODUCT_ID, SOURCE_AMOUNT * 1
BALANCE_AMOUNT
from (SELECT row_number() over (partition by a.PRODUCT_ID,
a.COMMON_COA_ID
order by a.PRODUCT_ID, a.COMMON_COA_ID)rw, SUM(DECODE(a.year_s, 1994,
a.month_03, 0) * 2 ) over (partition by a.PRODUCT_ID,
a.COMMON_COA_ID) SOURCE_AMOUNT , a.PRODUCT_ID, a.COMMON_COA_ID ,
a.GL_ACCOUNT_ID, a.ORG_UNIT_ID
from LEDGER_STAT a
where a.FINANCIAL_ELEM_ID=100
and a.ORG_UNIT_ID=2100
and a.GL_ACCOUNT_ID IN (SELECT /*+ parallel (ofsa_idt_rollup,
8) */ leaf_node
FROM OFSA_IDT_ROLLUP
WHERE OFSA_IDT_ROLLUP.sys_id_num = 2000779
and (OFSA_IDT_ROLLUP.leaf_node = 101301))
and (((a.CONSOLIDATION_CD = '100'))
and ((a.IDENTITY_CODE = 0)))
and (a.identity_code) IN (select distinct a.parent_identity_code
from FSI_M_DATA_IDENTITY_DETAIL a
where a.identity_code in (11040)
and a.as_of_date = '03/ 31/ 1994'
and a.src_drv_type = 'S' )
and a.year_s IN (1994) )
where rw = 1 ) SOURCE ON (TARGET.FINANCIAL_ELEM_ID = 10900.000000
AND TARGET.ORG_UNIT_ID = SOURCE.ORG_UNIT_ID
AND TARGET.GL_ACCOUNT_ID = SOURCE.GL_ACCOUNT_ID
AND TARGET.COMMON_COA_ID = SOURCE.COMMON_COA_ID
AND TARGET.PRODUCT_ID = SOURCE.PRODUCT_ID
AND TARGET.YEAR_S = 1994
AND TARGET.IDENTITY_CODE = 11040.000000
AND TARGET.ISO_CURRENCY_CD = SOURCE.ISO_CURRENCY_CD
AND TARGET.CONSOLIDATION_CD = 100
AND TARGET.ACCUM_TYPE_CD = 'D' ) WHEN MATCHED THEN
UPDATE
SET TARGET.MONTH_03 = TARGET.MONTH_03 + SOURCE.BALANCE_AMOUNT,
BALANCE_TYPE_CD = 101, ytd_03 = ((ytd_02* 2) +
SOURCE.BALANCE_AMOUNT )/ 3 , ytd_04 = ((ytd_03* 3) + month_04 )/
4 , ytd_05 = ((ytd_04* 4) + month_05 )/ 5 , ytd_06 = ((ytd_05* 5)
+ month_06 )/ 6 , ytd_07 = ((ytd_06* 6) + month_07 )/ 7 , ytd_08
= ((ytd_07* 7) + month_08 )/ 8 , ytd_09 = ((ytd_08* 8) + month_09
/ 9 , ytd_10 = ((ytd_09* 9) + month_10 )/ 10 , ytd_11 =
((ytd_10* 10) + month_11 )/ 11 , ytd_12 = ((ytd_11* 11) +
month_12 )/ 12 WHEN NOT MATCHED THEN
INSERT (FINANCIAL_ELEM_ID, ORG_UNIT_ID, GL_ACCOUNT_ID,
COMMON_COA_ID, PRODUCT_ID, ACCUM_TYPE_CD, BALANCE_TYPE_CD,
CONSOLIDATION_CD, CURRENCY_TYPE_CD, ISO_CURRENCY_CD,
IDENTITY_CODE, MONTH_01, MONTH_02, MONTH_03, MONTH_04, MONTH_05,
MONTH_06, MONTH_07, MONTH_08, MONTH_09, MONTH_10, MONTH_11,
MONTH_12, YTD_01, YTD_02, YTD_03, YTD_04, YTD_05, YTD_06, YTD_07,
YTD_08, YTD_09, YTD_10, YTD_11, YTD_12, YEAR_S )
VALUES ( 10900.000000, SOURCE.ORG_UNIT_ID, SOURCE.GL_ACCOUNT_ID,
SOURCE.COMMON_COA_ID, SOURCE.PRODUCT_ID, 'D' , 101 , 100 , 0 ,
SOURCE.ISO_CURRENCY_CD, 11040.000000 , 0.0, 0.0,
SOURCE.BALANCE_AMOUNT, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0,
0.0, 0, 0, (SOURCE.BALANCE_AMOUNT) / 3, ((
(SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4, ((((
(SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5, ((((((
(SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5)* 5) / 6, ((((((((
(SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5)* 5) / 6)* 6) / 7,
(((((((((( (SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5)* 5) /
6)* 6) / 7)* 7) / 8, (((((((((((( (SOURCE.BALANCE_AMOUNT) / 3)*
3) / 4)* 4) / 5)* 5) / 6)* 6) / 7)* 7) / 8)* 8) / 9,
(((((((((((((( (SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5)* 5)
/ 6)* 6) / 7)* 7) / 8)* 8) / 9)* 9) / 10, ((((((((((((((((
(SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5)* 5) / 6)* 6) / 7)*
7) / 8)* 8) / 9)* 9) / 10)* 10) / 11, ((((((((((((((((((
(SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5)* 5) / 6)* 6) / 7)*
7) / 8)* 8) / 9)* 9) / 10)* 10) / 11)* 11) / 12 , 1994)
Pct Distr Hint
MERGE INTO FSI_D_MERCHANT_CARDS TARGET USING (SELECT SUM
(IVW_SOURCE.IVW_TARGET_AMOUNT) TARGET_AMOUNT , IDENTITY_CODE,
ID_NUMBER
FROM(
SELECT ( Src.SOURCE_AMOUNT * Drv.drv_factor) IVW_TARGET_AMOUNT,
Drv.IDENTITY_CODE, Drv.ID_NUMBER
FROM (SELECT SUM(DECODE(a.year_s, 1994, a.month_03, 0) )
SOURCE_AMOUNT
FROM LEDGER_STAT a
where a.FINANCIAL_ELEM_ID=100
and a.ORG_UNIT_ID=2100
and a.GL_ACCOUNT_ID IN (SELECT leaf_node
FROM OFSA_IDT_ROLLUP
WHERE OFSA_IDT_ROLLUP.sys_id_num = 2000779
and (OFSA_IDT_ROLLUP.leaf_node = 101301))
and (((a.CONSOLIDATION_CD = '100'))
and((a.IDENTITY_CODE = 0)))
and (a.identity_code) IN (select distinct a.parent_identity_code
from FSI_M_DATA_IDENTITY_DETAIL a
where a.identity_code in (10316)
and a.as_of_date = '03/ 31/ 1994'
and a.src_drv_type = 'S' )
and a.year_s IN (1994)) Src , (SELECT ( ColVal / mTotal )
drv_factor , IDENTITY_CODE, ID_NUMBER
from (SELECT /*+ parallel(FSI_D_MERCHANT_CARDS, 8) */
a.IDENTITY_CODE, a.ID_NUMBER, (TOTAL_TRANSACTIONS) ColVal, (SUM
(TOTAL_TRANSACTIONS) OVER()) mTotal
from FSI_D_MERCHANT_CARDS a
where a.GL_ACCOUNT_ID=101301
and a.as_of_date='03/ 31/ 1994')
WHERE mTotal <> 0) Drv) IVW_SOURCE
WHERE IVW_TARGET_AMOUNT <> 0
GROUP BY IDENTITY_CODE , ID_NUMBER ) SOURCE ON
(SOURCE.IDENTITY_CODE = TARGET.IDENTITY_CODE
AND SOURCE.ID_NUMBER = TARGET.ID_NUMBER ) WHEN MATCHED THEN
UPDATE
SET IDENTITY_CODE_CHG = 10316.000000 , TARGET.ATM_EXP =
DECODE(identity_code_chg, 10316.000000, ATM_EXP, 0)+
(SOURCE.TARGET_AMOUNT * 1)DML Hint
MERGE /*+ parallel(ledger_stat, 4) */ INTO LEDGER_STAT TARGET
USING (SELECT (SUM (IVW_SOURCE.IVWSRC_AMOUNT) * 1 )
BALANCE_AMOUNT, ORG_UNIT_ID, GL_ACCOUNT_ID, PRODUCT_ID
FROM (SELECT (Src.src_amount * Drv.drv_factor) IVWSRC_AMOUNT,
Src.ORG_UNIT_ID, Src.GL_ACCOUNT_ID, Src.PRODUCT_ID
FROM (SELECT SUM(DECODE(a.year_s, 1994, a.month_03, 0) )
src_amount , a.GL_ACCOUNT_ID, a.PRODUCT_ID, a.ORG_UNIT_ID
from LEDGER_STAT a
where (((a.IDENTITY_CODE = 0)
or (a.IDENTITY_CODE = 1)
or (a.IDENTITY_CODE = 700)
or (a.IDENTITY_CODE = 779)))
and (a.identity_code) IN (select distinct a.parent_identity_code
from FSI_M_DATA_IDENTITY_DETAIL a
where a.identity_code in (10993)
and a.as_of_date = '03/ 31/ 1994'
and a.src_drv_type = 'S' )
and a.year_s IN (1994)
GROUP BY a.GL_ACCOUNT_ID, a.PRODUCT_ID, a.ORG_UNIT_ID) Src JOIN
(SELECT (ColVal/ mTotal) Drv_factor, ORG_UNIT_ID
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY a.ORG_UNIT_ID
ORDER BY a.ORG_UNIT_ID) rw, (SUM(DECODE(a.year_s, 1994,
a.month_03, 0)) over (PARTITION BY a.ORG_UNIT_ID ))ColVal,
(SUM(DECODE(a.year_s, 1994, a.month_03, 0)) over(PARTITION BY
a.ORG_UNIT_ID ))mTotal, a.ORG_UNIT_ID
from LEDGER_STAT a
where (((a.IDENTITY_CODE = 0)
or (a.IDENTITY_CODE = 1)
or (a.IDENTITY_CODE = 700)
or (a.IDENTITY_CODE = 779)))
and a.year_s IN (1994) AND(a.ORG_UNIT_ID) IN (SELECT ORG_UNIT_ID
FROM (SELECT SUM(DECODE(a.year_s, 1994, a.month_03, 0) )
src_amount , a.GL_ACCOUNT_ID, a.PRODUCT_ID, a.ORG_UNIT_ID
from LEDGER_STAT a
where (((a.IDENTITY_CODE = 0)
or (a.IDENTITY_CODE = 1)
or (a.IDENTITY_CODE = 700)
or (a.IDENTITY_CODE = 779)))
and (a.identity_code) IN (select distinct a.parent_identity_code
from FSI_M_DATA_IDENTITY_DETAIL a
where a.identity_code in (10993)
and a.as_of_date = '03/ 31/ 1994'
and a.src_drv_type = 'S' )
and a.year_s IN (1994)
GROUP BY a.GL_ACCOUNT_ID, a.PRODUCT_ID, a.ORG_UNIT_ID) ))
WHERE RW = 1
AND mTotal <> 0
AND ColVal <> 0) Drv ON (Src.ORG_UNIT_ID = Drv.ORG_UNIT_ID) )
IVW_SOURCE
where IVWSRC_AMOUNT <> 0
GROUP BY ORG_UNIT_ID, GL_ACCOUNT_ID, PRODUCT_ID ) SOURCE ON
(TARGET.FINANCIAL_ELEM_ID = 457.000000
AND TARGET.ORG_UNIT_ID = SOURCE.ORG_UNIT_ID
AND TARGET.GL_ACCOUNT_ID = SOURCE.GL_ACCOUNT_ID
AND TARGET.COMMON_COA_ID = 821.000000
AND TARGET.PRODUCT_ID = SOURCE.PRODUCT_ID
AND TARGET.YEAR_S = 1994
AND TARGET.IDENTITY_CODE = 10993.000000
AND TARGET.ISO_CURRENCY_CD = 'USD'
AND TARGET.CONSOLIDATION_CD = 100
AND TARGET.ACCUM_TYPE_CD = 'D' ) WHEN MATCHED THEN
UPDATE
SET TARGET.MONTH_03 = TARGET.MONTH_03 + SOURCE.BALANCE_AMOUNT,
BALANCE_TYPE_CD = 101, ytd_03 = ytd_02 + SOURCE.BALANCE_AMOUNT ,
ytd_04 = ytd_03 + month_04 , ytd_05 = ytd_04 + month_05 , ytd_06
= ytd_05 + month_06 , ytd_07 = ytd_06 + month_07 , ytd_08 =
ytd_07 + month_08 , ytd_09 = ytd_08 + month_09 , ytd_10 = ytd_09
+ month_10 , ytd_11 = ytd_10 + month_11 , ytd_12 = ytd_11 +
month_12 WHEN NOT MATCHED THEN
INSERT (FINANCIAL_ELEM_ID, ORG_UNIT_ID, GL_ACCOUNT_ID,
COMMON_COA_ID, PRODUCT_ID, ACCUM_TYPE_CD, BALANCE_TYPE_CD,
CONSOLIDATION_CD, CURRENCY_TYPE_CD, ISO_CURRENCY_CD,
IDENTITY_CODE, MONTH_01, MONTH_02, MONTH_03, MONTH_04, MONTH_05,
MONTH_06, MONTH_07, MONTH_08, MONTH_09, MONTH_10, MONTH_11,
MONTH_12, YTD_01, YTD_02, YTD_03, YTD_04, YTD_05, YTD_06, YTD_07,
YTD_08, YTD_09, YTD_10, YTD_11, YTD_12, YEAR_S )
VALUES ( 457.000000, SOURCE.ORG_UNIT_ID, SOURCE.GL_ACCOUNT_ID,
821.000000, SOURCE.PRODUCT_ID, 'D' , 101 , 100 , 0 , 'USD' ,
10993.000000 , 0.0, 0.0, SOURCE.BALANCE_AMOUNT, 0.0, 0.0, 0.0,
0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0, SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , 1994)