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.