| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: NVL and index usage
Raj,
the index will not be used.
think about it.
for those records that do not have a value (are null) for the indexed column - there is not an entry in the index - so they can not be evaluated to return a row.
secondly, even if all rows were represented in the index - why perform two queries - one for the index, and then one for the table - to evaluate whether to use the row in the query?
either way, it would not work properly.
that's why Oracle gave us function based indexes - so that we could use a function and get fast results from a large table.
hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Thursday, January 02, 2003 1:22 PM
To: Multiple recipients of list ORACLE-L
In a quick test on 9013 it changed the index which it was using and went from Range scan to fast full scan.
Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1).
Raj
QOTD: Any clod can have facts, but having an opinion is an art!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us
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 Thu Jan 02 2003 - 12:51:09 CST
 |
 |