Question of query speeds.

[fiat2]

Just a quick question:
Using Oracle v9.2

Is there a speed benefit in querying a database view rather than a database table?

I do follow a standard in creating all my data needs by with a view but besides ease of maintenance, I was thinking if speed was a factor...

 

[cmmrfrds]

The efficiency may be comparable if you need to do full table scans by the requirement of the query, otherwise, using the tables will usually be more efficient since you can limit the data and avoid full table scans where it is more efficient to use indexes. You can always make use of an inline views to limit the data in the query.

 

[SantaMufasa]

Fiat2 and Cmmrfrds,

A VIEW is simply a named query. As such, a VIEW uses the same indexes that the query would use; a VIEW will do a full table scan (or not) in the same way that the underlying SELECT would (or would not) do a full table scan.

Therefore, the performace of a VIEW is identical to the performance of querying a table.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

Dave, in most business requirement I have the data produced by the View should be limited. That is why I implied the full table scan, since there would normally be a where clause with limiting criteria when going after the table directly. I see the View as getting all the possible data needed and then applying the where criteria when using the View, do you see it differently. If there is not a way to send a parameter to the View, how is this done?

 

[SantaMufasa]

My point is that if you run a query (which uses indexes, hints, and any other methods to avoid a full table scan), and then you "CREATE VIEW..." using the same query to define the view, then the execution times should be identical.

If you create a view that does not restrict the returned rows (via a WHERE clause) access performance enhancers such as indexes do not apply...you want all rows, thus a full table scan is the best access method. If you then restrict the rows via a WHERE clause, then all bets are off.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

fiat2, you stated that "all my data needs by with a view". Please be aware of the second paragraph in Dave's last reponse. This is what I was trying to get at although not explained very well.