Let's say I want to create a relation with two attributes R(A,B).
Neither A nor B is a primary key for this relation and so there can be
duplicates values on both of them. Next I need to create two btree
indices on each attribute.

I 've tried two create this relation in berkeley DB. I create a primary
database in the key/value form, where key is attribute's A values and
value is attribute B' values. Since I can have duplicates I need to set
for example the DUPSORT flag.

BUT then I need a secondary index on attribute B. This is done by
creating a secondary database with key the attribute's B values.
UNFORTUNATELY there lies the constraint of Berkeley DB that the key of
primary database must be unique!


BUT I only want two secondary indices.

ANY ideas?

The common approach to this is to use unique record numbers as the keys
in the primary (without duplicates, obviously), and have both A and B
in the data. You could use a DB_RECNO type database for this.

Then create two secondaries: one for A and one for B. Both can have
DB_DUPSORT set.

Regards,
Michael.

Michael Cahill wrote:

So I can not create two secondary indices without creating a primary
one first? Correct?
This is my opinion too, but I want to be sure.

AND SO you cannot actually what is done with for example mysql have two
secondary indices in relation R and nothing more...

THE problem is that I want to measure the time need to query the BTREES
so by having another access method for the primary database is
additional time

AND another thing that I want to actually only create two btrees over a
relation -not inside a database- and I thought Berkeley DB was very
useful...

I described the common approach. If you only want two btrees, you can
achieve that by making the keys in one unique (and making it the
primary). You could do that by appending a unique ID to the non-unique
keys, for example.

Regards,
Michael.

Could be another approach to create a primary database with a struct as
key:

int a;
int b;

and then a secondary database with b as the key?

I mean if I properly set comparison and prefix comparison functions can
I answer with primary database queries over only a field?

Sure, that can also work.

Michael.