Re: constraints and optimizer

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 www.dbazine.com
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 .

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

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

(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd

____Denmark May 21-23rd

____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 14 March 2003 20:19

>
> Hello,
>
> Env: Oracle 9.2.0.2.0 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
transportable
> 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
present,
> 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
I
> would pass this along, hoping some kind soul(s) will provide the
> benefit of their experience.
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).