Oracle Database Discussions

Hello,

As you know in most of case NULL values are not indexed and then not searchable using index.
Then when you do where MyField is null, you have the risk of doing a full table scan
However most of people don't know that, and then doesn't care of this possible issue and of possible solution (bitmap or including a not null column)

SQL Server, MySQL and probably some others DB don't have the same behavior as they indexes NULL
I know this caveat can be used to have partial indexing by nulling non interesting values and then can't be removed

Then I would suggest to enhance the create index command to allow to index also null with something like that
Create index MyIndex on MyTable(MyColumn including nulls )

As you make this change, perhaps it would be geat to change the behavior documented bellow as it looks more as an old heritage too by adding keyword like "allow null duplicate" and "constraint on null duplicate"
Ascending unique indexes allow multiple NULL values. However, in descending unique indexes, multiple NULL values are treated as duplicate values and therefore are not permitted.

Laurent