[SQL] Index usage in bitwise operation context
Hello,
My question is about index usage when bitwise operations are invoked.
Situation Context:
--
Lets suppose we have 2 tables TBL1 and TBL2 as the following:
TBL1 {
. ;
integer categoryGroup; // categoryGroup is declared as an index on TABL1
. ;
}
TBL2 {
. ;
integer categoryGroup; // categoryGroup is declared as an index on TABL2
. ;
}
By conception, I suppose that:
- [categoryGroup] may hold a limited number of values, less than 32 values.
- [categoryGroup] is of type integer => it means 4 bytes => 32 bits
=> 32 places available to hold binary '0' or binary '1' values.
- [categoryGroup] is the result of an "OR bitwise operation" among a
predefined set of variables [variableCategory].
We suppose that [variableCategory] is of type integer (=>32 bits)
and each binary value of [variableCategory] may only hold a single binary
'1'.
Ex: variableCategory1 = 0010
variableCategory2 = 0010
variableCategory3 = 1000
If [categoryGroup] = variableCategory1 | variableCategory2 |
variableCategory3
=>[categoryGroup] = 00101010
Question:
--
I have an SQL request similar to:
SELECT . FROM TBL1, TBL2 WHERE
AND
TBL1.CATEGORY & TBL2.CATEGORY <> 0 //-- where & is the AND bitwise
operator
Qst:
1/ IS the above SQL request will use the INDEX [categoryGroup] defined on
TBL1 and TBL2 ?
2/ What should I do or How should I modify my SQL request in order
to force the query engine to use an index ? (the already defined index or
another useful index)
Thx a lot
Re: [SQL] Index usage in bitwise operation context
"W.Alphonse HAROUNY" <[EMAIL PROTECTED]> writes: > Question: > -- > I have an SQL request similar to: > > SELECT . FROM TBL1, TBL2 WHERE > AND > TBL1.CATEGORY & TBL2.CATEGORY <> 0 //-- where & is the AND bitwise > operator > > Qst: > 1/ IS the above SQL request will use the INDEX [categoryGroup] defined on > TBL1 and TBL2 ? No, & isn't an indexable operator for any of the standard indexing methods. You could create 32 partial indexes on some other key with clauses like WHERE tbl1.category & 0100... = 0100... But I don't think that would be useful for a join clause in any case. Second idea, you could create an expression index on tbl1 (category & 0100... = 0100..., category & 0010... = 0010..., category & 0001... = 0001..., ...) Again I don't see that it's going to be used for a join condition. Lastly, you could look for a GIST index method for varbit which would be superior to both of the above tactics. I'm still not sure it would be able to handle a join clause though, but maybe? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
