Detect Index Coalesce

indexoracleoracle-11g-r2

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;

Best Answer

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.

Related Question