Cause for explain plan differences? - probably a basic question

From: Szecsy Tamas <tszecsy_at_GEOMETRIA.hu>
Date: Mon, 11 Jun 2001 00:23:17 -0700
Message-ID: <F001.00323BFF.20010610235546@fatcity.com>

Hi,

I have two database instances 816SE and 817EE on WinNT. For the same query I get different result as follows (optimizer_mode=CHOOSE).

What actually I don't understand is, that why are there resource costs in the EE 8.1.7. version and not in the SE 8.1.6 version. Both databases are created in the default database as supplied by Oracle and the import utility is used to load data.

Could someone shed some light on this? Thank you in advance.

Regards,

Tamas Szecsy

Oracle SE 8.1.6 - The Execution plan

+++++++++++++++++++++++++++++++++++++++++++++++++

SELECT STATEMENT Optimizer=CHOOSE
  SORT (ORDER BY)
    NESTED LOOPS (OUTER)

      NESTED LOOPS (OUTER)
        NESTED LOOPS (OUTER)
          NESTED LOOPS (OUTER)
            NESTED LOOPS (OUTER)
              NESTED LOOPS (OUTER)
                NESTED LOOPS (OUTER)
                  NESTED LOOPS (OUTER)
                    NESTED LOOPS (OUTER)
                      NESTED LOOPS (OUTER)
                        TABLE ACCESS (FULL) OF HIBACIM
                        INDEX (UNIQUE SCAN) OF SYS_C0014232 (UNIQUE)
                      INDEX (UNIQUE SCAN) OF PK_KORZNEV_KORZET (UNIQUE)
                    INDEX (UNIQUE SCAN) OF PK_K_STATUS_CODE (UNIQUE)
                  INDEX (UNIQUE SCAN) OF PK_K_HCHHELY_CODE (UNIQUE)
                INDEX (UNIQUE SCAN) OF PK_K_HCHOKA_CODE (UNIQUE)
              INDEX (UNIQUE SCAN) OF PK_K_HCJELTIP_CODE (UNIQUE)
            INDEX (UNIQUE SCAN) OF PK_K_HCDOLG_CODE (UNIQUE)
          INDEX (UNIQUE SCAN) OF PK_K_HCDOLG_CODE (UNIQUE)
        INDEX (UNIQUE SCAN) OF PK_K_HCDOLG_CODE (UNIQUE)
      INDEX (UNIQUE SCAN) OF PK_K_HCHTIP_CODE (UNIQUE)

Oracle SE 8.1.7 - The Execution plan - CHOOSE

++++++++++++++++++++++++++++++++++++++++++++++

SELECT STATEMENT Optimizer=CHOOSE (Cost=1578048 Card=6096026 Bytes=1578870734)
  SORT* (ORDER BY) (Cost=1578048 Card=6096026 Bytes=1578870734)     NESTED LOOPS* (OUTER) (Cost=174 Card=6096026 Bytes=1578870734)

      NESTED LOOPS* (OUTER) (Cost=174 Card=673594 Bytes=172440064)
        NESTED LOOPS* (OUTER) (Cost=174 Card=410728 Bytes=99806904)
          NESTED LOOPS* (OUTER) (Cost=174 Card=250444 Bytes=57602120)
            NESTED LOOPS* (OUTER) (Cost=174 Card=152710 Bytes=33138070)
              NESTED LOOPS* (OUTER) (Cost=174 Card=93116 Bytes=18995664)
                NESTED LOOPS* (OUTER) (Cost=174 Card=93116 Bytes=17785156)
                  NESTED LOOPS* (OUTER) (Cost=174 Card=93116 Bytes=16574648)
                    NESTED LOOPS* (OUTER) (Cost=174 Card=93116
Bytes=15364140)
                      NESTED LOOPS* (OUTER) (Cost=174 Card=93116
Bytes=14153632)
                        TABLE ACCESS* (FULL) OF HIBACIM (Cost=174 Card=93116
Bytes=12943124)
                        INDEX* (UNIQUE SCAN) OF PK_K_HCHTIP_CODE (UNIQUE)
                      INDEX* (UNIQUE SCAN) OF PK_K_HCJELTIP_CODE (UNIQUE)
                    INDEX* (UNIQUE SCAN) OF PK_K_HCHOKA_CODE (UNIQUE)
                  INDEX* (UNIQUE SCAN) OF PK_K_HCHHELY_CODE (UNIQUE)
                INDEX* (UNIQUE SCAN) OF PK_K_STATUS_CODE (UNIQUE)
              INDEX* (UNIQUE SCAN) OF PK_K_HCDOLG_CODE (UNIQUE)
            INDEX* (UNIQUE SCAN) OF PK_K_HCDOLG_CODE (UNIQUE)
          INDEX* (UNIQUE SCAN) OF PK_K_HCDOLG_CODE (UNIQUE)
        INDEX* (UNIQUE SCAN) OF SYS_C001136 (UNIQUE)
      INDEX* (UNIQUE SCAN) OF PK_KORZNEV_KORZET (UNIQUE)

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Szecsy Tamas
  INET: tszecsy_at_GEOMETRIA.hu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Jun 11 2001 - 02:23:17 CDT