Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Hints and Case Statements

Hints and Case Statements

From: Scott Canaan <srcdco_at_ritvax.rit.edu>
Date: Thu, 18 Jul 2002 08:58:40 -0800
Message-ID: <F001.0049B778.20020718085840@fatcity.com>

    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).
Received on Thu Jul 18 2002 - 11:58:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US