Skip to content

USER_IDX_SUBPARTIS

功能描述

user_idx_subpartis系统视图用于查询当前用户所有表的索引二级分区信息。

字段说明

字段名类型说明
DB_IDOID_TYPE库ID
INDEX_IDOID_TYPE索引ID
SUBPARTI_NOINTEGER子分区号
SUBPARTI_NAMEVARCHAR子分区名
SUBPARTI_VALVARCHAR12分区条件值
RESERVED1VARCHAR保留字段
RESERVED2VARCHAR保留字段

示例

  • 查询二级分区表的索引信息
  • 通过二级分区索引ID查询索引列和表名
    sql
    SQL> CREATE TABLE idxsup_info(id INT,name VARCHAR(10))PARTITION BY RANGE(id) PARTITIONS(p1 VALUES LESS THAN(10000),p2 VALUES LESS THAN(200000))SUBPARTITION BY HASH(name) SUBPARTITIONS 2;   
    
    SQL> CREATE INDEX idxsup ON idxsup_info(id) GLOBAL PARTITION BY RANGE(id) PARTITIONS(p1 VALUES LESS THAN(10000),p2 VALUES LESS THAN(200000))SUBPARTITION BY HASH(name) SUBPARTITIONS 2;    
    
    SQL> SELECT st.table_name,si.index_id,si.index_name,sip.subparti_no,sip.subparti_name,sip.subparti_val FROM user_idx_subpartis sip JOIN user_tables st USING(db_id) JOIN user_indexes si USING(db_id,table_id) WHERE st.table_name='IDXSUP_INFO';
    
    TABLE_NAME | INDEX_ID | INDEX_NAME | SUBPARTI_NO | SUBPARTI_NAME | SUBPARTI_VAL | 
    ------------------------------------------------------------------------------
    IDXSUP_INFO| 1048592 | IDXSUP| 0 | IDX_SUBPART1| 0|
    IDXSUP_INFO| 1048592 | IDXSUP| 1 | IDX_SUBPART2| 1|

相关系统表

  • sys_databases
  • sys_tables
  • sys_indexes