Dynamically preventing use of index

From: Amar Kumar Padhi <TS2017_at_emirates.com>
Date: Mon, 08 Jul 2002 02:28:22 -0800
Message-ID: <F001.00490E8E.20020708022822@fatcity.com>

Hi,
I have a table with two column, each having non-unique index. The following select is used to fetch records. The variables ‘var1’ and ‘var2’ are provided for execution. Only one of the two variables is provided. The requirement is to use the index on that column only for which the value is provided and avoid use of the other column index. Is this possible in a select stmt in RBO?

Oracle uses both the indexes by default, irrespective of the values provided.

Select col1, col2
from am49
where col1 = var1
or col2 = var2;

The above query should use the index on col1 only when var1 is provided and vice versa.

On Oracle 8.1.7 (RBO)

rgds
amar
http://amzone.netfirms.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Amar Kumar Padhi
  INET: TS2017_at_emirates.com

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 Mon Jul 08 2002 - 05:28:22 CDT