RE: cost based optimizer

First, you should be aware that the Cost is only Oracle's best guess as to what Access Path is faster. It's often wrong. What version are you on? Depending on version you have several options  

1. Create a histogram on the XSKSHDRS181M1 column
2. Set the optimizer_index_cost_adj equal to a value under 100. This lowers the cost Oracle assumes an index will add. We have it set to 75 on our data warehouse.
3. Associate the access path resulting with using the hint with the SQL (I don't remember how to do this, but remember reading about it around a year ago).

Good luck!  

Jay Miller

-----Original Message-----
Sent: Friday, February 08, 2002 1:08 PM
To: Multiple recipients of list ORACLE-L

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)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Miller, Jay
  INET: JayMiller_at_TDWaterhouse.com

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).