EXPLAIN PLAN

From: T L James <tj4_at_ornl.gov>
Date: 1997/01/24
Message-ID: <5cb39i$p69@stc06.ctd.ornl.gov>#1/1

I have two SQL queries. They are identical except that two of the tables were swapped in the FROM clause. The explain plans are shown below. One of them runs very quickly; the other basically won't run. The plans are the same, except for the last few lines. Can anyone explain why there would be such a difference in performance? The query only returns 600 rows. The LAB_MEAS table has over 2 million records.

Thanks for any help you can provide.

Beverly Zygmunt
Oak Ridge National Lab

SQL Code:

SELECT ALL OREIS.PROJECT.PROJ_NAME, OREIS.PROJECT.PROJ_CODE,

OREIS.PARAMETER.CHEMICAL_NAME, OREIS.LAB_MEAS.RESULTS,
OREIS.LAB_MEAS.UNITS, OREIS.SAMPLE.MED_TYPE, OREIS.SAMPLE.D_COLLECTED,
OREIS.LOCATION.ADMIN_EAST, OREIS.LOCATION.ADMIN_NORTH,
OREIS.STATION.STA_NAME 

FROM OREIS.LAB_MEAS, OREIS.LAB_SMP,

OREIS.LOCATION, OREIS.PARAMETER, OREIS.PROJECT,
OREIS.PROJECT_STATION_EVENT, OREIS.SAMPLE, OREIS.STATION,
OREIS.STATION_EVENT WHERE (OREIS.PROJECT.PROJ_SITE='OFFSITE'

AND OREIS.SAMPLE.MED_TYPE='WS'
AND OREIS.PARAMETER.CHEMICAL_NAME='Barium') AND ((OREIS.LAB_MEAS.LAB_SMP_ID=OREIS.LAB_SMP.LAB_SMP_ID)

AND (OREIS.LAB_MEAS.PARAMTR=OREIS.PARAMETER.PARAMTR)
AND (OREIS.LAB_SMP.SAMPLE_ID=OREIS.SAMPLE.SAMPLE_ID)
AND (OREIS.STATION.LOCATION_ID=OREIS.LOCATION.LOCATION_ID)
AND (OREIS.PROJECT_STATION_EVENT.PROJECT_ID=OREIS.PROJECT.PROJECT_ID)
AND (OREIS.PROJECT_STATION_EVENT.STAT_EVENT_ID=

OREIS.STATION_EVENT.STAT_EVENT_ID) AND (OREIS.SAMPLE.STAT_EVENT_ID= OREIS.STATION_EVENT.STAT_EVENT_ID) AND
(OREIS.STATION_EVENT.STATION_ID=OREIS.STATION.STATION_ID)); In the slow code, PROJECT and LAB_MEAS were reversed in the FROM.

Plan 1 - FAST results

QUERY_PLAN

---

  SELECT STATEMENT
    NESTED LOOPS

      NESTED LOOPS
        NESTED LOOPS
          NESTED LOOPS
            NESTED LOOPS
              NESTED LOOPS
                NESTED LOOPS
                  NESTED LOOPS
                    TABLE ACCESS BY ROWID SAMPLE
                      INDEX RANGE SCAN SAMPLE_MED_TYPE_UK
                    TABLE ACCESS BY ROWID STATION_EVENT
                      INDEX UNIQUE SCAN STATION_EVENT_PK
                  TABLE ACCESS BY ROWID STATION
                    INDEX UNIQUE SCAN STATION_PK
                TABLE ACCESS BY ROWID LOCATION
                  INDEX UNIQUE SCAN LOCATION_PK
              TABLE ACCESS BY ROWID PROJECT_STATION_EVENT
                INDEX RANGE SCAN PROJ_STAT_EVT_STAT_EVT_ID_FK
            TABLE ACCESS BY ROWID PROJECT
              INDEX UNIQUE SCAN PROJECT_PK
          TABLE ACCESS BY ROWID PARAMETER
            INDEX RANGE SCAN PARAMETER_CHEM_NAME_UK
        TABLE ACCESS BY ROWID LAB_SMP
          INDEX RANGE SCAN LAB_SMP_SAMPLE_ID_FK
      TABLE ACCESS BY ROWID LAB_MEAS
        AND-EQUAL
          INDEX RANGE SCAN LAB_MEAS_LAB_SMP_ID_FK
          INDEX RANGE SCAN LAB_MEAS_PARAMTR_FK

Plan 2 - Never could get this one to return results:

QUERY_PLAN

---

  SELECT STATEMENT
    SORT AGGREGATE

      NESTED LOOPS
        NESTED LOOPS
          NESTED LOOPS
            NESTED LOOPS
              NESTED LOOPS
                NESTED LOOPS
                  NESTED LOOPS
                    NESTED LOOPS
                      TABLE ACCESS BY ROWID SAMPLE
                        INDEX RANGE SCAN SAMPLE_MED_TYPE_UK
                      TABLE ACCESS BY ROWID STATION_EVENT
                        INDEX UNIQUE SCAN STATION_EVENT_PK
                    TABLE ACCESS BY ROWID STATION
                      INDEX UNIQUE SCAN STATION_PK
                  INDEX UNIQUE SCAN LOCATION_PK
                TABLE ACCESS BY ROWID PROJECT_STATION_EVENT
                  INDEX RANGE SCAN PROJ_STAT_EVT_STAT_EVT_ID_FK
              TABLE ACCESS BY ROWID PROJECT
                INDEX UNIQUE SCAN PROJECT_PK
            TABLE ACCESS BY ROWID PARAMETER
              INDEX RANGE SCAN PARAMETER_CHEM_NAME_UK
          TABLE ACCESS BY ROWID LAB_MEAS
            INDEX RANGE SCAN LAB_MEAS_PARAMTR_FK
        TABLE ACCESS BY ROWID LAB_SMP
          INDEX UNIQUE SCAN LAB_SMP_PK

Received on Fri Jan 24 1997 - 00:00:00 CST