Scott Hess <sh...@google.com> writes: >> Hello, >> >> I need a unique value in the range 0 to 2^32 for every row in my table. >> What is the best way to implement this in SQLite? >> >> I suppose that for the first 2^32 inserts I could just use the >> rowid, but after that the rowid might increase above 2^32 while my >> desired unique value should look for some unused value less than 2^32 >> instead, so that is not an option. >> >> I guess the next option would be to define my own unique column instead >> of using the rowid. But after 2^32 inserts, I'm getting into trouble as >> well because I have to find an unused value and I don't quite know how >> to do that efficiently. >> >> The only two ideas that come to my mind is to use a random search like >> sqlite uses for the rowid, or to store the unused values in an >> additional table. But I have serious doubts about both solutions. While >> random search may have a good chance of success if there are 2^64 >> possible rowids (it's very unlikely that more than half of them are >> used, since that would already take up 8192 Petabyte), I don't think it >> works very well for 2^32 possible values (when there may well be only a >> couple of hundred unused ones). Using an additional table to store >> unused values would require me to initialize this table with the first >> 2^32 integers (taking up about 4 GB) when the main table is empty, which >> is rather crude. > > Since you're asking the question, it seems safe to assume that you > expect the table to have on the order of 2^32 values? And that you'll > be freeing up old values by deleting rows from the table?
Actually, no. I will have significantly less than 2^32 rows in the table. I was so worried that I'm losing 10 orders of magnitude compared to the full 64 bit sqlite rowids that I temporarily forgot that 2^32 is still a rather large number. After reading all the surprised comments about me having almost 2^32 entries in a table, I did some calculations and found it to be more than enough. I will therefore either compile with SQLITE_32BIT_ROWID (thanks Roger!) or implement my own random search that is used if min(rowid) == 0 and max(rowid) == 2^32 (my actual code doesn't use rowid but gives the column a proper name, but I did not want to complicate the question). Also thanks for all the other suggestions! Although I don't need to worry about having 2^32 entries at the moment, I will keep them in mind should I ever encounter that problem (or be asked about it). Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
