Performance Tuning....

Database
l_k
N/A

Performance Tuning....

Hi,

I have the below query which is running more than 1 hour and crunching the resources with high cpu skew.

Query:

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?

Thanks..
5 REPLIES

Re: Performance Tuning....

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.

Thx!
l_k
N/A

Re: Performance Tuning....

DDL is given below:

CREATE

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..

Thanks..
l_k
N/A

Re: Performance Tuning....

Anyone can help me on this?
N/A

Re: Performance Tuning....

I assume Account_Num/Account_Modifier_Num is actually Acct_Num/Acct_Modifier_Num

Time_Period_Cd is defined as NOT NULL in your DDL, but you filter for
"and Time_period is null;"

PPI on account_summary_dt seems to be the best for your query, but there will be other queries, too.

And
"where acct_summary_dt IN
(select calendar_date
from syscalendar where calendar_date between date-90 and date)"
is the same as
"where acct_summary_dt calendar_date between date-90 and date"

Dieter
l_k
N/A

Re: Performance Tuning....

Hi Dieter,
I mentioned incorrectly about the Time_period column,it is Time_period='DY'..
Thank you so much for your reply..