Create Index Slow down

[kozlow]

When I drop and recreate a 4Gig index, it is taking forever.

I see low read I/O from the Data Tablespace Disk and low I/O to the Temp Tablespace Disk.

I am running UDB 7.2 on a AIX server.

If the Indexes are smaller then 2 Gig, it flies through... I am guessing there is a tunable parm that I may be able to increase to help...

Any suggestions??????

 

[gregsimpson]

Kozlow,

I wonder whther it's down to page splits. See the following, icopied directly from the manual.

Cheers
Greg

The PAGE SPLIT SYMMETRIC, PAGE SPLIT HIGH, and PAGE SPLIT LOW clauses allow a choice in the page 4 split behavior when inserting into an index.

4 The PAGE SPLIT SYMMETRIC clause is a default page split behavior that splits roughly in the middle 4 of an index page. 4 Using this default behavior is best when the insertion into an index is random or does not follow one of the patterns 4 that are addressed by the PAGE SPLIT HIGH and PAGE SPLIT LOW clauses.

4 The PAGE SPLIT HIGH behavior is useful when there are ever increasing ranges in the index. 4 Increasing ranges in the index may occur when:

4 4 There is an index with multiple key parts and there are many values 4 (multiple index pages worth) where all except the last key part have the same value 4 All inserts into the table would consist of a new value which has the same value as existing keys for all 4 but the last key part 4 The last key part of the inserted value is larger than that of the existing keys
4 For example, if we have the following key values in the index; 4
4 (1,1),(1,2),(1,3), ... (1,n),
4 (2,1),(2,2),(2,3), ... (2,n),
4 ...
4 (m,1),(m,2),(m,3), ...(m,n)
4 then the next key to be inserted would have the value (x,y) where 1 <= x <= m and y > n. 4 If the insertions follow such a pattern, the PAGE SPLIT HIGH clause can be used so that page splits do not 4 result in many pages that are fifty percent empty.

4 Similarly, PAGE SPLIT LOW can be used when there are ever-decreasing ranges in the index, to avoid leaving 4 pages 50 percent empty.

Note:
4 4 If you want to add a primary or unique key, and you want the underlying index to use 4 SPLIT HIGH, SPLIT LOW, PCTFREE, LEVEL2 PCTFREE, MINPCTUSED, CLUSTER, or ALLOW REVERSE SCANS 4 you must first create an index specifying the desired keys and parameters. 4 Then use an ALTER TABLE statement to add the primary or unique key. 4 The ALTER TABLE statement will pick up and reuse the index that you have already created.