表分区-SYS_PARTIS
📄字数 2.2K
👁️阅读量 加载中...
功能描述
SYS_PARTIS系统表用于存储、管理系统库和用户库中的所有表分区信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | DB_ID | INTEGER | 库ID | √ | √ |
| 1 | TABLE_ID | INTEGER | 表ID | √ | √ |
| 2 | PARTI_NO | INTEGER | 分区号 | √ | √ |
| 3 | PARTI_NAME | VARCHAR | 分区名 | √ | √ |
| 4 | PARTI_VAL | VARCHAR | 分区条件值 | √ | √ |
| 5 | GSTO_NOS | INTEGER | 全局存贮号 | √ | √ |
| 6 | ONLINE | BOOLEAN | 是否在线 | √ | √ |
| 7 | RESERVED1 | VARCHAR | 保留字段 | √ | √ |
| 8 | RESERVED2 | VARCHAR | 保留字段 | √ | √ |
应用举例
- 1、查询一级分区表的分区情况
sql
SQL> CREATE TABLE tab_hash_part(id INT,name VARCHAR(30))PARTITION BY HASH(id) PARTITIONS 3;
SQL> SELECT sp.* FROM SYS_PARTIS sp JOIN SYS_TABLES st ON sp.db_id=st.db_id AND sp.table_id=st.table_id WHERE table_name='TAB_HASH_PART';
+-------+----------+----------+------------+-----------+----------+--------+-----------+-----------+
| DB_ID | TABLE_ID | PARTI_NO | PARTI_NAME | PARTI_VAL | GSTO_NOS | ONLINE | RESERVED1 | RESERVED2 |
+-------+----------+----------+------------+-----------+----------+--------+-----------+-----------+
| 1 | 1048588 | 0 | PART1 | 0 | 213 | T | <NULL> | <NULL> |
| 1 | 1048588 | 1 | PART2 | 1 | 217 | T | <NULL> | <NULL> |
| 1 | 1048588 | 2 | PART3 | 2 | 218 | T | <NULL> | <NULL> |
+-------+----------+----------+------------+-----------+----------+--------+-----------+-----------+
-- 将part2分区置为offline
SQL> ALTER TABLE tab_hash_part SET PARTITION PART2 OFFLINE;
SQL> SELECT sp.* FROM SYS_PARTIS sp JOIN SYS_TABLES st ON sp.db_id=st.db_id AND sp.table_id=st.table_id WHERE table_name='TAB_HASH_PART';
+-------+----------+----------+------------+-----------+----------+--------+-----------+-----------+
| DB_ID | TABLE_ID | PARTI_NO | PARTI_NAME | PARTI_VAL | GSTO_NOS | ONLINE | RESERVED1 | RESERVED2 |
+-------+----------+----------+------------+-----------+----------+--------+-----------+-----------+
| 1 | 1048588 | 0 | PART1 | 0 | 213 | T | <NULL> | <NULL> |
| 1 | 1048588 | 1 | PART2 | 1 | 217 | F | <NULL> | <NULL> |
| 1 | 1048588 | 2 | PART3 | 2 | 218 | T | <NULL> | <NULL> |
+-------+----------+----------+------------+-----------+----------+--------+-----------+-----------+- 2、查询二级分区表的分区情况
sql
SQL> CREATE TABLE t1(c1 INTEGER, c2 INTEGER) PARTITION BY RANGE(c1) PARTITIONS((100),(200))
SUBPARTITION BY RANGE(C2) SUBPARTITIONS((1000),(2000),(3000));二级分区表的全局存储号信息在系统表中常以一个不可读取的魔数（即在代码中出现但没有解释的数字常量）表示，需要由数据库内部系统函数format_gsto_nos来进行格式化展示。format_gsto_nos有以下两种重载形式。
- (1)接受1个参数，输出结果为逗号分隔的全局存储号字符串。
sql
SQL> SELECT format_gsto_nos(gsto_nos) AS gsto_nos FROM SYS_PARTIS WHERE table_id=(SELECT table_id FROM SYS_TABLES WHERE table_name='T1');
+-------------+
| GSTO_NOS |
+-------------+
| 214,215,216 |
| 217,218,219 |
+-------------+- (2)接受2个参数，分别是全局存储号的魔数和子分区号，输出结果为全局存储号魔数中子分区号指定的全局存储号数值。
sql
SQL> SELECT parti_no, subparti_no, format_gsto_nos(gsto_nos, subparti_no) AS gsto_no FROM SYS_PARTIS a
, SYS_SUBPARTIS b WHERE a.table_id = (SELECT table_id FROM SYS_TABLES WHERE table_name='T1');
+----------+-------------+---------+
| PARTI_NO | SUBPARTI_NO | GSTO_NO |
+----------+-------------+---------+
| 0 | 0 | 214 |
| 0 | 1 | 215 |
| 0 | 2 | 216 |
| 1 | 0 | 217 |
| 1 | 1 | 218 |
| 1 | 2 | 219 |
+----------+-------------+---------+- 3、删除分区后查询分区
sql
SQL> CREATE TABLE tab_list_part(id INT, name VARCHAR(20), city CHAR(20))PARTITION BY LIST (city)PARTITIONS(('四川'),('云南'),('贵州'),(OTHERVALUES));
SQL> SELECT sp.* FROM SYS_PARTIS sp JOIN SYS_TABLES st ON sp.db_id=st.db_id AND sp.table_id=st.table_id WHERE table_name='TAB_LIST_PART';
+-------+----------+----------+------------+-------------+----------+--------+-----------+-----------+
| DB_ID | TABLE_ID | PARTI_NO | PARTI_NAME | PARTI_VAL | GSTO_NOS | ONLINE | RESERVED1 | RESERVED2 |
+-------+----------+----------+------------+-------------+----------+--------+-----------+-----------+
| 1 | 1048586 | 0 | PART2 | '云南' | 213 | T | <NULL> | <NULL> |
| 1 | 1048586 | 1 | PART1 | '四川' | 214 | T | <NULL> | <NULL> |
| 1 | 1048586 | 2 | PART3 | '贵州' | 215 | T | <NULL> | <NULL> |
| 1 | 1048586 | 3 | PART4 | OTHERVALUES | 216 | T | <NULL> | <NULL> |
+-------+----------+----------+------------+-------------+----------+--------+-----------+-----------+
-- 删除分区
SQL> ALTER TABLE tab_list_part DROP PARTITION PART2;
SQL> SELECT sp.* FROM SYS_PARTIS sp JOIN SYS_TABLES st ON sp.db_id=st.db_id AND sp.table_id=st.table_id WHERE table_name='TAB_LIST_PART';
+-------+----------+----------+------------+-------------+----------+--------+-----------+-----------+
| DB_ID | TABLE_ID | PARTI_NO | PARTI_NAME | PARTI_VAL | GSTO_NOS | ONLINE | RESERVED1 | RESERVED2 |
+-------+----------+----------+------------+-------------+----------+--------+-----------+-----------+
| 1 | 1048586 | 0 | PART1 | '四川' | 214 | T | <NULL> | <NULL> |
| 1 | 1048586 | 1 | PART3 | '贵州' | 215 | T | <NULL> | <NULL> |
| 1 | 1048586 | 2 | PART4 | OTHERVALUES | 216 | T | <NULL> | <NULL> |
+-------+----------+----------+------------+-------------+----------+--------+-----------+-----------+