Common SQL DDL Clauses

Updated on 2024-12-06 GMT+08:00

View PDF

This chapter describes common compatible SQL DDL clauses, including allocate_extent_clause, constraint, deallocate_unused_clause, file_specification, logging_clause, parallel_clause, physical_attributes_clause, size_clause, storage_clause, and aggregate function nesting. For details, see Table 1.


ALLOCATE EXTENT   [ ( { SIZE size_clause       | DATAFILE 'filename'       | INSTANCE integer       } ...     )   ]
{ inline_constraint | out_of_line_constraint | inline_ref_constraint | out_of_line_ref_constraint }
DEALLOCATE UNUSED [ KEEP size_clause ]
{[ '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 ]}
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));
               ^
{ NOPARALLEL | PARALLEL [ integer ] }
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;
                                             ^
integer [ K | M | G | T | P | E ]
SQL> CREATE TABLE my_tab1 (id NUMBER(10) PRIMARY KEY, name VARCHAR2(50)) STORAGE (INITIAL 10M NEXT 5M);

Table created.
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.

**Table 1** Common SQL DDL clauses
No.	Oracle Database	GaussDB	Difference
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.	-

Previous topic: Conditions

Next topic: SQL Queries and Subqueries

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot