Myles

> SELECT
> JOB.JOB_ID,
> CLIENT.CLIENT_NAME,
> CLIENT_SITE.SITE_DESC,
> JOB.ENTERED_TS,
> JOB_STATUS.JS_NAME,
> CHARGE_TYPE.CT_NAME,
> JOB.JOB_DESC_SHORT
> FROM JOB
> INNER JOIN CLIENT ON (JOB.FK_CLIENT_ID = CLIENT.CLIENT_ID)
> LEFT OUTER JOIN CLIENT_SITE ON (JOB.FK_SITE_ID =
> CLIENT_SITE.SITE_ID)
> INNER JOIN JOB_STATUS ON (JOB.FK_JS_ID = JOB_STATUS.JS_ID)
> INNER JOIN JOB_PRIORITY ON (JOB.FK_JP_ID = JOB_PRIORITY.JP_ID)
> LEFT OUTER JOIN JOB_TECH ON (JOB.JOB_ID = JOB_TECH.JOB_ID)
> INNER JOIN CHARGE_TYPE ON (JOB.FK_CHARGETYPE_ID =
> CHARGE_TYPE.CT_ID)


> The performance results are reporting that all queries are
> indexed reads with the exception of the CLIENT join, which is
> showing as 'natural' and reporting as a NON-INDEXED read. I
> suspect this is where the performance problem is. I've
> checked the CLIENT table and verified indexes, etc. on it,
> but for some reason the optimizer is not using the indexes.

This is exactly as expected.

You don't have any criteria (i.e. WHERE) which would reduce the number
of Client table rows. Accordingly, you are tell the engine that you
want all Client rows, so the engine is reading the Client table front to
back (storage order)...


Sean