|
1 |
allocate_extent_clause
Syntax:
ALLOCATE EXTENT [ ( { SIZE size_clause | DATAFILE 'filename' | INSTANCE integer } ... ) ]
For example, after the employees table is created, change the allocated extent size of the table to 10M.
SQL> CREATE TABLE employees(EMPLOYEE_ID NUMBER(38), JOB_ID NUMBER(38), SALARY NUMBER(38), LAST_NAME VARCHAR2(16));
Table created.
SQL> ALTER TABLE employees ALLOCATE EXTENT (SIZE 10M);
Table altered.
|
Not supported. |
- |
|
2 |
constraint
Syntax:
{ inline_constraint | out_of_line_constraint | inline_ref_constraint | out_of_line_ref_constraint }
For example, when you create the staff table, the ID and NAME columns specified in the constraint clause cannot be empty.
SQL> CREATE TABLE staff(ID INT NOT NULL, NAME char(8) NOT NULL, AGE INT, ADDRESS CHAR(50), SALARY REAL);
Table created.
|
Supported. |
- |
|
3 |
deallocate_unused_clause
Syntax:
DEALLOCATE UNUSED [ KEEP size_clause ]
For example, after creating the employees table and performing some INSERT and DELETE operations, you want to use the deallocate_unused_clause to release the unused space of the employees table.
SQL> CREATE TABLE employees(EMPLOYEE_ID NUMBER(38), JOB_ID NUMBER(38), SALARY NUMBER(38), LAST_NAME VARCHAR2(16));
Table created.
- Perform some INSERT and DELETE operations.
SQL> ALTER TABLE employees DEALLOCATE UNUSED;
Table altered.
|
Not supported. |
- |
|
4 |
file_specification
Syntax:
{[ 'filename' | 'ASM_filename' ] [ SIZE size_clause ] [ REUSE ] [ autoextend_clause ]}
|
{[ 'filename | ASM_filename' | ('filename | ASM_filename' [, 'filename | ASM_filename' ]...) ] [ SIZE size_clause ] [ BLOCKSIZE size_clause [ REUSE ]}
For example, to create a temporary tablespace tbs_temp_01, the file_specification clause of the SQL statement specifies that a temporary database file templ01.dbf is created in the tablespace. The tablespace can be automatically expanded and allocated to the tablespace group tbs_grp_01.
SQL> CREATE TEMPORARY TABLESPACE tbs_temp_01 TEMPFILE 'temp01.dbf' AUTOEXTEND ON TABLESPACE GROUP tbs_grp_01;
Tablespace created.
|
Not supported. |
- |
|
5 |
logging_clause
Syntax:
{ LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING } |
Partially supported, with differences. |
- GaussDB does not support the LOGGING and FILESYSTEM_LIKE_LOGGING constraint clauses.
Example:
When a table is created in GaussDB with the LOGGING constraint clause, a syntax error is reported.
gaussdb=# CREATE LOGGING TABLE my_tab(id int, name char(16));
ERROR: syntax error at or near "LOGGING"
LINE 1: CREATE LOGGING TABLE my_tab(id int, name char(16));
^
When a table is created in GaussDB with the FILESYSTEM_LIKE_LOGGING constraint clause, a syntax error is reported.
gaussdb=# CREATE FILESYSTEM_LIKE_LOGGING TABLE my_tab(id int, name char(16));
ERROR: syntax error at or near "FILESYSTEM_LIKE_LOGGING"
LINE 1: CREATE FILESYSTEM_LIKE_LOGGING TABLE my_tab(id int, name cha...
^
- GaussDB supports only table-level UNLOGGED constraints and does not support column-level UNLOGGED constraints.
For example, when a table is created in GaussDB with the column-level UNLOGGED constraint clause, a syntax error is reported.
gaussdb=# CREATE UNLOGGED TABLE my_tab(id int UNLOGGED, name char(16));
ERROR: syntax error at or near "UNLOGGED"
LINE 1: CREATE UNLOGGED TABLE my_tab(id int UNLOGGED, name char(16))...
^
- GaussDB uses logging clauses only in the CREATE TABLE, CREATE TABLE AS, and SELECT INTO statements.
For example, when a TABLESPACE statement with the UNLOGGED constraint clause is created in GaussDB, a syntax error is reported.
gaussdb=# CREATE UNLOGGED TABLESPACE tbs1 RELATIVE LOCATION 'tablespace1/tablespace_1';
ERROR: syntax error at or near "TABLESPACE"
LINE 1: CREATE UNLOGGED TABLESPACE tbs1 RELATIVE LOCATION 'tablespac...
^
|
|
6 |
parallel_clause
Syntax:
{ NOPARALLEL | PARALLEL [ integer ] }
For example, if you create table t1 and specify PARALLEL 4 in the parallel_clause, a maximum of four parallel processes can be used to query and update table t1.
SQL> CREATE TABLE t1 (id NUMBER, name VARCHAR2(50)) PARALLEL 4;
Table created.
|
Not supported. |
- |
|
7 |
physical_attributes_clause
Syntax:
[ { PCTFREE integer | PCTUSED integer | INITRANS integer | storage_clause }... ] |
Partially supported, with differences. |
- GaussDB does not support PCTUSED.
For example, if you run an SQL statement to create the tbl1_ind index in the tbl1 table and set the space usage PCTUSED of the index to 20% in the physical_attributes_clause of the statement, an error is reported when the SQL statement is executed in GaussDB.
gaussdb=# CREATE INDEX tbl1_ind ON tbl1 (name) PCTUSED 20;
ERROR: syntax error at or near "PCTUSED"
LINE 1: CREATE INDEX tbl1_ind ON tbl1 (name) PCTUSED 20;
^
- GaussDB uses physical_attributes_clause only in the CREATE TABLE and CREATE INDEX statements.
For example, if you run an SQL statement to obtain data from the tbl1 table, create the materialized view tbl1_mv, and set the number of initial transactions of the view to 30 in the physical_attributes_clause, an error is reported when GaussDB executes the statement.
gaussdb=# CREATE MATERIALIZED VIEW tbl1_mv INITRANS 30 as select * from tbl1;
ERROR: syntax error at or near "INITRANS"
LINE 1: CREATE MATERIALIZED VIEW tbl1_mv INITRANS 30 as select * fro...
^
|
|
8 |
size_clause
Syntax:
integer [ K | M | G | T | P | E ]
For example, create a temporary tablespace tbs_temp_01 and a temporary database file templ01.dbf in the tablespace. The initial size of the tablespace is 5M as specified by the size_clause in the SQL statement, which can be automatically expanded. The tablespace can be allocated to the tablespace group tbs_grp_01.
SQL> CREATE TEMPORARY TABLESPACE tbs_temp_01 TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON TABLESPACE GROUP tbs_grp_01;
Tablespace created.
|
Not supported. |
- |
|
9 |
storage_clause
Syntax:
STORAGE ({ INITIAL size_clause | NEXT size_clause | MINEXTENTS integer | MAXEXTENTS { integer | UNLIMITED } | maxsize_clause | PCTINCREASE integer | FREELISTS integer | FREELIST GROUPS integer | OPTIMAL [ size_clause | NULL ] | BUFFER_POOL { KEEP | RECYCLE | DEFAULT } | FLASH_CACHE { KEEP | NONE | DEFAULT } | ( CELL_FLASH_CACHE ( KEEP | NONE | DEFAULT ) ) | ENCRYPT } ... ) |
Partially supported, with differences. |
- In Oracle Database, storage parameters are specified by the STORAGE clause. In GaussDB, storage parameters are specified by the WITH clause.
Example:
To create the my_tab1 table in the Oracle Database, set the initial size of the table to 10M in the storage_clause, and add 5 MB each time when more space is required, run the following SQL statement:
SQL> CREATE TABLE my_tab1 (id NUMBER(10) PRIMARY KEY, name VARCHAR2(50)) STORAGE (INITIAL 10M NEXT 5M);
Table created.
To create the my_tab2 table in GaussDB and set the storage engine type to ustore in the storage_clause, run the following SQL statement:
gaussdb=# CREATE TABLE my_tab2 (id NUMBER(10) PRIMARY KEY, name VARCHAR2(50)) with (storage_type=ustore);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "my_tab2_pkey" for table "my_tab2"
CREATE TABLE
- Optional storage parameters in GaussDB are greatly different from those in Oracle Database. For details, see the GaussDB parameter description in "SQL Reference > SQL Syntax > C > CREATE TABLE" in Developer Guide. WITH ({storage_parameter = value} [, ...]) describes the storage parameters supported by the CREATE TABLE statement.
|
|
10 |
Aggregate function nesting
For example, create the revenue table generated by nesting the aggregate functions MIN() and SUM() in the sales_amount column of the sales table.
SQL> CREATE TABLE sales(ID INT, SALES_AMOUNT INT);
Table created.
SQL> INSERT INTO sales VALUES(1, 100);
1 row created.
SQL> INSERT INTO sales VALUES (3, 200);
1 row created.
SQL> CREATE TABLE revenue as SELECT SUM(MIN(sales_amount)) as total from sales group by sales_amount;
Table created.
|
Supported. |
- |
