Temporary tables

In OceanBase Database's MySQL mode, temporary tables are special session-level tables mainly used for storing intermediate data. Different sessions can create temporary tables with the same name, and temporary tables can have the same name as existing regular tables. However, you cannot create views based on temporary tables. When a temporary table and a regular table share the same name in a session, operations such as SHOW CREATE TABLE, DESC, and DML statements will apply to the temporary table, effectively hiding the regular table.

Usage restrictions

• Temporary table routing: Because temporary tables need to support load balancing like regular tables, by default, you cannot bind a temporary table to a specific machine. You may consider turning off load balancing if needed.

• OceanBase Database does not support writing to temporary tables created within the same transaction when using the Serializable isolation level.

• If a temporary table with the same name as a regular table exists in the current session, it is not recommended to use database-level statistics collection in that session, as the results may be unexpected.

• Temporary tables are cleaned up by background processes, and to minimize the impact of DDL operations on business workloads, the cleanup speed is limited. If your scenario involves heavy use of temporary tables, it is recommended to delete them manually in a timely manner.

• Temporary tables cannot be accessed via DBLink.

• When connecting to OBServer through ODP, you need to manually adjust the following parameters: server_protocol, client_session_id_version, and proxy_id.

  Here are the specific steps:

  • Run the following command to modify the communication protocol configuration for OBProxy:

    ALTER proxyconfig SET server_protocol = 'OceanBase 2.0';
    

  • Run the following command to set the client session ID generation algorithm to version 2:

    ALTER proxyconfig SET client_session_id_version = 2;
    

  • Run the following command to set the ODP ID to 1. If you have multiple ODPs, use different numbers for each to ensure client session IDs do not conflict:

    ALTER proxyconfig SET proxy_id = 1;
    

References

• Create a table
• CREATE TABLE