So, if that second argument allows NULLs into the index (which is pretty cool, I've never seen that as an option before), why would you ever use the default? The only situation I can think of is if it gains you something on the optimization side (like ... if you're INSERTing records like crazy, and they initially start out with a NULL in this column).
But functionally, I've always wondered why NULLS have been excluded from the index.
now cause the hash to make sense? Not trying to beg the question anymore really. I guess what you're saying is that at some point in the past, the ability to index NULL values was a technical problem, and they've somehow rememdied it. But, they don't want to change the original behavior of the CREATE INDEX statement in case it causes applications to break/respond differently?
Not trying to beg the question anymore really. I guess what you're saying is that at some point in the past, the ability to index NULL values was a technical problem, and they've somehow rememdied it
No, Oracle is still not "indexing a NULL value", because by adding a constant the tuple is never null.
If you create an index on e.g. (col1, col2) a row where col1 IS NULL and col2 IS NULL would not be indexed as well.
I don't really now how Oracle actually builds up the index blocks (and I'm too lazy to read up the concepts manual on that right now) but for some reason it seems to make sense to not index a tuple that completely consists of null values.
AFAIK other DBMS (Postgres, DB2) do it the same way, so I guess it's something to do with how index trees are calculated.
but for some reason it seems to make sense to not index a tuple that completely consists of null values
That's the piece I'm trying to figure out.
For instance, I've always wanted an index for NULL values for a system where there are about 3-400 records where COST_CODE is NULL, out of about 4 million rows. but instead, we've been enduring a table scan when people are looking for those 3-400 recs.
It's always just irked me that I would need to either live with it, or create a dummy value which means "NULL", and enter it in the table. Or I probably could also try to set up a FUNCTION-BASED index, which also just seems like overkill when a NULL value could just be indexed.
In a programming sense (and a philosophical one) NULL is unknown. But the system obviously knows which values are NULL, since you can write a query like
select * from table where column is null
Since it can identify it ... then it should be able to index, which, it looks like it's now possible (although I don't know when this feature surfaced).
I'm just trying to get some advice on why I shouldn't always include this parameter on my CREATE INDEX statements, if I've got plenty of space, and I am not incurring performance problems as a result of indexing NULLs along with all the other values in an already-indexed column.