Re: cost based optimizer

explanation: what i've found is cost has absolutely no bearing on anything, a relatively useless number.

joe

Baylis, John wrote:

> 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*
> D atabase 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: Joseph S Testa
  INET: teci_at_the-testas.net

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