| 1 | allocate_extent_clause Syntax: ALLOCATE EXTENT [ ( { SIZE size_clause | DATAFILE 'filename' | INSTANCE integer } ... ) ] For example, after the employees table is created, the size of the table is changed to 10 MB. 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 null. 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 inserting and deleting data, 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.
- Insert and delete data.
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, create a temporary tablespace tbs_temp_01. In the file specification clause of SQL statements, create a temporary database file templ01.dbf in the tablespace, which can be automatically extended, and allocate the tablespace to 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 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 supports logging_clause only in the CREATE TABLE, CREATE TABLE AS, and SELECT INTO statements.
For example, when a tablespace is created in GaussDB with the UNLOGGED constraint clause, 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, when creating table t1 and specifying PARALLEL 4 in a 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, when you run a SQL statement to create an index named tbl1_ind in the tbl1 table and set space usage PCTUSED of the index to 20% in the physical_attributes_clause of the statement, GaussDB reports a syntax error when executing the SQL statement. 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 supports physical_attributes_clause only in the CREATE TABLE and CREATE INDEX statements.
For example, if you attempt 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 attribute clause, an error is reported when GaussDB executes the SQL 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, create a temporary database file templ01.dbf in the tablespace, set the initial size to 5 MB for SIZE in a SQL statement, enable automatic extension, and allocate the tablespace 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, the STORAGE clause specifies storage parameters. In GaussDB, the WITH clause specifies storage parameters.
Example: To create the my_tab1 table in the Oracle database, set the initial size of the table to 10 MB for STORAGE, 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 the GaussDB database and set the storage engine type to Ustore for STORAGE, 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 |
| 10 | Nested aggregate functions For example, create a table named revenue using the sales_amount column of the sales table and the nested aggregate functions MIN() and SUM(). 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 | - |
| 11 | Delete the system schema. Syntax: DROP USER schema_name CASCADE; For example, delete the SYS schema as the SYS user. SQL> DROP USER SYS;
DROP USER SYS
*
ERROR at line 1:
ORA-28050: specified user or role cannot be dropped | Supported | - |
