CREATE TABLE in 9.2 (Diffs from 8.1.5)

[JMac]

All our schema scripts were written for 7.3 but we used them in 8.1.5 anyway.

Now were migrating everything to 9.2.

The table scripts all have

INTITIAL
NEXT
PCTINCREASE
MINEXTENTS
MAXEXTENTS

set for them. In 9.2 I plan to use auto management of the extents. Do I need to/have to specify this in a 9i CREATE TABLE script? I suppose PCTINCREASE 0 need specifying, and possibly MAXEXTENTS? But INITIAL and NEXT?

What should the statement contain?

[slimdave]

best prectice would be to just assign the tables/indexes to tablespaces having an appropriate sent of properties.

If you have a mature system then you already know what the sizes of different segments are, so LMT's with uniform allocation would appear to be appropriate.

You don't need to specify any of those parameters in that case.

[JMac]

So ássign the extents size at the TBS level?
That would mean I'd strip out the small/static lookups tables into their own TBS with extent size of 100K, and the dynamic large tables into theirs with, say, 1Mb extents?

[slimdave]

Originally posted by JMac
So ássign the extents size at the TBS level?
That would mean I'd strip out the small/static lookups tables into their own TBS with extent size of 100K, and the dynamic large tables into theirs with, say, 1Mb extents?

yes, exactly.

You ought to start by knowing what the maximum i/o size is on your o/s. Then make DB_FILE_MULTIBLOCK_READ_COUNT*block_size equal to that. The uniform extent sizes you choose ought to be integer multiples of the maximum i/o size also.

reasonable choices of extent size are generally 128kb, 4Mb, 128Mb.

[JMac]

The OS is Windows NT4. Any experience of that Dave?

So , let me get this straight: MULTI_BLOCK_READ_COUNT will then read 'units' of blocks mapping to whole extents (or multiples of)?

But the extents size being integer mutiples of max i/o size? Does that mean there always be more than one i/o to read any extent?

[abhaysk]

Originally posted by JMac
So , let me get this straight: MULTI_BLOCK_READ_COUNT will then read 'units' of blocks mapping to whole extents (or multiples of)?

extent size shud me multiple of DBMBRC*Block Size ( which in turn should equal Max I/O which is 256K in most of the cases )...

Originally posted by JMac
But the extents size being integer mutiples of max i/o size? Does that mean there always be more than one i/o to read any extent?

Gotta be..(applies only for FTS/FFS)

[slimdave]

So , let me get this straight: MULTI_BLOCK_READ_COUNT will then read 'units' of blocks mapping to whole extents (or multiples of)?

But the extents size being integer mutiples of max i/o size? Does that mean there always be more than one i/o to read any extent?

The idea is that you don't want an extent to be, say, 1.5 times the size of a multiblock i/o, because it still takes two i/o operations to read it. If you had one of more tablespaces with uniform extent size equal to the max i/o size then it just takes a single i/o operation to read the entire table.