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"
----- Ursprüngliche Mail ---- Von: Dennis Cote <[EMAIL PROTECTED]> An: sqlite-users@sqlite.org Gesendet: Mittwoch, den 5. April 2006, 23:20:07 Uhr Betreff: Re: [sqlite] primary key and physical data organization Martin Pfeifle wrote: >Hi, >Assume I have a table containing an integer primary key. As far as I know, >this value is identical to the ROWID, right? >How does SQLite organize the data within the file? >Does it try to organize the data on the pages according to the primary key >(=ROWID) or according to the insertion order of the records? >Can anyone explain that to me? >Best Martin > > > Martin, An integer primary key is the rowid which is the key for the b-tree used to store the table. The way the b-tree is constructed is explained in the comment at the beginning of the source file btree.c which you can view here http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/btree.c&v=1.324. Basically it is a tree of blocks with similar rowid values and pointers to other blocks with larger and smaller rowid values. HTH Dennis Cote
