Oracle索引合并coalesce操作

caoyhao 2015-04-01

索引rebuild和rebuild online是运维环境中经常遇到的问题。但是无论哪种，大数据索引对象的rebuild都是消耗资源的大规模操作，都需要进行时间窗规划，避免对在线系统运行有影响。

本篇主要介绍对索引的另一种精简操作方法：coalesce合并。从之前的讨论我们已经知道，索引结构一般是一个不断“退化”的平衡结构，如果有一个新值加入，就可能会伴随叶子节点拓展，甚至包括分支节点创建。而一个值被删除修改，叶子节点只是被标注为已删除，不会进行节点合并和回收。这样，正常环境下的索引应该是叶子“支离破碎”、“缓慢膨胀”的段结构。

回收空间、让叶子节点更加紧密是管理员考虑rebuild的基本出发动机。紧密的新索引的确空间占用比较小，检索速度也较快。但是之后插入、更新、删除的过程后，依然伴随着空间分配过程的损耗。所以，笔者个人认为：也许健康的索引结构就应该是“支离破碎”、“缓慢膨胀”。Coalesce操作提供的一种逻辑重组索引的方式，仅对索引树进行重组，不进行数据回收。

1、环境介绍

笔者选择11gR2进行实验。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE  11.2.0.3.0    Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

创建数据表T，构建索引。

SQL> create table t as select * from dba_objects ;

Table created

SQL> create index idx_t_id on t(object_id);

Index created

为了模拟效果，删除大部分数据构成死节点。

SQL> select max(object_id) from t;

MAX(OBJECT_ID)

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

        164092 

SQL> delete t where object_id<164092;

77405 rows deleted 

SQL> commit;

Commit complete

重新收集统计量。

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed 

SQL> commit;

Commit complete 

SQL> select count(*) from t;

  COUNT(*)

----------

        1

2、coalesce操作

Delete操作既不会回收数据段，也不会回收索引段。当前一行数据表T对应的段信息如下：

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner='SYS' and segment_name='T';

 EXTENT_ID    FILE_ID  BLOCK_ID      BYTES    BLOCKS

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

        0          1      86984      65536          8

        1          1      86992      65536          8

        2          1      87000      65536          8

        3          1      87008      65536          8

        4          1      87016      65536          8

        5          1      87024      65536          8

        6          1      87032      65536          8

        7          1      88960      65536          8

        8          1      88968      65536          8

        9          1      88976      65536          8

        10          1      88984      65536          8

        11          1      88992      65536          8

        12          1      89000      65536          8

        13          1      89008      65536          8

        14          1      90360      65536          8

        15          1      91008      65536          8

        16          1      89088    1048576        128

        17          1      89216    1048576        128

        18          1      89344    1048576        128

        19          1      89472    1048576        128

 

 EXTENT_ID    FILE_ID  BLOCK_ID      BYTES    BLOCKS

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

        20          1      89600    1048576        128

        21          1      89728    1048576        128

        22          1      89856    1048576        128

        23          1      89984    1048576        128

 

24 rows selected

索引段如下：

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner='SYS' and segment_name='IDX_T_ID'; 

 EXTENT_ID    FILE_ID  BLOCK_ID      BYTES    BLOCKS

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

        0          1      91704      65536          8

        1          1      91712      65536          8

        2          1      91720      65536          8

        3          1      91728      65536          8

        4          1      91736      65536          8

        5          1      91744      65536          8

        6          1      91752      65536          8

        7          1      91760      65536          8

        8          1      91768      65536          8

        9          1      92544      65536          8

        10          1      92552      65536          8

        11          1      92560      65536          8

        12          1      92568      65536          8

        13          1      92576      65536          8

        14          1      92584      65536          8

        15          1      92592      65536          8

        16          1      91776    1048576        128

17 rows selected

多extent结构，表示结构没有回收。下面使用analyze语句分析一下索引的情况：

SQL> analyze index idx_t_id validate structure;

Index analyzed

SQL> select height, blocks, lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks, del_lf_rows from index_stats;

    HEIGHT    BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS DEL_LF_ROWS

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

        2        256      77406        172    1227792      7996        171          1      77405

索引树两层结构，包括了256个数据库，叶子节点包括77406个，被删除节点77405个。

开启10046事件跟踪coalesce过程操作。

SQL> select value from v$diag_info where name='Default Trace File'; 

VALUE

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

/home/oracle/app/diag/rdbms/awpdb/awpdb/trace/awpdb_ora_14931.trc 

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered. 

SQL> alter index idx_t_id coalesce;

Index altered. 

SQL> alter session set events '10046 trace name context off';

Session altered.

操作之后检查一下结构效果。

SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where owner='SYS' and segment_name='IDX_T_ID';

 EXTENT_ID    FILE_ID  BLOCK_ID      BYTES    BLOCKS

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

        0          1      91704      65536          8

        1          1      91712      65536          8

        2          1      91720      65536          8

        3          1      91728      65536          8

        4          1      91736      65536          8

        5          1      91744      65536          8

        6          1      91752      65536          8

        7          1      91760      65536          8

        8          1      91768      65536          8

        9          1      92544      65536          8

        10          1      92552      65536          8

        11          1      92560      65536          8

        12          1      92568      65536          8

        13          1      92576      65536          8

        14          1      92584      65536          8

        15          1      92592      65536          8

        16          1      91776    1048576        128

17 rows selected

索引段存储分配没有发生变化，还是17个extent。但是索引逻辑结构已经变化：

SQL> analyze index idx_t_id validate structure;

Index analyzed

SQL> select height, blocks, lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks, del_lf_rows from index_stats;

    HEIGHT    BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS DEL_LF_ROWS

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

        2        256          1          1          16      7996          0          1          0

索引高度和分配块数量没有变化，但是叶子节点进行了重组。被删除数据节点被整理合并。

3、10046文件分析

从10046事件文件分析的情况看，如下：

=====================

PARSING IN CURSOR #139851695602760 len=29 dep=0 uid=0 oct=11 lid=0 tim=1427182487640740 hv=4054144165 ad='aa2f2710' sqlid='a88sghvsuap55'

alter index idx_t_id coalesce

END OF STMT

PARSE #139851695602760:c=17997,e=56662,p=9,cr=117,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1427182487640739

根据游标编号，可以定位到检索读取数据过程。

WAIT #139851695602760: nam='db file sequential read' ela= 8 file#=1 block#=91705 blocks=1 obj#=164093 tim=1427182487878712

WAIT #139851695602760: nam='db file sequential read' ela= 6 file#=1 block#=91706 blocks=1 obj#=164093 tim=1427182487878751

WAIT #139851695602760: nam='db file sequential read' ela= 8 file#=1 block#=91707 blocks=1 obj#=164093 tim=1427182487878989

WAIT #139851695602760: nam='db file sequential read' ela= 9 file#=1 block#=91708 blocks=1 obj#=164093 tim=1427182487879576

WAIT #139851695602760: nam='db file sequential read' ela= 9 file#=1 block#=91709 blocks=1 obj#=164093 tim=1427182487879914

（篇幅原因，有省略……）

WAIT #139851695602760: nam='db file sequential read' ela= 7 file#=1 block#=91821 blocks=1 obj#=164093 tim=1427182487929761

大量单块读动作，每次集中在164093编号的对象上。

SQL> select object_name, owner from dba_objects where object_id=164093;

OBJECT_NAM OWNER

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

IDX_T_ID  SYS

说明：合并操作是针对原有索引数据进行读取，之后合并索引。

4、结论

相对于rebuild，coalesce操作讨论的比较少，伴随着结构的变化，并没有发生存储结构的调整回收。相对于rebuild，coalesce有几个优势：

  1. 不需要占用近磁盘存储空间 2 倍的空间
  2. 可以在线操作
  3. 无需重建索引结构，而是尽快地合并索引叶块，这样可避免系统开销过大
caoyhao

caoyhao

相关推荐

Oracle中的NVL,NVL2,NULLIF,COALESCE函数

NULLIF函数的作用是如果exp1和exp2相等则返回空，否则返回第一个值。COALESCE函数的作用是的NVL的函数有点相似，其优势是有更多的选项。如果第二个参数是空再看第三个参数是否为空，否则显示第二个参数，依次类推。COALESCE里的所有参数类型

Justdoit00 / 0评论 2020-07-04

oracle的一些函数收集

　　　　 依次遍历括号内的表达式，遇到非null值的时候返回该值。　　　　 处理多表查询的时候，处理空值。select ‘‘,‘b‘,‘c‘ from dual;select to_number(‘‘),0 from dual --所有的表

Streamoutput / 0评论 2019-12-05

解决 mybatis 使用sum 返回null 的 问题

　　在mybati中使用sum函数，如果返回的值是0,也就是没有数据可以计算的时候，不会返回0而是返回null.而我们在mapper定义的时候用的是integer,这样就会报错了。有个函数COALESCE,返回的是第一个非等于null的值。sql这样写CO

lonyness / 0评论 2018-12-12

mysql中的coalesce用法

在mysql中，其实有不少方法和函数是很有用的，这次介绍一个叫coalesce的，拼写十分麻烦，但其实作用是将返回传入的参数中第一个非null的值，比如。这个参数使用的场合为：假如某个字段默认是null，你想其返回的不是null，而是比如0或其他值，可以使

北桥源木 / 0评论 2012-10-23

Spark 重分区函数：coalesce和repartition区别与实现，优化Spark

Return a new RDDthat is reduced into numPartitions partitions. This results in a narrowdependency, e.g. if you go from 1000 part

RiverCode / 0评论 2018-08-20

Oracle DB NVL、NVL2、NULLIF、COALESCE函数

NVL函数将空值转换为实际值： 可以使用的数据类型为日期、字符和数字。数据类型必须匹配：– NVL– NVL– NVL要将空值转换为实际值，请使用NVL函数。语法NVL 在该语法中： expr1是可能包含空值的源值或表达式 expr2是用于转换空值的目标值

Before / 0评论 2013-10-20

Oracle COALESCE函数

Oracle COALESCE函数语法为COALESCE，n>=2,此表达式的功能为返回第一个不为空的表达式，如果都为空则返回空值。EMPNO ENAME COMM NEW_COMM---------- ------------

PoPoCandy / 0评论 2013-03-09

PostgreSQL的缩小数据集优化过程一例

小黄同学拿过来一句SQL问我，看看能不能优化，看了下，数据量不是很大，查询还不算很复杂。coalesce(sum(surgery_quantity + repair_quantity + orthod_quantity + radiation_quanti

81500491 / 0评论 2012-12-22

MySQL Left JOIN时指定NULL列返回特定值

我们有时会有这样的应用，需要在sql的left join时，需要使值为NULL的列不返回NULL而时某个特定的值，比如0. 这个时候，用is_null是行不通的，会报错的，可以用ifnull实现，但是COALESE似乎更符合标准。coalesce 函数可

jesseyoung / 0评论 2012-12-19

mysql中coalesce()的使用技巧小结

最近无意间发现mysql的coalesce，又正好有时间，就把mysql中coalesce()的使用技巧总结下分享给大家，下面来一起看看详细的介绍：。返回参数中的第一个非空表达式；select coalesce; // Return 1通过上面例子可以看出

rmrfmysql / 0评论 2019-04-15

mysql中替代null的IFNULL()与COALESCE()函数详解

在MySQL中isnull()函数不能作为替代null值！SELECT ISNULL AS bus_isnull FROM business WHERE id=2直接运行就会报错：。Incorrect parameter count in the call

MySQLl / 0评论 2019-04-15

SQL Server COALESCE函数详解及实例

很多人知道ISNULL函数，但是很少人知道Coalesce函数，人们会无意中使用到Coalesce函数，并且发现它比ISNULL更加强大，其实到目前为止，这个函数的确非常有用，本文主要讲解其中的一些基本使用：。至少应有一个 Null 值为 NULL 类型。

cxiaole / 0评论 2019-04-14

MySQL Left JOIN时指定NULL列返回特定值详解

coalesce 函数可以接受多个参数，将会返回这些参数中第一个非NULL的值，若提供的参数全部为NULL，则返回NULLifnull 函数和coalesce功能一样，只是只可以接受两个参数if 函数接受三个参数，实现类似于三元判断符(?SELECT a.

jjwwMySQL / 0评论 2019-04-06

Oracle数据库中COALESCE函数使用详解

　　数据库应用软件很多时候将多重的、相关的实体信息保存在同一个表格中。　　处理这样的表格通常意味着对每一行进行条件测试来检查哪个实体类型正在被处理，然后返回每种类型的不同结果集。CASE语句可以用来完成这一工作。　　使用COALESCE的秘密在于大部分包含

oracle / 0评论 2007-01-20

[Javascript Crocks] Recover from a Nothing with the `coalesce` M

Thealtmethod allows us to recover from a nothing with a default Maybe, but sometimes our recovery efforts might need to enlist s

墙里墙外 / 0评论 2018-05-14