Storing History DAta into tables

Database
Enthusiast

Storing History DAta into tables

I have a requirement to fit in 441GB of history data into a table which makes my table bulky and difficult to query. Just to explain it is a fact table and stores amount fields etc. I have a partition on the business date field.

What will be the best strategy to handle this requirement with respect to performance. . Experts can you please suggest.

7 REPLIES
Enthusiast

Re: Storing History DAta into tables

Hi,

The table is already partitioned so performance should not be an issue.

Can you please paste the DDL so that the table structure can be understood?

Also, Have you considered the stats collection after loading the table?

Khurram
Enthusiast

Re: Storing History DAta into tables

CREATE MULTISET TABLE BA_FCT ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

     BA_GENL_ID INTEGER TITLE 'Account_General_Identifier' NOT NULL,

      CAPTR_DT DATE FORMAT 'YYYY-MM-DD' TITLE 'Capture_Date' NOT NULL COMPRESS (DATE '2016-03-31'),

      TOT_ACNT_BAL_AMT DECIMAL(23,4) TITLE 'Total_Account_Balance_Amount' COMPRESS (0.0000 ,1.0000 ,1.0003  ),

      LDGR_BAL_AMT DECIMAL(23,4) TITLE 'Ledger_Balance_Amount' COMPRESS (0.0000 ,50000.0000 ,30000.0000 ,20000.0000 ,10000.0000 ,0.1800 ,1.0000 ,0.1300 ,0.0800 ,2.0000 ,0.0300  ),

      CLRD_BAL_AMT DECIMAL(23,4) TITLE 'Cleared_Balance_Amount' COMPRESS (0.0000 ,50000.0000 ,30000.0000 ,20000.0000 ,10000.0000 ,0.1800 ,1.0000 ,0.1300 ,0.0800 ,2.0000 ,0.0300 ,20.0000 ,500.0000  ),

      UCLRD_EFECT_LDG_AMT DECIMAL(23,4) TITLE 'Uncleared_Effects_Lodged_Amount' COMPRESS 0.0000 ,

      CR_INT_ACRU_AMT DECIMAL(23,4) TITLE 'Credit_Interest_Accrual_Amount' COMPRESS (0.0000 ,0.0001 ,0.0002 ,0.0003 ,0.0004 ,0.0005 ,0.0006 ,0.0007 ,0.0008 ,0.0012 ,0.0800 ,0.0300 ,-0.0200 ,0.1100 ,0.0600 ,0.2400 ,0.0100 ,0.1900 ,0.1400 ,0.0900 ,0.0400 ,0.2200 ,0.1700 ,0.1200 ,0.0700 ,0.2500 ,0.0200 ,0.2000 ,0.1500 ,0.1000 ,0.0500 ),

      DLY_CR_INT_ACRU_AMT DECIMAL(23,4) TITLE 'Daily_Credit_Interest_Accrual_Amount' COMPRESS (0.0000 ,0.0001 ,0.0002 ,0.0003 ,0.0004 ,0.0005 ,0.0006 ,0.0007 ,0.0008 ,0.0009 ,0.0010 ,0.0011 ,0.0012 ,0.0013  ),

      CR_INT_RATE DECIMAL(27,9) TITLE 'Credit_Interest_Rate' COMPRESS (,

      DLY_CR_INT_AC_UCLRD_ITM_AMT DECIMAL(23,4) TITLE 'Daily_Credit_Interest_Accrual_Uncleared_Items_Amount' COMPRESS 0.0000 ,

      DY_DR_INT_AC_AMT DECIMAL(23,4) TITLE 'Daily_Debit_Interest_Accrual_Amount' COMPRESS 0.0000 ,

      DR_INT_AC_AMT DECIMAL(23,4) TITLE 'Debit_Interest_Accrual_Amount' COMPRESS 0.0000 ,

      DR_INT_RT DECIMAL(27,9) TITLE 'Debit_Interest_Rate' COMPRESS (0.000000000 ,6.400000000 ,9.700000000 ,14.500000000  ),

      BUS_DT DATE FORMAT 'YYYY-MM-DD' TITLE 'Business_Date' NOT NULL,

      START_DATE DATE FORMAT 'YYYY-MM-DD' TITLE 'Start_Date' COMPRESS (DATE '2016-03-31',DATE '2016-04-18'),

      END_DATE DATE FORMAT 'YYYY-MM-DD' TITLE 'End_Date' COMPRESS (DATE '9998-12-31'),

      RECORD_DELETED_FLAG BYTEINT TITLE 'Record_Deleted_Flag' COMPRESS 0 ,

      CTL_ID SMALLINT TITLE 'Control_Id' COMPRESS 26 ,

      PROCESS_NAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Process_Name' ,

      PROCESS_ID INTEGER TITLE 'Process_Id' COMPRESS (34 ,35 ),

      UPDATE_PROCESS_NAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Update_Process_Name' COMPRESS ,

      UPDATE_PROCESS_ID INTEGER TITLE 'Update_Process_Id' COMPRESS ,

      START_TS TIMESTAMP(6) TITLE 'Start_Ts',

      END_TS TIMESTAMP(6) TITLE 'End_Ts',

      MO_CUST_INIT_TXN_CNT INTEGER TITLE 'Monthly_Customer_Initiated_Transactions_Count' COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ),

      ACNT_HLD_CNT INTEGER TITLE 'Account_Holder_Count' COMPRESS (1 ,2 ,4 ),

      GROS_ACNT_PAY_RATE DECIMAL(27,9) TITLE 'Gross_Account_Pay_Rate' ,

      ACNT_HLD_BAL_AMT DECIMAL(23,4) TITLE 'Account_Holder_Portion_Balance_Amount' COMPRESS (0.0000 ,0.0001 ,1.0000 ,0.1300 ,0.0800 ,0.0300 ,0.0050 ,-0.0200 ,20.0000 ,0.1100 ,5.0000 ,0.0600 ),

      C_AMT DECIMAL(23,4) TITLE 'Cool_Off_Amount' COMPRESS 0.0000 ,

      RMAN_IN_COOL_OFF_DY_CNT INTEGER TITLE 'Remaining In Cool Off Day Count' COMPRESS 0 ,

      UAM_CO_CD_UNIT_ID INTEGER TITLE 'Company_Code_Unit_Identifier' COMPRESS (11266 ,11272 ,11280 ,11463 ,11464 ),

      ACNT_CCY_ID INTEGER TITLE 'Account_Currency_Identifier' COMPRESS (1613 ,1616 ,1732 ),

      LOCL_RPT_CCY_ID INTEGER TITLE 'Local_Reporting_Currency_Identifier' COMPRESS (1613 ,1616 ,1731 ))

PRIMARY INDEX ( BA_GENL_ID )

PARTITION BY RANGE_N(BUS_DT  BETWEEN DATE '2014-01-01' AND DATE '2020-03-01' EACH INTERVAL '1' DAY ,

 NO RANGE);

Enthusiast

Re: Storing History DAta into tables

Yes stats are collected. 

Enthusiast

Re: Storing History DAta into tables

The table design seems to be OK, if this BA_GENL_ID is unique you can make the PI as UPI to have more faster access.

Also, you have used lot of compression, it may slow down the access time, is it necessory to compress each and every column? Is there a space constraint?

Which column you are using mostly in access queries and joins?

Khurram
Enthusiast

Re: Storing History DAta into tables

IS there any way to quickly uncompress a table?? Alter table remove Compression type of query . Loading and unloading this huge table is freaking me out.

Teradata Employee

Re: Storing History DAta into tables

Multi-value compression will NOT slow down access time, if anything it will improve performance by requiring fewer IOs. 

Enthusiast

Re: Storing History DAta into tables

Ok. Do you think making the partition as 1 month instead of 1 day will help.