Btree index extension question

Btree index extension question

Post by Dmitry Tkac » Sun, 17 Mar 2002 03:04:21

Hi, everybody!

I was wonderring if there is somebody out there who could help me with
understand how index extensions work...
Let me state the problem first.

I have many (15) boolean attributes and I need to be able to search the
database for entries with any combination of those attributes for being
true. For example - find all the entries, where a1=a2=a3=true or find
all the entries where a1=a2=a4=true etc...
Because there are so many of them (and the database is HUGE), putting
every attribute into a separate column and creating a separate index on
every possible combination, is really out of the question.
So, I was thinking about creating a single int2 column, with each bit
representing an attribute - so that, the first query I quoted above
would look like "select * from table where attributes & 7 = 7", and the
other query would be
"select * from table where attributes & 11 = 11' etc...

This looked so beautiful to me, but now I am stuck trying to index that
table  [:-(]

I started off, hoping to get away with btrees.

I defined an operator >>=(int2,int2) as 'select $1&$2=$2;'
It looks nice so far, but then the question is - what do I do with the
other operations? By analogy with 'normal' comparison operators, I would do:

 >> (I know the name is taken  [:-)]  as 'select not $2 >>= $1'
=<<                                  as 'select $2 >>= $1'
<<                                   as 'select not $1 >>= $2'
... and leave '=' intact.

But then I realized, that these set of operators, does not really define
a complete order - for example, if I compare, say, 5 and 3:
5 & 3 = 1, 3 & 5 = 1, so I get BOTH 5 << 3 and 5 >> 3 being true at the
same time  [:-(]

So my question is, first of all, is that a problem? Does btree require a
complete order defined? Will it work with partial order?
Secondly, if it is a problem, perhaps, I am missing something here,
assuming that there is no way to define a set of operations to do what I
want and provide a completely ordered set (or do I need it to define a
perfect complete order - what exactly is required for btree to work? Any

And finally, if there is just no way I could get away with btrees, can I
make an rtree to work for me? Could somebody explain to me (or point me
to a doc somewhere) the meaning of the strategies (and requirements -
like transitivity etc...) I need for an rtree, and also what support
functions (like comparison func in case of a btree) do I need?

Thank you very much for your attention.
Any input will be greatly appreciated.



1. Btree indexes. (question)

from 7-196 of the SQL Ref Manual:

"When data is added to a btree table, the index automatically expands.
 However, a btree index does not srink when rows are deleted from the
 btree table.  To shrink a btree index when necessary, you can use a special
 form of the modify statement

      modify tablename to merge    "

My question is:  What if you have a table that is a btree and also have secondary
indexes on the table that are btree.  Do you need to (or can you) use this modify
command on a secondary index?  Also when you use the above modify statement on the
table, are the secondary indexes deleted or do they still exist?

2. renaming a column

3. Btree Secondary Indexes

4. Ingres 6.4 max(fieldname by fieldname)

5. patch to allow btree indices on BYTEA

6. Changing servername on an Oracle RDBMS server (Sun Solaris)

7. Btree+ (indexing methods)

8. MSSQLServer service not stopping

9. reverse order on secondary btree indexes

10. btree-indexing-engine

11. Problem with btree index?

12. btree indexing

13. Corrupt Btree Index