RE: index hint ignored?

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

Hi Dan,  

Hmmmm. That's interesting. Yes, the column does allow nulls. Not sure if there actually are any null values, I'll have to check...  

Thanks!  

• Jerry

	-----Original Message-----
	From: Fink, Dan [mailto:Dan.Fink_at_mdx.com] 
	Sent: Friday, January 31, 2003 2:31 PM
	To: Multiple recipients of list ORACLE-L
	Subject: RE: index hint ignored?
	
	
	Gerald,
	    Is veh_year a nullable column? If so, an index lookup may

give you incorrect results (nulls are not indexed), so a full table scan will be used.

            Of course, is a full table scan a bad choice? Don't worry about the access path, find the plan that incurs the fewest I/Os and returns the quickest.          

        Dan Fink

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