Oracle Magazine - May/June 2011

constraints to be DEFERRABLE, the optimizer will start treating the column as if it supported NULLs—because it, in fact, does support NULLs during your transaction.

To demonstrate this, suppose you have
a table T with the columns and data shown
in Listing 1. In this example, column X is
created such that when you do a COMMI T,
X will not be NULL. However, during your
transaction, X is allowed to be NULL, because
the constraint is DEFERRABLE. Column Y, on
the other hand, is always NOT NULL. If you
were to index column Y—
—and then run a query to count rows that
could make use of this index on Y, but only if
Y is NOT NULL, as in the following query—

SQL> create index t_idx on t(y); Index created.

Code Listing 4: The preferred bulk stored procedure

create or replace procedure bulk
as
SQL> select count(*) from t;

cursor c is select rowid rid, object_name
from t t_bulk;

l_rids ridArray; l_onames onameArray;

N number := 100;
begin
open c;
loop
fetch c bulk collect
into l_rids, l_onames limit N;
for i in 1 .. l_rids.count
loop
l_onames(i) := substr(l_onames(i), 2)
||substr(l_onames(i), 1, 1);
end loop;
forall i in 1 .. l_rids.count
update t
set object_name = l_onames(i)
where rowid = l_rids(i);
exit when c%notfound;
end loop;
close c;
end;

Code Listing 5: TKPROF for slow_by_slow and bulk procedures Slow-by-slow processing in the slow_by_slow procedure

Bulk processing in the bulk procedure

UPDATE T SET OBJECT_NAME = :B1 WHERE ROWID = :B2

call count cpu elapsed disk query current rows —————————— ———————— ———————————— ——————————————— ——————————————— —————————————— ——————————————— —————————————— Parse 1 0.00 0.00 0 0 0 0 Execute 719 12.83 13.77 0 71853 74185 71825 Fetch 0 0.00 0.00 0 0 0 0 —————————— ———————— ———————————— ——————————————— ——————————————— —————————————— ——————————————— —————————————— total 720 12.83 13.77 0 71853 74185 71825

—you would be happy to see that the optimizer chose to use the small index on Y to count the rows rather than to do a full scan of the entire table T. However, if you dropped that index and indexed column X instead—

SQL> drop index t_idx; Index dropped.

SQL> create index t_idx on t(x); Index created.

—and then ran the query to count the rows once more, you would discover that the database does not and, in fact, cannot use your index:

SQL> select count(*) from t;

———————————————————————————————————————————————— |Id| Operation | Name| Rows| ———————————————————————————————————————————————— | 0| SELECT STATEMENT | | 1| | 1| SORT AGGREGATE | | 1| | 2| TABLE ACCESS FULL| T | 45| ————————————————————————————————————————————————

The query did a full scan of the table in order to count the rows. This is because in an Oracle Database B-tree index, the index will not contain an entry for any row in the table such that all of the columns in the index are NULL. Given that 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 from (wrong in terms of) a count against the table.