Here's a naive solution, which requires some programming language
around the SQL:
BEGIN TRANSACTION
n = (SELECT count(*) from table)
i = RandBetween(0, n)
row = (SELECT * from table LIMIT 1 OFFSET i)
END TRANSACTION
I'm posting this because I suspect that this naive solution isn't
correct, but I don't know why, and want to find out.
On May 30, 2007 3:55 PM, Alex Teslik <[EMAIL PROTECTED]> wrote:
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]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
