John Liva John Liva - 3 months ago 7
SQL Question

What is the most efficient way of creating a copy of a table with data and no constraints in Oracle?

What is the most efficient way of creating a copy of a table with data and no constraints (Primary key and foreign) in Oracle? some thing similar to the below query.

CREATE TABLE new_table
AS (SELECT * FROM old_table);

It's fine if we need to drop the constraints manually after copying but the creation of copy should be quick.

Please advise.

Answer

Using a CREATE TABLE AS SELECT statement the way you have it now is probably the most efficient way to do it. If not, it's pretty close.

It doesn't create constraints (apart from not null constraints) or indexes, so you have to create them manually after the operation completes.

You can specify that the operation should be parallelized by using the parallel keyword, though I believe that the feature is only available in the Enterprise Edition. Example:

create table new_table 
parallel
as
select * from old_table;

It's even possible to specify the number of threads to use by adding an integer parameter right after the parallel keyword. But, by default, it parallelizes according to the available CPUs on the server.

It is also possible to make the operation even faster by avoiding redo log generation. This is done by specifying the nologging keyword:

create table new_table 
parallel
nologging
as
select * from old_table;

However, because no redo log is generated, the operation is unrecoverable. So, if you're going to use that, you should consider backing up the database immediately after the operation completes. I would personally not use this option unless that extra performance is critical for some reason.

For more information on how to use the additional options with the create table as select statement, see the documentation: CREATE TABLE.

Comments