February 14, 2005

User Indexes

[See follow-up posted February 21.]

I was trying to tune a query in PeopleSoft HR the other day. It was doing all of the usual things, going through JOB, finding the current row for each employee, selecting active employees and then joining to various other tables. The SQL query plan showed nothing unusual other than a full table scan of JOB, but that seemed reasonable as it was the main table. So what was causing it to run so slowly?

I don't know how many times I've seen this situation without noticing something that (in hindsight) should have been obvious. There are indexes on the keys to the JOB table, so the database should have been able to select the current rows using only these indexes. So the check on EMPL_STATUS for active employees (ALPS, PALS, LAPS, SLAP, or however you remember it!) was the only reason for the table scan. There was no index on EMPL_STATUS as delivered. I decided to try adding a user index (Tools | Data Administration | Indexes) on EMPL_STATUS (and including EMPLID, EMPL_RCD, EFFDT and EFFSEQ in the definition of the index), and the result was dramatic: an 80% decrease in execution time. This was for an Oracle database.

Each index we add exacts a slight performance penalty whenever there is a row INSERT or DELETE or an UPDATE to the column in question. Therefore, we don't want to add lots of indexes without a good reason. But EMPL_STATUS is checked all the time by reports, interfaces, user queries, etc., so it seemed like a good candidate. You might want to take a look at JOB to see if you have an index on that field. Are there other very commonly used fields that should have indexes? I'm starting to look.

Until next time...