RE: CTAS only recreate non-NULL contrainsts - WHY?
Date: Tue, 25 Mar 2008 13:07:13 -0400
Why should Oracle assume that just because the source table has a PK or even just an index defined on it that you would want the index on the new table? In many cases I have not wanted the existing indexes or constraints on the copy. If I need these objects I can use DDL to add them.
- Mark D Powell -- Phone (313) 592-5148
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Peter Teoh Sent: Tuesday, March 25, 2008 2:03 AM
To: Robert Freeman
Cc: Oracle-L Freelists
Subject: Re: CTAS only recreate non-NULL contrainsts - WHY?
Thank you Robert. So I guessed it will ALWAYS be true that NON-NULL constraints will never be violated, have to think about it :-).
But on the other hand, if CTAS is on only one table, can't Oracle be smart enough to recreate the index at the same time - if the columns included in the CTAS statement is a superset of the columns that make up the index of the table?
As a special case, if it is just a simple "create table as select * from mytable" then simply just recreate all the index and constraints. Will this lead to any problems?
On Tue, Mar 25, 2008 at 1:40 PM, Robert Freeman
> Well, think about it.
> If I do a CTAS which is a join between two tables, then which primary
key gets moved over? You can define the primary key in the CTAS command if you need to like this:
> create table newemp (empid primary key)
> as select empid from emp;
> With respect to FK's, the same logic applies.... you could be
creating rows that would violate the existing FK (say with an outer join).
> Robert G. Freeman
> Oracle Database 11g New Features (Oracle Press) Portable DBA: Oracle
> (Oracle Press) Oracle Database 10g New Features (Oracle Press)
> Oracle9i RMAN Backup and Recovery (Oracle Press) Oracle9i New Feature
> Blog: http://robertgfreeman.blogspot.com (Oracle Press)
> ----- Original Message ----
> From: Peter Teoh <htmldeveloper_at_gmail.com>
> To: Oracle-L Freelists <oracle-l_at_freelists.org>
> Sent: Monday, March 24, 2008 10:33:26 PM
> Subject: CTAS only recreate non-NULL contrainsts - WHY?
> It is known that CTAS only recreate the constraints if it is
> while others like primary key, foreign keys are all not created.
> Is there any reason why?
-- Regards, Peter Teoh -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 25 2008 - 12:07:13 CDT