Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    May 2010
    Posts
    56

    Unanswered: Better way of calculating RunningSum,MonthlySumAvg and YearlySumAvg

    I am calculating Running Sum, Monthly_Avg, Yearly_Avg as follows using three Partition BYs for 500+Million of Rows.
    Query never seems to be executing..Is there any better way of writing this..Kindly help..Thanks..

    Select a,b,c,
    SUM (
    blnc)
    OVER (
    PARTITION 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
    ORDER BY d.date_value)
    AS crnt_blnc,
    SUM (
    blnc)
    OVER (
    PARTITION 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,
    d.fin_mnth_num
    || d.fin_year_strt
    || d.fin_year_end
    ORDER BY d.date_value)
    / d.mnth_to_dt
    AS mtd_avg,
    SUM (
    blnc)
    OVER (
    PARTITION 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,
    d.fin_year_strt || d.fin_year_end
    ORDER BY d.date_value)
    / yr_to_dt AS ytd_avg
    FROM FactTable f, DateDimensionTable d
    f.val_dt_dmn_id = d.date_dmn_id)

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ...
    FROM FactTable f, DateDimensionTable d
    f.val_dt_dmn_id = d.date_dmn_id)
    Looking into last part of your query, it might return syntax error.

    You might want to do like...
    ...
    FROM FactTable f, DateDimensionTable d
    WHERE f.val_dt_dmn_id = d.date_dmn_id/*)*/
    Sorry, I wrote too much without understanding your requirements enough.
    Last edited by tonkuma; 03-22-12 at 10:50. Reason: Removed "Even if it might be like this, the query ... in FactTable." and "Consider to specify GROUP BY clause."

  3. #3
    Join Date
    May 2010
    Posts
    56
    Thanks for the reply..It's actually where only..But is there any faster way of doing this.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since we don't have your tables or data, we can not compile, run, test, or improve posted SQL.

    Beside my SQL Formatter, thinks it has syntax error & won't even pretty it up for inspection.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2010
    Posts
    56
    My requirement is that:
    I have two tables FactTable and DateDimensionTable
    Structures are as follows:


    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 DateDimensionTable
    (
    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
    )



    I call combination of hrarchy_dmn_id,
    prduct_dmn_id,
    pstng_crncy_id,
    acnt_dmn_id,
    txn_id,
    acntng_entry_src,
    acntng_entry_typ,
    revsn_dt
    as a UNIQUE TRANSACTION.
    (These values are in FactTable)

    Now DateDimensionTable is like a calendar table which has all the dates populated in calendar format including Holiday or not.

    My financial year is from 1Nov, 2010 to 31 October, 2011.

    SCENARIO- If I have one transaction on 1 Nov, 2010 i.e.


    Now I have to calculate CurrentBalance in the query(For all the UNIQUE TRANSACTIONS and FOR ALL THE DATES till the RunDate):

    Calculated as :
    CURRENTBALANCE for a day- Running Sum of BALANCE for every Unique Transaction for a Day.
    BALANCE is Sum of pstng_amnt in FactTablef.pstng_typ-> Make it Positive for Credit and Negative for Debit).
    For Example- If Balance is 200(200 as Credit) on 1 Nov for a Unique Transaction(Naming U1 now for understanding,Actually it's a combination of values given on top of

    my content). So currentbalance is 200
    -100 Balance on 2 Nov(100 as Debit) for U1. CurrentBalance is 200+(-100)= 100 now.
    Now if there are no more values of U1 on further dates still CurrentBalance as a running sum will be 100.
    So if Run Date is 5 Nov.
    Current Balance will be
    100 on 3 Nov
    100 on 4 Nov
    100 on 5 Nov

    So the Query output should be:
    HRARCHY_DMN_ID,PRDUCT_DMN_ID,CRNCY_DMN_ID,ACNT_DMN _ID,TXN_ID,ACNTNG_ENTRY_SRC,ACNTNG_ENTRY_TYP,VAL_D T_DMN_ID,REVSN_DT,CRNT_BLNC,Mnthly_avg,Yearly_Avg

    6,19,153,58,C20903,K2,0,2192,11/1/2010 12:00:00.000000 AM,200,200/1=200,200/1=200
    6,19,153,58,C20903,K2,0,2193,11/2/2010 12:00:00.000000 AM,100,100/2,100/2
    6,19,153,58,C20903,K2,0,2194,11/3/2010 12:00:00.000000 AM,100,100/3,100/3
    6,19,153,58,C20903,K2,0,2195,11/4/2010 12:00:00.000000 AM,100,100/4,100/4
    6,19,153,58,C20903,K2,0,2196,11/5/2010 12:00:00.000000 AM,100,100/5,100/5

    Similarly Mnthly_avg,Yearly_Avg will be reset after month end and year end..



    Insert foll data to implment and check it:


    INSERT INTO FactTable (PSTNG_FCT_ID,
    ACNTNG_ENTRY_ID,
    ACNTNG_ENTRY_SRC,
    TXN_ID,
    TRD_ID,
    ACNTNG_ENTRY_TYP,
    HRARCHY_DMN_ID,
    PSTNG_CRNCY_ID,
    PNL_TYP_DMN_ID,
    ACNT_DMN_ID,
    PRDUCT_DMN_ID,
    SCRTY_ID,
    GL_ACTVTY_TYP,
    ACNTNG_EVENT,
    SRC_SYSTEM_DT,
    VAL_DT_DMN_ID,
    REVSN_DT,
    TRNST,
    PSTNG_TYP,
    PSTNG_AMNT,
    PSTD_ON_GL,
    PSTD_DT,
    CREATED_BY,
    CREATION_DT,
    MODIFIED_BY,
    MODIFICATION_DT)
    VALUES (
    1360,
    'AE1',
    'K2',
    'C20903',
    'C20903',
    0,
    6,
    153,
    -1,
    58,
    19,
    'SID1',
    'AT1',
    'MONTH END',
    TO_DATE ('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MIS'),
    2192,
    TO_TIMESTAMP ('11/1/2010 12:00:00.000000 AM',
    'fmMMfm/fmDDfm/YYYY fmHH12fm:MIS.FF AM'),
    '48082',
    'Credit',
    200,
    2,
    TO_DATE ('03/13/2012 16:55:25', 'MM/DD/YYYY HH24:MIS'),
    'File InputProcessor',
    SYSDATE,
    'ExportToGL',
    SYSDATE);

    INSERT INTO FactTable (PSTNG_FCT_ID,
    ACNTNG_ENTRY_ID,
    ACNTNG_ENTRY_SRC,
    TXN_ID,
    TRD_ID,
    ACNTNG_ENTRY_TYP,
    HRARCHY_DMN_ID,
    PSTNG_CRNCY_ID,
    PNL_TYP_DMN_ID,
    ACNT_DMN_ID,
    PRDUCT_DMN_ID,
    SCRTY_ID,
    GL_ACTVTY_TYP,
    ACNTNG_EVENT,
    SRC_SYSTEM_DT,
    VAL_DT_DMN_ID,
    REVSN_DT,
    TRNST,
    PSTNG_TYP,
    PSTNG_AMNT,
    PSTD_ON_GL,
    PSTD_DT,
    CREATED_BY,
    CREATION_DT,
    MODIFIED_BY,
    MODIFICATION_DT)
    VALUES (
    1360,
    'AE1',
    'K2',
    'C20903',
    'C20903',
    0,
    6,
    153,
    -1,
    58,
    19,
    'SID1',
    'AT1',
    'MONTH END',
    TO_DATE ('11/02/2010 00:00:00', 'MM/DD/YYYY HH24:MIS'),
    2193,
    TO_TIMESTAMP ('11/2/2010 12:00:00.000000 AM',
    'fmMMfm/fmDDfm/YYYY fmHH12fm:MIS.FF AM'),
    '48082',
    'Debit',
    100,
    2,
    TO_DATE ('03/13/2012 16:55:25', 'MM/DD/YYYY HH24:MIS'),
    'File InputProcessor',
    SYSDATE,
    'ExportToGL',
    SYSDATE);

    COMMIT;

    --more data in next page..

  6. #6
    Join Date
    May 2010
    Posts
    56
    INSERT INTO DateDimensionTable (DATE_DMN_ID,
    DATE_VALUE,
    DAY_VALUE,
    CLNDR_DAY_NUM,
    CLNDR_WEEK_NUM,
    CLNDR_MNTH_NUM,
    CLNDR_MNTH_VALUE,
    CLNDR_MNTH_STRT_DT,
    CLNDR_MNTH_END_DT,
    CLNDR_YEAR,
    FIN_WEEK_NUM,
    FIN_WEEK_STRT_DT,
    FIN_WEEK_END_DT,
    FIN_MNTH_NUM,
    FIN_QTR_NUM,
    FIN_QTR_STRT_DT,
    FIN_QTR_END_DT,
    FIN_YEAR_STRT_DT,
    FIN_YEAR_END_DT,
    FIN_YEAR_STRT,
    FIN_YEAR_END,
    LEAP_YEAR_IND,
    WEEKEND_IND,
    HOLIDAY_IND,
    WEEK_TO_DT,
    MNTH_TO_DT,
    YR_TO_DT)
    VALUES (2195,
    TO_DATE ('11/04/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    'THU',
    4,
    44,
    11,
    'NOV',
    TO_DATE ('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    TO_DATE ('11/30/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    2010,
    2,
    TO_DATE ('11/04/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    TO_DATE ('11/10/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    1,
    1,
    TO_DATE ('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    TO_DATE ('01/31/2011 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    TO_DATE ('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    TO_DATE ('10/31/2011 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    2010,
    2011,
    0,
    0,
    0,
    1,
    4,
    4);

    INSERT INTO DateDimensionTable (DATE_DMN_ID,
    DATE_VALUE,
    DAY_VALUE,
    CLNDR_DAY_NUM,
    CLNDR_WEEK_NUM,
    CLNDR_MNTH_NUM,
    CLNDR_MNTH_VALUE,
    CLNDR_MNTH_STRT_DT,
    CLNDR_MNTH_END_DT,
    CLNDR_YEAR,
    FIN_WEEK_NUM,
    FIN_WEEK_STRT_DT,
    FIN_WEEK_END_DT,
    FIN_MNTH_NUM,
    FIN_QTR_NUM,
    FIN_QTR_STRT_DT,
    FIN_QTR_END_DT,
    FIN_YEAR_STRT_DT,
    FIN_YEAR_END_DT,
    FIN_YEAR_STRT,
    FIN_YEAR_END,
    LEAP_YEAR_IND,
    WEEKEND_IND,
    HOLIDAY_IND,
    WEEK_TO_DT,
    MNTH_TO_DT,
    YR_TO_DT)
    VALUES (2196,
    TO_DATE ('11/05/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    'FRI',
    5,
    45,
    11,
    'NOV',
    TO_DATE ('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    TO_DATE ('11/30/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    2010,
    2,
    TO_DATE ('11/04/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    TO_DATE ('11/10/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    1,
    1,
    TO_DATE ('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    TO_DATE ('01/31/2011 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    TO_DATE ('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    TO_DATE ('10/31/2011 00:00:00', 'MM/DD/YYYY HH24:MI.sS'),
    2010,
    2011,
    0,
    0,
    0,
    2,
    5,
    5);

    COMMIT;

    P.S.- Actual dataset will be huge ( Hundreds of Millions of rows)

    Thanks..

  7. #7
    Join Date
    May 2010
    Posts
    56
    Kindly replace . with : in HH24:MI.sS
    BTW I take this as a privilege to convey to you anacedent.
    It's good to communicate with you..Have learnt a lot from your replies..
    And all 3 of your signature lines are fantastic..I have always thought about them..Especially the first one..

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am calculating Running Sum, Monthly_Avg, Yearly_Avg as follows using three Partition BYs for 500+Million of Rows.
    >Query never seems to be executing..Is there any better way of writing this..Kindly help..Thanks..

    >Select a,b,c,

    posted SQL appears to be incomplete & in any case invalid syntax.
    to what do "a,b,c" refer?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    May 2010
    Posts
    56
    Thanks for the reply anacedent.

    I am using the foll. query

    WITH Clause1
    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,
    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),
    Date_Min_Mx
    AS (SELECT 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,
    f.blnc AS blnc,
    d.date_value AS min_val_dt,
    GREATEST (
    MAX (
    d.date_value)
    OVER (
    PARTITION 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),
    '5-Nov-2010')
    AS max_val_dt
    FROM Clause1 f, datedimensiontable d
    WHERE d.holiday_ind = 0 AND f.val_dt_dmn_id = d.date_dmn_id),
    Recrds_Selection
    AS ( SELECT 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_dmn_id AS val_dt_dmn_id,
    -- mx.blnc as blnc
    SUM (
    CASE d.date_value
    WHEN mx.min_val_dt THEN mx.blnc
    ELSE 0
    END)
    AS blnc
    FROM Date_Min_Mx mx, datedimensiontable d
    WHERE mx.min_val_dt <= d.date_value
    AND mx.max_val_dt >= d.date_value
    GROUP BY mx.hrarchy_dmn_id,
    mx.prduct_dmn_id,
    mx.crncy_dmn_id,
    mx.acnt_dmn_id,
    mx.txn_id,
    mx.txn_src,
    mx.acntng_entry_typ,
    d.date_dmn_id),
    fnl_set
    AS (SELECT 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,
    f.val_dt_dmn_id,
    SUM (
    blnc)
    OVER (
    PARTITION 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
    ORDER BY d.date_value)
    AS crnt_blnc,
    SUM (
    blnc)
    OVER (
    PARTITION 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,
    d.fin_mnth_num
    || d.fin_year_strt
    || d.fin_year_end
    ORDER BY d.date_value)
    / d.mnth_to_dt
    AS mtd_avg,
    SUM (
    blnc)
    OVER (
    PARTITION 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,
    d.fin_year_strt || d.fin_year_end
    ORDER BY d.date_value)
    / yr_to_dt
    AS ytd_avg
    FROM Recrds_Selection f, datedimensiontable d
    WHERE d.holidaY_ind = 0 AND f.val_dt_dmn_id = d.date_dmn_id)
    SELECT *
    FROM fnl_set

    Explain plan is :
    Plan
    SELECT STATEMENT ALL_ROWSCost: 292,888,269 Bytes: 1,536,289,072,125 Cardinality: 4,096,770,859
    16 VIEW PERF_ETL. Cost: 292,888,269 Bytes: 1,536,289,072,125 Cardinality: 4,096,770,859
    15 WINDOW SORT Cost: 292,888,269 Bytes: 426,064,169,336 Cardinality: 4,096,770,859
    14 WINDOW SORT Cost: 292,888,269 Bytes: 426,064,169,336 Cardinality: 4,096,770,859
    13 WINDOW BUFFER Cost: 292,888,269 Bytes: 426,064,169,336 Cardinality: 4,096,770,859
    12 SORT GROUP BY Cost: 292,888,269 Bytes: 426,064,169,336 Cardinality: 4,096,770,859
    11 MERGE JOIN Cost: 2,222,014 Bytes: 426,064,169,336 Cardinality: 4,096,770,859
    2 SORT JOIN Cost: 30 Bytes: 151,920 Cardinality: 3,376
    1 TABLE ACCESS FULL TABLE DateDimensionTable Cost: 29 Bytes: 151,920 Cardinality: 3,376
    10 FILTER
    9 SORT JOIN Cost: 1,965,158 Bytes: 2,863,253,362 Cardinality: 48,529,718
    8 VIEW PERF_ETL. Cost: 1,276,079 Bytes: 2,863,253,362 Cardinality: 48,529,718
    7 WINDOW BUFFER Cost: 1,276,079 Bytes: 3,542,669,414 Cardinality: 48,529,718
    6 SORT GROUP BY Cost: 1,276,079 Bytes: 3,542,669,414 Cardinality: 48,529,718
    5 HASH JOIN Cost: 440,373 Bytes: 3,542,669,414 Cardinality: 48,529,718
    3 TABLE ACCESS FULL TABLE DateDimensionTable Cost: 29 Bytes: 91,152 Cardinality: 3,376
    4 TABLE ACCESS FULL TABLE FactTable Cost: 440,191 Bytes: 2,232,367,028 Cardinality: 48,529,718

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    WITH clause1 
         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, 
                    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), 
         date_min_mx 
         AS (SELECT 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 
                    , 
                    f.blnc                                       AS 
                    blnc, 
                    d.date_value                                 AS min_val_dt, 
                    Greatest (MAX (d.date_value) over ( PARTITION 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), '5-Nov-2010') AS max_val_dt 
             FROM   clause1 f, 
                    datedimensiontable d 
             WHERE  d.holiday_ind = 0 
                    AND f.val_dt_dmn_id = d.date_dmn_id), 
         recrds_selection 
         AS (SELECT 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_dmn_id       AS val_dt_dmn_id, 
                    -- mx.blnc as blnc 
                    SUM (CASE d.date_value 
                           WHEN mx.min_val_dt THEN mx.blnc 
                           ELSE 0 
                         END)           AS blnc 
             FROM   date_min_mx mx, 
                    datedimensiontable d 
             WHERE  mx.min_val_dt <= d.date_value 
                    AND mx.max_val_dt >= d.date_value 
             GROUP  BY mx.hrarchy_dmn_id, 
                       mx.prduct_dmn_id, 
                       mx.crncy_dmn_id, 
                       mx.acnt_dmn_id, 
                       mx.txn_id, 
                       mx.txn_src, 
                       mx.acntng_entry_typ, 
                       d.date_dmn_id), 
         fnl_set 
         AS (SELECT 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, 
                    f.val_dt_dmn_id, 
                    SUM (blnc) over ( PARTITION 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 
                       ORDER BY 
                    d.date_value) 
                       AS crnt_blnc, 
                    SUM (blnc) over ( PARTITION 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, 
                    d.fin_mnth_num|| 
                    d.fin_year_strt|| d.fin_year_end ORDER BY d.date_value) / 
                    d.mnth_to_dt 
                       AS 
                    mtd_avg, 
                    SUM (blnc) over ( PARTITION 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, 
                    d.fin_year_strt|| 
                    d.fin_year_end ORDER BY d.date_value) / yr_to_dt 
                       AS ytd_avg 
             FROM   recrds_selection f, 
                    datedimensiontable d 
             WHERE  d.holiday_ind = 0 
                    AND f.val_dt_dmn_id = d.date_dmn_id) 
    SELECT * 
    FROM   fnl_set;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use <code_tags> is explained in #1 STICKY post (URL below)
    http://www.dbforums.com/oracle/10316...s-posters.html

    & report formatted EXPLAIN PLAN using <code_tags>
    Why so many Full Table Scans?
    Are all columns in the WHERE clause indexed?
    Are Statistics current for all tables & indexes?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    May 2010
    Posts
    56
    Sorry about code_tags..Will work on that..

    -All the columns in Where Clause are indexed except datedimensiontable.holiday_ind.
    Created that now.
    -Statistics are upto date.
    -Full Table Scans may be due to missing index.
    But still no change..Full Table Scans only.

  13. #13
    Join Date
    May 2010
    Posts
    56
    I use foll method to calculate stats on table and indexes attached:
    BEGIN DBMS_STATS.GATHER_TABLE_STATS('SCHEMANAME','DATEDI MENSIONTABLE', Estimate_Percent=>dbms_stats.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>TRUE, Degree=>dbms_stats.AUTO_DEGREE); END;

    Although gathered Schema Stats some hours before.

    Also first With Clause i.e. clause1, I have CTASed(Created a new table from this with clause).
    Using this new table in subsequent With Clause.
    So I don't think Indexes on FactTable are going to help me.

  14. #14
    Join Date
    May 2010
    Posts
    56
    There has to be a better way of writing this query but I am missing it..

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >There has to be a better way of writing this query but I am missing it..
    Nothing is impossible for the person who does not have to do it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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