RE: (SOT) Joins aren't always better than subqueries (longish post)

[James Holmes]

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            

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


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208035
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