Oracle Performance Tuning Tips
Declare NOT NULL constraints
People sometimes do not bother to define columns as NOT NULL in the data dictionary,
even though these columns should not contain nulls,
and indeed never do contain nulls because the application ensures that a value is always supplied.
You may think that this is a matter of indifference, but it is not.
The optimizer sometimes needs to know that a column is not nullable,
and without that knowledge it is constrained to choose a less than optimal execution plan.
The optimizer is being improved with every Oracle release,
and it is highly likely that there will be even more optimizer tricks in future versions
that will be enabled or disabled by the presence of NOT NULL constraints.
Therefore, it is advisable to make NOT NULL constrains explicit in the data dictionary now,
even if you do not envisage any immediate benefit to query execution performance due to the above three factors.
An index on a nullable column cannot be used to drive access to a table
unless the query contains one or more predicates against that column that exclude null values.
Of course, it is not normally desirable to use an index based access path unless the query contains such predicates,
but there are important exceptions.
For example, if a full table scan would otherwise be required against the table
and the query can be satisfied by a fast full scan against the index,
then the latter plan will normally prove more efficient.
Another important case is sort elimination.
If the best provisional execution plan requires a sort operation
that could be eliminated by choosing a possibly unqualified index range scan earlier in the execution plan,
then the optimizer will cost that alternative and make a decision based on total query execution cost
despite that the index range scan might be a relatively expensive single table access path.
This form of sort elimination is particularly attractive to the optimizer if the database block size is large
But, of course, it is not available to the optimizer
unless it is known that no rows with unindexed null values might be missed by the index range scan.
NOT IN subqueries cannot be unnested as anti-joins unless there are NOT NULL constraints on the join columns,
or equivalent predicates in the query.
This restriction is required because if the data were to contain nulls, the semantics of a NOT IN subquery would differ from those of an anti-join.
For an anti-join the subquery is unnested and instantiated once in a temporary segment
and then joined to the parent query block with inverse join semantics.
That is, rows from the parent query block are returned if and only if there is no row of the instantiated subquery to which they can be equi-joined.
If the instantiated subquery could contain null join keys, then parent rows that could not be equi-joined to any of the known values would be returned,
whereas the semantics of a NOT IN predicate are that it should have the truth value of UNKNOWN for such rows,
and that they should therefore not be returned.
NOT NULL constraints also serve to guarantee that certain materialized view joins are lossless,
and thus to validate join compatibility when considering using the materialized view to rewrite queries that do not include that join.
There is a simple script on the Ixora web site,
that can identify columns that appear to be good candidates for NOT NULL constraints.