RE: Size of segment

Shreepad,

There is no problem with a segment having more than 1 extent. It is a common misconception (another of those Myths, if you please) that all tables and indexes should reside in 1 extent, or less than 10 extents even. There is a paper available from the web - that many say is the definitive word on fragmentation of extents, called "How to stop defragmenting and start living", written by Juan Loaiza (sp?) which a google search should easily find..

The basics of this paper are thus:

You should have a set of three tablespaces (for data and indexes) that each deal with a UNIFORM extent size, ranging through 160k, 5120k and 160M at the tablespace level. When creating a new object (table or index), you should determine how large the table/index is going to be, and create it in the appropriate tablespace, without defining specific storage clauses at the table level - therefore inheriting the Tablespace storage clauses as a default (gaining the uniform extent size for all objects within the tablespace). Of course, if you are already using LMTs (Locally Managed Tablespaces) with uniform extent allocation, then the need to do this has gone away.

Once a table/index reaches 1024 extents (much more than the myth of 1-10 extents), then the object should be considered for a move up to a larger extent sized tablespace (and will still, by all accounts, take up 32 extents from the start of it's life in the new tablespace anyway - if moved straight away).

I would again recommend reading the paper above - it's been a while since I had the chance to read it, and I'm not sure if it's been updated at all
(anybody?), but it has an absolute wealth of information!

HTH Mark

 Mark Leith             | T: +44 (0)1905 330 281
 Sales & Marketing      | F: +44 (0)870 127 5283
 Cool Tools UK Ltd      | E: mark_at_cool-tools.co.uk
===================================================
           http://www.cool-tools.co.uk
       Maximising throughput & performance

-----Original Message-----
Shreepad.Vaidya_at_alltel.com
Sent: 30 April 2002 12:04
To: Multiple recipients of list ORACLE-L

Hi,
Thanks to all those who replied. Just clearing the structure.

I have Hp LVM and the tables and indexes are on different tablespaces and different disks .

Since the growth pattern has changed drastically , the no of extents have increased tremendously . Hence the need to

rebuild the indexes .

We plan to rebuild them into contiguous extents (only question being 1 extent or say 4-5 extents to take care of growth:--- 1 extent every 2 months )

      Will the performance differ if they are on 1 extent or 5 extents ( both being contiguous) ?

                  thanks

                        shreepad

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: Shreepad.Vaidya_at_alltel.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: Mark Leith
  INET: mark_at_cool-tools.co.uk

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