Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
Thanks for the clarification! –  jcolebrand Jan 25 '11 at 20:29
add comment

3 Answers

up vote 8 down vote accepted

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.

share|improve this answer
    
The date would have been nice, but I think gathering evidence that a Coalesce has occurred is about the best we can do. The solution I came up with is similar to yours, but from a slightly different angle, so I will post it separately. –  Leigh Riffel Jan 26 '11 at 14:03
add comment

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

share|improve this answer
    
Index operations for CREATE INDEX, ALTER INDEX, ANALYZE INDEX, and DROP INDEX can be audited. Does a Coalesce invoke one of these and if so is there a way to distinguish it from the named audit action? –  Leigh Riffel Jan 25 '11 at 21:21
add comment

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';
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.