?
Solved

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

Posted on 2008-11-13
7
Medium Priority
?
957 Views
Last Modified: 2013-12-19
I need to duplicate a table, what is the fastest way?
0
Comment
Question by:U4enik
  • 3
  • 2
  • 2
7 Comments
 
LVL 10

Accepted Solution

by:
kukno earned 1000 total points
ID: 22947918
CREATE TABLE new_table AS SELECT * FROM old_table;
0
 

Author Comment

by:U4enik
ID: 22947931
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
 
LVL 10

Assisted Solution

by:kukno
kukno earned 1000 total points
ID: 22947981
O.K. I think you should explain why you want to copy the table?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:U4enik
ID: 22948139
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
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 1000 total points
ID: 22948159
How big is the table?
rows and size?
0
 

Author Comment

by:U4enik
ID: 22948198
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
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 1000 total points
ID: 22948242
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month15 days, 11 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question