Large Query --> several small queries. How?

I have an app which uses JSP webpages as the front end. The middle is WebLogic threads. The back is Oracle 8i on solaris. The entry point of the app allows the user to select a project, a customer, an order, or any combintation therein. Good.

Problem: The user also has the option to select ALL projects, or ALL orders, or ALL ...
The query used to get this information is the same, except there is no key to lookup and hence the optimizier chooses a decidedly (sp?) different path. The query used joins 5 tables and 3 views (each of which is a 2 table join) together to get all the data. Oracle just dies when this query is issued. If I take out a couple of tables from the join (and their data from the select), it behaves much better. I've tried additional indexes and stats, but cannot get the complete query to run any better.

Solution? What I want to do is break down the query into smaller selects. I.E. join a few of the tables, then use that result set to join the next and so on (self-optimized). The problem here is that the java beans (threads, front end, whatever) can only issue ONE query. (We have alot of views because of this, but that is beside the point). Can I use stored procs, functions, temp tables to run several queries behind the scenes?

All suggestions welcome... and thanks! Received on Thu Aug 31 2000 - 15:27:05 CDT