9i query runs slower than 8i

From: Ken Chesak <datavector_at_hotmail.com>
Date: 22 Sep 2004 09:16:08 -0700
Message-ID: <3f2f39c4.0409220816.3cc309ff@posting.google.com>

The following query runs slower in 9i about 30 seconds than 8i about 15 seconds. Seems 8i is choosing a hash join and 9i is not.

Any ideas?

SELECT (beg_bal - closed) beg_bal

          FROM
         (select count(*) beg_bal
          FROM rg101 c
           WHERE dt_run < '01-jan-04'
             ) beg_bal_tbl,
        (select /* NO_UNNEST */ count(*) closed
        FROM rg101 c, s_code_disposition s_code_disposition
          WHERE c.cd_disposition = s_code_disposition.cd_disposition
            and action_status = 'Cleared'
            and dt_disposition < '01-jan-04'
            AND dt_run < '01-jan-04')

Oracle9i Enterprise Edition Release 9.2.0.4.0

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3401 Card=1 Bytes=26)    1 0 MERGE JOIN (CARTESIAN) (Cost=3401 Card=1 Bytes=26)

   2    1     VIEW (Cost=270 Card=1 Bytes=13)
   3    2       SORT (AGGREGATE)
   4    3         INDEX (FAST FULL SCAN) OF 'IX_RG101_09' (NON-UNIQUE) (Cost=270
 Card=686814 Bytes=5
          494512)

   5    1     FIRST ROW
   6    5       VIEW (Cost=3131 Card=1 Bytes=13)
   7    6         SORT (AGGREGATE)
   8    7           NESTED LOOPS (Cost=3131 Card=225703 Bytes=8125308)
   9    8             TABLE ACCESS (FULL) OF 'RG101' (Cost=3131 Card=580380 Byte
s=12768360)
  10    8             TABLE ACCESS (BY INDEX ROWID) OF 'S_CODE_DISPOSITION'
  11   10               INDEX (UNIQUE SCAN) OF 'PK_S_CODE_DISPOSITION' (UNIQUE)

Oracle8i Enterprise Edition Release 8.1.7.4.0

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5249 Card=1 Bytes=26)    1 0 MERGE JOIN (CARTESIAN) (Cost=5249 Card=1 Bytes=26)

   2    1     VIEW (Cost=352 Card=1 Bytes=13)
   3    2       SORT (AGGREGATE)
   4    3         INDEX (FAST FULL SCAN) OF 'IX_RG101_09' (NON-UNIQUE) (Cost=352
 Card=535257 Bytes=3
          746799)

   5    1     FIRST ROW
   6    5       VIEW
   7    6         SORT (AGGREGATE)
   8    7           HASH JOIN (Cost=4897 Card=294477 Bytes=9128787)
   9    8             TABLE ACCESS (FULL) OF 'S_CODE_DISPOSITION' (Cost=1 Card=1
0 Bytes=120)
  10    8             TABLE ACCESS (FULL) OF 'RG101' (Cost=4893 Card=353372 Byte

s=6714068) Received on Wed Sep 22 2004 - 11:16:08 CDT