5. Populating Tables : Bulk Copying : Example: Perform a Bulk Copy to Create a Hash Table
 
Share this page                  
Example: Perform a Bulk Copy to Create a Hash Table
The following sequence of statements allows a bulk copy to be performed. This example creates a hash table:
CREATE TABLE tmp1
    (col1 INTERGER NOT NULL, 
    col2 CHAR(25))
    WITH NOJOURNALING;
MODIFY tmp1 TO hash;
COPY tmp1() FROM 'tmp1.saved' 
    WITH ROW_ESTIMATE = 10000, 
    MAXPAGES = 1000, 
    ALLOCATION = 1000;
Bulk copy is chosen for the copy because the table is not journaled (it is created with nojournaling), it has no indexes (none have yet been created), and the table has under 18 pages (it is a newly created table that has not yet been populated with data).
The MODIFY...TO HASH operation is quick because the table is empty at this point. The ROW_ESTIMATE parameter allows a more efficient sort than if the default estimate (of 0 rows) is used. Additionally, for the hash table, ROW_ESTIMATE enables the number of hash buckets to be calculated efficiently. This calculation uses the row width (set by the CREATE TABLE statement), ROW_ESTIMATE , MAXPAGES, and the (default) FILLFACTOR. The COPY statement includes an ALLOCATION clause to preallocate disk space for the table to grow, increasing efficiency of later row additions.