First of all please excuse me for bringing forth again a conversation
that is months old. I've just discovered the "without rowid" ability of
SQLite and i have to say many many thanks for it.
Let me describe a use case where a not unique key and without rowid are
most welcome. We have a distributed big data system here which uses
SQLite for the partitions. To be able to efficiently execute join
queries on splited partitions, we need to assemble the partitions of one
side of the query to create an index on them.
So in the query:
select y,z from A, B where A.x = B.x;
We assemble the pieces of B using this query:
create table B as select * from ALL("b1.db", "b2.db", "b3.db");
and then we create an index on them:
create index idx_b_x on B(x,y);
Above essentially creates 2 copies of table B's data (because all our
indexes are always covering indexes).
So to avoid these 2 copies, what we would like to have are indexes
*without backing tables*. These are essentially tables "without rowid"
having plain keys (as opposed to primary keys).
We could work around the primary key limitation of "without rowid"
tables by creating a dummy rowid column and putting it at the right of
the table's key that we care about:
CREATE TABLE B(
x, y, rowid, PRIMARY KEY(x, rowid)
) WITHOUT ROWID;
and then insert into it the data:
insert into B select * from ALLWITHDUMMYROWID("b1.db", "b2.db", "b3.db");
but having this capability inside SQLITE would be a lot better.
Nevertheless above comments, thank you again for "without rowid".
They'll be very useful for our use case.
Regards,
l.
On 19/11/13 21:53, Richard Hipp wrote:
On Mon, Nov 18, 2013 at 5:45 PM, Nico Williams <n...@cryptonector.com>wrote:
Obviously a B-Tree-based table will need *some* key, but it won't need
that key to be UNIQUE.
Yeah it does. Every storage system has a unique key, be it an address in
memory or a filename and offset on disk. There must be some way to
distinguish between two record. Two records with the same key are
indistinguishable from one another and are hence the same record.
The question is whether or not the unique identifier is exposed to the
application.
SQLite allows the unique identifier to be a PRIMARY KEY ... WITHOUT ROWID.
Or it allows it to be the rowid. You get to choose. But every table has
to have one or the other.
You can argue that it is theoretically possible to create a table where the
key is something other than PRIMARY KEY or rowid (perhaps it is the
filename+offset suggested above) that is simply not exposed to the
application layer. Yes, that is theoretically possible. But SQLite does
not do that and I do not see any reason to add the capability.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
