I have the below query which is running more than 1 hour and crunching the resources with high cpu skew.
SEL acct_num, acct_modified_num, acct_summary_dt, acct_end_ledg_dt FROM dp_edw.Acct_Bal_DD where acct_summary_dt IN (select calendar_date from syscalendar where calendar_date between date-90 and date) and type_in IN ('ledger','os_lt') and Time_period is null;
In the above query acct_num and cct_modified_num is the composite PI..
I am expecting a suggestion from you for the column acct_summary_dt for the introduction of INDEX column.. What type of index i have to introduce PPI or Value order NUSI or PI and which will be efficient?
LK, Before anybody can provide you with a constructive suggestion regarding the creation of an useful secondary index, it would be most helpful if you could post the table ddl and a bit about the data distribution.
SET TABLE DP_EDW.ACCT_BAL_DD ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT (
Account_Summary_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL, Time_Period_Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, Balance_Category_Type_Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, Acct_Num VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, Acct_Modifier_Num CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, Acct_End_Ldgr_Bal_Categ_Amt DECIMAL(18,2) COMPRESS (0.00 ,30.00 ) )
PRIMARY INDEX ( Account_Num,Account_Modifier_Num) ;
Data distribution is based on the acct_no and acct_modifier_no.. We are using only last 90 days of data from the table for the column account_summary_dt mentioned in the above SELECT query..So,we need a good choice of INDEX for accessing 90 days is enough..I believe PPI is overhead for this type of usage.. ( and also here time_period is null,for this what i have to do to access only null part) Expecting a quick reply from you..