RE: index hint ignored?

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Fri, 31 Jan 2003 11:31:15 -0800
Message-ID: <F001.00540D35.20030131113115@fatcity.com>

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-----
Sent: Friday, January 31, 2003 11: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: Fink, Dan
  INET: Dan.Fink_at_mdx.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:31:15 CST