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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
