RegisterSign In
By Alfredo Sanchez (alfy) on Mar 26, 2011 4:25 AM.
Simple Database and conjunction indexes
I was wondering: is there a possible to select data from a SDB table based on the AND or OR logic over two or more fields of the table? The current implementation supports the retrieving of a one-column index.

What I mean is: I want an expression (in SQL-like syntax, just for convenience) SELECT * FROM table WHERE a = value1 AND b = value2;

Is that possible?
By Tobias Downer (toby) on Mar 26, 2011 3:38 PM.
Under the hood, an SQL database will perform this sort of query either with an index reduction + scan, or use a composite index. The preferred option would be to use a composite index. eg, Instead of a or b having an index, the index would be on a and b. If a composite index is not available, then the next best method is to use any single column index that is available on the column that will result in the greatest reduction, then perform a scan on the remaining. eg. if (b = value2) reduces down to a small number of rows then do that first then scan on (a = value1). If no index is available then a full scan is the only remaining option.

A simple way to make a composite index using an SDB table is to create an extra column and put a concatenation of a and b into that column and create an index on that.

OR logic is quite a bit different. The optimal way to do ORs is by using single column indexes and a union operation.
Please sign in or register to post in this topic.
The text on this page is licensed under the Creative Commons Attribution 3.0 License. Java is a registered trademark of Oracle and/or its affiliates.
Mckoi is Copyright © 2000 - 2017 Diehl and Associates, Inc. All rights reserved.