Menu
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.
468
To sum up: If you have any nulls in your database, then you're getting wrong answers to certain of your queries. What's more, you have no way of knowing, of course, just which queries you're getting wrong answers to and which not; all results become suspect.
If you used zero values, your programs wouldn't know the difference between a user entering a zero or the database storing zero as a placeholder. This is one reason advocates for NULL database values prefer it. With programming, having NULLs in your database improves the logic.
A null value in a relational database is used when the value in a column is unknown or missing. A null is neither an empty string (for character or datetime data types) nor a zero value (for numeric data types).
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.
151
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
