How do NULL values affect performance in a database search?

sql database oracle database-performance query-performance
Jakob Ojvind Nielsen picture Jakob Ojvind Nielsen · Jun 19, 2009 · Viewed 51.8k times · Source

In our product we have a generic search engine, and trying to optimze the search performance. A lot of the tables used in the queries allow null values. Should we redesign our table to disallow null values for optimization or not?

Our product runs on both Oracle and MS SQL Server.

Answer

Quassnoi picture Quassnoi · Jun 19, 2009

In Oracle, NULL values are not indexed, i. e. this query:

SELECT  *
FROM    table
WHERE   column IS NULL

will always use full table scan since index doesn't cover the values you need.

More than that, this query:

SELECT  column
FROM    table
ORDER BY
        column

will also use full table scan and sort for same reason.

If your values don't intrinsically allow NULL's, then mark the column as NOT NULL.

Related questions

How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
How can I get column names from a table in Oracle?

I need to query the database to get the column names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and eventTime, then I would …

Read More