如何获得Oracle分区索引类型

碰巧在墨天轮上看资料就看到了eygle的这篇文章《如何获得 Oracle 分区索引的类型 - 全局分区索引、本地分区索引》，秉承了eygle大神一如既往的风格，文章“短小”，但是“精悍”，从我看eygle的第一本书开始，就记住了他这句“由点及面”，不仅在数据库领域中，在其他工作领域，这种学习方法，都是适用的。正所谓“授之以鱼不如授之以渔”。

P.S. https://www.modb.pro/db/23633?from=timeline&isappinstalled=0

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

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

• RANGE
• HASH
• SYSTEM
• LIST

SUBPARTITIONING_TYPEVARCHAR2(7) Type of composite partitioning method:

• NONE
• HASH
• SYSTEM
• LIST

PARTITION_COUNTNUMBERNOT NULLNumber of partitions in the indexDEF_SUBPARTITION_COUNTNUMBER For a composite-partitioned index, the default number of subpartitions, if specifiedPARTITIONING_KEY_COUNTNUMBERNOT NULLNumber of columns in the partitioning keySUBPARTITIONING_KEY_COUNTNUMBER For a composite-partitioned index, the number of columns in the subpartitioning keyLOCALITYVARCHAR2(6) Indicates whether the partitioned index is local (LOCAL) or global (GLOBAL)ALIGNMENTVARCHAR2(12) Indicates whether the partitioned index is PREFIXED or NON_PREFIXEDDEF_TABLESPACE_NAMEVARCHAR2(30) For a local index, the default tablespace to be used when adding or splitting a table partitionDEF_PCT_FREENUMBERNOT NULLFor a local index, the default PCTFREE value to be used when adding a table partitionDEF_INI_TRANSNUMBERNOT NULLFor a local index, the default INITRANS to be used when adding a table partitionDEF_MAX_TRANSNUMBERNOT NULLFor a local index, the default MAXTRANS to be used when adding a table partitionDEF_INITIAL_EXTENTVARCHAR2(40)NOT NULLFor 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 specifiedDEF_NEXT_EXTENTVARCHAR2(40)NOT NULLFor a local index, the default NEXT (in Oracle blocks), or DEFAULT if no NEXTvalue was specifiedDEF_MIN_EXTENTSVARCHAR2(40)NOT NULLFor a local index, the default MINEXTENTS value to be used when adding a table partition, or DEFAULT if no MINEXTENTS value was specifiedDEF_MAX_EXTENTSVARCHAR2(40)NOT NULLFor a local index, the default MAXEXTENTS value to be used when adding a table partition, or DEFAULT if no MAXEXTENTS value was specifiedDEF_PCT_INCREASEVARCHAR2(40)NOT NULLFor a local index, the default PCTINCREASE value to be used when adding a table partition, or DEFAULT if no PCTINCREASE value was specifiedDEF_FREELISTSNUMBERNOT NULLFor a local index, the default FREELISTS value to be used when adding a table partition, or DEFAULT if no FREELISTS value was specifiedDEF_FREELIST_GROUPSNUMBERNOT NULLFor a local index, the default FREELIST GROUPS value to be used when adding a table partition, or DEFAULT if no FREELIST GROUPS value was specifiedDEF_LOGGINGVARCHAR2(7) For a local index, the default LOGGING attribute to be used when adding a table partition, or DEFAULT if no LOGGING attribute was specifiedDEF_BUFFER_POOLVARCHAR2(7) For a local index, the default buffer pool to be used when adding a table partitionDEF_PARAMETERSVARCHAR2(1000) Default parameter string for domain indexes