I have been breaking my head not knowing if I am having issues in the 'requirements' or in the 'implementation'. If any of you that might possibly have some experience in this area can please respond to this, I will greatly appreciate.
In our project Oracle DB, we use about 7 core tables in which about 3 million records are loaded. Overall there are about 75 columns in these 7 tables.
Our requirement says: The actor shall be allowed to query records based on any of the 25 listed attributes. (These 25 attributes are scattered in those 7 tables)
* attr1, attr2, ... attr25
Now, the number of possible 'search criteria' combinations using these 25 attributes are large. Does this requirement look realistic? Or should we revisit the requirement and consider for 'Operation Profile Index' or 'Most commonly used query patterns' so that queries that are most often used will be optimized best using reasonable number of indexes? Or is there any other implementation strategies(use of caching, should we use our own application caching or use DB Caching itself?, may be use of J2EE features, etc).
Or If I am totally thinking way out, please suggest some techniques, Thanks.