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!
About the Author
Soniya Shah
Information Developer
Currently, a first year law student with a background in science and technology. Experienced technical writer, with specializations in software documentation, big data, blog development, and website development. I build user-centered content to communicate complex and technical information more easily.
I used to work for Vertica full time for about 3 years. I still work at Vertica part time while going to law school.
Update: Soniya is now doing her law internship, and no longer working at Vertica. Good luck, Soniya!
