We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Superfast Table Copy — Vertica Forum

Superfast Table Copy

[Deleted User][Deleted User] Administrator
in Tips from the Team

This blog post was authored by Jim Knicely.

Very often we need to make a copy of a very large table in order to do some development or quality assurance type of duties.

Typically we’ll use a CREATE TABLE AS SELECT (CTAS) statement to make that copy.

Example:

dbadmin=> SELECT COUNT(*) FROM big_number_table;
   COUNT
------------
1000000000
(1 row)
Time: First fetch (1 row): 1383.065 ms. All rows formatted: 1383.127 ms

dbadmin=> CREATE TABLE big_number_table_copy AS SELECT * FROM big_number_table;
CREATE TABLE
Time: First fetch (0 rows): 393328.528 ms. All rows formatted: 393328.566 ms

But that took a very long time (~ 40 minutes)!

Fortunately Vertica has the lightweight, in-memory COPY_TABLE function!

Example:

dbadmin=> SELECT COPY_TABLE('big_number_table', 'big_number_table_copy_fast');
                                               COPY_TABLE
--------------------------------------------------------------------------------------------------------
Created table big_number_table_copy_fast.
Copied table big_number_table to big_number_table_copy_fast
(1 row)
Time: First fetch (1 row): 206.614 ms. All rows formatted: 206.664 ms

It made the same copy in less than one second. Wow!

Have fun!

Comments

Sign In or Register to comment.