RE: Options to speed up a 7 table join executed frequently.
Date: Wed, 23 Mar 2011 10:52:46 -0400
Message-ID: <058601cbe969$f9a02110$ece06330$_at_gmail.com>
One other option is using a result cache in 11g. Depending on how frequently the table is changed and how often the query is run, you might be able to cache the results and have certain runs be almost instantaneous. If the query is run 100 times a second and it changes twice a second, that would still be a 98% reduction in elapsed time and logical io. Also depending on your bottleneck the reduction in logical IO may speed up the system overall.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Subodh Deshpande
Sent: Wednesday, March 23, 2011 10:37 AM
To: Joel.Patterson_at_crowley.com
Cc: oracle-l_at_freelists.org
Subject: Re: Options to speed up a 7 table join executed frequently.
it all depends actually, like is query is only column list or functions, group functions, string functions, computational functions,
what are those tables meant for like are they transactions, history or masters and how they relate with each other
do they have proper indexes, if there is varity of columns..
depending upon that, sub-queryes, inline views are other available options
and finally how you want to see query output..
thanks..subodh
On 23 March 2011 17:38, <Joel.Patterson_at_crowley.com> wrote:
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
-- ============================== DO NOT FORGET TO SMILE TODAY ==============================Received on Wed Mar 23 2011 - 09:52:46 CDT
_____
Checked by AVG - www.avg.com Version: 10.0.1204 / Virus Database: 1498/3524 - Release Date: 03/23/11
_____
Checked by AVG - www.avg.com Version: 10.0.1204 / Virus Database: 1498/3524 - Release Date: 03/23/11 -- http://www.freelists.org/webpage/oracle-l