jcon.no: Oracle blog

For several years now it’s has been clear that we shouldn’t just rebuild an index. In the worst case this could make performance even worse than it used to be (for the next following days). So the question is “What indexes should be rebuilt?”. I just read this article from Jonathan Lewis that might just give us a possible answer. In 10g Oracle introduced the DBMS_SPACE.create_index_cost procedure, which lets us estimate how big the index should be.

Here is an example given by Jonathan Lewis:

declare
    m_used  number;
    m_alloc number;
begin
    dbms_space.create_index_cost(
        ddl     => 'create index t2_i1 on t2(n1, n2) TABLESPACE TEST_8K',
        used_bytes  => m_used,
        alloc_bytes => m_alloc
    );
    dbms_output.put_line(
        'Used bytes: ' || m_used ||
        '...allocated bytes: ' || m_alloc);
end;
/
 
Used bytes: 24000...allocated bytes: 1048576

Note! Be careful using the TABLESPACE syntax, if not Oracle will use the default settings of SYSs default tablespace to calculate allocated bytes.

The article also includes some related links. The first link is to a script that checks all the indexes for a spesific schema. By the way – DO READ the notes in the script. There is also a link to a index analysis on FIFO columns. This article uses a LAST_UPDATE_DATE as an example. Such indexes might be candidates for rebuilds.