Hints and Case Statements

    I have run into a performance issue that I can't find any information on. There is a query that has a case statement in it. We added indexes to the fields being referenced in the case statement, but Oracle doesn't use the indexes. I added hints to the SQL to force it to use the indexes, it still won't do it. I am wondering if the optimizer realizes that the fields are being referenced, since they are inside the case. It wants to do a full table scan each time. I can get it to use an index on the same table, if I specify and index on a field outside of the case.

Required information:

    Oracle Version: 8.1.7.0.0
    Platform: Sun Solaris 2.8

Thank you,

--
Scott Canaan (srcdco_at_rit.edu)


(585) 475-7886

"Life is like a sewer, what you get out of it depends on what you put
into it" - Tom Lehrer


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Scott Canaan
  INET: srcdco_at_ritvax.rit.edu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).