What is the difference between SELECT data from table directly or from view?
And What is the best use for each one?
views:
490answers:
4
+1
A:
In most databases, they are functionally interchangeable (disregarding materialized views, which are something entirely different anyway.) There are two common reasons for creating views. 1. An abstraction (and column-aliasing) mechanism, and 2. For permissions and access control. But as for efficiency, it's not an issue.
le dorfier
2009-02-22 21:40:38
Not true. Some databases have performance issues when views are nested.
Jason Cohen
2009-02-22 21:45:26
And do you have any references? That seems highly unlikely, (although that's why I said "in most databases".)
le dorfier
2009-02-22 21:58:25
Yes. In Oracle nested views can behave badly. We've experienced this ourselves when comparing expanded queries versus the same queries defined as multiple views inside Oracle.
Jason Cohen
2009-02-22 22:18:27
I'll be interested - and surprised - to see if we get any corroboration on that. It would be a pretty devastating indictment of any commercial DBMS product.
le dorfier
2009-02-22 22:50:27
A:
Think of it this way:
A view is just a select statement that lives on the server and has been compiled by the SQL engine.
Usually views are used to limit/simplify the results from the table.
Regards K
Khb
2009-02-22 21:42:16
+1
A:
It depends on the database and the definition of the view.
A simple view that aliases columns or performs simple calculations will not be different from making the query directly.
However, in some cases the views can be much slower. An example: In Oracle, if you nest view queries too much (e.g. one view uses another as a table, which uses another, etc.), you can create awful performance.
In general you need to test with the specific database and queries in question.
Jason Cohen
2009-02-22 21:43:01
Are you claiming that using the view makes your query slower than the inline subselect it represents?
Khb
2009-02-22 21:59:30
If it's inefficient, I'd bet that it's because the views are inappropriately adding more fields and/or joins than are needed. But it's still the same performance as the equivalent single SQL statement.
le dorfier
2009-02-22 21:59:57
No, we've tried using the SAME queries, just in views or in selects with sub-queries. The latter can be much faster. Why? Dunno, Oracle is full of mystery, and not in a good way.
Jason Cohen
2009-02-22 22:19:18
No, I'm not claiming views are automatically slower; indeed they are usually faster just because they're precompiled. But unfortunately reality isn't the same as theory with complex views.
Jason Cohen
2009-02-22 22:19:55
If you read that article closely, you'll see that it's talking about "materialized views", which are really another animal, which really creates a special form of table.
le dorfier
2009-02-22 22:28:22