Fetch data from view vs query?

Question

Approach_1

Through SQL query

Select * from employee where id = 100

Approach_2

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

Answer 1

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).

Answer 2

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 .