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 GTT?
A GTT stores data for a specific period of time, which can be the lifecycle of a transaction or session.
Column definitions for a GTT can be visible to all sessions. However, data in a GTT created during a session is visible only to that session and can be modified solely within it.
For example, you can create a GTT to store temporary data in a course selection scenario. During the session, course selection data is private to the session. When the student confirms the list of selected courses, the application moves course selection data from the GTT to a normal table. When the session ends, the database automatically deletes data in the GTT.
Create a GTT
You can execute the CREATE GLOBAL TEMPORARY TABLE statement to create a GTT. The ON COMMIT clause specifies whether to create the GTT at the transaction level or session level. The details are as follows:
ON COMMIT DELETE ROWS: specifies to create a transaction-level GTT where data is deleted upon the commit of the transaction.ON COMMIT PRESERVE ROWS: specifies to create a session-level GTT where data is deleted upon the end of the session.
You can create indexes on a GTT. Data in the index table of the GTT is also temporary and private to a session.
Examples
Example 1: Create a transaction-level GTT
Create a transaction-level GTT named
tbl1.obclient [SYS]> CREATE GLOBAL TEMPORARY TABLE tbl1(col1 INT) ON COMMIT DELETE ROWS;Insert a data record into
tbl1.obclient [SYS]> INSERT INTO tbl1 VALUES(1);Query the data in
tbl1.obclient [SYS]> SELECT * FROM tbl1;The return result is as follows:
+------+ | COL1 | +------+ | 1 | +------+ 1 row in setCommit the transaction.
obclient [SYS]> COMMIT;Query the data in
tbl1again.obclient [SYS]> SELECT * FROM tbl1;The return result is as follows:
Empty set
Example 2: Create a session-level GTT
Perform the following steps in Session 1:
Create a session-level GTT named
tbl2.obclient [SYS]> CREATE GLOBAL TEMPORARY TABLE tbl2(col1 INT) ON COMMIT PRESERVE ROWS;Insert a data record into
tbl2.obclient [SYS]> INSERT INTO tbl2 VALUES(1);Query the data in
tbl2.obclient [SYS]> SELECT * FROM tbl2;The return result is as follows:
+------+ | COL1 | +------+ | 1 | +------+ 1 row in setCommit the transaction.
obclient [SYS]> COMMIT;Query the data in
tbl2again.obclient [SYS]> SELECT * FROM tbl2;The return result is as follows:
+------+ | COL1 | +------+ | 1 | +------+ 1 row in set
Perform the following steps in Session 2 or after exiting Session 1 and reconnecting to the database:
Query the data in tbl2.
obclient [SYS]> SELECT * FROM tbl2;
The return result is as follows:
Empty set