Through SQL query

Select * from employee where id = 100


Here is the db view(employee_view)

Select * from employee

Then use the DB view in sql query from java

select * from employee_view where id = 100

My understanding is that approach_2 is bad in performance in comparison to approach_1. Reason is - In second case first all data will be fetched from view and then each row will be sequentially scanned(basically index on id column will not be used here). Right or approach_2 is same as approach_1 as ultimately query will be same?

But in approach_2 index on id will be used ad only one row corresponding to 100 will be fetched. Is that correct ?

This is simple example i gave here. But in practical i extracted some part of query to DB view and just appended the where clause with that view in my java code. I see explain plan got changed and some of the indexes were not used

Update :- Oracle is the DB here

  • Can you add explain plans for both queries?
    – arthur
    Jan 3, 2018 at 12:51
  • Your understanding is incorrect. Study query execution plans for an objective proof.
    – mustaccio
    Jan 3, 2018 at 13:50

2 Answers 2


Both approaches are equivalent at the DB level (in modern DBMSes).

Generally, the database:

  1. Gets the query
  2. Checks it (syntactically and semantically)
  3. Rewrites it (in case some logical expressions could be simplified, in your case none)
  4. Creates possible plans
  5. Chooses the optimal plan
  6. Executes it.

In Approach (2) compared to approach (1), in step (3) above the view would be replaced with the table itself.

If the optimal plan involves an index scan, the index will be used (e.g., there is one row in the answer set and there are 1000000 rows overall in the table). Otherwise, a table will be scanned instead (e.g., the answer set would contain 1 row and the complete table is also 1 row).


In some cases using views may affect performance, for instance if multiple tables are joined; however, you cannot tell upfront which version will be faster. In simple cases like in your example Oracle optimizer will push the predicate inside view. You may find some advanced tuning approaches regarding views and push predicates in http://www.orafaq.com/tuningguide/push%20predicates.html.

Keep in mind that SQL is declarative language - it lets you specify what you want to get with very little control on how to actually get it. Generating plan for physical execution is optimizer's task which is supposed to generate the best plan within the reasonable time and hardware constraints, not the best possible plan .

  • I believe your statement In some cases using views may affect performance, for instance if multiple tables are joined true only for materialized view not for simple views. For simple views query/execution plan will(should) always be same Jan 3, 2018 at 17:25
  • Not necessarily. Queries against views with unions , joins, aggregates or window functions may generate different execution plans vs their non-view equivalents. For trivial cases that involve few tables , execution plan is the same
    – a1ex07
    Jan 3, 2018 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.