RE: Large Query --> several small queries. How?

Hi Glenn,

You can do it with in-line views and the ORDERED hint.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
@
@ Going to OpenWorld?
@ Catch the Ixora performance tuning seminar too!
@ See http://www.ixora.com.au/seminars/ for details.

-----Original Message-----
From: Glenn Travis [mailto:Glenn.Travis_at_wcom.com] Sent: Friday, 1 September 2000 8:36
To: Multiple recipients of list ORACLE-L Subject: 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!

-- 
Author: Glenn Travis
  INET: Glenn.Travis_at_wcom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).