| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Large Query --> several small queries. How?
This is a multi-part message in MIME format.
--Boundary_(ID_Pg8jsIgkvl1kiicja0/VKw)
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7bit
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!
--Boundary_(ID_Pg8jsIgkvl1kiicja0/VKw)
Content-type: text/html; charset=iso-8859-1
Content-transfer-encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2919.6307" name=3DGENERATOR></HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D740321519-25082000>I have =
an app which=20
uses JSP webpages as the front end. The middle is WebLogic =
threads. =20
The back is Oracle 8i on solaris. The entry point of the app =
allows the=20
user to select a project, a customer, an order, or any combintation=20
therein. Good. </SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D740321519-25082000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D740321519-25082000>Problem: The=20
user also has the option to select ALL projects, or ALL orders, or ALL=20
...</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D740321519-25082000>The =
query used to=20
get this information is the same, except there is no key to lookup and =
hence the=20
optimizier chooses a decidedly (sp?) different path. The query =
used joins=20
5 tables and 3 views (each of which is a 2 table join) together to get =
all the=20
data. Oracle just dies when this query is issued. If I take =
out a=20
couple of tables from the join (and their data from the select), it =
behaves=20
much better. I've tried additional indexes and stats, but cannot =
get the=20
complete query to run any better.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D740321519-25082000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D740321519-25082000>Solution? What=20
I want to do is break down the query into smaller selects. I.E. =
join a few=20
of the tables, then use that result set to join the next and so on=20
(self-optimized). The problem here is that the java beans =
(threads, front=20
end, whatever) can only issue ONE query. (We have alot of =
views=20
because of this, but that is beside the point). Can I use stored =
procs,=20
functions, temp tables to run several queries behind the=20
scenes?</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D740321519-25082000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D740321519-25082000>All =
suggestions=20
welcome... and thanks!</SPAN></FONT></DIV>
Received on Fri Aug 25 2000 - 14:50:33 CDT
 |
 |