Database Reference
In-Depth Information
EODA@ORA12CR1> select * from table(dbms_xplan.display(null,null,'BASIC'));
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| T |
-----------------------------------
It full-scanned the table. It had to full-scan the table in order to count the rows. This is due to the fact that in an
Oracle B*Tree index, index key entries that are entirely null are not made. That is, the index will not contain an entry
for any row in the table
T
, such that all of the columns in the index are null. Since
X
is allowed to be null
temporarily,
the optimizer has to assume that
X
might be null and therefore would not be in the index on
X
. Hence a count returned
from the index might be different (wrong) from a count against the table.
We can see that if
X
had a nondeferrable constraint placed on it, this limitation is removed; that is, column
X
is in
fact as good as column
Y
if
the
NOT NULL
constraint is not deferrable:
EODA@ORA12CR1> alter table t drop constraint x_not_null;
Table altered.
EODA@ORA12CR1> alter table t modify x constraint x_not_null not null;
Table altered.
EODA@ORA12CR1> explain plan for select count(*) from t;
Explained.
EODA@ORA12CR1> select * from table(dbms_xplan.display(null,null,'BASIC'));
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX FULL SCAN| T_IDX |
----------------------------------
So, the bottom line is, only use deferrable constraints where you have an identified need to use them. They
introduce subtle side effects that could cause differences in your physical implementation (non-unique vs. unique
indexes) or in your query plans—as just demonstrated!
Bad Transaction Habits
Many developers have some bad habits when it comes to transactions. I see this frequently with developers who have
worked with a database that “supports” but does not “promote” the use of transactions. For example, in Informix (by
default), Sybase, and SQL Server, you must explicitly
BEGIN
a transaction; otherwise, each individual statement is a
transaction all by itself. In a similar manner to the way in which Oracle wraps a
SAVEPOINT
around discrete statements,
these databases wrap a
BEGIN WORK
/
COMMIT
or
ROLLBACK
around each statement. This is because, in these databases,
locks are precious resources, and readers block writers and vice versa. In an attempt to increase concurrency, these
databases want you to make the transaction as short as possible—sometimes at the expense of data integrity.
Oracle takes the opposite approach. Transactions are always implicit, and there is no way to have an
“autocommit” unless an application implements it (see the “Using Autocommit” section later in this chapter for more
details). In Oracle, every transaction should be committed when it must and never before. Transactions should be as
