tunning

[tamilselvan]

SELECT DISTINCT ARRG_KEY, GAS.st_code ST_CODE, GAC.cnty_code CNTY_CODE,
       LPAD(LOC1, 3, '0') TWP, LPAD(LOC2, 3, '0') RNG, 
       LPAD(LOC3, 2, '0') SEC, LOC4,
       SUBSTR(LOC4, 1, INSTR(LOC4, '/') - 1) QTR1,
       SUBSTR(LOC4, INSTR(LOC4, '/') + 2, 
              INSTR(LOC4, '/', 1, 2) - INSTR(LOC4, '/') - 2) QTR2,
       SUBSTR(LOC4, INSTR(LOC4, '/', 1, 2) + 2, 
              INSTR(LOC4, '/', 1, 3) - INSTR(LOC4, '/', 1, 2) - 2) QTR3,
       SUBSTR(LOC4, INSTR(LOC4, '/', 1, 3) + 2, 
              INSTR(LOC4, '/', 1, 4) - INSTR(LOC4, '/', 1, 3) - 2) QTR4
FROM I_ALL_AGREEMENTS AA, 
     DOE.TRACT_MASTER_1 TM, 
     DOE.LAND_CONTRACT_MASTER_7 XRF, 
     (SELECT ID, MIN(COUNTY_REF) COUNTY_REF 
        FROM DOE.LAND_CONTRACT_MASTER_9 
       GROUP BY ID) LCM, 
     AREA_COUNTIES GAC, 
     AREA_STATES GAS, 
     DOE.COUNTYMASTER CM
WHERE AA.agmt_num = XRF.id 
  AND AA.subs_num = '000' 
  AND TM.id = XRF.tract_ref 
  AND AA.AGMT_NUM = LCM.id 
  AND SUBSTR(LCM.county_ref, 1, 
          INSTR(LCM.county_ref, '*') - 1) = GAS.st_pstl_abbr --(+)
  AND GAS.st_code = GAC.st_code 
  AND LCM.county_ref = CM.id AND DESCRIPTION = GAC.cnty_name --(+)
  AND TM.id IN 
      (SELECT ID 
        FROM DOE.TRACT_MASTER_0 
       WHERE MAP_TYPE_CODE = 'TR')
ORDER BY ARRG_KEY, ST_CODE, CNTY_CODE, 
         TWP, RNG, SEC, QTR1, QTR2, QTR3;

(16) MERGE JOIN CARTESIAN - Find why the plan goes for MERGE JOIN CARTESIAN.

Tamil