I am in a trouble doing the performace tuning of a table(Oracle 22.214.171.124) having 200 Millions Records. The application which uses the table creates ad-hoc queries(queries which are not defined beforehand). This leads to create individual indexes on each of the fields (b*tree or bitmap) not composite as queries are not known. The range queries and aggregate queries are very slow and takes upto 9 minutes.
The same application is running on Sybase IQ Server 12 having a similar setup of indexes and gives the result back in 2-6 seconds. I tried to look read about the indexes in Sybase, Sybase has two other kind of indexes which are call HG (High Group ) and HNG(High Non Group). These indexes helps in doing aggregate and range queries and birngs the results back in seconds.
Does any body has an idea to how to go about this problem and suggest something to improve the performance ?.