Posted by 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 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.
Learning is not a spectator sport 