[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Equal distribution from random rows
From:       John Elrick <john.elrick () fenestra ! com>
Date:       2007-05-31 12:33:47
Message-ID: 465EC0AB.8070801 () fenestra ! com
[Download RAW message or body]

Alex Teslik 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:
>
>
>   
SNIP
> did I do something incorrectly?
>
>   

Not incorrectly, just differently.  In my test I unintentionally used an 
INTEGER PRIMARY KEY:

CREATE TABLE FOO(
  ID INTEGER PRIMARY KEY,
  MYSTUFF TEXT
);

If you retrieve the ROWID from this test (full Ruby program at end):

CREATE TABLE FOO(
  ID INTEGER INTEGER PRIMARY KEY,
  MYSTUFF TEXT
);

INSERT INTO FOO VALUES (1, 'One');
INSERT INTO FOO VALUES (2, 'Two');
INSERT INTO FOO VALUES (3, 'Three');


You get this result:

["rowid", "ID", "MYSTUFF"]
["1", "1", "One"]
["2", "2", "Two"]
["3", "3", "Three"]
["rowid", "ID", "MYSTUFF"]
["1", "1", "One"]
["3", "3", "Three"]

I have confirmed that removing the PRIMARY KEY designator permits VACUUM 
to reorder the ROWID.  So I would amend my statement:

"After running a simple test, I confirmed a suspicion.  VACUUM may not 
reorder the ROWIDs if you use an INTEGER PRIMARY KEY, so you would still 
have breaks under those conditions."

Thanks for running your test.  I wouldn't have thought there would be a 
difference.


John


Full program:
-------------
require 'sqlite3'

FILENAME = ":memory:"
$db = SQLite3::Database.new( FILENAME )
$db.execute_batch(<<eof
CREATE TABLE FOO(
  ID INTEGER INTEGER PRIMARY KEY,
  MYSTUFF TEXT
);

INSERT INTO FOO VALUES (1, 'One');
INSERT INTO FOO VALUES (2, 'Two');
INSERT INTO FOO VALUES (3, 'Three');
eof
)

$db.execute2('SELECT ROWID,* FROM FOO') do |i|
  p i
end

$db.execute('DELETE FROM FOO WHERE ID = 2')
$db.execute('VACUUM')

$db.execute2('SELECT ROWID,* FROM FOO') do |i|
  p i
end


-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe@sqlite.org
-----------------------------------------------------------------------------

[prev in list] [next in list] [prev in thread] [next in thread]