RE: index hint ignored?

From: Cunningham, Gerald <Gerald.Cunningham_at_usi.net>
Date: Fri, 31 Jan 2003 11:31:18 -0800
Message-ID: <F001.00540D6D.20030131113118@fatcity.com>

Thanks, Lisa.  

This particular table is rather small (~20,000 rows, 1.3 Mb in size, 20 distinct values for VEH_YEAR) - so it may be faster doing a FTS scan, not sure. I was mainly wondering why my hint was ignored. It's a WebSphere app (The person who wrote it is long gone!) where this statement gets executed thousands of times a day, so if I could shave some time off the query it may help...    

Thanks again!  

• Jerry

	-----Original Message-----
	From: Koivu, Lisa [mailto:Lisa.Koivu_at_efairfield.com] 
	Sent: Friday, January 31, 2003 1:56 PM
	To: ORACLE-L_at_fatcity.com; Cunningham, Gerald
	Subject: RE: index hint ignored?
	
	
	Hi Jerry, 
	 
	Methinks it's because this is a small table.  20 records?

Peanuts. Why bother with the index.          

        On the same token you should probably not spend a lot of time worrying about this... unless this is just a learning exercise :)          

        hth         

	Lisa Koivu 
	Oracle Diaper Administrator 
	Fairfield Resorts, Inc. 
	5259 Coconut Creek Parkway 
	Ft. Lauderdale, FL, USA  33063 
	

	 
	 

		-----Original Message-----
		From: Cunningham, Gerald
[mailto:Gerald.Cunningham_at_usi.net]
		Sent: Friday, January 31, 2003 1:31 PM
		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:31:18 CST