Detect Index Coalesce

Question

Is there a way to know if a COALESCE has been done on an index? Doing one doesn't seem to update the CREATED, LAST_DDL_TIME, or LAST_ANALYZED attributes.

Note: I am not referring to the COALESCE that returns the first non-null expr in the expression list, but the one that is used to make free space in a block contiguous like this:

ALTER INDEX [Index Name] COALESCE;

Answer 1

It is not completely clear what your after with your question. I assume you want to know if and importantly when an alter index .. coalesce was performed.

I think without (an in other answers already mentioned auditing setup) it is impossible to get such a date (although I would want to know if someone knows better).

That said, the effects of an alter index coalesce can be made visible with a analyze index .. compute statistics.

create table ix_test (
  a number
);

create unique index ix_test_ix on ix_test(a);

insert into ix_test 
select
  rownum 
from
  dba_objects, dba_objects, dba_objects
where
  rownum < 1e7;

commit;

Now that the table is filled, the index is analyzed and some index related values extracted:

analyze index ix_test_ix compute statistics;

select
  LEAF_BLOCKS,
  AVG_LEAF_BLOCKS_PER_KEY,
  CLUSTERING_FACTOR,
  SAMPLE_SIZE
from
  user_indexes
where 
  index_name = 'IX_TEST_IX';

LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR SAMPLE_SIZE
----------- ----------------------- ----------------- -----------
          2                   19875                 1       15152

All but one row deleted and index analyzed (again):

delete from ix_test where a != 500000;
analyze index ix_test_ix compute statistics;

The query above returns the same values.

Coalescing the index:

alter index ix_test_ix coalesce;
analyze index ix_test_ix compute statistics;

The query above now returns

LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR SAMPLE_SIZE
----------- ----------------------- ----------------- -----------
          1                       1                 1           1

So, there is a possibility to find out if there was a coalescing done, but it's not straight forward and probably useless, depending on your context.

Answer 2

If you do a coalesce, isn't it worth gathering indexes at the same time. But have you tried AUDIT

http://psoug.org/reference/auditing.html

Answer 3

The solution I used doesn't give me exactly what I wanted, but did provide evidence that a COALESCE had been done. I did an ANALYZE and looked at the number of leaf blocks in USER_INDEXES. Then I did an Gather_Index_Stats and compared the number of leaf blocks. If the ANALYZE was showing more leaf blocks than Gather_Index_Stats then a COALESCE could consolidate free space and therefore was likely not done. If the numbers were the same then either no COALESCE was necessary, or it was done. Here is my test script.

--Setup Table and Index.
create table t1 as select mod(rownum,5) myrow from dual connect by rownum<=1500000;
create index t1i1 on t1 (myrow);
delete from t1 where myrow IN (1,2,3);
commit;

--Analyze table and examine leaf blocks.
analyze table t1 compute statistics;
select leaf_blocks from user_indexes where index_name='T1I1';
--Gather Stats and examine leaf blocks.
execute sys.dbms_stats.Gather_Index_Stats(ownname=>user,indname=>'T1I1');
select leaf_blocks from user_indexes where index_name='T1I1';

--If there is a difference in the number of leaf blocks shown between running 
--these then a coalesce would be able to coalesce something.

alter index t1i1 coalesce;

--Analyze table and examine leaf blocks.
analyze table t1 compute statistics;
select leaf_blocks from user_indexes where index_name='T1I1';
--Gather Stats and examine leaf blocks.
execute sys.dbms_stats.Gather_Index_Stats(ownname=>user,indname=>'T1I1');
select leaf_blocks from user_indexes where index_name='T1I1';