Results 1 to 7 of 7
  1. #1
    Join Date
    May 2010
    Posts
    56

    Unanswered: Kindly help with rewriting the foll. query in a better way

    IS there a better way of writing the foll query:
    When I have 12,50,00,000 rows in Fact Table, the query is unable to execute. I use more than 200GB of temporary space. But I still get Temp Tablespace Full Error:

    --Foll WITH Clause is to calculate Sum of Debit-Credit to calculate BLNC acc. to Group By values
    WITH crnt_blnc_set
    AS ( SELECT f.hrarchy_dmn_id,
    f.prduct_dmn_id,
    f.pstng_crncy_id AS crncy_dmn_id,
    f.acnt_dmn_id,
    f.txn_id AS txn_id,
    f.acntng_entry_src AS txn_src,
    f.acntng_entry_typ AS acntng_entry_typ,
    f.val_dt_dmn_id,
    f.revsn_dt,
    SUM (
    DECODE (
    f.pstng_typ,
    'Credit', f.pstng_amnt,
    0))
    - SUM (
    DECODE (
    f.pstng_typ,
    'Debit', f.pstng_amnt,
    0))
    AS blnc
    FROM FactTable f
    GROUP BY f.hrarchy_dmn_id,
    f.prduct_dmn_id,
    f.pstng_crncy_id,
    f.acnt_dmn_id,
    f.txn_id,
    f.acntng_entry_src,
    f.acntng_entry_typ,
    f.val_dt_dmn_id,
    f.revsn_dt),
    --Foll WITH Clause calculates Min and Max Date Ids for the Group By conditions as mentioned
    min_mx_dt
    AS ( SELECT /*+parallel(32)*/
    f.hrarchy_dmn_id AS hrarchy_dmn_id,
    f.prduct_dmn_id AS prduct_dmn_id,
    f.crncy_dmn_id AS crncy_dmn_id,
    f.acnt_dmn_id AS acnt_dmn_id,
    f.txn_id AS txn_id,
    f.txn_src AS txn_src,
    f.acntng_entry_typ AS acntng_entry_typ,
    MIN (f.val_dt_dmn_id) AS min_val_dt,
    GREATEST (MAX (f.val_dt_dmn_id), 2689) AS max_val_dt
    FROM crnt_blnc_set f
    GROUP BY f.hrarchy_dmn_id,
    f.prduct_dmn_id,
    f.crncy_dmn_id,
    f.acnt_dmn_id,
    f.txn_id,
    f.txn_src,
    f.acntng_entry_typ),

    /*Foll WITH Clause has a Cartesian Join on date_dmn to populate missing entries
    This requirement is because if we have a distinct row for
    hrarchy_dmn_id,
    prduct_dmn_id,
    crncy_dmn_id,
    acnt_dmn_id,
    txn_id,
    txn_src,
    acntng_entry_typ Combination and If wehave a missing entry for that in the max date provided then we actually create those missing entries*/

    slctd_rcrds
    AS ( SELECT /*+ ordered use_nl(d) parallel(mx, 4) */
    mx.hrarchy_dmn_id AS hrarchy_dmn_id,
    mx.prduct_dmn_id AS prduct_dmn_id,
    mx.crncy_dmn_id AS crncy_dmn_id,
    mx.acnt_dmn_id AS acnt_dmn_id,
    mx.txn_id AS txn_id,
    mx.txn_src AS txn_src,
    mx.acntng_entry_typ AS acntng_entry_typ,
    d.date_value AS val_dt,
    d.date_dmn_id AS val_dt_dmn_id
    FROM min_mx_dt mx, date_dmn d
    WHERE mx.min_val_dt <= d.date_dmn_id
    AND mx.max_val_dt >= d.date_dmn_id
    ),

    --Foll. WITH clause actually has a outer Join with Firt With Clause to populate the values accordingly

    cmbnd_rcrds
    AS (
    SELECT /*+ USE_HASH(c) */ s.hrarchy_dmn_id AS hrarchy_dmn_id,
    s.prduct_dmn_id AS prduct_dmn_id,
    s.crncy_dmn_id AS crncy_dmn_id,
    s.acnt_dmn_id AS acnt_dmn_id,
    s.txn_id AS txn_id,
    s.txn_src AS txn_src,
    s.acntng_entry_typ AS acntng_entry_typ,
    s.val_dt_dmn_id AS val_dt_dmn_id,
    NVL (c.revsn_dt, s.val_dt) AS revsn_dt,
    NVL (c.blnc, 0) AS blnc,
    0 AS prvs_rcrd_ind
    FROM slctd_rcrds s, crnt_blnc_set c
    WHERE s.hrarchy_dmn_id = c.hrarchy_dmn_id(+)
    AND s.prduct_dmn_id = c.prduct_dmn_id(+)
    AND s.crncy_dmn_id = c.crncy_dmn_id(+)
    AND s.acnt_dmn_id = c.acnt_dmn_id(+)
    AND s.txn_id = c.txn_id(+)
    AND s.txn_src = c.txn_src(+)
    AND s.acntng_entry_typ = c.acntng_entry_typ(+)
    AND s.val_dt_dmn_id = c.val_dt_dmn_id(+))
    Select * from cmbnd_rcrds

  2. #2
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Try to reformulate the query to get a better execution plan
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  3. #3
    Join Date
    May 2010
    Posts
    56
    Thanks for the reply magicwand. After much optimisation I have made this query in this way. I know it's very difficult to imagine without actual db and explain plan but is there anything which anyone finds can be done in this query to make it faster.
    Regards..

  4. #4
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Absolutely impossible without DDL and execution plan ...
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm not sure.
    But, something like the following example may return same results as your query.

    Code:
    WITH
     crnt_blnc_set AS (
    SELECT f.hrarchy_dmn_id
         , f.prduct_dmn_id
         , f.pstng_crncy_id   AS crncy_dmn_id
         , f.acnt_dmn_id
         , f.txn_id AS txn_id
         , f.acntng_entry_src AS txn_src
         , f.acntng_entry_typ AS acntng_entry_typ
         , f.val_dt_dmn_id
         , f.revsn_dt
         , SUM( DECODE( f.pstng_typ
                      , 'Credit' ,   f.pstng_amnt
                      , 'Debit'  , - f.pstng_amnt
                      , 0 )
              ) AS blnc
         , MIN(f.val_dt_dmn_id)
              OVER( PARTITION BY f.hrarchy_dmn_id
                               , f.prduct_dmn_id
                               , f.pstng_crncy_id
                               , f.acnt_dmn_id
                               , f.txn_id
                               , f.acntng_entry_src
                               , f.acntng_entry_typ
                  ) AS min_val_dt
         , GREATEST(
           MAX(f.val_dt_dmn_id)
              OVER( PARTITION BY f.hrarchy_dmn_id
                               , f.prduct_dmn_id
                               , f.pstng_crncy_id
                               , f.acnt_dmn_id
                               , f.txn_id
                               , f.acntng_entry_src
                               , f.acntng_entry_typ
                  )
           , 2689 ) AS max_val_dt
     FROM  FactTable f
     GROUP BY
           f.hrarchy_dmn_id
         , f.prduct_dmn_id
         , f.pstng_crncy_id
         , f.acnt_dmn_id
         , f.txn_id
         , f.acntng_entry_src
         , f.acntng_entry_typ
         , f.val_dt_dmn_id
         , f.revsn_dt
    )
    SELECT
           c.hrarchy_dmn_id   AS hrarchy_dmn_id
         , c.prduct_dmn_id    AS prduct_dmn_id
         , c.crncy_dmn_id     AS crncy_dmn_id
         , c.acnt_dmn_id      AS acnt_dmn_id
         , c.txn_id           AS txn_id
         , c.txn_src          AS txn_src
         , c.acntng_entry_typ AS acntng_entry_typ
         , d.date_dmn_id      AS val_dt_dmn_id
         , CASE d.date_dmn_id
           WHEN c.val_dt_dmn_id THEN
                c.revsn_dt
           ELSE d.date_value
           END AS revsn_dt
         , CASE d.date_dmn_id
           WHEN c.val_dt_dmn_id THEN
                c.blnc
           ELSE 0
           END AS blnc
         , 0   AS prvs_rcrd_ind
     FROM  crnt_blnc_set c
     INNER JOIN
           date_dmn      d
      ON   d.date_dmn_id
           BETWEEN c.min_val_dt
               AND c.max_val_dt
    ;

  6. #6
    Join Date
    May 2010
    Posts
    56
    Thank you Tonkuma and MagicWand.
    The table structure and the Explain Plan are as follows.
    Also my main concern is if I can remove the Outer Join somehow in the final with clause..


    CREATE TABLE FactTable
    (
    PSTNG_FCT_ID NUMBER(15) NOT NULL,
    ACNTNG_ENTRY_ID VARCHAR2(255 BYTE) NOT NULL,
    ACNTNG_ENTRY_SRC VARCHAR2(255 BYTE) NOT NULL,
    TXN_ID VARCHAR2(255 BYTE) NOT NULL,
    REF_ID VARCHAR2(255 BYTE),
    TRD_ID VARCHAR2(255 BYTE),
    ACNTNG_ENTRY_TYP NUMBER(1) NOT NULL,
    HRARCHY_DMN_ID NUMBER(15) NOT NULL,
    PSTNG_CRNCY_ID NUMBER(15) NOT NULL,
    PNL_TYP_DMN_ID NUMBER(15),
    ACNT_DMN_ID NUMBER(15) NOT NULL,
    PRDUCT_DMN_ID NUMBER(15) NOT NULL,
    SCRTY_ID VARCHAR2(255 BYTE),
    GL_ACTVTY_TYP VARCHAR2(255 BYTE),
    ACNTNG_EVENT VARCHAR2(255 BYTE) NOT NULL,
    SRC_SYSTEM_DT DATE NOT NULL,
    VAL_DT_DMN_ID NUMBER(15) NOT NULL,
    REVSN_DT TIMESTAMP(6) NOT NULL,
    TRNST VARCHAR2(255 BYTE),
    PSTNG_TYP VARCHAR2(255 BYTE) NOT NULL,
    PSTNG_AMNT NUMBER(20,5) NOT NULL,
    PSTD_ON_GL NUMBER(1),
    PSTD_DT DATE,
    PSTNG_RMRKS VARCHAR2(255 BYTE),
    CREATED_BY VARCHAR2(50 BYTE) NOT NULL,
    CREATION_DT TIMESTAMP(6),
    MODIFIED_BY VARCHAR2(50 BYTE),
    MODIFICATION_DT TIMESTAMP(6)
    )

    CREATE TABLE DateDimension
    (
    DATE_DMN_ID NUMBER(15) NOT NULL,
    DATE_VALUE DATE NOT NULL,
    DAY_VALUE VARCHAR2(20 BYTE) NOT NULL,
    CLNDR_DAY_NUM NUMBER(15) NOT NULL,
    CLNDR_WEEK_NUM NUMBER(15) NOT NULL,
    CLNDR_MNTH_NUM NUMBER(15) NOT NULL,
    CLNDR_MNTH_VALUE VARCHAR2(20 BYTE) NOT NULL,
    CLNDR_MNTH_STRT_DT DATE NOT NULL,
    CLNDR_MNTH_END_DT DATE NOT NULL,
    CLNDR_YEAR NUMBER(15) NOT NULL,
    FIN_WEEK_NUM NUMBER(15) NOT NULL,
    FIN_WEEK_STRT_DT DATE NOT NULL,
    FIN_WEEK_END_DT DATE NOT NULL,
    FIN_MNTH_NUM NUMBER(15) NOT NULL,
    FIN_QTR_NUM NUMBER(15) NOT NULL,
    FIN_QTR_STRT_DT DATE NOT NULL,
    FIN_QTR_END_DT DATE NOT NULL,
    FIN_YEAR_STRT_DT DATE NOT NULL,
    FIN_YEAR_END_DT DATE NOT NULL,
    FIN_YEAR_STRT NUMBER(15) NOT NULL,
    FIN_YEAR_END NUMBER(15) NOT NULL,
    LEAP_YEAR_IND NUMBER(1) NOT NULL,
    WEEKEND_IND NUMBER(1) NOT NULL,
    HOLIDAY_IND NUMBER(1) NOT NULL,
    WEEK_TO_DT NUMBER(15) NOT NULL,
    MNTH_TO_DT NUMBER(15) NOT NULL,
    YR_TO_DT NUMBER(15) NOT NULL
    )








    Plan
    SELECT STATEMENT ALL_ROWSCost: 4,470,116 Bytes: 468,594,231,749 Cardinality: 662,792,407
    33 TEMP TABLE TRANSFORMATION
    9 PX COORDINATOR
    8 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10001 :Q1001Cost: 15,334 Bytes: 27,416,265,528 Cardinality: 55,837,608
    7 LOAD AS SELECT PARALLEL_COMBINED_WITH_PARENT SYS_TEMP_0FD9D6605_3E71E8EB :Q1001
    6 HASH GROUP BY PARALLEL_COMBINED_WITH_PARENT :Q1001Cost: 15,334 Bytes: 27,416,265,528 Cardinality: 55,837,608
    5 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1001Cost: 15,334 Bytes: 27,416,265,528 Cardinality: 55,837,608
    4 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10000 :Q1000Cost: 15,334 Bytes: 27,416,265,528 Cardinality: 55,837,608
    3 HASH GROUP BY PARALLEL_COMBINED_WITH_PARENT :Q1000Cost: 15,334 Bytes: 27,416,265,528 Cardinality: 55,837,608
    2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1000Cost: 15,276 Bytes: 27,416,265,528 Cardinality: 55,837,608
    1 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT PSTNG_FCT1 :Q1000Cost: 15,276 Bytes: 27,416,265,528 Cardinality: 55,837,608
    32 PX COORDINATOR
    31 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ20003 :Q2003Cost: 4,454,782 Bytes: 468,594,231,749 Cardinality: 662,792,407
    30 HASH JOIN RIGHT OUTER BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q2003Cost: 4,454,782 Bytes: 468,594,231,749 Cardinality: 662,792,407
    14 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q2003Cost: 34,989 Bytes: 20,213,214,096 Cardinality: 55,837,608
    13 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ20001 :Q2001Cost: 34,989 Bytes: 20,213,214,096 Cardinality: 55,837,608
    12 VIEW PARALLEL_COMBINED_WITH_PARENT PERF_ETL. :Q2001Cost: 34,989 Bytes: 20,213,214,096 Cardinality: 55,837,608
    11 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q2001Cost: 34,989 Bytes: 20,213,214,096 Cardinality: 55,837,608
    10 TABLE ACCESS FULL TABLE (TEMP) PARALLEL_COMBINED_WITH_PARENT SYS.SYS_TEMP_0FD9D6605_3E71E8EB :Q2001Cost: 34,989 Bytes: 20,213,214,096 Cardinality: 55,837,608
    29 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q2003Cost: 4,039,288 Bytes: 228,663,380,415 Cardinality: 662,792,407
    28 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ20002 :Q2002Cost: 4,039,288 Bytes: 228,663,380,415 Cardinality: 662,792,407
    27 VIEW PARALLEL_COMBINED_WITH_PARENT PERF_ETL. :Q2002Cost: 4,039,288 Bytes: 228,663,380,415 Cardinality: 662,792,407
    26 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q2002
    24 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q2002Cost: 4,039,288 Bytes: 239,268,058,927 Cardinality: 662,792,407
    22 VIEW PARALLEL_COMBINED_WITH_PARENT PERF_ETL. :Q2002Cost: 160,264 Bytes: 19,487,325,192 Cardinality: 55,837,608
    21 HASH GROUP BY PARALLEL_COMBINED_WITH_PARENT :Q2002Cost: 160,264 Bytes: 18,761,436,288 Cardinality: 55,837,608
    20 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q2002Cost: 160,264 Bytes: 18,761,436,288 Cardinality: 55,837,608
    19 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ20000 :Q2000Cost: 160,264 Bytes: 18,761,436,288 Cardinality: 55,837,608
    18 HASH GROUP BY PARALLEL_COMBINED_WITH_PARENT :Q2000Cost: 160,264 Bytes: 18,761,436,288 Cardinality: 55,837,608
    17 VIEW PARALLEL_COMBINED_WITH_PARENT PERF_ETL. :Q2000Cost: 34,989 Bytes: 18,761,436,288 Cardinality: 55,837,608
    16 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q2000Cost: 34,989 Bytes: 20,213,214,096 Cardinality: 55,837,608
    15 TABLE ACCESS FULL TABLE (TEMP) PARALLEL_COMBINED_WITH_PARENT SYS.SYS_TEMP_0FD9D6605_3E71E8EB :Q2000Cost: 34,989 Bytes: 20,213,214,096 Cardinality: 55,837,608
    23 INDEX RANGE SCAN INDEX (UNIQUE) PARALLEL_COMBINED_WITH_PARENT DATE_DMN_PK :Q2002Cost: 0 Cardinality: 21
    25 TABLE ACCESS BY INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT DATE_DMN :Q2002Cost: 0 Bytes: 144 Cardinality: 12

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Also my main concern is if I can remove the Outer Join somehow in the final with clause..
    My example was not used OUTER JOIN(traditional way or ANSI style).

    Anyway, it would be highly required to publish sample data and expected results from the data,
    for verification of my sample query and further investigation of other possible modifications of your query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •