Oracle Index on VARRAY data type colum
588495Jul 17 2007 — edited Jul 18 2007I need one help for oracle object design.
I have one column which is multivalued like YahooChatID = a@yahoo.com,b@yahoo.com,c@yahoo.com (this can have n number of values)
Now in table I want to store it in one record (As this belongs to my one business entity).
Any idea of oracle Object Oriented feature will help me to design it perfectly.
Following solution is not feasible for me from performance point of view:
1.If I will store it in relational format (master –Detail) then this will heat my performance a lot because in one record there are 100 multivalued column and every mutivalued column can have n number of values and master detail design is forcing me to store 100*n possible rows to store one entity.(where in my case 18000 Resource /Second is coming and with this design I have to store 18000*100*n rows/second and if I will create per hour oracle partition then even that will not help me)
2. VARRAY datatype of oracle does not support indexing .(I don’t know why oracle is not supporting indexing on it , otherwise this could be one of the best solution in my case .I mean oracle should if it claims that it is not only relational database it is Object Oriented DBMS also.)
3. Nested table is fixed structure where in my case mutivalued column can have n number of values where value of n can be 1 to 1000.
4. Storing in one column by comma separated a@yahoo.com,b@yahoo.com,c@yahoo.com creates problem when I want to search value b@yahoo.com then my index for this column will not be used.
5.Oracle text indexing is not feasible from performance point of view.
6.Function index is again not helping because mutivalued column can have n number of values and n is not fix.
Any help or suggestion will be really appreciated.