Don't forget the automatic index that gets created when the primary key isn't 
an alias of the rowid.

Also why on earth would you ever have a foreign key that references the rowid 
if you're not using it as the id of the record? You even mentioned "vaccum 
allows SQLite to renumber the rows using the smallest possible numbers" so you 
would never, ever want to use rowid as a foreign key field unless it was an 
integer primary key visible as the primary key of the table.

Hmm, during a vacuum, when SQLite re-numbers the rowids, if you made a foreign 
key to the rowid with "on update cascade" does it in fact cascade since it's 
not a normal transaction?


-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Monday, January 08, 2018 1:02 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

Lets take a simple example of an employee table and couple of requirements:

x) Ids shall be non-reusable
x) Ids shall be indicative of the country the employee is located in at the 
time of hiring
x) Ids shall be assigned in a way that employee counts and seniority may not be 
simply deducted
x) Ids shall be usable for 100 years' worth of business

Looking at country numbering schemes, the UN currently uses 3 digit numbers (10 
bits), using 16 bits gives some wiggle room.
Looking at the largest employers, the US DoD currently employs 3.2 million, at 
a turnover rate of 25% for uniformed personnel, giving an estimated range of 
80.000.000 (27 bits) so using 32 bits gives us some wiggle room.

The Employee ID can be built as the sum of the coutry coude left shifted by 32 
bits plus the country-specific serial number, giving 48 bits of data, and 
scrambled in a way that ensures that some of the high order bits are set, 
yielding 15 digit global employee ids, that SQLite stores as 7 byte integers.

Declaring

Create table employee( id integer primary key, ...)

forces SQLite to use this 7 byte value as the rowid, not only in the employee 
table, but also in all the foreign keys that refer to it. Let's assume that 
there are a dozen relations that refer to the employee id. This gives a total 
of 13 * 7 = 91 bytes of storage for storing and referencing employee ids.

Declaring

Create table employee (id integer, ... , primary key (id));

allows SQLite to use (and reuse) the rowid. Even if you store 1 million active 
records (about twice the size of the US Postal Service), letting SQLite have 
cutody of the rowid uses only up to 3 bytes for a rowid. The total is then 7 + 
12*3 = 43 bytes of storage for storing the employee id and the rowid 
references, which translates to over 50% savings.

The smaller size of rowids in the internal btree structures should also just 
about cancel out the need to keep an index that translates employee ids to 
rowids. Purging records and then performing a vaccum allows SQLite to renumber 
the rows using the smallest possible numbers.

So, not assigning an external meaning to the rowid (and forcing the inner 
workings to comply) can, at least in some cases, actually save space.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Montag, 08. Jänner 2018 16:59
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

Gunter, doing it your way would obviously take up more space so what gains 
would make up for that? Faster inserts maybe?



I tried inserting a record in Tbl with an ID 1 less than the min existing ID 
and then did the same with an ID of 1 more than the existing max ID. There was 
little time difference between the two insertions although neither were exactly 
lightning fast.



________________________________
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Hick Gunter <h...@scigames.at>
Sent: Monday, January 8, 2018 1:19:00 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Re: difference between 'ID IS NULL' and 'ID = 
NULL'

>-----Ursprüngliche Nachricht-----
>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
>Auftrag von x
>Gesendet: Montag, 08. Jänner 2018 11:39
>An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Betreff: [EXTERNAL] Re: [sqlite] difference between 'ID IS NULL' and 'ID = 
>NULL'
>
>However, I’m still confused. Reading this https://sqlite.org/queryplanner.html 
>suggests the table is stored in RowID order. So what happens if I insert a 
>>record into Tbl with a lower ID than the existing 2.4 million Ids?

IMHO you should NEVER set the rowid (INTEGER PRIMARY KEY) field yourself, only 
copy it's value to the foreign key field of a referring row and within a 
transaction. Using the internal rowid in a foreign key for double lookup speed 
is the intended use.

SQLite "usually" uses 1 more than the highest rowid currently in use; if the 
maximum possible rowid is used, it will select a random rowid, hoping to find a 
free rowid within a limited number of attempts.

SQLite does allow you to modify/set the rowid, but this will fail if the target 
rowid is already in use. If you require custom rowids, these should be kept in 
separate fields. You can still use the SQLite rowid in foreign keys.


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to