如何获得Oracle分区索引类型

r囧r小猫 2023-07-25 12:40 16阅读 0赞

碰巧在墨天轮上看资料就看到了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

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

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

**近期热文：**

《[《decode函数的妙用》网友的两个问题解答][decode]》

《[decode函数的妙用][decode 1]》

《[虚拟内存详解][Link 1]》

《[7号球衣的故事：重要的不是号码，而是穿着它的人][7]》

《[插入"&"特殊字符的几种思考][Link 2]》

《[connect by超乎你想象][connect by]》

《[TOEIC科普一下][TOEIC]》

[decode]: https://blog.csdn.net/bisal/article/details/105445417
[decode 1]: https://blog.csdn.net/bisal/article/details/105424100
[Link 1]: https://blog.csdn.net/bisal/article/details/105383561
[7]: https://blog.csdn.net/bisal/article/details/105283168
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815f05ddf6d613a8fd4f4454278476e27e37675d810d3aa4e74a8e0c8d06b6e168ea9c8ecb&idx=1&mid=2247490294&scene=21&sn=46ac00aeef5cae9c81dcd234c9766853#wechat_redirect
[connect by]: https://blog.csdn.net/bisal/article/details/105236326
[TOEIC]: https://blog.csdn.net/bisal/article/details/105383559