FAQ : 如何获得 Oracle 分区索引的类型 - 全局分区索引、本地分区索引

原创 eygle 2020-04-10

2712

Oracle 数据库针对分区的信息，通过多个数据字典视图来维护，所以在获取信息时，经常会困惑 DBA 们。

例如，如何获取分区索引的类型，如何判断一个索引，是全局分区索引，还是本地分区索引？

通过 DBA_PART_INDEXES 中的 LOCALITY 字段就可以判断：

SQL> select distinct (locality) from  dba_part_indexes; 

LOCALI
------
LOCAL
复制

LOCAL 意味着是本地索引；GLOBAL 则意味着是全局索引。

DBA_PART_INDEXES 和 ALL_PART_INDEXES、USER_PART_INDEXES 视图的字段相同，分别展示的是数据库（DBA级别）、用户权限级别（ALL）和用户所属级别（USER）的分区索引信息。

至于分区类型等，可以通过这个视图查询获得：

SQL> select TABLE_NAME,PARTITIONING_TYPE,LOCALITY from dba_part_indexes where rownum < 10;

TABLE_NAME					   PARTITION LOCALI
-------------------------------------------------- --------- ------
STREAMS$_APPLY_SPILL_MSGS_PART			   LIST      LOCAL
STREAMS$_APPLY_SPILL_MSGS_PART			   LIST      LOCAL
LOGMNRC_CONCOL_GG				   RANGE     LOCAL
LOGMNRC_CON_GG					   RANGE     LOCAL
LOGMNRC_CON_GG					   RANGE     LOCAL
LOGMNRC_CON_GG					   RANGE     LOCAL
LOGMNRC_GSBA					   RANGE     LOCAL
LOGMNRC_GSII					   RANGE     LOCAL
LOGMNRC_GTCS					   RANGE     LOCAL
复制

视图信息引入如下，供参考：

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the partitioned index
`INDEX_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the partitioned index
`TABLE_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the partitioned table
`PARTITIONING_TYPE`	`VARCHAR2(7)`		Type of partitioning method: `RANGE` `HASH` `SYSTEM` `LIST`
`SUBPARTITIONING_TYPE`	`VARCHAR2(7)`		Type of composite partitioning method: `NONE` `HASH` `SYSTEM` `LIST`
`PARTITION_COUNT`	`NUMBER`	`NOT NULL`	Number of partitions in the index
`DEF_SUBPARTITION_COUNT`	`NUMBER`		For a composite-partitioned index, the default number of subpartitions, if specified
`PARTITIONING_KEY_COUNT`	`NUMBER`	`NOT NULL`	Number of columns in the partitioning key
`SUBPARTITIONING_KEY_COUNT`	`NUMBER`		For a composite-partitioned index, the number of columns in the subpartitioning key
`LOCALITY`	`VARCHAR2(6)`		Indicates whether the partitioned index is local (`LOCAL`) or global (`GLOBAL`)
`ALIGNMENT`	`VARCHAR2(12)`		Indicates whether the partitioned index is `PREFIXED` or `NON_PREFIXED`
`DEF_TABLESPACE_NAME`	`VARCHAR2(30)`		For a local index, the default tablespace to be used when adding or splitting a table partition
`DEF_PCT_FREE`	`NUMBER`	`NOT NULL`	For a local index, the default `PCTFREE` value to be used when adding a table partition
`DEF_INI_TRANS`	`NUMBER`	`NOT NULL`	For a local index, the default `INITRANS` to be used when adding a table partition
`DEF_MAX_TRANS`	`NUMBER`	`NOT NULL`	For a local index, the default `MAXTRANS` to be used when adding a table partition
`DEF_INITIAL_EXTENT`	`VARCHAR2(40)`	`NOT NULL`	For a local index, the default `INITIAL` value (in Oracle blocks) to be used when adding a table partition, or `DEFAULT` if no `INITIAL` value was specified
`DEF_NEXT_EXTENT`	`VARCHAR2(40)`	`NOT NULL`	For a local index, the default `NEXT` (in Oracle blocks), or `DEFAULT` if no `NEXT` value was specified
`DEF_MIN_EXTENTS`	`VARCHAR2(40)`	`NOT NULL`	For a local index, the default `MINEXTENTS` value to be used when adding a table partition, or `DEFAULT` if no `MINEXTENTS` value was specified
`DEF_MAX_EXTENTS`	`VARCHAR2(40)`	`NOT NULL`	For a local index, the default `MAXEXTENTS` value to be used when adding a table partition, or `DEFAULT` if no `MAXEXTENTS` value was specified
`DEF_PCT_INCREASE`	`VARCHAR2(40)`	`NOT NULL`	For a local index, the default `PCTINCREASE` value to be used when adding a table partition, or `DEFAULT` if no `PCTINCREASE` value was specified
`DEF_FREELISTS`	`NUMBER`	`NOT NULL`	For a local index, the default `FREELISTS` value to be used when adding a table partition, or `DEFAULT` if no `FREELISTS` value was specified
`DEF_FREELIST_GROUPS`	`NUMBER`	`NOT NULL`	For a local index, the default `FREELIST GROUPS` value to be used when adding a table partition, or `DEFAULT` if no `FREELIST GROUPS` value was specified
`DEF_LOGGING`	`VARCHAR2(7)`		For a local index, the default `LOGGING` attribute to be used when adding a table partition, or `DEFAULT` if no `LOGGING` attribute was specified
`DEF_BUFFER_POOL`	`VARCHAR2(7)`		For a local index, the default buffer pool to be used when adding a table partition
`DEF_PARAMETERS`	`VARCHAR2(1000)`		Default parameter string for domain indexes

oracle

最后修改时间：2020-04-10 15:56:07

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

FAQ : 如何获得 Oracle 分区索引的类型 - 全局分区索引、本地分区索引

评论

相关阅读