Hi
I have a question about the most efficient way to query many indexed columns.
Here is the scenario:
Say I have a table with 100 fields
Table {f1,f2,f3,f4,…f100}
The first 10 fields are core fields and the client wishes to query them in any
combination.
This is too many fields to create a secondary index for every combination so I
create just 10, one for each core field.
Then when a query is submitted, I create a single query for each secondary
index and return just the PK, thus taking advantage to the index:
Example – for each indexed field included in the query:
SELECT PK FROM Table WHERE <indexed field> = <some value>’;
Then once I have a list of all PK values from all the index queries I will
either get the combination or intersection of all PKs, depending on the query
logic (and/or)and then run a final select:
SELECT * FROM Table WHERE pk IN(…);
Does this sound like a reasonable approach?
Thanks,
Ralph
