By now, you might be able to see where I’m going in this section. I have made references to other databases and how features are implemented differently in each. With the exception of some read-only applications, it is my contention that building a wholly database-independent application that is highly scalable is extremely hard—it is, in fact, quite impossible unless you know exactly how each database works in great detail. And, if you knew how each database worked in great detail, you’d understand that database independence is not something you really want to achieve (a very circular argument!).
To illustrate, let’s revisit our initial resource scheduler example (prior to adding the FOR UPDATE clause). Let’s say this application had been developed on a database with an entirely different locking/concurrency model from that of Oracle. What I’ll show here is that if you migrate your application from one database to another, you’ll have to verify that it still works correctly in these different environments and substantially change it as you do!
Let’s assume that we had deployed the initial resource scheduler application in a database that employed blocking reads (reads are blocked by writes). Also consider that the business rule was implemented via a database trigger (after the INSERT had occurred but before the transaction committed, we would verify that only our row existed in the table for that time slot). In a blocking read system, due to this newly inserted data, it would be true that insertions into this table would serialize.
The first person would insert their request for “room A” from 2:00 p.m. to 3:00 p.m. on Friday and then run a query looking for overlaps. The next person would try to insert an overlapping request and, upon looking for overlaps, would become blocked (waiting for the newly inserted data to become available for reading). In that blocking read database, our application would be apparently well behaved, though it could just as easily deadlock (a concept covered in Chapter 6 on locking) if we both inserted our rows and then attempted to read each other’s data. Our checks on overlapping resource allocations would have happened one after the other, never concurrently.
If we migrated this application to Oracle and simply assumed it would behave in the same way, we would be in for a shock. On Oracle, which does row-level locking and supplies nonblocking reads, it appears to be ill-behaved. As we saw previously, we had to use the FOR UPDATE clause to serialize access. Without this clause, two users could schedule the same resource for the same times. This is a direct consequence of not understanding how the database we have works in a multiuser environment.
I have encountered issues such as this many times when an application is being moved from database A to database B. When an application that worked flawlessly in database A does not work or works in an apparently bizarre fashion on database B, the first thought is that database B is a “bad database.”
The simple truth is that database B just works differently. Neither database is wrong or bad; they are just different. Knowing and understanding how they work will help you immensely in dealing with these issues. Taking an application from Oracle to SQL Server exposes SQL Server’s blocking reads and deadlock issues—it goes both ways.
For example, I was asked to help convert some Transact-SQL (the stored procedure language for SQL Server) into PL/SQL. The developer doing the conversion was complaining that the SQL queries in Oracle returned the “wrong” answer. The queries looked like this:
The goal here was to find all of the rows in T where x was NULL if some condition was not met or where x equaled a specific value if some condition was met.
The complaint was that, in Oracle, this query would return no data when L_SOME_ VARIABLE was not set to a specific value (when it was left as NULL). In Sybase or SQL Server, this was not the case—the query would find the rows where x was set to a NULL value. I see this on almost every conversion from Sybase or SQL Server to Oracle. SQL is supposed to operate under tri-valued logic, and Oracle implements NULL comparisons the way ANSI SQL requires them to be implemented (where NULL signifies a state of unknown and not a value). Under those rules, comparing x to a NULL is neither true nor false—it is, in fact, unknown. The following snippet shows what I mean:
SQL> select * from dual where null=null; no rows selected
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 (by default; in current releases of SQL Server, the default behavior may be modified to reflect the ANSI standard).
None of the databases’ processing is wrong—it is just different. And all of the databases are, in fact, ANSI compliant (ANSI compliance does not mean you support 100 percent of the standard, not by a long shot; see the next section “The Impact of Standards” for details), 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 (if it did, it would break thousands of existing legacy applications built on that database).
In this case, one solution to the problem is to write the query like this instead:
However, this leads to another problem. In SQL Server, this query would use an index on x. This might not be the case in Oracle since a B*Tree index (more on indexing techniques in the Chapter 11 on indexes) will not index an entirely NULL entry. Hence, if you need to find NULL values, B*Tree indexes are not always useful.
Note As long as at least one column of an Oracle B*Tree index is defined as NOT NULL, all rows in the table will, in fact, appear in the index, and the predicate where x is null can and will use an index to retrieve the rows.
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 following are the important points to recognize from this example:
•\ Databases are different. Experience with 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 mechanisms).
•\ Being aware of the database, 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, such as, “How do I create a temporary table in a stored procedure?” I don’t answer such questions directly. Instead, I respond with a question: “Why do you want to do that?” Many times, the answer that comes back is: “In SQL Server we created temporary tables in our stored procedures and we need to do this in Oracle.” That’s what I expected to hear. My response, then, is easy: “You don’t 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 (because the table wouldn’t exist at compile time).
•\ 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.
This is not to say that you can’t use temporary tables in Oracle. You can, you probably will. It is just that you will use them differently in Oracle than you did in SQL Server (and vice versa).
