Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

oracle views

oracle views

oracle views

do oracle views "improve performance"? My understanding is they are for security; they are dynamic. The issue is this:

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.

We have a discussion at work and it seems quite clear to me but I said I would ask.

RE: oracle views

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.:
CREATE OR REPLACE VIEW v_table AS SELECT * FROM my_table WHERE <<view_condition>>;
SELECT * FROM v_table WHERE <<condition>>;
is the same thing like
SELECT * FROM my_table WHERE <<view_condition>> AND <<condition>>;

So, querying a view is slower than querying a table which contains the exact rows (matching <<view_condition>>).

Querying a view based on a table with 1 million rows, with <<view_condition>> returning only 100 rows is slower than querying a table with 100 rows.

I hope my answer will help you, good luck.

Eduard Stoleru

RE: oracle views

I knew this but needed backup explanation.

RE: oracle views

Eduard and Mixc,

My understanding is there *are* cases when accessing a view will be faster than accessing a raw table.

Each time you present an SQL statement to the server to be excuted it is compiled - unless the same statement has been compiled before.

A view is based upon an SQL statement like:

Create View vThingy As
    Select * From Thingy Where col1 = 'X';

You can then say:

Select * From vThingy Where col1 = 'X';

and get only the 'X' records. The statement

Select * From Thingy Where col1 = 'X';

on which the view is based will only be compiled once.

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.

Bit of an academic point this, the compile phase of executing an SQL statement doesn't take that long anyway.

Cargill's Corporate Web Site

RE: oracle views

Another issue to consider is if the SQL written against the view contains its own where clause.  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.  This can result in a poorer access path than if the entire query were written directly against the table with a single where clause.

RE: oracle views

If you want to discover if the result will be faster, run explain plan for 'select ......'

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.)  Downsides of views, IMHO, are when used in forms, and you need to update 'through' the view.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close