>Here is another SQL that is running slow for me. I think I'm probably still missing
>something that you are trying to teach me. Can you spot my error?
>
>SELECT FIRST 25 SKIP 0 DISTINCT
> s.OBJ_GUID, s.CMTS, s.CTK, s.REC_DSC, s.REC_ACT, s.NOTE, l.NEW_VAL, f.FLD_DSPLY_NM,
> t.TBL_DSPLY_NM
>FROM
> JET_SAV_PNT s
> JOIN JET_CHG_LOG l ON l.PAR_GUID = s.OBJ_GUID
> JOIN JET_FLD_NMS f ON f.TBL_NM = l.TBL_NM AND f.FLD_NM = l.FLD_NM
> JOIN JET_TBL_NMS t ON t.TBL_NM = f.TBL_NM
>WHERE s.CBY = 'JMA'
>ORDER BY s.CMTS DESC
>
>This takes 3.5 seconds to run. I changed the order of the joins and also made them
>regular joins instead of LEFT joins. Those improvements brought the execution time
>down to 3.5 seconds from > 9 seconds.

No, I cannot see any obvious "error", well, unless you call it an error not telling us the PLAN or something about the tables/indexes involved (no point in checking indexes used on tiny tables, nor to use an index for CBY if 50% contain the value JMA in that field) ;o) I'd expect a plan similar to

PLAN SORT(JOIN(JOIN(JOIN(s INDEX CBY_INDEX, l INDEX PAR_GUID_INDEX), f INDEX(FLD_NM_INDEX), t INDEX(TBL_NM_INDEX))

Of course, there might be an index also on f.TBL_NM - I've no idea whether that one will slow down or increase your performance. You can easily check that by changing to

JOIN JET_FLD_NMS f ON f.TBL_NM || '' = l.TBL_NM AND f.FLD_NM = l.FLD_NM

By the way, I think the order of the JOINs is irrelevant if only plain (INNER) JOINs like your query above is used. It's once words like LEFT/RIGHT are added that the order becomes relevant.

HTH,
Set