Database Reference
In-Depth Information
■
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.
Note
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.
NULL
s) can have as big an impact as fundamental
differences (such as concurrency control mechanisms).
•
Minor differences (such as treatment of
•
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).
