The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Superfast Table Copy
![[Deleted User]](https://us.v-cdn.net/6029397/uploads/defaultavatar/nD0LWW9MQTB29.jpg)
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!
1
Comments
wow . any export schema - all objects with data option to simply data copy option?
@sreeblr - Sorry, not sure I understand your question
Are you asking how to "super fast" copy all tables in a schema?
copy schema from one server to another do we have any "super fast schema copy. "
@sreeblr - Not that I am aware of
Usually its the network that's the bottle neck. There is a great discussion here that might help you: https://forum.vertica.com/discussion/comment/239767#Comment_239767
Basically zipping the data prior to sending it over the network will help make the copy "faster".