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.
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
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.