A temporary table stores data that exists only during a transaction or session. This topic describes how to create a global temporary table (GTT) in OceanBase Database.
What is a global temporary table?
A global temporary table stores data for a specific period of time, which can be the lifecycle of a transaction or session.
A global temporary table can be visible to all sessions. However, data in the global temporary table is private to a session, and the session can access and modify only its own data.
For example, a course schedule application enables a college student to create semester schedules. Each row in the GTT represents a course schedule. During the session, the course schedule data is private to the session. When the student confirms a course schedule, the application moves the row of the selected schedule to a normal table. When the session ends, the database automatically deletes data in the GTT.
Create a global temporary table
You can create a global temporary table using the CREATE GLOBAL TEMPORARY TABLE statement. The ON COMMIT clause specifies whether to create a transaction- or session-level temporary table, as detailed below:
ON COMMIT DELETE ROWS: Transaction-level temporary table, where data is deleted upon transaction commit.ON COMMIT PRESERVE ROWS: Session-level temporary table, where data is deleted at the end of the session.
Global temporary tables can have indexes created on them. The index data for global temporary tables is also temporary and is private to the session.
Examples
Example 1: Create a transaction-level temporary table
Create a transaction-level temporary table
tbl1.obclient [SYS]> CREATE GLOBAL TEMPORARY TABLE tbl1(col1 INT) ON COMMIT DELETE ROWS;Insert a row into the temporary table
tbl1.obclient [SYS]> INSERT INTO tbl1 VALUES(1);Query the data in the temporary table
tbl1.obclient [SYS]> SELECT * FROM tbl1;The result is as follows:
+------+ | COL1 | +------+ | 1 | +------+ 1 row in setCommit the transaction.
obclient [SYS]> COMMIT;Query the data in the temporary table
tbl1again.obclient [SYS]> SELECT * FROM tbl1;The result is as follows:
Empty set
Example 2: Create a session-Level temporary table
In Session 1, perform the following operations:
Create a session-level temporary table
tbl2.obclient [SYS]> CREATE GLOBAL TEMPORARY TABLE tbl2(col1 INT) ON COMMIT PRESERVE ROWS;Insert a row into the temporary table
tbl2.obclient [SYS]> INSERT INTO tbl2 VALUES(1);Query the data in the temporary table
tbl2.obclient [SYS]> SELECT * FROM tbl2;The result is as follows:
+------+ | COL1 | +------+ | 1 | +------+ 1 row in setCommit the transaction.
obclient [SYS]> COMMIT;Query the data in the temporary table
tbl2again.obclient [SYS]> SELECT * FROM tbl2;The result is as follows:
+------+ | COL1 | +------+ | 1 | +------+ 1 row in set
In Session 2, perform the following operations. You can also perform the operations after exiting Session 1 and reconnecting to the database.
Query the data in the temporary table tbl2.
obclient [SYS]> SELECT * FROM tbl2;
The result is as follows:
Empty set