Home » SQL & PL/SQL » SQL & PL/SQL » Performance & tuning views
Performance & tuning views [message #577424] Fri, 15 February 2013 06:55 Go to next message
piraco
Messages: 2
Registered: April 2012
Junior Member
Hi all,

i've the following situation:
I've developed, for developers who are using java, a view on our db.
This view contains several columns, most of them returns function based values,
and it can obviuosly be read in different ways, which of one is by a single condition (i.e. select * from myview where column="fixed value")
The develepers say that the resultset of this view, red by this single condition, need to be more performant.
Now:
I've make all I know to speed up the view (from the very beginning: reading every joined table whith the appropriate keys, until adding indexes if and where necessary, and so on)
So now I'focused on the functions that I've made: i've noticed that , within a function, instead of writing in it a simple select-from-where statement,
if I use insetead dynamic sql (execute immediate 'my select..' into ...using...),
it seems to me to be more performant, and the ovearll results of the view seems better (tkproofed it)

Two questions:
1) Does this make sense? For my knloledge, dynamic sql is not better than a simple query with bind variables.
2) Is my "function based" approach correct? or am I missing something else and others methods for speeding up my view?

Thanks in advance, any help would be appreciated
Re: Performance & tuning views [message #577425 is a reply to message #577424] Fri, 15 February 2013 07:52 Go to previous messageGo to next message
joy_division
Messages: 4520
Registered: February 2005
Location: East Coast USA
Senior Member
With only a description and not seeing any code, I won't guess.
Re: Performance & tuning views [message #577426 is a reply to message #577425] Fri, 15 February 2013 07:57 Go to previous messageGo to next message
BlackSwan
Messages: 22837
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Performance & tuning views [message #577427 is a reply to message #577424] Fri, 15 February 2013 08:05 Go to previous message
Michel Cadot
Messages: 59275
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if I use insetead dynamic sql (execute immediate 'my select..' into ...using...),
it seems to me to be more performant, and the ovearll results of the view seems better (tkproofed it)

Quote:
1) Does this make sense? For my knloledge, dynamic sql is not better than a simple query with bind variables.

Are not these 2 sentences in contradiction? If you can proof it then you have bad knowledge... but I doubt you can proof it as gthe first sentence is wrong.

Quote:
2) Is my "function based" approach correct?


Custom functions called by SQL (and even more views) are (at 99.999%) the bed of bad performances.

Regards
Michel
Previous Topic: Using Collections
Next Topic: how to install exceldocumenttype
Goto Forum:
  


Current Time: Tue Sep 30 08:22:35 CDT 2014

Total time taken to generate the page: 0.05404 seconds