Home » SQL & PL/SQL » SQL & PL/SQL » Caching SQL sattements
Caching SQL sattements [message #254847] Sun, 29 July 2007 09:43 Go to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi,
I have 2 (related) queries regarding caching and hard parsing the sql statements

1)
Using functions in sql queries increases the recursive calls

However, instead of causing several sql statements to be cached shall we use functions in sql queries while using in a loop?

2)
Every time trigger is fired all the sql statements in are hard parsed.

However, iF I put the sql statements in a packaged code and call it from trigger those aren't hard parsed evey time.

If trigger is also a strored pl/sql routine, what could be reason behind this (that sqls in triggers are getting hard parsed)?

Please Suggest

Thanks and Regards,
OraSaket
Re: Caching SQL sattements [message #254851 is a reply to message #254847] Sun, 29 July 2007 10:28 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) Do you really mean function in SQL queries or queries inside function?
Quote:
However, instead of causing several sql statements to be cached shall we use functions in sql queries while using in a loop?

Precise your question, this is not comprehensible.

2)
Quote:
Every time trigger is fired all the sql statements in are hard parsed.

This is wrong.
Procedure and triggers are not stored in the same way (and that's depend on oracle version) but you have your answer: put your trigger code inside a procedure.

Regards
Michel



Previous Topic: Cursor and sql statment
Next Topic: using bulk collect with select
Goto Forum:
  


Current Time: Sat Dec 10 20:41:24 CST 2016

Total time taken to generate the page: 0.08046 seconds