Database Reference
In-Depth Information
EODA@ORA12CR1> insert into t(x,y,z)
2 select rownum, rownum, rpad('x',30,'x')
3 from all_users;
45 rows created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
In this example, column
X
is created such that when you
COMMIT
,
X
will not be null. However, during your
transaction
X
is allowed to be null since the constraint is deferrable. Column
Y
, on the other hand, is always
NOT NULL
.
Let's say you were to index column
Y
:
EODA@ORA12CR1> create index t_idx on t(y);
Index created.
And you then ran a query that could make use of this index on
Y
—but only if
Y
is
NOT NULL
, as in following query:
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 |
----------------------------------
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.
