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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
