How can I avoid IS [NOT] NULL in an SQL select statement? [closed]

开发者_运维问答 It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

IS [NOT] NULL meddles with the indexes of SQL statements in Oracle, is there any way I can replace IS NULL in an SQL statement?

According to Oracle 9i Performance Tuning Tips and Techniques, having IS [NOT] NULL suppresses the indexes of the columns.

For example:

select * from users where user_id is not null;

---

For IS NOT NULL your claim is not true. Oracle will use an index on that column (provided an index scan is indeed faster than a full table scan - which is not always the case)

Oracle does not put tuples that are all NULL into an index, therefor if you define an index on a single column e.g. CREATE INDEX idx_user_id ON person (user_id) that index will not contain rows where user_id is NULL. As a result of that, the index cannot be used for a IS NULL condition.

However you can use a workaround if you need to do frequent IS NULL selections, by forcing the nulls to be indexed using a constant in the index definition: CREATE INDEX idx_user_id ON person (user_id, 1). That index will be used for a IS NULL condition (again provided other access methods are not more efficient).

---

Are you allowed to change the db structure?

If yes, in order to not have any WHERE column IS NULL or WHERE column IS NOT NULL condition in your queries, then fully normalize your tables (i.e. 5NF or 6NF) and make all columns that are used in conditions NOT NULL.

---

I'm not an Oracle expert but I seriously doubt that a serious RDBMS such as Oracle cannot use index on a nullable column. See this question and answers that support this opinion. Perhaps the problems (you think) you have are not caused by the NULLs in your column but by the selectivity of them: oracle-10g-optimize-where-is-not-null

---

For your simple query:

select * from users where user_id is not null;

the optimizer will choose not to use the index - as it would do for any other query - if the selectivity is not high enough. If the Nulls are few on the table, then fully scanning the table will be faster - or at least the optimizer thinks so.

---

There is no efficient way to do the same thing without changing the data.

You can use a magic value instead of null, for example -1. That would allow you to make the field non-nullable, which increases performance somewhat, and works better with indexes. However, this conflicts with the usual recommendation to avoid magic values, so it's a compromise between performance and best practice.