Menu
Recently started working with a database in which the convention is to create a view for every table. If you assume that there is a one to one mapping between tables and views, I was wondering if anyone could tell me the performance impacts of doing something like this. BTW, this is on Oracle.
612
Because you store data in a table on the database, it can be quicker to access. Once you open the application, you can quickly access the information you seek. Data in a view can take longer to access because you have to run a query first. If you want results for data from multiple tables, this can take even longer.
Most note that they operate slower than simply joining in the information they need from the base tables in every query, throwing out the advantages of the views.
Views enable us to hide some of the columns from the table. It simplifies complex queries because it can draw data from multiple tables and present it as a single table. It helps in data security that shows only authorized information to the users.
Views make queries faster to write, but they don't improve the underlying query performance.
Assuming the question is about non-materialized views -- Really depends on the query that the view is based on, and what is being done to it. Sometimes, the predicates can be pushed into the view query by the optimizer. If not, then it wouldn't be as good as against the table itself. Views are built on top of tables -- why would you expect that the performance would be better?
Layering views, where you build one view on top of another, is a bad practice because you won't know about issues until run time. It's also less of a chance that predicate pushing will occur with layered views.
Views can also be updateable -- they aren't a reliable means to restricting access to resources if someone has INSERT/UPDATE/DELETE privileges on the underlying tables.
Materialized views are as good as tables, but are notoriously restrictive in what they support.
66
You don't explain what you're doing in the views? A 1:1 with the tables sounds like you are using the views more like synonyms than a view. IOW, are the views = "SELECT * FROM table", then you'll see no performance hit except on hard parse.
If you are joining to other tables or placing filter clauses in them which prevent predicate pushing than you're bound to see a major hit sometime.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
