• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 969
  • Last Modified:

What is the fastest way to copy data from one oracle table to another

I need to duplicate a table, what is the fastest way?
0
U4enik
Asked:
U4enik
  • 3
  • 2
  • 2
4 Solutions
 
kuknoCommented:
CREATE TABLE new_table AS SELECT * FROM old_table;
0
 
U4enikAuthor Commented:
Thanks, but sorry, I didn't tell that before, the old table doesn't have indexes, and the new one should have some.

And this is a batch job that should be executed once per day.

So is it rational to recreate table and indexes each time? Or is it better to create a table with indexes once and then just empty it and fill it?
0
 
kuknoCommented:
O.K. I think you should explain why you want to copy the table?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
U4enikAuthor Commented:
I need to copy it because I need to run a query on it that needs indexes and I can't set indexes on original table.
0
 
SujithData ArchitectCommented:
How big is the table?
rows and size?
0
 
U4enikAuthor Commented:
it is 600 000 rows, here is one row example

1 300 0002 FSEE0070000289      F0133867      N 43 2003 A1080501 000 KBUS EEK 0000 -542979,00 -542979,00
-2979,00      -542979,00 000000000000 0
0
 
SujithData ArchitectCommented:
Its relatively a small table.
I would go for having a second table, truncating and loading each time rather than creating a second table every time.

You may use APPEND hint to speed up the load.

insert /*+ APPEND */ into <table 2>
select * from <table 1>;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now