Union ALL View Folding not working after adding Soft RIs

Database

Union ALL View Folding not working after adding Soft RIs

UNION ALL View folding is on of the optimizations which TD Query Rewrite applies to the Query. What I understand about that is if we have UNION ALL view say joining two CLAIM tables and then we define SOft RI between a dimension say PRODUCT and the individual tables in the View then when a query is executed against the view joined to the dimension the Joins from the dimension are pushed down down to the individual tables inside the view rather than materializing the view first and then joining the Dimension with the resuting spool.

I have tried to come up with a case study for the same but its not doing as expected. Could you please suggest what am I doing wrong here or I am missing something.

Given below are the DDLs and the query. You ca just try to run these DDLS and see the explain.

CREATE TABLE MEDICAL_CLAIM(

    CL_ID               DECIMAL(38, 0)     NOT NULL TITLE 'Claim Id',

    MB_ID                 DECIMAL(38, 0)     NOT NULL TITLE 'Member Id',

    SVC_PV_ID      DECIMAL(38, 0)     NOT NULL TITLE 'Servicing Provider Id',

    REF_PV_ID      DECIMAL(38, 0)     NOT NULL TITLE 'Referring  Provider Id',

    PCP_ID      DECIMAL(38, 0)     NOT NULL TITLE 'Primary Care Physician Id',

    DIAG1_CD    CHAR(10)     NOT NULL TITLE 'Primary diagnosis Code',

    DIAG2_CD    CHAR(10)     NOT NULL TITLE 'Secondary diagnosis Code',   

    PROC1_CD    CHAR(10)     NOT NULL TITLE 'Primary Procedure Code',       

    PROC2_CD    CHAR(10)     NOT NULL TITLE 'Secondary Procedure Code',           

    CL_SVC_DT            DATE               NOT NULL TITLE 'Claim Service Date',   

    CURR_CD           CHAR(3)            NOT NULL TITLE 'Currency Code',

    BILL_AMT               DECIMAL(38, 10)    NOT NULL TITLE 'Billed Amount',

    COPAY_AMT               DECIMAL(38, 10)    NOT NULL TITLE 'Co-Payment Amount',

    COINS_AMT               DECIMAL(38, 10)    NOT NULL TITLE 'Co-Insurance Amount',   

    DED_AMT               DECIMAL(38, 10)    NOT NULL TITLE 'Deductible Amount',       

    EDW_CREAT_DT          DATE      NOT NULL TITLE 'EDW Created Date',

    EDW_UPDT_DT          DATE      NOT NULL TITLE 'EDW Updated Date'

)

PRIMARY INDEX (CL_ID)

PARTITION BY RANGE_N(CL_SVC_DT BETWEEN DATE '2008-01-01' AND DATE '2010-12-31' EACH INTERVAL '1' DAY ,

 NO RANGE)

;

CREATE TABLE BEH_CLAIM(

    BEH_CL_ID               DECIMAL(38, 0)     NOT NULL TITLE 'Claim Id',

    MB_ID                 DECIMAL(38, 0)     NOT NULL TITLE 'Member Id',

    SVC_PV_ID      DECIMAL(38, 0)     NOT NULL TITLE 'Servicing Provider Id',

    REF_PV_ID      DECIMAL(38, 0)     NOT NULL TITLE 'Referring  Provider Id',

    DIAG1_CD    CHAR(10)     NOT NULL TITLE 'Primary diagnosis Code',

    DIAG2_CD    CHAR(10)     NOT NULL TITLE 'Secondary diagnosis Code',   

    PROC1_CD    CHAR(10)     NOT NULL TITLE 'Primary Procedure Code',       

    PROC2_CD    CHAR(10)     NOT NULL TITLE 'Secondary Procedure Code',           

    CL_SVC_DT            DATE               NOT NULL TITLE 'Claim Service Date',   

    CURR_CD           CHAR(3)            NOT NULL TITLE 'Currency Code',

    BILL_AMT               DECIMAL(38, 10)    NOT NULL TITLE 'Billed Amount',

    COPAY_AMT               DECIMAL(38, 10)    NOT NULL TITLE 'Co-Payment Amount',

    COINS_AMT               DECIMAL(38, 10)    NOT NULL TITLE 'Co-Insurance Amount',   

    DED_AMT               DECIMAL(38, 10)    NOT NULL TITLE 'Deductible Amount',       

    EDW_CREAT_DT          DATE      NOT NULL TITLE 'EDW Created Date',

    EDW_UPDT_DT          DATE      NOT NULL TITLE 'EDW Updated Date'

)

PRIMARY INDEX (BEH_CL_ID)

PARTITION BY RANGE_N(CL_SVC_DT BETWEEN DATE '2008-01-01' AND DATE '2010-12-31' EACH INTERVAL '1' DAY ,

 NO RANGE)

;

CREATE TABLE MEMBER(

    MB_ID                DECIMAL(38, 0)    NOT NULL TITLE 'Member Id' ,

    MB_FRST_NM             VARCHAR(50) NOT NULL TITLE 'Member First Name',

    MB_LAST_NM             VARCHAR(50) NOT NULL  TITLE 'Member Last Name',

    AGE          SMALLINT  NOT NULL,

    DOB            DATE               NOT NULL TITLE 'Date Of Birth',       

    GNDR_CD  CHAR(1)       NOT NULL TITLE 'Gender Code',         

    PCP_ID      DECIMAL(38, 0)     NOT NULL TITLE 'Primary Care Physician Id',   

    EFF_BEG_DT            DATE               NOT NULL TITLE 'Coverage Begin Date',           

    EFF_END_DT            DATE               NOT NULL TITLE 'Coverage End Date',               

    EDW_CREAT_DT          DATE      NOT NULL TITLE 'EDW Created Date',

    EDW_UPDT_DT          DATE      NOT NULL TITLE 'EDW Updated Date'

)

UNIQUE PRIMARY INDEX (MB_ID)

;

REPLACE VIEW CLAIM AS

LOCK ROW FOR ACCESS

SELECT

    CL_ID,

    MB_ID,

    SVC_PV_ID,

    REF_PV_ID,

    DIAG1_CD,

    DIAG2_CD,

    PROC1_CD,

    PROC2_CD,

    CL_SVC_DT,

    CURR_CD,

    BILL_AMT,

    COPAY_AMT,

    COINS_AMT,

    DED_AMT,

    EDW_CREAT_DT,

    EDW_UPDT_DT

FROM

MEDICAL_CLAIM

UNION ALL

SELECT

    BEH_CL_ID,

    MB_ID,

    SVC_PV_ID,

    REF_PV_ID,

    DIAG1_CD,

    DIAG2_CD,

    PROC1_CD,

    PROC2_CD,

    CL_SVC_DT,

    CURR_CD,

    BILL_AMT,

    COPAY_AMT,

    COINS_AMT,

    DED_AMT,

    EDW_CREAT_DT,

    EDW_UPDT_DT

FROM

BEH_CLAIM ;

ALTER TABLE MEDICAL_CLAIM ADD

FOREIGN KEY ( MB_ID) REFERENCES WITH NO CHECK OPTION MEMBER( MB_ID );

ALTER TABLE BEH_CLAIM ADD

FOREIGN KEY ( MB_ID ) REFERENCES WITH NO CHECK OPTION MEMBER( MB_ID );

SELECT MB.MB_ID, SUM(BILL_AMT)

FROM

CLAIM CL

INNER JOIN MEMBER MB

ON CL.MB_ID = MB.MB_ID

GROUP BY MB.MB_ID;

5 REPLIES
N/A

Re: Union ALL View Folding not working after adding Soft RIs

This optimization is cost based, so you should test using populated tables and some WHERE conditions.

If the optimizer uses your expected approach it would result in two joins plus aggregates and a final aggregate to combine those intermediate SUMs.

Btw, i don't think you need the FK definition as MB_ID is already unique.#

Dieter

Re: Union ALL View Folding not working after adding Soft RIs

Thanks for the revert Dieter. Actually I got through one of the articles from S. Molini where he has mentioned a sample case study to demonstrate this UNION ALL view folding and he did use the Where clauses in the query.

But he manipualted the COLLECT STATS by putting artificial stats in the DB like below

COLLECT STATISTICS SRC1_TRANSACTION COLUMN(trans_id)

VALUES (0,0,1,1,1,37000000,37000000);

COLLECT STATISTICS SRC1_TRANSACTION COLUMN(invoice_date)

VALUES (0,0,DATE'2008-01-01',DATE'2009-09-21',13500,3196,37000000);

Could you please clarify what do we understand by 7 of the fields mentioned above in the Values (). That can really help me to put some sample stats for empty tables and then see how TD works for different demographics.

I was trying to work up a case study on my own case study so that I can actually use that in my projects.

As you pointed out that MB_ID is unique but that need to be defined as unique (UPI or USI) in the parent table to be defined as a FK with no check option in the other table right ??

Any insight will realy be very helpful.

Thanks

Manik

N/A

Re: Union ALL View Folding not working after adding Soft RIs

Hi Manik,

this COLLECT STATS VALUES syntax is undocumented, when you use the System Emulation Tool to move/fake stats it's actually submitting it this way (and the COLLECT STATS statement in the output file has some comments in it declaring the meaning of each parameter)

In TD13 you better use the COLLECT STATS FROM syntax to copy existing stats from another table.

And since TD13 you can also define a soft FK referencing any non-unique column. It's a dummy FK on a dummy PK :-)

Dieter

Re: Union ALL View Folding not working after adding Soft RIs

The last statement was a great information to me Dieter !!

But just if it does not wastes much of ur time could u please elaborate the comma separated values in this un documented form of Collect Stats only if it isn't of much inconvenience to you :)

That will really help largely for me since working in a banking project its really required for me to do run the tests on Dev since moving data from Prod to dev is not allowed.

Thanks a lot.

Manik

N/A

Re: Union ALL View Folding not working after adding Soft RIs

Hi Manik,

this is an excerpt from a TSET export ".col" file:

COLLECT STATISTICS "AU"."CUSTOMER"  COLUMN "last_name"

 VALUES

(

  /* TimeStamp      */  TIMESTAMP '2011-11-16 12:52:09' ,

  /* Version        */  4,

  /* Sampled NO */   /* SampleSize 0 */

  /* Col  0,( last_name):  1, CV, 20, 20,  0, X(20) */

  /* Interval[0]     */

  /* NumNulls        */  0,

  /* NumIntervals    */  200,

  /* NumAllNulls     */  0,

  /* AvgAmpRPV       */  30.046498,

  /* OneAMPSampleEst       */  8971646,

  /* AllAMPSampleEst       */  8972733,

  /* NumAmps        */  2,

  /* Table Summary   */

  /* Min. Value      */  'AA',

  /* Mode Value      */  'SMITH',

  /* Mode Freq.      */  88085,

  /* Total Values    */  151671,

  /* Total Rows      */  8975009,

  /* Interval[1] */

  /* Max. Value     */  'BROWN',

  /* Mode Value      */  'ANDERSON',

  /* Mode Freq.      */  28262,

  /* # Other Val.   */  -2,

  /* # Other Rows   */  51162,

  /* Interval[2] */  'GARCIA',  'DAVIS',  39751,  -2,  31817,

  /* Interval[3] */  'JACKSON',  'HERNANDEZ',  26185,  -2,  24693,

  /* Interval[4] */  'JONES',  'JOHNSON',  68844,  -2,  50517,

  /* Interval[5] */  'LOPEZ',  'LEE',  22459,  -2,  23040,

  /* Interval[6] */  'MARTINEZ',  'MARTIN',  24937,  -2,  28732,

  /* Interval[7] */  'MOORE',  'MILLER',  41807,  -2,  25900,

  /* Interval[8] */  'SMITH',  'RODRIGUEZ',  29813,  -2,  88085,

  /* Interval[9] */  'THOMAS',  'TAYLOR',  26704,  -2,  26345,

  /* Interval[10] */  'WHITE',  'THOMPSON',  23887,  -2,  23707,

  /* Interval[11] */  'WILSON',  'WILLIAMS',  56865,  -2,  29027,

  /* Interval[12] */  'AGRAZ',  'ADAMS',  15313,  1038,  38847,

  /* Interval[13] */  'ALDRED',  'AGUILAR',  4686,  652,  28984,

  /* Interval[14] */  'ALOMARI',  'ALLEN',  17177,  526,  29008,

  /* Interval[15] */  'ALVARA',  'ALSTON',  1041,  217,  6026,

  /* Interval[16] */  'ANGERHOFER',  'ALVAREZ',  6258,  732,  35273,

It's starting with interavl zero (global info) followed by interval 1 to n with details.

For DATE columns you have to supply the internally stored integer value, e.g. 1120108 for '2012-01-08'

Dieter