Greetings Douglas,

Thanks so much for your time and efforts to try and assist me on this. When I say I inherited this, I should have said that the guy that did the bulk of the database work has been laid off due to the economic down turn and I'm the only one left to work on it. My normal development activities are in Delphi applications, so if I seem like somewhat of a newie, I am so please bare with me. That said, let me also say that I'm not completely green as I have done quite a bit of database work like designing and creating tables, triggers, and several hundred stored procedures, and then I would send the metadata to the database developer and most of the time he would apply it as is. When I say I'm green it is because I still am not sure on some things like when it is appropriate to add an index. I'll try to answer each question to the best of my understanding and ability, so if I did not answer correctly or give enough detail, please let me know.

Douglas: This is more of a guess. Without more information about your data and
metadata it's hard to come up with a solution.
I can provide any metadata you need so please do not hesitate to ask.

Douglas: Is there an index in T.TRAN_ID?
TE_TRAN Stores that available transactions that a user can add to a case (Wrapper for up to 8 debts). TRAN_ID is the primary key for this table. There are no other indicies for this table. Now my green gills show through... Should I also add TRAN_ID as an index to this table, as my thinking is if it is a primary key, it does not need to be in the indicies. Am I correct?

Douglas: If there is, try placing an index in AT1.TRAN_DATE or AT1.NEXT_RUN_DATE.
Since T.TRAN_ID is the primary key, should I try and add an index on AT1.TRAN_DATE and/or AT1.NEXT_RUN_DATE?

Douglas: What is the approximate record count of each involved table?
ACCT_TRAN_DETAIL AT1 1,283,858 rows
ACCT_TRAN_MASTER ATM 1,077,370 rows
TE_TRAN T 149 rows
TE_PLAN_TRAN TP 71 rows
ACCT_TRAN_QUE_STATUS ATQS, ATQS2 and ATQS3 9 rows

Douglas: What is the selectivity of the indexes that appear in the plan?
Again my green gills will show here as I do not understand what you are asking for. Can you explain how I determine this? I use Database Workbench for all of my development by the way...

Douglas: What other indexes are present in the involved tables?

ACCT_TRAN_DETAIL AT1 has one index
CREATE ASC INDEX IX_ACCT_TRAN_DET_POS_IDX ON ACCT_TRAN_DETAIL (POSITION_INDEX);
The POSITION_INDEX is nothing more than a way to order the transaction detail rows for each transaction. This allows the user to change the order in which a transaction detail is processed within a transaction.

ACCT_TRAN_MASTER ATM has no indicies
TE_TRAN T has no indicies
TE_PLAN_TRAN TP has no indicies
ACCT_TRAN_QUE_STATUS ATQS, ATQS2 and ATQS3 has no indicies

Would it be helpful to send you the metadata for these tables?

Thanks again for your help as I really appreciate it.

Mike


[Non-text portions of this message have been removed]