RE: index hint ignored?

From: Janardhana Babu Donga <jbdonga_at_ucdavis.edu>
Date: Fri, 31 Jan 2003 12:08:52 -0800
Message-ID: <F001.00540EB3.20030131120852@fatcity.com>

Try Analyzing the table first and issue the select stmt.  

• Babu

-----Original Message-----
Sent: Friday, January 31, 2003 11:56 AM
To: Multiple recipients of list ORACLE-L

I've tried it both ways, with the comma and without - same result.    

-----Original Message-----
Sent: Friday, January 31, 2003 2:13 PM
To: Multiple recipients of list ORACLE-L

Hi,  

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-----
Gerald
Sent: Friday, January 31, 2003 10:31 AM
To: Multiple recipients of list ORACLE-L

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: Janardhana Babu Donga
  INET: jbdonga_at_ucdavis.edu

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 - 14:08:52 CST