RE: index hint ignored?

From: Cunningham, Gerald <Gerald.Cunningham_at_usi.net>
Date: Fri, 31 Jan 2003 11:56:04 -0800
Message-ID: <F001.00540DDE.20030131115604@fatcity.com>

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-----
		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: Cunningham, Gerald
  INET: Gerald.Cunningham_at_usi.net

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:56:04 CST