Middleware optimizer/translator

I am looking for a software solution that would act as a middleware component which can allow us to optimize a query based on known rules that we would build.

For example, taking a query that references a start and end date in a particular table, translate that query to use series of batch numbers that encompass those dates. The batches would be recorded in another table that contains the min, max start and end dates. The batch number is used as the partitioning key. The reason for this need (desire?) is that partitioning by by 2 date columns and querying as below still produces an unacceptable partition pruning plan. Mainly because Oracle can't know that startdate always is less than enddate.

select *
from table
where :time between startdate and enddate   and enddate between :date1 and :date2

to

select *
from table
where batch in (123,124,125)

Anyway... What we are looking for is some kind of middleware that would be interpreted by the user queries (i.e. via Net8 or SQL*Net) as if it was the database and the query is translated and optimized before being sent along to the "real" database. This would, hopefully, allow us to change the rules as the data itself changes and prevent the need to always update the client programs. I would hope that it would also allow us to reject inappropriate queries (e.g. select * from table_of_500_million_rows).

In another example the data might be replicated on more then one system with with different partitioning schemes. Some method of directing the query to the appropriate data store that is optimized for the conditions specified would be great also.

For example, another query might request all data for a customer ( one of 5 million) and the query could be directed to a replicated table that is partiioned on the customer column.

Any help or suggestions are welcome.

Thanks

Mike Winterer
mailto:mikew3_at_corp.earthlink.net Received on Mon Jun 19 2000 - 00:00:00 CEST