Predicate Problems

Whenever you look at an execution plan, you must look at the predicate section (introduced in 9i) before you start trying to work out what’s gone wrong. Here’s an example (stripped down to the basic issue from a more complex query) that shows the importance of this check.

Oracle always tries to transform your query before optimizing it. One of the transformation steps involves rewriting your predicates. This step is allowed to introduce new predicates from your table constraints, and may also rearrange your predicates through transitive closure.

So here’s a little bug from 10.2.0.1 and 9.2.0.6, fixed in 10.2.0.3 and 9.2.0.8, where Oracle turns constraints into predicates then invokes transitive closure – and introduces redundant work into the execution path as a consequence.

rem
rem     Script:         transitivity_joke_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Apr 2008
rem

create table t1
as
with generator as (
        select  --+ materialize
                rownum id
        from    all_objects
                where rownum <= 3000  -- > comment to avoid wordpress format issue
)
select
        rownum                  id,
        mod(rownum,10)          n1,
        mod(rownum,10)          n2,
        2 * mod(rownum,10)      n3,
        lpad(rownum,10,'0')     vc1,
        rpad('x',100)           padding
from
        generator v1,
        generator v2
where
        rownum <= 10000  --> comment to avoid wordpress format issue
;

alter table t1 add constraint t1_pk primary key(id);

alter table t1 modify n1 not null;
alter table t1 modify n2 not null;
alter table t1 modify n3 not null;

alter table t1 add constraint t1_ck_n3 check (n3 >= 0);
alter table t1 add constraint t1_sum check (n1 + n2 = n3);

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                estimate_percent => 100,
                block_sample     => true,
                method_opt       => 'for all columns size 1'
        );
end;
/

The table, stripped to a minimum, represents a product table with the requirement embedded in the database that: “quantity_on_hand + quantity _reserved = quantity _total”.

explain plan for
select
        count(*)
from
        t1
where
        id < 100  -- > comment to avoid wordpress format issue
;

select * from table(dbms_xplan.display);

The query isn’t a realistic one, but it demonstrates an important point.
If you look at the query, and check the code that created the table you will see that the optimizer should be able to execute this query without visiting the table. You might also expect the optimizer to work out correctly that the number of rows returned would be about 100. So why does the plan look like this (in 9.2.0.6 and 10.2.0.1) ?

------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |     1 |    10 |     4 |
|   1 |  SORT AGGREGATE                |       |     1 |    10 |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID  | T1    |     5 |    50 |     4 |
|*  3 |    INDEX RANGE SCAN            | T1_PK |    99 |       |     2 |
------------------------------------------------------------------------

Line 2 says we’re going to visit the table, and the number of rows returned is going to be five !
When you check the predicates, you can see what’s gone wrong. (Notice that lines 2 and 3 both have asterisks to show that there are related predicates in the predicate section of the output).

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N1"+"N2">=0)
   3 - access("ID"<100)

We have to go to the table to resolve the predicate “n1 + n2 >= 0”, and the predicted cardinality has dropped from 99 to 5 because that predicate (since it involves a “non-trivial function” applied to the columns) has been given the fixed “unknown range” selectivity of 5%. (In this case, the virtual column technology introduced in Oracle 11g would have helped correct the cardinality error.)
But where did the spurious predicate come from ? The optimizer has pulled the two constraints from the table – and then used transitive closure to generate an equivalent, but irrelevant, predicate.

if   n1 + n2 = n3
and  n3 >= 0
then n1 + n2 >= 0

Sometimes the optimizer can be too clever for its own good.
I don’t know if this ever appeared as a published bug – but the execution plans for 9.2.0.8 and 10.2.0.3 show the expected index-only access.

Always check the predicate section of the execution plan.