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)
