Title: NVL and index usage
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
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni
Title: NVL and index usage
In a quick test on 9013 it changed the index which
it was using and went from Range scan to fast full scan.
- Original Message -
From:
Jamadagni, Rajendra
To: Multiple recipients of list ORACLE-L
Sent: Thursday, January 02, 2003 9:48
AM
It won't be able to use the index as far as I know. Unless it's a functional
index ;).
Mogens
Jamadagni, Rajendra wrote:
NVL and index usage
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
Title: RE: NVL and index usage
Thanks ... the development is rolling out a new change by adding a new nullable column to a table and adding following to all appropriate queries ...
and nvl(new_column,'A') = nvl(some_value,'A')
I learned of this few minutes ago and luckily
If you mean
where nvl(col,:x) = :y then yes it will be negated.
If memory serves, I've even had problems in the past
using nvl in a function based index, the workaround
doing the equivalent with decode, but I can't remember
the specifics
hth
connor
--- Jamadagni, Rajendra
[EMAIL PROTECTED]
Title: 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
Title: RE: NVL and index usage
Thanks Tom and everyone ...
as I mentioned they plan to use it as follows ...
and nvl(new_column,'A') = nvl(some_value,'A')
So I asked them to see the possibility of creating the column with a DEFAULT VALUE of 'A' so the where clause can be written
Title: RE: NVL and index usage
No idea ... my guess is few years down the line, they will make it varchar2(2) ...
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal
: NVL and index usage
Sent by:
[EMAIL PROTECTED
Title: NVL and index usage
Just
curios, why varchar2(1) and not char(1)?
Waleed
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02,
2003 12:48 PMTo: Multiple recipients of list
ORACLE-LSubject: NVL and index usage
Does
Title: NVL and index usage
Just
curious, why varchar2(1) and not char(1)?
Regards,
Waleed
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02,
2003 12:48 PMTo: Multiple recipients of list
ORACLE-LSubject: NVL and index usage
What is the difference?
Just curios, why varchar2(1) and not char(1)?
Waleed
-Original Message-
Sent: Thursday, January 02, 2003 12:48 PM
To: Multiple recipients of list ORACLE-L
Does anyone know off hand if using NVL on an indexed
column negate use of an index in
12 matches
Mail list logo