Re: Building indexes in parallel

From: Naren Chintala <naren_at_mink.att.com>
Date: 1996/09/27
Message-ID: <324BDF39.24C2_at_mink.att.com>#1/1

Walter Muntzenberger wrote:
>
> Naren Chintala wrote:
> >
> > chuckh_at_dvol.com (Chuck Hamilton) wrote:
> > >Is it possible to build a primary key index in parallel? If so, what's
> > >the syntax?
> > >--
> > >Chuck Hamilton
> > >chuckh_at_dvol.com
> >
> > Don't do it. It will fragment you index. Email me if you need more information.
> >
> > Naren Chintala
> > naren_at_mink.att.com
>
> What do you mean by fragment your index?
>
> --
> _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
> _/
> _/
> _/ Walter Muntzenberger _/_/ _/_/_/ _/_/_/
> _/
> _/ Principal Consultant _/ _/ _/ _/ _/
> _/
> _/ QED Systems Consulting _/ _/ _/_/_/ _/ _/
> _/
> _/ Email: wmuntz_at_ix.netcom.com _/ _/ _/ _/ _/
> _/
> _/ Text pager: 800.SKY.TEL2 pin 1645838 _/_/ _/_/_/ _/_/_/
> _/
> _/ _/
> _/
> _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

Hope this answers your question.

PARALLEL INDEX CREATION

---

As far as Oracle documentation and my experience "at the end the index extents are NOT combined".

When creating an index using the parallel, the STORAGE clause refers to the storage of each of the subindexex created by the query server process. Therefore, an index created with an initial of 5M and a parallel degree of 8 (like in our case) consumes at least 40M of storage during index creation because each process starts with an extent of 5M. When the query coordinator process combines the sorted subindexes, some of the extents may be trimmed, and the resulting index may be smaller than the requested 40M.

Please note that creating indexes frequently using the parallel query FRAGMENTS the tablespace. This MAY have an impact on query performance.

1. When the index is created WITHOUT the parallel query option then only ONE extent is allocated when the index is created.

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE EXTENT_ID BYTES ------ --------------- ------------- ---------- ---------- ---------- SYSTEM IDX1_ALARM_LOG INDEX APPL_INDX 0 135168000 = 132,000K 2) When the index is created WITH the parallel query option then the number of extents will be 8 (since we specified a paralel degree of 8 when creating the index). The initial extent for the parallel query option was specified as 16,500K (since 132,000/8 = 16,500KB). As can be seen from the results below the index is stored in 8 extents. This causes "FRGAMENTATION" of the index.

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE EXTENT_ID BYTES ------ --------------- ------------- ---------- ---------- ----------

SYSTEM IDX1_ALARM_LOG  INDEX         APPL_INDX           0   16916480
SYSTEM IDX1_ALARM_LOG  INDEX         APPL_INDX           1   16318464
SYSTEM IDX1_ALARM_LOG  INDEX         APPL_INDX           2   10223616
SYSTEM IDX1_ALARM_LOG  INDEX         APPL_INDX           3   15032320
SYSTEM IDX1_ALARM_LOG  INDEX         APPL_INDX           4   12378112
SYSTEM IDX1_ALARM_LOG  INDEX         APPL_INDX           5   11198464
SYSTEM IDX1_ALARM_LOG  INDEX         APPL_INDX           6   10846208
SYSTEM IDX1_ALARM_LOG  INDEX         APPL_INDX           7   12410880

Received on Fri Sep 27 1996 - 00:00:00 CEST