On Wed, 30 May 2007 15:18:18 -0400, John Elrick wrote <snip> > After running a simple test, I confirmed a suspicion. VACUUM > doesn't reorder the ROWIDs, so you still have breaks.
My tests show otherwise: [alex]# cat 01_vacuum_table_test.sql CREATE TABLE foo ( string varchar(1) not null ); INSERT INTO foo (string) VALUES ('a'); INSERT INTO foo (string) VALUES ('b'); INSERT INTO foo (string) VALUES ('c'); INSERT INTO foo (string) VALUES ('d'); [alex]# sqlite3 foo.db < 01_vacuum_table_test.sql [alex]# sqlite3 --header --column foo.db "SELECT rowid,* FROM foo" rowid string ---------- ---------- 1 a 2 b 3 c 4 d [alex]# sqlite3 --header --column foo.db "DELETE FROM foo WHERE rowid = 2" [alex]# sqlite3 --header --column foo.db "DELETE FROM foo WHERE rowid = 4" [alex]# sqlite3 --header --column foo.db "SELECT rowid,* FROM foo" rowid string ---------- ---------- 1 a 3 c [alex]# sqlite3 --header --column foo.db "VACUUM" [alex]# sqlite3 --header --column foo.db "SELECT rowid,* FROM foo" rowid string ---------- ---------- 1 a 2 c did I do something incorrectly? > > Two suggestions: > > 1. If the image data are fairly stable, copy the table to a new > table so as to eliminate the breaks in the ROWIDs. > 2. If the table is relatively small, load all the potential ids > into memory as a list and select randomly from the list. The table is small now, but expected to grow considerably to over a million rows. I coded a random selector from an array at the application level, and it works, but it will degrade dramatically over time. Thanks, Alex ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------