2 Replies Latest reply: Jul 18, 2014 6:26 AM by user3530955 RSS

    Parallel Hint in OBIEE 10g

    user3530955

      Hi

       

      I have added a parallel hint in OBIEE 10g at the physical table level.

      It is generating a query as below

       

      /* Formatted on 2014/07/18 11:50:21 AM (QP5 v5.149.1003.31008) */

        SELECT DISTINCT

               D1.c8 AS c1,

               D1.c9 AS c2,

               D1.c10 AS c3,

               NVL (D1.c6, D1.c5) AS c4,

               D1.c1 AS c5,

               D1.c4 AS c6,

               D1.c3 AS c7,

               D1.c2 AS c8,

               NVL (D1.c6, NVL (D1.c5, 0)) - D1.c4 - D1.c3 - D1.c2 - D1.c1 AS c9,

               D1.c7 AS c10

          FROM (  SELECT                                   /*+ parallel(T11555,8) */

                        SUM (T11555.TDS_VAL_EXC_AMOUNT)

                            AS c1,

                         SUM (T11555.TRADING_YTD_BALANCE) AS c2,

                         SUM (T11555.FSAH_VAL_EXC_AMOUNT) AS c3,

                         SUM (T11555.CE_VAL_EXC_AMOUNT) AS c4,

                         SUM (T11555.LIQUIDATION_VALUE) AS c5,

                         SUM (T11555.HOME_LIQUIDATION_VALUE) AS c6,

                         SUM (T11555.TRADING_MANUAL) AS c7,

                         T11471.CALENDAR_DATE AS c8,

                         T10607.LEVEL2_NAME AS c9,

                         T10607.PORTFOLIO_KEY_NAME AS c10

                    FROM XXGL_TIME_DIM_V T11471,

                         XXGL_PORTFOLIO_SEC_DIM_V T10607,

                         XXGL_BS_DASHBOARD_V T11555

                   WHERE (    T10607.PORTFOLIO_CD = T11555.PORTFOLIO

                          AND T10607.LEVEL2_NAME = 'EQUITY TRADING [EQT]'

                          AND T10607.USER_NAME = UPPER ('C_BTR_NIK')

                          AND T10607.HIERARCHY_NAME = 'MANAGEMENT'

                          AND T11471.CALENDAR_DATE = T11555.CALENDAR_DATE

                          AND T11471.CALENDAR_DATE =

                                 TO_DATE ('2014-05-05', 'YYYY-MM-DD')

                          AND T11555.CALENDAR_DATE =

                                 TO_DATE ('2014-05-05', 'YYYY-MM-DD')

                          AND T11555.TRADING_INSTR_IND = 'T'

                          AND T11555.GLOBAL_TYPE_CODE <> 'NOST')

                GROUP BY T10607.LEVEL2_NAME,

                         T10607.PORTFOLIO_KEY_NAME,

                         T11471.CALENDAR_DATE) D1

         WHERE (   CASE WHEN D1.c1 = 0 THEN 'Y' ELSE 'N' END <> 'Y'

                OR CASE WHEN D1.c3 = 0 THEN 'Y' ELSE 'N' END <> 'Y'

                OR CASE WHEN D1.c4 = 0 THEN 'Y' ELSE 'N' END <> 'Y'

                OR CASE

                      WHEN NVL (D1.c6, D1.c5) - D1.c4 - D1.c3 - D1.c2 - D1.c1 < 0

                           OR NVL (D1.c6, D1.c5) - D1.c4 - D1.c3 - D1.c2 - D1.c1 > 0

                      THEN

                         'N'

                      ELSE

                         'Y'

                   END <> 'Y')

      ORDER BY c1, c2, c3

       

      What my DBA has asked for is that we have the parallel hin at the outermost part of the query and not table specific as I have indicated below.

       

      /* Formatted on 2014/07/18 11:50:21 AM (QP5 v5.149.1003.31008) */

        SELECT /*+ parallel(8) */ DISTINCT

               D1.c8 AS c1,

               D1.c9 AS c2,

               D1.c10 AS c3,

               NVL (D1.c6, D1.c5) AS c4,

               D1.c1 AS c5,

               D1.c4 AS c6,

               D1.c3 AS c7,

               D1.c2 AS c8,

               NVL (D1.c6, NVL (D1.c5, 0)) - D1.c4 - D1.c3 - D1.c2 - D1.c1 AS c9,

               D1.c7 AS c10

          FROM (  SELECT                                 

                        SUM (T11555.TDS_VAL_EXC_AMOUNT)

                            AS c1,

                         SUM (T11555.TRADING_YTD_BALANCE) AS c2,

                         SUM (T11555.FSAH_VAL_EXC_AMOUNT) AS c3,

                         SUM (T11555.CE_VAL_EXC_AMOUNT) AS c4,

                         SUM (T11555.LIQUIDATION_VALUE) AS c5,

                         SUM (T11555.HOME_LIQUIDATION_VALUE) AS c6,

                         SUM (T11555.TRADING_MANUAL) AS c7,

                         T11471.CALENDAR_DATE AS c8,

                         T10607.LEVEL2_NAME AS c9,

                         T10607.PORTFOLIO_KEY_NAME AS c10

                    FROM XXGL_TIME_DIM_V T11471,

                         XXGL_PORTFOLIO_SEC_DIM_V T10607,

                         XXGL_BS_DASHBOARD_V T11555

                   WHERE (    T10607.PORTFOLIO_CD = T11555.PORTFOLIO

                          AND T10607.LEVEL2_NAME = 'EQUITY TRADING [EQT]'

                          AND T10607.USER_NAME = UPPER ('C_BTR_NIK')

                          AND T10607.HIERARCHY_NAME = 'MANAGEMENT'

                          AND T11471.CALENDAR_DATE = T11555.CALENDAR_DATE

                          AND T11471.CALENDAR_DATE =

                                 TO_DATE ('2014-05-05', 'YYYY-MM-DD')

                          AND T11555.CALENDAR_DATE =

                                 TO_DATE ('2014-05-05', 'YYYY-MM-DD')

                          AND T11555.TRADING_INSTR_IND = 'T'

                          AND T11555.GLOBAL_TYPE_CODE <> 'NOST')

                GROUP BY T10607.LEVEL2_NAME,

                         T10607.PORTFOLIO_KEY_NAME,

                         T11471.CALENDAR_DATE) D1

         WHERE (   CASE WHEN D1.c1 = 0 THEN 'Y' ELSE 'N' END <> 'Y'

                OR CASE WHEN D1.c3 = 0 THEN 'Y' ELSE 'N' END <> 'Y'

                OR CASE WHEN D1.c4 = 0 THEN 'Y' ELSE 'N' END <> 'Y'

                OR CASE

                      WHEN NVL (D1.c6, D1.c5) - D1.c4 - D1.c3 - D1.c2 - D1.c1 < 0

                           OR NVL (D1.c6, D1.c5) - D1.c4 - D1.c3 - D1.c2 - D1.c1 > 0

                      THEN

                         'N'

                      ELSE

                         'Y'

                   END <> 'Y')

      ORDER BY c1, c2, c3

       

      I have tried doing an evaluate in the report as indicated in a few blogs but there is always reference to a physical table.

       

      Any pointers would be amazing.

       

      thanks