Re: EXPLAIN PLAN

I am going to answer my own question on this. I determined just after sending the previous message that the sequence of the tables is one of the key factors in query speed. That is, in the query below, since I am going to join lab_meas and lab_smp, I want them next to each other. The same for lab_smp and sample, and so on. (It does appear that small tables can be stuck off anywhere.)

I have never seen this documented. Does anyone have more information.

Thanks.

/beverly

 In article p69_at_stc06.ctd.ornl.gov, tj4_at_ornl.gov ( T L James) writes:
> 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