Forum OpenACS Q&A: Response to Question about an index

Posted by Andrew Piskorski on
Well, I'm no Oracle expert either, but Oracle does not ever index NULL values. So if you do something like:
select foo  from my_table  where bar is null
you will force a full table scan. I don't know why Oracle does it that way - you'd think indexing nulls just like any other value would be useful. Also, that's for normal b-tree indexes - I'm not sure whether the same is true of bitmap indexes.

On the other hand,

select foo  from my_table  where bar is NOT null
will be able to use the index just fine. (Whether Oracle actually uses the index or not is at the optimizer's discretion, of course).

On the inserting thing, yes, Don's correct - Oracle coerces empty string to null on inserts, and I believe updates as well. Funny, I can't remember ever needing to insert a real empty string into Oracle. Offhand, I can't think of a good way to do that.