Archives of the TeradataForum

Message Posted: Tue, 22 Dec 2009 @ 19:57:53 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Problem using join index.

From:		Baig, Mohammad

Hi Khan,

Yes I can run the query directly with case statements using tables.. and the optimizer is using JI, But when I put the same case in view.. and if I use view in the query the optimizer is not using JI.

Could you please let me know how to collect stats on the index of the JI. Sorry, I am not aware of it.

Hi Victor,

BTW, the view was not mentioned in the original posting, so mentioning it was a useful clarification. DDL would be even more useful to avoid confusion.

I did not mention about the view in my original posting coz I did not realize at the point of time that the problem was with view not the case..

Ok, here we go these are the small versions of them please help me out resolving this.

     -- TABLES
     CREATE SET TABLE LOAD_WORK.F_CORE_LOANS ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           CCCP_KEY DECIMAL(18,0) TITLE 'cccp_key' NOT NULL,
           PROC_YYMM_DT INTEGER TITLE 'proc_yymm_dt',
           RECORD_TYPE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'record_type'
              DEFAULT ' ' COMPRESS (' ','A'),
           ACC_APPSYS_ID CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC
              TITLE 'acc_appsys_id' NOT NULL COMPRESS '   ',
           ACC_CO_NO SMALLINT TITLE 'acc_co_no' DEFAULT 0  COMPRESS 0 ,
           ACCNO CHAR(21) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'accno'
              NOT NULL COMPRESS '000000000000000000000',
           PRIVACY_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'privacy_cd'
              NOT NULL COMPRESS ' '
           )
     PRIMARY INDEX XIE1f_core_loans ( CCCP_KEY ) PARTITION BY RANGE_N(PROC_YYMM_DT  BETWEEN
     10001  AND 10012  EACH 1 ,
     10101  AND 10112  EACH 1 ,
     10201  AND 10212  EACH 1 ,
     10301  AND 10312  EACH 1 ,
     10401  AND 10412  EACH 1 ,
     10501  AND 10512  EACH 1 ,
     10601  AND 10612  EACH 1 ,
     10701  AND 10712  EACH 1 ,
     10801  AND 10812  EACH 1 ,
     10901  AND 10912  EACH 1 ,
     11001  AND 11012  EACH 1 ,
     11101  AND 11112  EACH 1 ,
     11201  AND 11212  EACH 1 ,
     11301  AND 11312  EACH 1 ,
     11401  AND 11412  EACH 1 );



     CREATE SET TABLE LOAD_WORK.F_CORE_APPS_KEY ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           CCCP_KEY DECIMAL(18,0) TITLE 'cccp_key' NOT NULL,
           CCCP_KEY_SEQ_NO DECIMAL(3,0) TITLE 'cccp_key_seq_no' DEFAULT 0.
              COMPRESS (0. ,1. ,2. ,3. ,4. ,5. ,6. ,7. ,8. ,9. ),
           APP_ACC_APPSYS_ID CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC
              TITLE 'app_acc_appsys_id' DEFAULT '   ' COMPRESS '   ',
           APP_ACCNO CHAR(21) CHARACTER SET LATIN NOT CASESPECIFIC
              TITLE 'app_accno' DEFAULT '                     '
              COMPRESS '000000000000000000000',
           PRIVACY_CD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC
              TITLE 'privacy_cd' DEFAULT ' ' COMPRESS ' '
          )
     UNIQUE PRIMARY INDEX XAK1f_core_apps_key ( CCCP_KEY );

     -- Views

     REPLACE VIEW RTL1LOAD.AWS_CORE_LOANS
        (CCCP_KEY,
        CCCP_KEY_SEQ_NO,
        SEQUENCE_NO,
        PROC_YYMM_DT,
        RECORD_TYPE,
        ACC_APPSYS_ID,
        ACC_CO_NO,
        ACCNO
        ) AS  LOCKING ROW
        FOR ACCESS (
        SELECT
        CCCP_KEY                      ,
        1 AS CCCP_KEY_SEQ_NO,
        1 AS SEQUENCE_NO,
        PROC_YYMM_DT                  ,
        RECORD_TYPE                   ,
        ACC_APPSYS_ID                 ,
        ACC_CO_NO                     ,
        CASE
        WHEN PRIVACY_CD=' ' THEN ACCNO
        ELSE 'PRIVATE'
        END
        FROM ECR_RTL_FCT01.F_CORE_LOANS );

     REPLACE VIEW RTL1LOAD.AWS_CORE_APPS_KEY(
      CCCP_KEY                      ,
      CCCP_KEY_SEQ_NO               ,
      APP_ACC_APPSYS_ID             ,
      APP_ACCNO
       ) AS  LOCKING ROW
       FOR ACCESS (
      SELECT
       CCCP_KEY                      ,
       1 AS CCCP_KEY_SEQ_NO,
        APP_ACC_APPSYS_ID             ,
      CASE
        WHEN PRIVACY_CD = ' ' THEN APP_ACCNO
        ELSE 'PRIVATE'
      END
       FROM ECR_RTL_DIM03.F_CORE_APPS_KEY );

     -- Join Index

     CREATE JOIN INDEX LOAD_WORK.LOANS_RETAIL_EXPSR AS SELECT
        CL.CCCP_KEY
       ,CL.PROC_YYMM_DT
       ,CL.RECORD_TYPE
       ,CL.ACC_APPSYS_ID
       ,CL.ACC_CO_NO
       ,CL.ACCNO
       ,CL.PRIVACY_CD
       ,AK.CCCP_KEY AS AK_CCCP_KEY
       ,AK.APP_ACC_APPSYS_ID
       ,AK.APP_ACCNO
       ,AK.PRIVACY_CD AS AK_PRIVACY_CD
     FROM
     LOAD_WORK.F_CORE_LOANS CL
      LEFT OUTER JOIN
     LOAD_WORK.F_CORE_APPS_KEY AK
      ON CL.CCCP_KEY = AK.CCCP_KEY
      PRIMARY INDEX (CCCP_KEY)
     PARTITION BY RANGE_N(PROC_YYMM_DT  BETWEEN 10001  AND 10012  EACH 1 ,
     10101  AND 10112  EACH 1 ,
     10201  AND 10212  EACH 1 ,
     10301  AND 10312  EACH 1 ,
     10401  AND 10412  EACH 1 ,
     10501  AND 10512  EACH 1 ,
     10601  AND 10612  EACH 1 ,
     10701  AND 10712  EACH 1 ,
     10801  AND 10812  EACH 1 ,
     10901  AND 10912  EACH 1 ,
     11001  AND 11012  EACH 1 ,
     11101  AND 11112  EACH 1 ,
     11201  AND 11212  EACH 1 ,
     11301  AND 11312  EACH 1 ,
     11401  AND 11412  EACH 1 );

     --USING JI
     SELECT
        CL.CCCP_KEY
       ,CL.PROC_YYMM_DT
       ,CL.RECORD_TYPE
       ,CL.ACC_APPSYS_ID
       ,CL.ACC_CO_NO
       , CASE
        WHEN CL.PRIVACY_CD=' ' THEN ACCNO
        ELSE 'PRIVATE'
        END
       ,AK.CCCP_KEY AS AK_CCCP_KEY
       ,AK.APP_ACC_APPSYS_ID
       ,CASE
        WHEN AK.PRIVACY_CD = ' ' THEN AK.APP_ACCNO
        ELSE 'PRIVATE'
        END
     FROM
     LOAD_WORK.F_CORE_LOANS CL
      LEFT OUTER JOIN
     LOAD_WORK.F_CORE_APPS_KEY AK
      ON CL.CCCP_KEY = AK.CCCP_KEY

     -- NOT USING JI
     SELECT
        CL.CCCP_KEY
       ,CL.PROC_YYMM_DT
       ,CL.RECORD_TYPE
       ,CL.ACC_APPSYS_ID
       ,CL.ACC_CO_NO
       ,CL.ACCNO
       ,AK.CCCP_KEY AS AK_CCCP_KEY
       ,AK.APP_ACC_APPSYS_ID
       ,AK.APP_ACCNO
     FROM
     RTL1LOAD.AWS_CORE_LOANS CL
      LEFT OUTER JOIN
     RTL1LOAD.AWS_CORE_APPS_KEY AK
      ON CL.CCCP_KEY = AK.CCCP_KEY

Thanks & Regards

Mohammad Baig


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference