Updated on 2024-07-19 GMT+08:00

COLUMN STORE

The table orientation can be converted from ROW-STORE to COLUMN store using the WITH (ORIENTATION=COLUMN) in the CREATE TABLE statement. This feature can be enabled/disabled using the rowstoreToColumnstore configuration parameter.

Input: CREATE TABLE with change orientation to COLUMN STORE

1
2
3
4
5
6
7
CREATE MULTISET VOLATILE TABLE tab1
      ( c1 VARCHAR(30) CHARACTER SET UNICODE    
      , c2 DATE
      , ...
      )
 PRIMARY INDEX (c1, c2) 
 ON COMMIT PRESERVE ROWS;

Output:

1
2
3
4
5
6
7
CREATE LOCAL TEMPORARY TABLE tab1 
     ( c1 VARCHAR(30)  
     , c2 DATE   
     , ...
     ) WITH (ORIENTATION = COLUMN)
  ON COMMIT PRESERVE ROWS 
  DISTRIBUTE BY HASH (c1, c2);