Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: An index being rebuilt changes it's extent sizes?

RE: An index being rebuilt changes it's extent sizes?

From: Hamid Alavi <HamidA_at_aimdirect.co.nz>
Date: Thu, 2 Nov 2000 09:49:01 +1300
Message-Id: <10667.120868@fatcity.com>


Diana,
do this for change the default tablespce:

Alter index <index_name> modify default attributes tablespace <ACCTO_INDX>

-----Original Message-----
From: Ruiz, Mary A (CAP, CDI) [mailto:Mary.Ruiz_at_gecapital.com] Sent: Thursday, 2 November 2000 09:30
To: Multiple recipients of list ORACLE-L Subject: RE: An index being rebuilt changes it's extent sizes?

Diana:

   what is the default storage for the tablespace ACCT0_INDX ? Did you know you can put a storage clause on your rebuild statement to override the tablespace's default initial extent size ?

Mary Ruiz/ Atlanta

-----Original Message-----
Sent: Wednesday, November 01, 2000 1:46 PM To: Multiple recipients of list ORACLE-L

List,

I'm very confused. I have a partitioned index created with LOCAL into a locally managed tablespace with uniform extent sizes of 480K. When I create the index, everything is OK, except it goes into the data tablespaces where the base table is (because of the LOCAL). The extent sizes are 480K, which is 30 blocks. For reference, the statement is:

CREATE INDEX DOC_INDEX_VALUE_LOWER_STRING ON DOC_INDEX_VALUE(DOMAIN_ID, lower(string_value), INDEX_ID)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE ACCT0_INDX
STORAGE (INITIAL 480K NEXT 480K)
LOCAL
;

Then, I rebuild each partition into the associated index tablespace using a command like the following:

alter index DOC_INDEX_VALUE_LOWER_STRING rebuild partition ACCT1051 tablespace ACCT1051_INDX;

After the rebuild, the index partitions suddenly have 330 blocks rather than 30. Any idea why? This only happens with the one index out of all of them. It is a function-based index, but it isn't the only one...

Any ideas? I'm baffled. ;-)

TIA, Diana Duncan
Database Architect
filefrenzy
diana_at_filefrenzy.com
919.760.1151 <-- New number
--

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

Author: Diana Duncan
  INET: Diana_at_fileFRENZY.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: Ruiz, Mary A (CAP, CDI)
  INET: Mary.Ruiz_at_gecapital.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 Received on Wed Nov 01 2000 - 14:49:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US