Options to speed up a 7 table join executed frequently.

From: <Joel.Patterson_at_crowley.com>
Date: Wed, 23 Mar 2011 08:08:24 -0400
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1615348EFC_at_JAXMSG01.crowley.com>



A developer asked for suggestions to speed up a query in oracle 10.2.0.4 Solaris 10, soon to be 11gr2.

He comes in around 2 hours from now, and I have asked to see the query. However...., here is the background.

The query is generated by hibernate.... The query is called very frequently. The underlying data in the tables changes constantly.

The query joins around seven tables.

I thought perhaps a seven table join would be to complicated for a materialized view. I figure a normal view doesn't stop the execution of the query... I thought perhaps a stored procedure where the data could be gathered manually via cursors, and all tools available there. A temporary table... yet that doesn't solve the problem either, and so many people generate the query...

So, I'm looking for ideas, I am just in the beginning stage.

P.S. I will be out after today until Monday.

Joel Patterson
Database Administrator
904 727-2546

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 23 2011 - 07:08:24 CDT

Original text of this message