oracle views

[mixc]

do oracle views &quot;improve performance&quot;? My understanding is they are for security; they are dynamic. The issue is this:<br><br>would retrieving data from a table using a view to filter out a subset be as fast as retrieving data from a table which contains only the subset which is needed? Joins are involved. <br><br>We have a discussion at work and it seems quite clear to me but I said I would ask.

 

[stoleru]

Hi mixc! Querying a view, which was build on a table restricted with a WHERE clause, is the same thing like querying the source table with a SELECT containing the view restriction, i.e.:<br>CREATE OR REPLACE VIEW v_table AS SELECT * FROM my_table WHERE &lt;&lt;view_condition&gt;&gt;;<br>SELECT * FROM v_table WHERE &lt;&lt;condition&gt;&gt;;<br>is the same thing like<br>SELECT * FROM my_table WHERE &lt;&lt;view_condition&gt;&gt; AND &lt;&lt;condition&gt;&gt;;<br><br>So, querying a view is slower than querying a table which contains the exact rows (matching &lt;&lt;view_condition&gt;&gt.<br><br>Example:<br>Querying a view based on a table with 1 million rows, with &lt;&lt;view_condition&gt;&gt; returning only 100 rows is slower than querying a table with 100 rows.<br><br>I hope my answer will help you, good luck. <p>Eduard Stoleru<br><a href=mailto:e_stoleru@yahoo.com>e_stoleru@yahoo.com</a><br><a href= > </a><br>

 

[mixc]

thanks<br>I knew this but needed backup explanation.<br>

 

[MikeLacey]

Eduard and Mixc,<br><br>My understanding is there *are* cases when accessing a view will be faster than accessing a raw table.<br><br>Each time you present an SQL statement to the server to be excuted it is compiled - unless the same statement has been compiled before.<br><br>A view is based upon an SQL statement like:<br><FONT FACE=monospace><b><br>Create View vThingy As<br>&nbsp;&nbsp;&nbsp;&nbsp;Select * From Thingy Where col1 = 'X';<br></font></b><br>You can then say:<br><FONT FACE=monospace><b><br>Select * From vThingy Where col1 = 'X';<br></font></b><br>and get only the 'X' records. The statement<br><FONT FACE=monospace><b><br>Select * From Thingy Where col1 = 'X';<br></font></b><br>on which the view is based will only be compiled once.<br><br>In this, trivial, example I doubt you would ever be able to detect the advantage. Any advantage would come from a view based upon a more complex SQL statement.<br><br>Bit of an academic point this, the compile phase of executing an SQL statement doesn't take that long anyway.<br><br> <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[karluk]

Another issue to consider is if the SQL written against the view contains its own where clause.&nbsp;&nbsp;From what I've read the Oracle optimizer can't always combine the where clause in the view definition with the where clause in the SQL.&nbsp;&nbsp;This can result in a poorer access path than if the entire query were written directly against the table with a single where clause.

 

[connectxyzzy]

If you want to discover if the result will be faster, run explain plan for 'select ......'<br><br>You may find the plan is identical, sometimes not. But the overall advantages of views usually outweigh the disadvantages (reuse of code, maintenance, clarity, security, granularity, etc.)&nbsp;&nbsp;Downsides of views, IMHO, are when used in forms, and you need to update 'through' the view.