Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h1O4HIk03758 for ; Sun, 23 Feb 2003 22:17:18 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h1O4HHQ03753 for ; Sun, 23 Feb 2003 22:17:17 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id RAA43541; Sun, 23 Feb 2003 17:00:36 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00556213; Sun, 23 Feb 2003 16:23:42 -0800 Message-ID: Date: Sun, 23 Feb 2003 16:23:42 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Barbara Baker Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Barbara Baker Subject: initial/next computation with DOP 4 Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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@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@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).