Oracle-L: Re: extents question on LMT

From: <Jared.Still_at_radisys.com>
Date: Tue, 28 Jan 2003 12:34:02 -0800
Message-ID: <F001.0053C7DF.20030128123402@fatcity.com>

No, you can't compress into a single extent if the TBS is LMT with uniform extent size, and the size of the index is
> extent size. Don't know about auto extent size feature.
Why would you want to compress into a single extent? There's no benefit to doing so.

Jared

"Guang Mei" <zlmei_at_hotmail.com>
Sent by: root_at_fatcity.com
01/28/2003 12:06 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        extents question on LMT

Hi:

Oracle 8173 on Sun 2.8.

When we had tablespace created as DMT, I used to occuasionally find all indexes in a schema which have multiple extents and run a script to compress
each of them into one single extent (maybe this is not necessary, but that's
another topic). But I find lately that with LMT, I can not simply do that.

Oracle will decide how many extents it allocates depending on the storage parameters I pass in:

ALTER INDEX ID_IG_PK REBUILD TABLESPACE INDEXES STORAGE (INITIAL 5M NEXT 5M
pctincrease 0) nologging;

Does this mean I just can not "compress" some large index into a single extent in a LMT?

Example:

SQL> ALTER INDEX MT.ID_IG_PK REBUILD TABLESPACE INDEXES STORAGE (INITIAL 50K
NEXT 50K pctincrease 0) nologging;

Index altered.

SQL> select BYTES,EXTENTS,blocks, INITIAL_EXTENT,NEXT_EXTENT 2 from dba_segments where owner='MT' and segment_name='ID_IG_PK';

BYTES EXTENTS BLOCKS INITIAL_EXTENT NEXT_EXTENT ---------- ---------- ---------- -------------- -----------

516096 1 63 57344 516096

SQL> ALTER INDEX MT.ID_IG_PK REBUILD TABLESPACE INDEXES STORAGE (INITIAL 5M
NEXT 5M pctincrease 0) nologging;

Index altered.

SQL> select BYTES,EXTENTS,blocks, INITIAL_EXTENT,NEXT_EXTENT 2 from dba_segments where owner='MT' and segment_name='ID_IG_PK';

BYTES EXTENTS BLOCKS INITIAL_EXTENT NEXT_EXTENT ---------- ---------- ---------- -------------- -----------

5677056 11 693 5242880 516096

TIA. Guang

The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  INET: zlmei_at_hotmail.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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.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 Tue Jan 28 2003 - 14:34:02 CST