Home » SQL & PL/SQL » SQL & PL/SQL » Cursor cache in nested queries ? (Oracle 10.2, PL/SQL)
icon5.gif  Cursor cache in nested queries ? [message #336992] Tue, 29 July 2008 10:19 Go to next message
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 Smile )

[Updated on: Tue, 29 July 2008 10:21]

Report message to a moderator

Re: Cursor cache in nested queries ? [message #337002 is a reply to message #336992] Tue, 29 July 2008 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Cursor caching work on literal values not on the whole string.
In short, you can't.

Regards
Michel
Re: Cursor cache in nested queries ? [message #337004 is a reply to message #336992] Tue, 29 July 2008 11:10 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
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?
Re: Cursor cache in nested queries ? [message #337012 is a reply to message #337004] Tue, 29 July 2008 11:40 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
See if this is an option for you.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279
Re: Cursor cache in nested queries ? [message #337014 is a reply to message #337004] Tue, 29 July 2008 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
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!
Previous Topic: Start value of sequence 00001
Next Topic: INSTEAD OF UPDATE TRIGGER DOUBT
Goto Forum:
  


Current Time: Thu Dec 08 02:14:00 CST 2016

Total time taken to generate the page: 0.08537 seconds