cost based optimizer

From: Baylis, John <JBaylis_at_mail.canfor.ca>
Date: Fri, 08 Feb 2002 10:44:03 -0800
Message-ID: <F001.0040A724.20020208100825@fatcity.com>

I have a table of 500,000 records that is analyzed and contains several indexes that are analyzed.

Can someone explain to me why the cost without a hint is much lower than the cost with a hint?

Using a hint is 10 times faster than without a hint even though the cost is much higher.

Since this is a third party app, I cannot add hints. What aoptions do I have?

select  /*+ Index("ICMSSHDR" XSKSHDRS181M1) Use this index XSKSHDRS181M1 */ PKTS_ICMSSHDR from ICMSSHDR
where (FK_IX_ICMSSHDR_DELV='x' and (SHDR_DELV_WHSE_CODE<'86'));

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=213529 Card=228346 Bytes=12102338)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ICMSSHDR' (Cost=213529 Card=228346 Bytes=12102338)
   2    1     INDEX (RANGE SCAN) OF 'XSKSHDRS181M1' (NON-UNIQUE) (Cost=2346 Card=228346)

select  PKTS_ICMSSHDR from ICMSSHDR
where (FK_IX_ICMSSHDR_DELV='x' and (SHDR_DELV_WHSE_CODE<'86'));

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3526 Card=228346 Bytes=12102338)
   1    0   TABLE ACCESS (FULL) OF 'ICMSSHDR' (Cost=3526 Card=228346 Bytes=12102338)

Thanks

John Baylis
Database Administrator
Canadian Forest Products Ltd.
Vancouver B.C. Canada

(604) 697-6476 (Office)
(604) 313-6054 (Cell) Received on Fri Feb 08 2002 - 12:44:03 CST