Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> constraints and optimizer
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.
Thanks to any responders.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: becker.bill_at_marshfieldclinic.org 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-LReceived on Fri Mar 14 2003 - 14:19:41 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).