Re: Algorithm for calculating extent size in LMT

There is the case of partitioned tables, of course -

    create empty table for latest "period"     sqlload empty table
    index loaded table
    add partition (N data segments)
    exchange partition with loaded table     drop empty table

But as you point out, and especially if you do this regularly, you will have sized the extents suitably.

The thing that I find really nice with LMTs, though, is that when all the fuss has died down and the tablespaces for a time-period are no longer subject to change, you can move/rebuild all the partitions and they migrate to the "front" of the tablespace' files - allowing you to trim the files to minimum size before making them readonly and backing them up. With DMTs it can be tricky 'making' Oracle rebuild an object into exactly the right place in a file to get the same effect.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Thomas Kyte wrote in message ...


>

>the BIG different for DMT's is doing ddl (truncate, drop) on an object with

tons


>of extents can take a while.  But then again, in a production system -- do

you


>truncate or drop often (and if you do -- you can easily fix the problem by

>better sizing the extents -- point being that if you have a DMT table in

>production with 1,000 extents and you are not planning on

truncating/dropping it


>-- don't worry about it.  If you do plan on dropping/truncating it -- fix

the


>extent size after you do that in order to avoid this issue in the future.)

>