This topic describes how to use CREATE TABLE AS SELECT (CTAS) to write query results into a new table while creating it, and how to combine it with the direct load hint and default_load_mode.
Data sources can be internal tables, external tables, or SELECT statements containing FILES(...).
Direct load modes (full/incremental)
The CREATE TABLE AS SELECT statement specifies the direct load mode by setting the DIRECT() hint. If no hint is specified, the default behavior can be controlled by the default_load_mode parameter.
Syntax
CREATE /*+ [APPEND | DIRECT(need_sort,max_error,{'full'|'inc'|'inc_replace'})] parallel(N) */ TABLE table_name [AS] select_sentence
For more information about the CREATE TABLE AS SELECT syntax, see CREATE TABLE.
Parameter description:
Parameter |
Description |
|---|---|
| APPEND | DIRECT() | Use hints to enable the direct load feature.
NoticeWhen |
| parallel(N) | The degree of parallelism for loading data. This parameter is required and must be an integer greater than 1. |
Examples
Use direct load to import data from table tbl1 into other tables.
Create a table
tbl1and write test data into it.obclient [test]> CREATE TABLE tbl1(c1 int); obclient [test]> INSERT INTO tbl1 VALUES (1),(2),(3); obclient [test]> SELECT * FROM tbl1;Use
CREATE TABLE AS SELECTto import data intotbl2.Full mode:
obclient [test]> CREATE /*+ direct(true, 0, 'full') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;Incremental mode:
obclient [test]> CREATE /*+ direct(true, 0, 'inc') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1; obclient [test]> CREATE /*+ direct(true, 0, 'inc_replace') parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;(Optional) Use
APPEND, which is equivalent toDIRECT(true, 0):obclient [test]> CREATE /*+ append parallel(4) */ TABLE tbl2 AS SELECT * FROM tbl1;
If no hint is specified, you can control the default mode with the
default_load_modeparameter:obclient [test]> ALTER SYSTEM SET default_load_mode ='FULL_DIRECT_WRITE'; obclient [test]> CREATE TABLE tbl2 AS SELECT * FROM tbl1;or:
obclient [test]> ALTER SYSTEM SET default_load_mode ='INC_DIRECT_WRITE'; obclient [test]> CREATE TABLE tbl2 AS SELECT * FROM tbl1;