A little while ago I discussed one side-effect of transitive closure and predicate generation. Coincidentally, David Aldridge and Jeff Hunter have both come up with further examples of predicate generation – in their examples generated from check constraints – that can cause problems.
Constraints are a very good thing – they protect your database, and make sure that incorrect data cannot appear by accident, or through the back door; they can also give the optimizer more options for finding an efficient access path.
But – and it’s an important but – the optimizer and run-time engines are not yet handling generated predicates correctly.
Both Dave and Jeff have examples of a check constraint similar to : trunc(date_col) = date_col, when this is applied as a generated predicate, it introduces a 1% scale factor to the selectivity. (As a general rule, predicates like function(col) = const are given a 1% selectitivity, although in some special cases for built-in functions, the underlying column selectivity is used instead). The change in selectivity can, of course, make a big difference to the execution path.
But in Jeff’s case, his query returns 355,000 rows and applies the generated predicate (which must be true, because it started life as a check constraint) to every single row. As a consequence the query with the check constraint in place take 3.48 seconds; the query without the check constraint takes 0.53 seconds.
You can’t really do anything to work around this (at present) – but if you remenber to check the filter_predicates and access_predicates from the execution plan (using dbms_xplan is the easy option for this) you may get an early clue about why your queries are doing something a little odd, or taking some extra (unexpected) time.