Transactions - Oracle Database Transactions and Locking Revealed - page 91

You would be happy to see the optimizer chose to use the small index on Y to count the rows rather than to
full-scan the entire table T . However, let's say that you drop that index and index column X instead:
EODA@ORA12CR1> drop index t_idx;
Index dropped.
EODA@ORA12CR1> create index t_idx on t(x);
Index created.
And you then ran the query to count the rows once more, you would discover that the database does not, in fact
cannot, use your index:
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 | 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 |
----------------------------------