Fragmentation

Dear friends,

Could anybody explain me how is the best way (in the storage clause) to create a table/tablespace to minimize fragmentation ? I didn´t understand it yet.

Hugs,

Rômulo.

You would want your table to fit into one large extent.
However, table size may be unpredictable and if you are using locally managed tablespaces (Oracle 8i and above), you need not worry much about fragmentation.

Use the MINIMUM EXTENT clause while creating the tablespace so that any extent deallocated can be used effectively, thus reducing the wastage of space.

Here is a link on Fragmentation:
http://otn.oracle.com/deploy/performance/pdf/defrag.pdf

If u r using Oracle8i, Locally managed tablespaces can help.ANd better knowing your data will
certainly.

what I do is

in Oracle 8

create several tablespaces, probbaly 5, each tablespace suits for each size of segments

micro
small
medium
big
maco

use minimum extent clause always, number of extents doesnt really affect perfomance in reading but when allocating it will, dictionary tables would have migrated, chained rows and high ST enqueue contention

in Oracle 8i use
LMT with uniform size extents, create several as above although number of extents doesnt affect perfomance in LMT

Thanks for every answers.

Hugs.

Rômulo