RE: index hint ignored?

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Fri, 31 Jan 2003 12:23:53 -0800
Message-ID: <F001.00540F1B.20030131122353@fatcity.com>

I don't think it matters if there are nulls or not. As long as they are allowed is the issue (as I recall from an earlier thread).  

Dan

-----Original Message-----

Sent: Friday, January 31, 2003 12:56 PM
To: Multiple recipients of list ORACLE-L

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-----

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

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 - 14:23:53 CST