| 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 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. | - |
| 11 | Dropping a system schema Syntax: DROP USER schema_name CASCADE; For example, drop 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. | - |
