Re:How much TEMP space is needed for index creation

From: <dgoulet_at_vicr.com>
Date: Mon, 21 May 2001 06:12:39 -0700
Message-ID: <F001.00309266.20010521061059@fatcity.com>

Zabair,

Since you've got data in the table you must have an idea of how big the actual index will be. Rule of thumb for me it 3 times the size of the actual index for a normal b-tree index. Two times for a bit map. This has yet to fail me.

Dick Goulet

____________________Reply Separator____________________
Author: "zabair ahmed" <zabaira_at_hotmail.com>
Date:       5/21/2001 12:40 AM

OK, i've got a table with 302068428 rows. Im in the process of creating a nonunique index on this table, how much space do i need in the TEMP tablespace for the creation of this index.

Name                                      Null?    Type
----------------------------------------- -------- ---------------------
SEQUENCEID                                NOT NULL NUMBER(20)
TRANSACTIONCODE                                    VARCHAR2(1)
DISTRICT                                           VARCHAR2(3)
CUSTOMERACCOUNT                                    VARCHAR2(8)
CUSTOMERSUBACCOUNT                                 VARCHAR2(3)
SUBSCRIBERSEQNUM                                   VARCHAR2(7)
EFFECTIVEDATE                                      VARCHAR2(8)
PRICEABLEITEMID                                    VARCHAR2(5)
BASICPRICEABLEITEMID                               VARCHAR2(5)
SERVICESTATUSCODE                                  VARCHAR2(2)
SERVICINGSALESFORCEID                              VARCHAR2(8)
SERVICINGSALESFORCELVL                             VARCHAR2(1)
SUPPLSERVSTARTDATE                                 VARCHAR2(8)
ORIGSUPPLSERVSTARTDT                               VARCHAR2(8)
PRICEABLEITEMTYPE                                  VARCHAR2(2)
INPLANIND                                          VARCHAR2(1)
BILLFREQUENCY                                      VARCHAR2(1)
MAILBOXNAME                                        VARCHAR2(30)
MAILBOXLANGUAGE                                    VARCHAR2(2)
PAGERTYPE                                          VARCHAR2(1)
APPACCESSMAILBOXNO                                 VARCHAR2(10)
TELEANSWLANUAGE1                                   VARCHAR2(2)
TELEANSWLANUAGE2                                   VARCHAR2(2)
TELEANSWLANUAGE3                                   VARCHAR2(2)
SERVICESEQNUM                                      VARCHAR2(7)
SWITCHRELATEDIND                                   VARCHAR2(1)
EXPIRATIONDATE                                     VARCHAR2(8)
UPLOADDATE                                         DATE
D2SUBID                                            NUMBER
SWPRODRELID                                        NUMBER
D_EFFECTIVEDATE                                    DATE
D_EXPIRATIONDATE                                   DATE

The non-unique index will be on these columns, SEQUENCEID, D_EFFECTIVEDATE, D_EXPIRATIONDATE, SERVICESTATUSCODE, TRANSACTIONCODE The sort area size is set to 10000000.

How do i work out how much space is needed.

TIA Zabair

Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

--

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

Author: zabair ahmed
INET: zabaira_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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).
--

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

Author:
INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------