索引-SYS_INDEXES

📄字数 1.9K

👁️阅读量 加载中...

功能描述

SYS_INDEXES系统表用于存储、管理系统库和用户库中所有表的索引信息。

字段说明

序号	字段名	类型	说明	V11	V12
0	DB_ID	INTEGER	库ID	√	√
1	TABLE_ID	INTEGER	主表ID	√	√
2	INDEX_ID	INTEGER	索引ID	√	√
3	INDEX_NAME	VARCHAR	索引名	√	√
4	INDEX_TYPE	INTEGER	索引类型	√	√
5	IS_PRIMARY	BOOLEAN	是否主键	√	√
6	IS_UNIQUE	BOOLEAN	是否唯一值	√	√
7	IS_LOCAL	BOOLEAN	是否局部索引（若是局部则分区方式与主表同）	√	√
8	PARTI_TYPE	INTEGER	分区类型（0：无分区；1：范围；2：列表；3：hash）	√	√
9	PARTI_NUM	INTEGER	分区数	√	√
10	PARTI_KEY	VARCHAR	分区键	√	√
11	SUBPARTI_TYPE	INTEGER	子分区类型（0：无分区；1：范围；2：列表；3：hash）	√	√
12	SUBPARTI_NUM	INTEGER	子分区数	√	√
13	SUBPARTI_KEY	VARCHAR	子分区键	√	√
14	GSTO_NO	INTEGER	存贮号	√	√
15	COPY_NUM	INTEGER	副本数	√	√
16	BLOCK_SIZE	INTEGER	块大小（单位：K）	√	√
17	CHUNK_SIZE	INTEGER	存贮单元大小（单位：M）	√	√
18	FIELD_NUM	INTEGER	字段数	√	√
19	KEYS	VARCHAR	索引键（字段或函数，用逗号分隔）	√	√
20	FILTER	VARCHAR	文档过滤器名	√	√
21	VOCABLE	VARCHAR	词表名	√	√
22	LEXER	VARCHAR	语法分析器名	√	√
23	WORD_LEN	INTEGER	词分割长度	√	√
24	ENABLE_TRANS	BOOLEAN	是否需要进行事务支持	√	√
25	CREATE_TIME	DATETIME	首次创建时间	√	√
26	IS_SYS	BOOLEAN	是否系统内建	√	√
27	KEEPIN_CACHE	BOOLEAN	是否KEEP CACHE	√	√
28	NOLOGGING	BOOLEAN	是否不记redo日志	√	√
29	VALID	TINYINT	是否有效	√	√
30	SLOW_MODIFY	BOOLEAN	是否开启缓变	×	√
31	ONLINELOG_ID	INTEGER	online日志ID	×	√
32	RESERVED3	VARCHAR	保留字段	√	√

INDEX_TYPE

编号	索类型	名称
0	BTREE	B树索引
1	RTREE	R树索引
2	FULLTEXT	全文索引
3	BITMAP	位图索引

应用举例

• 查询数据库中所有索引的相关信息

SQL> CREATE TABLE tab_index(c1 INT, c2 INT, c3 INT);

SQL> CREATE INDEX index1 ON tab_index(c1) INDEXTYPE IS BTREE;

SQL> ALTER TABLE tab_index ADD CONSTRAINT pk1 PRIMARY KEY(c2);

SQL> ALTER TABLE tab_index ADD CONSTRAINT uk1 UNIQUE(c3);

SQL> SELECT * FROM SYS_INDEXES;
+-------+----------+----------+--------------------------+------------+------------+-----------+----------+------------+-----------+-----------+---------------+--------------+--------------+---------+----------+------------+------------+-----------+------+--------+---------+--------+----------+--------------+--------------------------+--------+--------------+-----------+-------+-------------+--------------+-----------+
| DB_ID | TABLE_ID | INDEX_ID |        INDEX_NAME        | INDEX_TYPE | IS_PRIMARY | IS_UNIQUE | IS_LOCAL | PARTI_TYPE | PARTI_NUM | PARTI_KEY | SUBPARTI_TYPE | SUBPARTI_NUM | SUBPARTI_KEY | GSTO_NO | COPY_NUM | BLOCK_SIZE | CHUNK_SIZE | FIELD_NUM | KEYS | FILTER | VOCABLE | LEXER  | WORD_LEN | ENABLE_TRANS |       CREATE_TIME        | IS_SYS | KEEPIN_CACHE | NOLOGGING | VALID | SLOW_MODIFY | ONLINELOG_ID | RESERVED3 |
+-------+----------+----------+--------------------------+------------+------------+-----------+----------+------------+-----------+-----------+---------------+--------------+--------------+---------+----------+------------+------------+-----------+------+--------+---------+--------+----------+--------------+--------------------------+--------+--------------+-----------+-------+-------------+--------------+-----------+
| 1     | 1048605  | 1048606  | INDEX1                   | 0          | F          | F         | F        | 0          | 1         | <NULL>    | 0             | 1            | <NULL>       | 224     | 3        | <NULL>     | <NULL>     | 1         | "C1" | <NULL> | <NULL>  | <NULL> | 0        | F            | 2025-06-26 14:54:19.414  | F      | <NULL>       | F         | 1     | <NULL>      | <NULL>       | <NULL>    |
| 1     | 1048605  | 1048607  | PK_IDX_S3120817509496642 | 0          | T          | T         | F        | 0          | 1         | <NULL>    | 0             | 1            | <NULL>       | 223     | 3        | <NULL>     | <NULL>     | 1         | "C2" | <NULL> | <NULL>  | <NULL> | 0        | F            | 2025-06-26 14:54:24.130  | F      | <NULL>       | F         | 1     | <NULL>      | <NULL>       | <NULL>    |
| 1     | 1048605  | 1048608  | UK_IDX_S1463017509496693 | 0          | F          | T         | F        | 0          | 1         | <NULL>    | 0             | 1            | <NULL>       | 226     | 3        | <NULL>     | <NULL>     | 1         | "C3" | <NULL> | <NULL>  | <NULL> | 0        | F            | 2025-06-26 14:54:29.479  | F      | <NULL>       | F         | 1     | <NULL>      | <NULL>       | <NULL>    |
+-------+----------+----------+--------------------------+------------+------------+-----------+----------+------------+-----------+-----------+---------------+--------------+--------------+---------+----------+------------+------------+-----------+------+--------+---------+--------+----------+--------------+--------------------------+--------+--------------+-----------+-------+-------------+--------------+-----------+

• ONLINELOG_ID字段举例

SQL> CREATE TABLE tab_index_online(c1 INT);

SQL> CREATE INDEX index1 ON tab_index_online(c1) INDEXTYPE IS BTREE ONLINE;

SQL> SELECT INDEX_NAME,INDEX_TYPE,ONLINELOG_ID FROM SYS_INDEXES;
+------------+------------+--------------+
| INDEX_NAME | INDEX_TYPE | ONLINELOG_ID |
+------------+------------+--------------+
| INDEX1     | 0          | 0            |
+------------+------------+--------------+

相关系统表

• SYS_TABLES
• SYS_COLUMNS
• SYS_CONSTRAINTS