You can use the INSERT-SELECT statement to add data to an Object File System table by retrieving the new row data from another table. See SQL Data Manipulation Language for details of syntax and usages of the INSERT-SELECT statement.
You can use ORDER BY to group similar values into objects; therefore, fewer objects need to be read at query time. For details about the ORDER BY clause, see CREATE TABLE with STORAGE Option Usage.
- An ORDER BY clause defined for an Object File System table specifies on what columns the data is sorted for an INSERT-SELECT into the Object File System table. The source spool of an INSERT-SELECT is sorted and is distributed based on the value range of the order by columns. This value-distributed spool is then used by the AMPs to build data objects. Each AMP processes its rows from the spool to construct new data objects at 16MB boundaries. The last object created by an AMP may be smaller and data from a subsequent INSERT-SELECT is not merged into this object. This only makes sure data is sorted locally within an object. If multiple columns are specified, data is sorted on the first column at the top level and then on the second column and so on.
- If the target Object File System table has an ORDER BY clause defined and the INSERT-SELECT statement specifies a LOCAL ORDER BY clause, the source is spooled and sorted locally on the LOCAL ORDER BY columns first and then value-range distributed based on the table’s ORDER BY columns.
- If an ORDER BY clause is not defined for an Object File System table, the source spool is not sorted and distributed by value ranges for an INSERT-SELECT into the Object File System table.
A large number of rows must be inserted for each participating AMP by an INSERT-SELECT to create reasonably sized large objects for the table. If not, there might be a potential impact on read performance.