关于oracle12c的异步延迟全局索引

        在Oracle 12c之前，每当删除一个表分区时，在该表上创建的所有全局索引都会变得不可用，除非我们在 alter table table_name drop partition or truncate partition语句中指定了update indexes子句。如果没有指定update index子句，则必须重新构建索引，站在性能角度考虑，update index在维护期间性能消耗是比较大的，为什么update index性能消耗比较大？12c的异步延迟全局索引又是如何工作的？，以及索引维护相关工作原理和细节？就是今天我们要聊的话题。 

       在下面的截图中，我们有一个表，有2列5行。在该表上创建一个索引，该索引是在该表的ID字段上创建的，其索引项包括行地址(ROWID)。ROWID是行(包括哪个数据文件、哪个块、块中的哪个位置)的物理位置的地址，这里我们不对ROWID做具体工作原理分析，只是说明问题情况：

         好的，现在，考虑一下如果我们执行alter table table_name drop partition删除表分区会发生什么情况，其中包含值Argentina和Colombia它将导致如下结果:

      我们看到Agentina和Colombia值不再存在，因为在删除分区时这两条数据同步是被删除。但是，我们在左侧看到，与删除的表分区的值相关的索引项仍然存在于索引中。现在它们是孤立的索引项，因为它们所指向的表中的行已经不存在了。在这种情况下，索引需要的是:维护的。

       注意，以上截图内容来自戈麦斯先生，Indexes From the Concept to Internals文章，依次借鉴来说明问题。

       好的，书归正传，对于小索引，与索引维护相关的性能开销是可以接受的，但是当我们使用大表并在其上创建全局索引时，我们的索引可以是几十g，甚至几百g。重建索引会影响性能，这无需置疑，因为它使用多个IO资源扫描表并创建b树(或位图)分支和叶节点。正如刚才我们描述的，如果表很大，性能问题势必是DBA首先考虑的。另一方面，如果我们使用UPDATE INDEXES子句，我们可以得到以下好处:

       索引使用基表操作更新。不需要重新构建索引。
       全局索引的可用性更高，因为它们没有标记为不可用。在索引完成维护(所有孤立项都是固定的)，可以使用这些索引，性能开销相对很小。
       我们也不必确定哪些索引依赖于我们要从中删除分区的表。所有依赖的索引都是自动维护的，不需要DBA的干预
       我们来看一个实验 ：

--创建测试分区表

--插入测试数据

--收集统计信息

--检查每个表分区中有多少行。在一个特定的分区中创建了一行。分区list_t_600_partition_p1的值为如人饮水冷暖自知，下一个分区的值为小dawn，最后一个分区的值为小沙 

--然后，我们创建一个global index 

--查看索引的内部结构，我们可以看到这些值已经成功地建立了索引

--现在我们使用update index删除一个表分区：

--在删除表分区之后查看索引内部， 可以看到索引维护是立即执行的。 索引条目如人饮水冷暖自知被标记为“D”， 表示该索引条目被“删除”， 可以被另一个索引条目重用

       在这种情况下，索引维护的结果是只有一个标记为“D”的索引条目。但是想想大型索引:在一个上百GB的全局索引中修复孤立索引需要多长时间?这个操作的性能下降会影响多少会话？可怕不 。

        Oracle 12c可以通过延迟相关索引维护来优化某些DROP分区和TRUNCATE分区命令的性能，同时让全局索引处于有效状态，通过仅对元数据进行索引维护，优化了分区维护操作DROP分区和TRUNCATE分区。
       默认情况下执行删除和截断的异步全局索引维护;但是，为了向后兼容，UPDATE INDEXES子句仍然是必需的。

       异步全局索引维护本身也存在局限性：例如只能在堆组织表执行，不支持object type对象类型表，同时也不支持domain index
      索引上的维护操作可以使用自动调度器作业系统来执行。PMO_DEFERRED_GIDX_MAINT_JOB清除所有全局索引。默认情况下，此作业计划在每天凌晨2点运行。我们可以在任何时候使用DBMS_SCHEDULER运行此作业。如果我们想主动清理索引，就需要运行_job。还可以根据我们的特定需求修改作业以使用不同的调度运行。但是，Oracle不太建议我们这么做。

      DBMS_PART.CLEANUP_GIDX -这个PL/SQL过程收集系统中可能需要清理的全局索引列表，并运行将索引恢复到干净状态所需的操作。
     利用最传统的方式：ALTER INDEX REBUILD [PARTITION] -这条SQL语句重新构建整个索引或索引分区，就像在Oracle数据库12c版本之前的版本中所做的那样。生成的索引(分区)不包含任何过时的条目。
  ALTER INDEX [PARTITION] COALESCE CLEANUP——这条SQL语句清除索引块中的孤立条目orphaned entries 。

删除旧的测试表

创建新的测试分区表

为分区表添加主键

创建索引

插入测试数据 

收集统计信息 

这里，给大家介绍一个索引结构查看的脚本，以下为脚本具体内容：

----------------------------------------------------------------------------------------------------------------------------------------
--Description :B-tree索引结构脚本分享
--Author :如人饮水冷暖自知
----------------------------------------------------------------------------------------------------------------------------------------

1. 脚本使用说明：
         这个脚本将根据已存在的表和索引的统计信息来核实b-tree索引结构。这个脚本将计算如下的条目：
   1.1估计索引的理论大小
   1.2索引布局
   1.3这个脚本会将收集的信息以历史记录的形式保存在INDEX_HIST表中。这对避免做定时索引重建很有帮助
   我们可以自定义这个历史记录
   注意：这个脚本不使用analyze table ... validate structure，而依赖内部功能SYS_OP_LBID

   1.4使用该脚本前初始化操作：
   grant select on dba_tablespaces to <user>;
   grant select on dba_indexes to <user>;
   grant select on dba_tables to <user>;
   grant select on dba_ind_columns to <user>;
   grant select on dba_tab_cols to <user>;
   grant select on dba_objects to <user>;
   grant select on v_$parameter to <user>;

   
   

   注意：脚本依赖于最新的统计信息，请首先收集统计信息，使之能够看到schema中的变化，在运行package index_util,
   具体实施办法详见如下脚本示例
   ----------------------------------------------------------- 
   

2. 创建索引结构保存的堆组织表

        3. 收集对应用户统计信息：

         4.创建计算索引结构的package

--------------------------------------------------------------------
   5.收集统计信息后执行exec index_util.inspect_schema ('&OWNER');
--------------------------------------------------------------------
            6.  查询满足条件的索引

--------------------------------------------------------------------
            7. 查询索引布局

注：第一列列出索引块中的行数，第二列列出具有这个索引条目数的块数。例如有xx个块分别有xxx行，1个块有xx行

--------------------------------------------------------------------

           8. 查询索引变化过程

--------------------------------------------------------------------

广告结束，书归正传

       我们来验证一下索引创建情况：

      Oracle12c版本以后，当与UPDATE INDEXES子句组合使用时，DROP分区和TRUNCATE分区命令现在只维护元数据索引。此功能仅适用于堆表，不支持对象类型、域索引或SYS拥有的表，刚才我们说过了。
       如果只是删除或截断分区，全局索引将被标记为无效。在12c之前，使用UPDATE INDEXES子句会导致将它们作为操作的一部分重新构建，从而降低整个操作的速度。在下面的示例中，我们截断一个分区并检查索引的状态 。

  USER_INDEXES数据字典中的ORPHANED_ENTRIES列显示索引维护尚未完成 。

       现在我们触发一个手动索引维护， 如下截图会有所显示：

       DBMS_PART包为分区对象的维护和管理操作提供的一个接口， 该API是一个调用者的权限包， 使用用户的权限运行即可。

      由于使用异步全局索引维护的先前分区维护操作， 全局索引可以包含指向不再存在的数据段的条目。 这些陈旧的索引行不会在对表或索引的任何操作期间导致任何正确性问题或损坏， 无论这些操作是查询、 DMLs、 DDLs还是analyze。 此过程将标识和清理这些全局索引， 以确保存储和性能方面的效率 。

    在执行ALTER TABLE ... MOVE PARTITION ... ONLINE操作。 这个过程会主动清理这些失败的在线移动操作， 而不是等待后台进程(SMON)来完成这些操作DBMS_PART详细的介绍可以参考https://docs.oracle.com/database/121/ARPLS/d_part.htm#ARPLS74832官方文档， 这里就不做详细介绍了， 这个API非常简单， 不像DBMS_PIPE或者DBMS_SCHEDULER那么复杂 。

      以下截图为第一个案例中创建的t_600_partition表，我们drop掉其中一个分区，然后查询具体的分区内容，最后打印游标缓 存执行计划，来看具体操作内容： 

   执行计划如下：

    在执行计划中看到了一个新的过滤器，TBL$OR$IDX$PART$NUM，该过滤器在Oracle未公开的文档中有简短说明，主要作用在TBL$或$IDX$PART$NUM”两个函数上
      TBL$或$IDX$PART$NUM函数用于查找特定行将属于哪个分区。这个函数没有文档。它的格式是

TBL$或$IDX$PART$NUM(PARTITIONED_TABLE_

NAME,0,d#，p#，COLUMN_NAME)

     那么Oracle是如果跟踪那些孤立的索引条目呢，是的，这里有个新的数据字典：INDEX_ORPHANED_ENTRY_V$

       这里需要注意，对于过滤器本身并不决定于我们干掉多少个分区，因为通过下面的数据字典验证我们可以确定：

       从Oracle12c开始，我们可以删除表分区，而不必担心索引维护。数据库将正常工作，性能开销很低，因为不会立即执行索引维护。DBA可以决定何时通过计划的作业执行索引维护，甚至可以选择手动执行索引维护。我们还比较了11gR2和12c之间的操作行为，并查看了索引内部结构，这样我们就可以看到这个新功能是如何与内部结构相关联的 。