Oracle-L: RE: index hint ignored?

From: K Gopalakrishnan <kaygopal_at_yahoo.com>
Date: Fri, 31 Jan 2003 11:12:40 -0800
Message-ID: <F001.00540CC4.20030131111240@fatcity.com>

MessageHi,

THe syntax is incorrect. Incorrect HINTs are treated as comments. replace the comma with blank space and your hint will work as expected.

Best Regards,
K Gopalakrishnan

  -----Original Message-----
  From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Cunningham, Gerald
  Sent: Friday, January 31, 2003 10:31 AM   To: Multiple recipients of list ORACLE-L   Subject: index hint ignored?

Hi there.

I have a non-unique index on a table, and I'm trying to force Oracle to use the index - but it always does a FTS. Why? (I've tried it with and without the alias)

  SQL> set autotrace traceonly
  SQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) */ DISTINCT veh_year     2 FROM TIREADVISOR.vehicle A
    3 ORDER BY veh_year DESC;

20 rows selected.

Execution Plan

     0      SELECT STATEMENT Optimizer=CHOOSE (Cost=118 Card=20 Bytes=80)
     1    0   SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)
     2    1     SORT (UNIQUE) (Cost=67 Card=20 Bytes=80)
     3    2       TABLE ACCESS (FULL) OF 'VEHICLE' (Cost=16 Card=19607

Bytes=78428)

===

  select TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION   from dba_ind_columns
  where INDEX_OWNER = 'TIREADVISOR'
  and TABLE_NAME = ('VEHICLE') order by 1,2,4,3

  TABLE_NAME                     INDEX_NAME
  ------------------------------ ------------------------------
  COLUMN_NAME          COLUMN_POSITION
  -------------------- ---------------
  VEHICLE                        VEHICLE_PK
  VEH_ID                             1

  VEHICLE                        VEHICLE_VEH_YEAR_INDX
  VEH_YEAR                           1

Thanks for any help!

Jerry

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: K Gopalakrishnan
INET: kaygopal_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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). Received on Fri Jan 31 2003 - 13:12:40 CST