OraFAQ Forum: SQL & PL/SQL » CREATE TABLE AS vs CREATE TABLE then INSERT

Home » SQL & PL/SQL » SQL & PL/SQL » CREATE TABLE AS vs CREATE TABLE then INSERT (PL/SQL Developer 10.0.5.1710)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CREATE TABLE AS vs CREATE TABLE then INSERT [message #626101]

Mon, 20 October 2014 14:33

techiesue83
Messages: 5
Registered: October 2014
Location: Pascagoula, MS

Junior Member

I apologize if this question has already been answered, but I did not find it in my search.

A little background before the question:

In order to add columns to or modify the storage attributes of an existing table and retain the data, we have historically renamed the table, recreated it with the new column and/or storage attributes, and inserted the data from the old into the new table. Some of our tables are very large and this procedure may take hours or even days to complete. Another issue is that either during the create or the insert, we sometimes receive errors that it could not allocate extents in the tablespace. We end up adding a lot of space in order to get the new table created and loaded, even though we will be dropping the temporarily renamed table.

I recently used a basic CREATE TABLE AS statement after using the above method and receiving the allocation error. By basic, I mean I did not include a storage clause. It worked without adding another table to the tablespace or increasing the size of the existing tables. It also ran in a fraction of the time the previous method normally takes. From the documentation I read, this does not maintain the storage attributes of the original table.

Questions: How is the space allocation handled with CREATE TABLE AS if storage attributes are not defined in the statement? Is the resultant table in a stable space allocation state, or should further analysis be done to ensure it is sufficient to support future growth? If I use this method to modify the initial extent and do specify storage attributes, is space allocated similarly to the CREATE/INSERT methodology? And, if so, would I lose the processing time advantage we realized?

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626103 is a reply to message #626101]

Mon, 20 October 2014 14:46

Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

How is the space allocation handled with CREATE TABLE AS if storage attributes are not defined in the statement?

By the default storage parameters of the tablespace and those defined in Oracle code and described in the documentation.

SQL> desc dba_tablespaces
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 TABLESPACE_NAME                  NOT NULL VARCHAR2(30)
 BLOCK_SIZE                       NOT NULL NUMBER
 INITIAL_EXTENT                            NUMBER
 NEXT_EXTENT                               NUMBER
 MIN_EXTENTS                      NOT NULL NUMBER
 MAX_EXTENTS                               NUMBER
 PCT_INCREASE                              NUMBER
 MIN_EXTLEN                                NUMBER
 STATUS                                    VARCHAR2(9 CHAR)
 CONTENTS                                  VARCHAR2(9 CHAR)
 LOGGING                                   VARCHAR2(9 CHAR)
 FORCE_LOGGING                             VARCHAR2(3 CHAR)
 EXTENT_MANAGEMENT                         VARCHAR2(10 CHAR)
 ALLOCATION_TYPE                           VARCHAR2(9 CHAR)
 PLUGGED_IN                                VARCHAR2(3 CHAR)
 SEGMENT_SPACE_MANAGEMENT                  VARCHAR2(6 CHAR)
 DEF_TAB_COMPRESSION                       VARCHAR2(8 CHAR)
 RETENTION                                 VARCHAR2(11 CHAR)
 BIGFILE                                   VARCHAR2(3 CHAR)

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626104 is a reply to message #626103]

Mon, 20 October 2014 15:01

techiesue83
Messages: 5
Registered: October 2014
Location: Pascagoula, MS

Junior Member

Thank you, Michel, for your quick response. Not being a DBA, I didn't think about the tablespace definition. Do you have any ideas about any of my other questions? I know they are somewhat theoretical. Can you explain why the CREATE TABLE AS seems to be so much more efficient?

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626105 is a reply to message #626104]

Mon, 20 October 2014 15:46

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Why do you rebuild the entire table when you have something as simple as a new column. Just add the column. Unless you have a default value, it will take about 10 seconds and your done. What your doing makes no sense.

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626106 is a reply to message #626105]

Mon, 20 October 2014 17:32

techiesue83
Messages: 5
Registered: October 2014
Location: Pascagoula, MS

Junior Member

I agree, Bill. However, the database we are maintaining is a replication of a COTS system. When the vendor modifies their table structures, our ETL team insists we maintain the columns in the same physical order. The only way to add a column, and for it to be inserted anywhere but the end, is to recreate the table with the columns in the desired sequence. I have tried, to no avail, to convince them it doesn't matter. So, my only alternative is to find the most efficient way in which to add columns and modify the initial extents.

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626110 is a reply to message #626106]

Tue, 21 October 2014 00:09

Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

The only way to add a column, and for it to be inserted anywhere but the end, is to recreate the table with the columns in the desired sequence.

It is not the only way, you can define a view upon the table.

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626117 is a reply to message #626106]

Tue, 21 October 2014 01:08

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

techiesue83

When the vendor modifies their table structures, our ETL team insists we maintain the columns in the same physical order.

I sincerely hope that your ETL team's requirement is not based on the fact that they intesively (ab)use "SELECT *", such as

insert into some_table
select * 
from another_table

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626123 is a reply to message #626101]

Tue, 21 October 2014 01:55

John Watson
Messages: 8928
Registered: January 2010
Location: Global Village

Senior Member

The underlying implementation of CREATE TABLE AS... is to create a new table, copy the rows into it, do some data dictionary magic to rename the objects, then drop the original table. You can see this happening if you query dba_objects while the operation is in progress. So whatever you do, you will need at least double the storage space. It may however use direct I/O and other optimizations which perhaps your previous INSERT technique does not.

Do you have Enterprise Edition licences? If so, using DBMS_REDEFINITION will undoubtedly be a superior solution, in that downtime is minimized and all dependent objects and constraints maintained throughout.

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626142 is a reply to message #626123]

Tue, 21 October 2014 08:02

Bill B
Messages: 1971
Registered: December 2004

Senior Member

there is an easy way to do what you want. say you have a table called MYTABLE with the following columns COL1,COL2,COL3 and they want to add a new column between COL1 and COL2 called COL1A. Do the following.

rename MYTABLE to MYTABLE_REAL and add your column COL1A at the end.

Then you make a view called MYTABLE that is made up of the query

select COL1,COL1A,COL2,COL3 FROM MYTABLE_REAL;

The program will see the table MYTABLE in the correct order and updates, inserts, and deletes will work through the view with no performance hits.

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626143 is a reply to message #626123]

Tue, 21 October 2014 08:03

techiesue83
Messages: 5
Registered: October 2014
Location: Pascagoula, MS

Junior Member

@John Watson - Thank you. I will look into DBMS_REDEFINITION further. Especially if it maintains all column properties. I have discovered that CREATE TABLE AS does not retain default value definitions.

@Littlefoot - The reason the ETL team insists on the same physical order is that they do not want to have to search for columns in huge tables when they are creating mappings in Informatica. To have our replicated tables in a different physical order than the source application tables is, evidently, a huge impact on their development time. Don't get me going on that. Smile

@Michel Cadot - A view could be used as the source of an Informatica transformation, but ultimately, the physical table structure would be an issue when it came to mapping to the target table and they would have the same complaint noted above.

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626144 is a reply to message #626143]

Tue, 21 October 2014 08:09

Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Bill explained with more detailed what I mean by "view".
The view will just redefined the order of the columns and has the name of your current table which is renamed to avoid its direct access;
All privileges are granted on the view and not the table.

It is much easier to change a view definition than to copy a table, it requires (almost) no time and no space.

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626145 is a reply to message #626142]

Tue, 21 October 2014 08:10

techiesue83
Messages: 5
Registered: October 2014
Location: Pascagoula, MS

Junior Member

Thank you, Bill, for that suggestion. I didn't think one could use a view as the target of updates. If that is the case, then I take back my comment to Michel. I will do some testing.

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626146 is a reply to message #626143]

Tue, 21 October 2014 08:12

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Informatica....lord have mercy....you have my sympathies.

Report message to a moderator

Re: CREATE TABLE AS vs CREATE TABLE then INSERT [message #626155 is a reply to message #626143]

Tue, 21 October 2014 13:30

knw15pwr
Messages: 134
Registered: March 2010

Senior Member

techiesue83 wrote on Tue, 21 October 2014 18:33

The reason the ETL team insists on the same physical order is that they do not want to have to search for columns in huge tables when they are creating mappings in Informatica. To have our replicated tables in a different physical order than the source application tables is, evidently, a huge impact on their development time. Don't get me going on that.

Thats the dumbest thing ive heard. Informatica creates its own SELECT/INSERT query based on the columns defined. It never uses a "SELECT *". It sort of acts like a view in itself. The sequence of columns (called ports in informatica) can be altered to whatever is desired with just 3 clicks of the mouse button!

Report message to a moderator

Previous Topic:	What is wrong with this trigger?
Next Topic:	problem with case statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 16:44:32 CDT 2024