Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: constraints and optimizer

Re: constraints and optimizer

From: Jonathan Lewis <>
Date: Fri, 14 Mar 2003 13:29:25 -0800
Message-ID: <>

All recent versions of Oracle have used
unique, pk and not-null constraints to
help optimise queries - but I don't suppose you were thinking of those in particular.

In general Oracle did not make use of
check constraints except for partition
views, but Oracle 9 can now make very
good use of check constraints to help
optimise queries.

There is an example on
by Vadim Tropashko demonstrating that
Oracle can combine the constraint

    col_X = upper(col_X)
with the predicate

    upper(col_X) = 'abc'
to infer the predicate

    col_X = 'abc'
and so be able to use an index on col_X .


Jonathan Lewis

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see )

____UK_______April 8th
____UK_______April 22nd

____Denmark May 21-23rd

____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ

> Hello,
> Env: Oracle on Solaris 9
> I suspect this is a foolish question, but I will ask anyway:
> (It's Friday; my brain stops working after Wednesday)
> How much does the presence of constraints influence the optimizer,
> if the indexes are present?
> We are developing a method for transporting a large volume of data
> between a staging instance and a query instance of Oracle, using
> tablespaces (tts). When the tts export uses CONSTRAINTS=Y, the
> subsequent tts import takes about 4 hours; when the tts export
> excludes constraints, it takes about 1 hour. We prefer the 1 hour.
> I know we can do the constraints in a separate step and create them
> as ENABLE NOVALIDATE to save time, but the question was posed:
> Do we need them at all in an instance that will receive no updates,
> only queries, if the indexes exist?
> My first thought was, yes, the optimizer uses them, but I'm not sure
> how much value they add if the indexes exist. Is the optimizer MUCH
> more likely to make an intelligent choice if the constraints are
> or is the presence of an index the major deciding factor?
> We do intend to do some testing with in-house queries, but I thought
> would pass this along, hoping some kind soul(s) will provide the
> benefit of their experience.

Please see the official ORACLE-L FAQ:
Author: Jonathan Lewis

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Mar 14 2003 - 15:29:25 CST

Original text of this message