may be the tagline for a major retailer, but it’s not always a reliable term in Oracle. A question at a panel session here at the Hotsos Symposium made me think of a situation with a client just last week.

Can you tell which of these execution plans is better?

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=36111)

  HASH (GROUP BY) (Cost=36111 Card=679428 Bytes=48918816)

    HASH JOIN (OUTER) (Cost=24847 Card=679428 Bytes=48918816)

      INDEX (RANGE SCAN) OF “INDEX_1″ (Cost=5139 Card=559570 Bytes=31335920)

      INDEX (RANGE SCAN) OF “INDEX_2″ (Cost=10913 Card=1206767 Bytes=19308272)

SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6)

  HASH (GROUP BY) (Cost=6 Card=1 Bytes=72)

    NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=72)

      INDEX (RANGE SCAN) OF “INDEX_1″ (Cost=3 Card=1 Bytes=56)

      INDEX (RANGE SCAN) OF “INDEX_2″ (Cost=2 Card=1 Bytes=16)

At first glance, most people would say “the second one, with a cost of 6″.  However, in actuality the second execution plan averaged an elapsed time of 5076 seconds, while the first, with the much higher “cost”, averaged 80 seconds.

The plan which performed poorly was derived based on statistics that the optimizer had, which apparently didn’t properly represent the data sought by the query.  So its “cost” is going to be inaccurate, just as the execution plan is not optimal.