initial/next computation with DOP 4

From: Barbara Baker <barbarabbaker_at_yahoo.com>
Date: Sun, 23 Feb 2003 16:23:42 -0800
Message-ID: <F001.00556213.20030223162342@fatcity.com>

OpenVMS 7.1-2
Oracle 8.1.7.4
db_block_size 4096

I need to re-create a large table and its associated indexes as fast as possible, and with a limited amt of disk space. I have a new tblspace at 7000m for index creation.

I'm creating indexes with DOP4. (I really need the speed I get with parallel to create the indexes.) In my testing, I keep running out of contiguous space in my index tablespace -- it gets fragmented all-to-blazes. I end up with each index at 4 extents.

I have a pretty good estimate of how large the indexes will be. Is there some sane way to compute a reasonable initial and next extent when using parallel?

As an example, I created an index (wodh_pk) with initial 600m next 20m pctincrease 0. The index is now 1334m with 4 extents. If I know the index should be about 1300 megs, what's a good initial and next size?

Thanks for any assistance!
Barb

SELECT SEGMENT_NAME, BYTES/1024/1024 M, EXTENTS, NEXT_EXTENT,
  PCT_INCREASE FROM DBA_SEGMENTS
  WHERE TABLESPACE_NAME='ARCHIDX';  

Segment                             Next         Pct 
    Name              M Extents      Extent   Increase

------------ ---------- ------- ------------ ---- 
WODH_PK      1333.55859       4   20,971,520        0 
WODH_FK1     821.289063       4   20,971,520        0 

here's the code . . .

create unique INDEX
 REPORT_REP.WODH_PK
 ON
REPORT_REP.WORK_ORDER_DETAILS_NOHIST(WORK_ORDER_KEY)  TABLESPACE ARCHIDX

                    STORAGE(INITIAL 600M 
                            NEXT 20M 
                            MINEXTENTS 1 
                            MAXEXTENTS 249) 

PARALLEL (DEGREE 4)
;

---

Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Barbara Baker
  INET: barbarabbaker_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Sun Feb 23 2003 - 18:23:42 CST