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? > Your query ran in 2.6 seconds. The explain plan for that is: > > SELECT STATEMENT Optimizer Mode=CHOOSE > SORT GROUP BY > NESTED LOOPS > MERGE JOIN > SORT JOIN > NESTED LOOPS > NESTED LOOPS > TABLE ACCESS FULL DIVWEB.WRKENROLMENTS > TABLE ACCESS BY INDEX ROWID DIVWEB.WRKPERIODS > INDEX UNIQUE SCAN DIVWEB.PKWRKPERIODS > TABLE ACCESS BY INDEX ROWID DIVWEB.ESCUNITS > INDEX UNIQUE SCAN DIVWEB.PK_ESCUNITS > SORT JOIN > TABLE ACCESS FULL DIVWEB.WRKCALCS > TABLE ACCESS BY INDEX ROWID DIVWEB.WRKLOCATIONS > INDEX UNIQUE SCAN DIVWEB.PK_WRKLOCATIONS > My slower query (4.3 secs): > > SELECT STATEMENT Optimizer Mode=CHOOSE > SORT GROUP BY > MERGE JOIN > SORT JOIN > NESTED LOOPS > NESTED LOOPS > MERGE JOIN > TABLE ACCESS BY INDEX ROWID DIVWEB.WRKLOCATIONS > INDEX UNIQUE SCAN DIVWEB.PK_WRKLOCATIONS > FILTER > TABLE ACCESS FULL DIVWEB.WRKCALCS > TABLE ACCESS BY INDEX ROWID DIVWEB.WRKPERIODS > INDEX UNIQUE SCAN DIVWEB.PKWRKPERIODS > TABLE ACCESS BY INDEX ROWID DIVWEB.ESCUNITS > INDEX UNIQUE SCAN DIVWEB.PK_ESCUNITS > SORT JOIN > TABLE ACCESS FULL DIVWEB.WRKENROLMENTS As you can see in both of these the SORT GROUP BY is the last step. So in all intermediate steps the database has many more rows, which makes it slower. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:208208 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
