Database Independence? (Con't) - Page 2

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:

select *
  from t
  where ( x = l_some_variable OR 
        (x is null and
         l_some_variable is NULL ))

[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:

select * 
       from t 
       where nvl(x,-1) = nvl(l_some_variable,-1)

And we created a function-based index:

create index t_idx on t( nvl(x,-1) );

With minimal change, we achieved the same end result. The important points to recognize from this are that:

• Databases are different. Experience in one will in part carry over to another but you must be ready for some fundamental differences as well as some very minor differences.
• Minor differences (such as treatment of Nulls) can have as big an impact as fundamental differences (such as concurrency control mechanism).
• Being aware of the database and how it works and how its features are implemented is the only way to overcome these issues.

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:

• Doing DDL is a scalability inhibitor.
• Doing DDL constantly is not fast.
• Doing DDL commits your transaction.
• You would have to use Dynamic SQL in all of your stored procedures in order to access this table — no static SQL.
• Dynamic SQL in PL/SQL is not as fast or as optimized as static SQL.

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.