Database Independence? (Con't) - Page 2
October 4, 2001
This can be confusing the first time you see it — it proves that, in Oracle, Null is neither equal to nor not equal to Null. SQL Server, by default, does not do it that way: in SQL Server and sybase, Null is equal to Null. Neither Oracle's, sybase nor SQL Server's SQL processing is wrong — they are just different. Both databases are in fact ANSI compliant databases but they still work differently. There are ambiguities, backward compatibility issues, and so on, to be overcome. For example, SQL Server supports the ANSI method of Null comparison, just not by default (it would break thousands of existing legacy applications built on that database).
In this case, one solution to the problem was to write the query like this instead:
[The highlighted lines above are one line. They have been split for formatting purposes.]
However, this leads to another problem. In SQL Server, this query would have used an index on x. This is not the case in Oracle since a B*Tree index (more on indexing techniques in Chapter 7) will not index an entirely Null entry. Hence, if you need to find Null values, B*Tree indexes are not very useful.
What we did in this case, in order to minimize impact on the code, was to assign X some value that it could never in reality assume. Here, X, by definition, was a positive number — so we chose the number —1. Thus, the query became:
And we created a function-based index:
With minimal change, we achieved the same end result. The important points to recognize from this are that:
Developers frequently ask me (usually more than once a day) how to do something specific in the database. For example, they will ask the question 'How do I create a temporary table in a stored procedure?' I do not answer such questions with a direct answer — I always respond with a question: 'Why do you want to do that?. Many times, the answer will come back: 'In SQL Server we created temporary tables in our stored procedures and we need to do this in Oracle.' That is what I expected to hear. My response, then, is easy — 'you do not want to create temporary tables in a stored procedure in Oracle (you only think you do).' That would, in fact, be a very bad thing to do in Oracle. If you created the tables in a stored procedure in Oracle you would find that:
The bottom line is that you don't want to do it exactly as you did it in SQL Server (if you even need the temporary table in Oracle at all). You want to do things as they are best done in Oracle. Just as if you were going the other way from Oracle to SQL Server, you would not want to create a single table for all users to share for temporary data (that is how Oracle does it). That would limit scalability and concurrency in those other databases. All databases are not created equal — they are all very different.