本文详述 OceanBase 数据库 Oracle 租户在创建全局临时表时会自动创建分区的原因。
OceanBase 数据库 Oracle 租户在创建全局临时表后自动分区的示例。
CREATE GLOBAL TEMPORARY TABLE test_global_temp1(id number);
obclient [LX]> select OWNER,OBJECT_NAME,count(*) from dba_objects where OBJECT_TYPE='TABLE PARTITION' and owner='LX' group by OWNER,OBJECT_NAME order by OBJECT_NAME;
+-------+-------------------+----------+
| OWNER | OBJECT_NAME | COUNT(*) |
+-------+-------------------+----------+
| LX | TEST_GLOBAL_TEMP1 | 16 |
+-------+-------------------+----------+
5 rows in set (0.058 sec)
obclient [LX]> select * from dba_objects where OBJECT_NAME = 'TEST_GLOBAL_TEMP1';
+-------+-------------------+----------------+------------------+----------------+-----------------+-----------+---------------+------------------------------+--------+-----------+-----------+-----------+-----------+--------------+
| OWNER | OBJECT_NAME | SUBOBJECT_NAME | OBJECT_ID | DATA_OBJECT_ID | OBJECT_TYPE | CREATED | LAST_DDL_TIME | TIMESTAMP | STATUS | TEMPORARY | GENERATED | SECONDARY | NAMESPACE | EDITION_NAME |
+-------+-------------------+----------------+------------------+----------------+-----------------+-----------+---------------+------------------------------+--------+-----------+-----------+-----------+-----------+--------------+
| LX | TEST_GLOBAL_TEMP1 | NULL | 1102810162709713 | NULL | TABLE | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.115764 PM | VALID | Y | N | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P0 | 0 | 0 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P1 | 1 | 1 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P2 | 2 | 2 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P3 | 3 | 3 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P4 | 4 | 4 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P5 | 5 | 5 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P6 | 6 | 6 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P7 | 7 | 7 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P8 | 8 | 8 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P9 | 9 | 9 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P10 | 10 | 10 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P11 | 11 | 11 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P12 | 12 | 12 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P13 | 13 | 13 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P14 | 14 | 14 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
| LX | TEST_GLOBAL_TEMP1 | P15 | 15 | 15 | TABLE PARTITION | 19-APR-23 | 19-APR-23 | 19-APR-23 03.11.34.135140 PM | VALID | N | Y | N | 0 | NULL |
+-------+-------------------+----------------+------------------+----------------+-----------------+-----------+---------------+------------------------------+--------+-----------+-----------+-----------+-----------+--------------+
17 rows in set (0.245 sec)
全局临时表在创建时可以指定分区关键字，如果没有指定，则默认按照系统设置的分区策略进行分区，以提高查询性能和减少资源占用。这种分区方式称为自动分区。自动分区是一种优化技术，将数据按照特定的规则分散到多个物理存储设备上，以提高查询性能和减少资源占用。全局临时表作为一种临时性数据存储结构，也需要考虑这些优化技术，因此会自动创建分区。
所以总结来看，全局临时表自动创建分区是为了提高查询性能和减少资源占用，是一种优化技术的应用。
适用版本
OceanBase 数据库所有版本。
