Cursor cache in nested queries ? [message #336992] |
Tue, 29 July 2008 10:19  |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
Hello,
How to use cursor caching for the following:
I need to execute thousands of complicated SQL queries daily.
Every query is like:
EXECUTE IMMEDIATE
'INSERT INTO results_table
(col1,col2,col3,col4,col5,col6)
SELECT a,b,:val1,:val2,c,d
FROM (
SELECT a,b,c,d
FROM input_data
WHERE a = :val3
AND b < :val4
AND ' || dynamic_condition ||
' )'
Note that this is simplified version of the query.
This dynamic_condition makes the inner-most select to vary a lot -> cursor sharing is not wanted for this.
However the second SELECT could be cached.
Assuming that CURSOR_SHARING is set to SIMILAR - how would cache work like? How can I check it on my own?
Can you give me some hints to optimize this one? (no - I don't want to include :val1 and :val2 into inner-most select to get rid of one select )
[Updated on: Tue, 29 July 2008 10:21] Report message to a moderator
|
|
|
|
|
|
Re: Cursor cache in nested queries ? [message #337014 is a reply to message #337004] |
Tue, 29 July 2008 11:52   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
wakula wrote on Tue, 29 July 2008 18:10 | Thanks Michel,
Does it mean that Oracle will split my long string into sub-queries, caching every sub-query, and then execute them with re-using existing objects?
|
Not at all. It caches the whole statement.
I made a typo instead of "cursor caching" I wanted to write "cursor sharing";
Regards
Michel
[Updated on: Wed, 30 July 2008 01:06] Report message to a moderator
|
|
|
Re: Cursor cache in nested queries ? [message #337069 is a reply to message #336992] |
Tue, 29 July 2008 18:49  |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
Thanks for all your replies.
The link given above - it does not fit my case.
What is the tricky part is that the dynamic part is taken from the DB. Something like:
SELECT conditions INTO statement FROM queries;
EXECUTE IMMEDIATE 'SELECT x FROM input WHERE ' || conditions;
-- conditions could be 'a=1 and b=2 and sum(...) or ...
Thanks again!
|
|
|