Re: table record distribution query.

paul_hallam_at_hotmail.com (Paul Hallam) wrote in message news:<5ed44bd3.0309030045.71b424ff_at_posting.google.com>...
> How does oracle distribute records in a table that is in a tablespace
> with multiple datafiles.
>
> Very simplisticly Ingres will distribute sequential records evenly
> across multiple datafiles for a single table. (Not exact but good
> enough for this question.)
>
> How does oracle distribute records for a table that is in a tablespace
> with multiple datafiles.
>
> Any pointer to where in the documentation this is explained would also
> be appreciated.
>
> Many thanks
>
> Paul

Paul, Oracle distributes rows based on the free list whose membership is determined by interaction of the table settings for pctfree and pctused with DML activity. See the Concepts manual for details.

Oracle distributes extents accross the files in a tablespace based on a whole slew of factors: tablespace space management scheme, free space availability, next extent size requested, etc....

But if you load an empty tablespace one table at a time you can watch Oracle stripe the extents for a large enough object accross the files that make up the tablespace. Oracle does not attempt to rebalance objects that are unevenly distributed or when new files are added to the tablespace (as DB2 does).

Once a tablespace is loaded the availability of free space extents sufficient to hold the requested allocation is the primary driver of where an extent is allocated in conjuction with the tablespace space management scheme: uniform extents, autoallocate, or dictionary managed.

HTH -- Mark D Powell -- Received on Thu Sep 04 2003 - 15:43:24 CEST