Martin Pfeifle wrote:
Thanks,
If we look at a B+-tree, all records are stored at the leaf level and form at
least a logical list.
But what happens, if we insert new records which do not fit on the
corresponding leaf page any more. Assume this page has to be split. Where is
the newly created physical page stored? Does the logical list of blocks also
lead to a list of consecutive physical blocks?
Is the physical ordering of the records in the file independent of the
insertion ordering?
Our goal is that all records are physically clustered according to their ROWID.
In order to achieve this goal, does it make sense to reorganize a table by for
instance a command like
"insert into reorganized_table
select * from original_table ordered by rowid"
Martin,
The physical order does depend upon the insertion order.
You can reorganize a database as you have suggested or by running the
vacuum command which does essentially the same thing for every table in
the database. In either case the source tables are scanned in rowid
order as the records are copied to the destination table. The inserts
into the destination table are always appending new pages to the end of
the new database file, so your tables end up allocated to sequential
pages in the file.
Dennis Cote
