Ah, the PK on wrkenrolments is actually a separate column (enrolmentid) so there isn't an index to use. Maybe I should define a composite key for that purpose instead?
-----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, 1 June 2005 10:59 To: CF-Talk Subject: Re: (SOT) Joins aren't always better than subqueries (longish post) James Holmes wrote: > > My quick query (800ms): > > SELECT STATEMENT Optimizer Mode=CHOOSE > TABLE ACCESS BY INDEX ROWID DIVWEB.ESCUNITS > INDEX UNIQUE SCAN DIVWEB.PK_ESCUNITS > TABLE ACCESS FULL DIVWEB.WRKENROLMENTS > TABLE ACCESS BY INDEX ROWID DIVWEB.WRKPERIODS > INDEX UNIQUE SCAN DIVWEB.PKWRKPERIODS > TABLE ACCESS BY INDEX ROWID DIVWEB.WRKLOCATIONS > INDEX UNIQUE SCAN DIVWEB.PK_WRKLOCATIONS > SORT GROUP BY > TABLE ACCESS FULL DIVWEB.WRKCALCS Let's start with this one. What Oracle does here is first a full table scan of the WRKCACLS table, then a sort and group by. Then for each result of that it passes all the other tables to search for matching rows. The obvious reason why this is so fast is that many rows get discarded early on. I wonder why the unique index on WRKENROLLMENTS (periodid, unitid) isn't used. Is the WRKENROLLMENTS table very small perhaps? [snip] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208210 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
