Learning is not a spectator sport

July 26, 2013

How big will my index be ?

Filed under: Uncategorized — connormcdonald @ 6:44 pm

You’ve got a big table…you want to add an index.  Will you have enough space ?

Well, its not perfect but you can a reasonable approximation using DBMS_SPACE

SQL> drop table T purge;

Table dropped.

SQL> create table T nologging as
  2  select d.* from dba_objects d,
  3  ( select 1 from dual connect by level <= 100 )
  4  /

Table created.

SQL> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------


SQL> set serverout on
SQL> declare
  2    x int;
  3    y int;
  4  begin
  5    dbms_space.CREATE_INDEX_COST(
  6       'create index ix on T ( owner, object_type, object_id)'
  7       ,used_bytes=>x
  8       ,alloc_bytes=>y);
  9    dbms_output.put_line(x);
 10    dbms_output.put_line(y);
 11  end;
 12  /
366481370
528482304

PL/SQL procedure successfully completed.

So not statistics, the estimate is about 500MB. Now we calculate stats

SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL> set serverout on
SQL> declare
  2    x int;
  3    y int;
  4  begin
  5    dbms_space.CREATE_INDEX_COST(
  6       'create index ix on T ( owner, object_type, object_id)'
  7       ,used_bytes=>x
  8       ,alloc_bytes=>y);
  9    dbms_output.put_line(x);
 10    dbms_output.put_line(y);
 11  end;
 12  /
153222000
268435456

PL/SQL procedure successfully completed.

And the estimate is different. Lets see if it understands COMPRESS

SQL> set serverout on
SQL> declare
  2    x int;
  3    y int;
  4  begin
  5    dbms_space.CREATE_INDEX_COST(
  6       'create index ix on T ( owner, object_type, object_id) compress 2'
  7       ,used_bytes=>x
  8       ,alloc_bytes=>y);
  9    dbms_output.put_line(x);
 10    dbms_output.put_line(y);
 11  end;
 12  /
153222000
268435456

PL/SQL procedure successfully completed.

Hmmm…not brilliant. Anyway, lets look at what the real space usage is

SQL> create index ix on T ( owner, object_type, object_id);

Index created.

SQL> select bytes from user_segments where segment_name = 'IX';

     BYTES
----------
 268435456

SQL> select leaf_blocks*8192 from user_indexes where index_name ='IX';

LEAF_BLOCKS*8192
----------------
       264577024

SQL> alter index ix rebuild compress 2;

Index altered.

SQL> select bytes from user_segments where segment_name = 'IX';

     BYTES
----------
 148897792

SQL> select leaf_blocks*8192 from user_indexes where index_name = 'IX';

LEAF_BLOCKS*8192
----------------
       139190272

So the estimate was pretty good for the uncompressed index.  Of course, the space an index occupies is not necessarily the same as the amount of temporary space that might be needed to create that index.  But at least its a start.

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The WordPress Classic Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: