On Sun, Dec 28, 2008 at 11:36 AM, SoftTech <miket@...> wrote:

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

Yes. It is already indexed.

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

Yes. Do it in a test database first. :)

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

Selectivity is also called "index statistic". I'm not familiar with
Workbench but it probably displays a number called "index statistic"
or "index selectivity" somewhere near the place where you define
indexes for a table.

More specifically, this is what I think needs to change in your plan:
- "T NATURAL" should look like "T INDEX (PK_NAME)"
- "ATM INDEX (REFTE_TRAN259)" should look like "ATM INDEX (SOME OTHER INDEX)"

run the sql again with the new index and post the results (time and plan).

hth,
Regards,
--
Douglas Tosi
www.sinatica.com