Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> 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
>
> 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).Received on Fri Mar 14 2003 - 15:29:25 CST