Index Size

How do you find indexes that might be worth the effort of rebuilding.

One option is to report indexes that take up much more space than they need to – and there are some fairly easy ways to find those indexes. 10g, for example, gave us the create_index_cost procedure in the dbms_space package so, for a single index, we can write a little routine that does something like the following example – running on a 10.2.0.3 database:

rem
rem     Script:         index_space.sql
rem     Dated:          Aug 2008
rem     Author:         J.P.Lewis
rem

declare
        m_used  number;
        m_alloc number;
begin
        dbms_space.create_index_cost(
                ddl             => 'create index t2_i1 on t2(n1, n2)',
                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: 131072

You may wonder why there is such a difference between used bytes and allocated bytes. There are two parts to the explanation, and to give you a clue to one part of the reason I’ll repeat the test but change the DDL slightly:

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

After including the tablespace name in the create index statement the ‘allocated bytes’ has jumped from 128KB to 1MB.

Part of the explanation of the difference between allocated and used comes from tablespace extent sizing – after working out the space that the index needs Oracle then works out the extent allocation needed in the relevant tablespace to hold that index … and different tablespaces may have different space management definitions.

But here’s an unexpected detail – if you don’t specify a tablespace the code uses the default_tablespace of the sys schema as the target tablepace! This is a little odd; you might have expected the default to be the default_tablespace of the (nominal) owner of the index – especially since the dbms_space package is defined as authid current_user. This doesn’t change even if you include the index owner explicitly in the DDL. So make sure you specify the tablespace (and any other storage parameters you expect to use) in your create index statement or the allocated bytes may turn out to be a poor estimate of the final segment size.

But there’s more

My first test gave 24,000 bytes as the used bytes – and that should fit comfortably inside a single 64KB extent in the system tablespace – so why was the allocated space reported as 128KB / two extents?

Another little demonstration, using a relatively new feature of explain plan,  gives us a clue:

explain plan for
create index t2_i1 on t2(n1, n2)
;

select * from table(dbms_xplan.display(null,null,'-cost'));

PLAN_TABLE_OUTPUT
----------------------------
Plan hash value: 2685098699

-------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Time     |
-------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |       |  3000 | 24000 | 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| T2_I1 |       |       |          |
|   2 |   SORT CREATE INDEX    |       |  3000 | 24000 |          |
|   3 |    TABLE ACCESS FULL   | T2    |  3000 | 24000 | 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - estimated index size: 131K bytes

14 rows selected.

Notice the value in the Bytes column of operation 3; this is the value reported as used bytes by the create_index_cost procedure – and if you enable sql_trace when running that procedure you will find that it really does call explain plan to generate the figures it reports. (There is a fourth optional parameter to the procedure that can be used to specify the plan_table that should be used to do this.)

This figure is simply: dba_tables.num_rows * sum(dba_tab_columns.avg_col_len). It doesn’t allow for the various overheads of an index entry – i.e. the stored rowid, the lock byte, the flag byte, the length byte(s), or the two byte pointer to each row in the leaf block’s row directory – but it looks as if this information is included as a step in the final extent calculations.

This arithmetic introduces another pair of errors: first, the sizing estimate doesn’t allow for index compression, [update: even in 19c] so the estimate can be much larger than it really should be; secondly the avg_col_len is calculated as a weighted average that allows for nulls then rounds up to the nearest whole number so in cases where there are lots of nulls in a column it’s easy for the final estimate to be too big.

Conclusion:

Oracle offers us a packaged procedure that makes it easy to find out roughly how big an index ought to be, and how much space it ought to take up – but it has a number of flaws and should be treated with a little caution.

Of course, it’s also a little heavy handed to call this procedure (and the implicit explain plan for every index in your system – so in a future note I’ll be showing you a simple piece of code that you might feel happy to run against every (simple, B-tree) index in your database.