Re: Strange Cost Based Optimizer Decision Making

From: Jimbo1 <nightfanguk_at_yahoo.co.uk>
Date: 20 Dec 2005 01:53:49 -0800
Message-ID: <1135072429.472680.240940@g44g2000cwa.googlegroups.com>

Howdo all,

No worries Matthias. ;o) The problem was the SECTION_ID column occuring before the TIME_STAMP column in the composite index. Thanks for giving me that idea Spendius.

After recreating the index with TIME_STAMP occuring before SECTION_ID, I ran the query again (without gathering stats at this point in time, so there are none at all on the index yet - I'm creating those now).

Here are the results:

SQL> SELECT /*+ CHOOSE */
  2 MAX(time_stamp)
  3 FROM large_table;

MAX(TIME_

---

30-SEP-05 Elapsed: 00:00:00.03

Execution Plan

---

   0 SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=34394 Card=1 Bytes=8)

   1 0 SORT (AGGREGATE)

   2    1     PARTITION RANGE (ALL)
   3    2       INDEX (FULL SCAN (MIN/MAX)) OF 'LARGE_TABLE_PK'

(UNIQUE) (Cost=34394 Card=65623008 Bytes=524984064)

One 4 minute query reduced to 0.03 seconds.

Thanks also to Mladen for contributing; that was very useful to read, although the DBMS_STATS.LOCK_TABLE_STATS procedure is not available in 8i. I've actually already been trying to buy the "Oracle Cost-Based Fundamentals" book you mentioned, but it will be another 4 - 6 weeks before Amazon UK have it in stock again, and everywhere else I've tried no longer seems to have it in stock either. I think demand exceeded supply when it was released a few months ago, and the publishers are having to print more.

Thanks to all of you for your contributions.

Cheers.

James Received on Tue Dec 20 2005 - 03:53:49 CST