Crazy optimizer

From: Alexandr I. Alesinsky <al_at_investor.kharkov.ua>
Date: 1995/09/15
Message-ID: <ADewQMmipB_at_investor.kharkov.ua>#1/1

I hope, that following results achieved on SCO, Oracle 7.1.4 may be interest to you

alter session set optimizer_goal = Choose;

Session altered.

_at_migrate/bookt

Table truncated.

Explained.

 Query Plan

---

SELECT STATEMENT   Cost = 26829         <=====================

  MERGE JOIN OUTER
    SORT JOIN

      MERGE JOIN
        SORT JOIN
          MERGE JOIN
            SORT JOIN
              TABLE ACCESS FULL GLUED
            SORT JOIN
              TABLE ACCESS FULL SUBACC
        SORT JOIN
          TABLE ACCESS FULL SUBACC
    SORT JOIN
      TABLE ACCESS FULL BANK

14 rows selected.

alter session set optimizer_goal = first_rows;

Session altered.

_at_migrate/bookt

Table truncated.

Explained.

 Query Plan

---

SELECT STATEMENT   Cost = 1         <==========================

  NESTED LOOPS
    NESTED LOOPS

      NESTED LOOPS OUTER
        TABLE ACCESS FULL GLUED
        TABLE ACCESS BY ROWID BANK
          INDEX RANGE SCAN BANK#MFO
      INDEX RANGE SCAN SUBACC#SUBACC

    INDEX RANGE SCAN SUBACC#SUBACC 9 rows selected.

alter session set optimizer_goal = all_rows;

Session altered.

_at_migrate/bookt

Table truncated.

Explained.

 Query Plan

---

SELECT STATEMENT   Cost = 26829        <========================

  MERGE JOIN OUTER
    SORT JOIN

      MERGE JOIN
        SORT JOIN
          MERGE JOIN
            SORT JOIN
              TABLE ACCESS FULL GLUED
            SORT JOIN
              TABLE ACCESS FULL SUBACC
        SORT JOIN
          TABLE ACCESS FULL SUBACC
    SORT JOIN
      TABLE ACCESS FULL BANK

14 rows selected.

alter session set optimizer_goal = rule;

Session altered.

_at_migrate/bookt

Table truncated.

Explained.

 Query Plan

---

SELECT STATEMENT Cost =
  NESTED LOOPS OUTER
    NESTED LOOPS

      NESTED LOOPS
        TABLE ACCESS FULL GLUED
        INDEX RANGE SCAN SUBACC#SUBACC
      INDEX RANGE SCAN SUBACC#SUBACC
    TABLE ACCESS BY ROWID BANK
      INDEX RANGE SCAN BANK#MFO

9 rows selected.

spool off

Changing of OPTIMIZER_COAL reduce statement cost in 26829 times! May be this is a world record ?

Alexander Alesinsky,
JSV Investor Received on Fri Sep 15 1995 - 00:00:00 CEST