Hi,
What is the fastest way to move data from one table to another in the same schema.
like move data from table A to table B.
No fo records=16 million.
Thanks
Sam
Printable View
Hi,
What is the fastest way to move data from one table to another in the same schema.
like move data from table A to table B.
No fo records=16 million.
Thanks
Sam
Same structure of table? Into an empty table or preserving the current rows of the target table? You need to keep the data in the old table still?
yes...and the target table is partitioned.
I need to insert records into 1 partition..which is empty right now..and need to preserve the data in the other partitions.
Structure is the same of both the tables.
Why do you just drop that empty table ( call table_b), then do
create table_b as select * from table_a. It will create a table_b with the same name you drop and the data that contains in table_a.
16Million records
i would suggest export - import.... using table export and
table import option only.
hope this helps...
Quote:
Originally Posted by aspdba
Quote:
Originally Posted by topgun
why do it the slow way?
its within the same database so that will always be slower than insert /*+append*/ into ...
thanks for the inputs sir davey23uk.... i am still new with Oracle DBA so i usually use exp/imp for big tables... but it seems that using SQL hints is also faster...
Quote:
Originally Posted by davey23uk
just think about it, import does normal inserts so why take the data out of the database just to put it back in.
The append hint generates no redo and goes right to the datafiles - nice and quick
If the tables have the same columns and the target table is partitioned, then a partition exchange would be possible. You'd have to have the same null attributes etc., but that ought to be easy to arrange
I think the fastest way would be the partition exchange that was suggested. Look in the documentation for the alter table exchange partition syntax. There are also some examples on asktom.
if you want to use the same tablespace as the tablespace used by your source table you may use exchange partition.
If your target partition should be on a different tablespace or if you want to use different storage parameters you may either do an
an alter table move and an exchange partition
or
an insert append
Insert / +append / does faster copy of data from one table to another. It might take within 2 min to insert 16M records. I am not sure about the partition stuff.
That totaly depends on the amount of data, of the available HW ressources, of the number of indexes...Quote:
Originally Posted by srt
Also using the "partition stuff" to move the data to the partitioned table would probably take less then 10 secondes if the indexes has not to be rebuilt and the source table is not partitioned (=> target partition is not subpartitioned).