report HuaweiCloudDeveloper Posted on 2025/01/14 19:18:21 2.6k+ 0 0
[Abstract] A table is the basic unit of a database object. It is a two-dimensional table composed of rows and columns. A two-dimensional table is also called a relation. A relational database can also be called a two-dimensional table database. In a relational database, data operations and management are all centered around tables. There are seven types of GaussDB tables: ordinary tables, temporary tables, non-log tables, external tables, partitioned tables, column-stored tables, and materialized views. Each type of table has different business scenarios.
1 Problem phenomenon
A temporary table is a special type of table used to store temporary data that is short-lived during a session or transaction. It is usually used to store intermediate calculation results , temporary data processing , and step-by-step results of complex queries.
Advantages of temporary tables:
• Short-term data storage: used to store temporary data and will not occupy database storage space for a long time.
• Performance optimization: can be used to reduce the calculation time of complex queries and improve query performance.
• Convenient data processing: suitable for data operations that require multi-step processing.
2 Technical Background
GaussDB temporary tables are divided into global temporary kazakhstan mobile phone number list tables (global temp table) and local temporary tables (local temp table)
• Temporary tables only exist during a database session . Temporary tables created in a session will be automatically deleted when the session ends.
• Temporary tables are visible only to the current session. Multiple session connections to the database cannot access temporary tables created by each other ( except for global temp tables ).
• Since the life cycle of a temporary table starts when the table is created in a session and ends when the session ends, and ordinary temporary tables have session isolation, they are often used to store query results or process intermediate result data to avoid being reused by subqueries and complex queries.
• The metadata of global temporary tables is visible to all sessions and continues to exist after the session ends . However , user data, indexes, and statistics between sessions are isolated from each other, and each session can only see and modify the data submitted by itself. When creating a global temporary table, you can specify a schema with a prefix other than pg_temp_ . There are two modes for global temporary tables.