如何获得Oracle分区索引类型

2020-04-16 18:40:37 浏览数 (1)

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

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

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

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

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

代码语言：javascript复制
SQL> select distinct (locality) from  dba_part_indexes;

LOCALI
------
LOCAL

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

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

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

代码语言：javascript复制
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:RANGEHASHSYSTEMLIST

SUBPARTITIONING_TYPE

VARCHAR2(7)

Type of composite partitioning method:NONEHASHSYSTEMLIST

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 NEXTvalue 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

  • 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

linux windows oracle 数据分析 云推荐引擎

0 人点赞