I need to create a table that will contain several million records. This table will be searchable in a quite dynamic way so I expect to need quite a lot of indices. However a number of data stored in this table will be quite dynamic - a number of searchable keys might be added/removed depending on business requirements and I would prefer not to have to add fields for those.
What is the fastest approach:
1) storing the dynamic data in a WORD-INDEXed record. For instance comma separated.
field-value = "key1=value1,key2=value2,key3=value3"
2) storing the dynamic data in a sub-table consisting of a value pair?
parentId Key Value
1 key1 value1
1 key2 value2
1 key3 value2
3) an extent character field where every extent matches a specific key.
field-value[1] = value1
field-value[2] = value2
field-value[3] = value3
4) A totally different approach?
My first priority is that search is quick. Second priority in this case is ease of use.
1) is the fastest and has been used successfully by a number of others