Teradata high volume table optimization

Question

I have to optimize this table structure. This table has 43 million records and it will grow.

Any suggestions on creating the PPI n all. This table along with 5 similar tables are being used in a Business view.

CREATE MULTISET TABLE GEEDW_Q_PLP_S.CDR_ODS_INTER_DATE_398850F1 ,
    NO FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO
    (
        ID VARCHAR(128)  TITLE 'ID' NOT NULL
       ,ROOT_INTERFACE_NAME VARCHAR(128) TITLE 'ROOT INTERFACE NAME'
       ,INTERFACE_NAME VARCHAR(512) TITLE 'INTERFACE NAME'
       ,ATTRIBUTE_NAME VARCHAR(512) TITLE 'ATTRIBUTE NAME'
       ,ATTRIBUTE_VALUE TIMESTAMP(0) TITLE 'ATTRIBUTE VALUE'
       ,CHECKSUMTEXT VARCHAR(40) TITLE 'CHECKSUMTEXT'
       ,DW_LOAD_DTTM TIMESTAMP(0) TITLE 'DW LOAD DTTM' NOT NULL
       ,DW_CREATED_BY VARCHAR(20) TITLE 'DW CREATED BY' NOT NULL
       ,DW_UPDATED_DTTM TIMESTAMP(0) TITLE 'DW UPDATED DTTM' NOT NULL
       ,DW_UPDATED_BY VARCHAR(20) TITLE 'DW UPDATED BY' NOT NULL)
    PRIMARY INDEX CDR_ODS_INTER_DATE_398850F1_PI ( ID,ROOT_INTERFACE_NAME );

Without any knowledge about PK/FKs and the most common access/join path and data distribution there's no way to tell. Btw, 43 million is not high volume in a warehouse. — dnoeth, Sep 22 '14 at 12:16
Thank you Dnoeth for replying. There is a business view where there is a join on 5 similar table based on ID and Root interface name. I was also looking for column partitioning option but it is disabled in my system. — user3901666, Sep 23 '14 at 06:00
The fastest joins in Teradata are on the PI, so the same PI for all tables is the best you can get (unless there are too many rows per value). Adding partitioning (standard, not columnar) might help to get faster access, but then all tables should share the same partitioning definition and the partitioning columns must be added to the join condition. — dnoeth, Sep 23 '14 at 21:28

Teradata high volume table optimization

0 Answers0