Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

constraints and optimizer

From: <becker.bill_at_marshfieldclinic.org>
Date: Fri, 14 Mar 2003 12:19:41 -0800
Message-ID: <F001.0056AC27.20030314121941@fatcity.com>

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-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 - 14:19:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US