Is view faster than a natural join in Oracle? what about postgresql?

Question

SELECT cec.*
  FROM mam.category cec


SELECT cec.year, ces.*
  FROM mam.subcategory ces
  JOIN mam.category cec ON CEC.CATEGORY_ID = CES.CATEGORY_ID


SELECT cec.year, ceo.*
  FROM mam.options ceo
  JOIN mam.subcategory ces ON CES.SUBCATEGORY_ID = CEO.SUBCATEGORY_ID
  JOIN olr.iep_cost_est_category cec ON CEC.CATEGORY_ID = CES.CATEGORY_ID

According to a friend, views in oracle are actually faster for cache purposes. Is this true? What about postgresql? I've tried google and stackoverflow (closest one is MS SQL).

Answer 1

Views

Views, which means non-materialized views, are not cached. They are simply a prepared SQL statement that is run in place of the view reference in a query. Think of them like macros, or variables holding the SELECT statement contained in the view.

Materialized views (not supported by PostgreSQL) are similar to tables because they can be indexed. But materialized views are notoriously restricted (IE: no non-deterministic values) in what they can support.

Natural JOINs

None of the examples you posted are natural JOINs, which look like this:

      SELECT cec.year, ces.*
        FROM mam.subcategory ces
NATURAL JOIN mam.category cec

The syntax is frowned upon (though being ANSI) because it's ambiguous at best and leaves you open to problems if:

• columns get added or renamed
• no more than two tables can be joined using this method
• gives you little control over the specifics of a join if columns join across the tables in an unusual way. It's not obvious what the join criteria is -- being explicit is both readable and guarantees consistent results.

Conclusion

Non-materialized views are largely irrelevant with regard for JOIN syntax. Data and indexing will have a larger impact on performance.

Answer 2

Views can sometimes help with caching slightly. The basis is that

SELECT a.name, b.zipcode 
FROM table_a a JOIN table_b b ON a.id = b.id

is a different query from

SELECT a.name, b.zipcode 
FROM table_b b JOIN table_a a ON a.id = b.id

even though they are logically identical. If both get sent to Oracle, they both end up in the query cache. [The query cache is where Oracle stores queries so it doesn't have to repeat syntax/permission checks and the calculation a query execution path.] By having a view that encapsulates the join between table_a and table_b, there's less chance of multiple queries ending up in the cache that are logically identical.

This is part of a more generic principle of "Don't repeat yourself". If you repeat code, you need to repeat testing and patching and you have more code that can go wrong. Any performance benefit is a bonus. So there are benefits to views, but performance isn't a significant one.