Data … as usual

Today, one of my client asks me how to monitor index creation and was a little bit curious about what happened during this process.

To answer him, I remember operation that are executed to build an index:

1- Data needed to build the index is read

2- A sort segment is created

3- The index is progressively build as a temporary segment in the destination tablespace

The first phase is monitored like a classic read operation : V$SESSION_WAIT, 10046 event trace etc.

Next phase can be monitored by querying the V$SORT_USAGE view:

select USERNAME,SESSION_NUM,TABLESPACE,CONTENTS,SEGTYPE,BLOCKS*dbbs/1024/1024 as sizeMb
  2* from v$sort_usage, (select to_number(value) dbbs from v$parameter where name='db_block_size')

USERNAME   SESSION_NUM TABLESPACE CONTENTS  SEGTYPE       SIZEMB
---------- ----------- ---------- --------- --------- ----------
LAURENT             46 TEMP       TEMPORARY SORT             101

Finally, when the sort segment has been created, you will see a new segment in the index tablespace. This segment is a TEMPORARY segment with a numeric format name (Usually the final size is close to the sort segment size) :

select segment_name,segment_type,sum(bytes)/1024/1024/1024 as SIZE_GB
from dba_segments
where tablespace_name='DWH_P_IDX'
group by segment_name,segment_type;
SEGMENT_NAME                   SEGMENT_TYPE       SIZE_GB
------------------------------ ------------------ -------
.../...
14.4483                        TEMPORARY           1.1875
.../...

If you repeat this query, you will see the segment size grow. When the operation ends, this temporary segment becomes an INDEX segment which is … your index.