Our DB team just created a Database View that combines two tables to pull the records. However, this does not pull all other required data from other table. This view has only fields that are used in the search. The view does not have all the fields required to present in the report.
My understanding is, Views are best used to combine all required tables (not in all cases, though) and required fields to be sent to the report/presentation. We are going to map this View to Hibernate and use Hibernate to pull the data. DB team argues that, you don't need all columns and tables, and pointed me to do all these stuff with Hibernate mappings.
I am wondering whats the point of the view then. Or may be I am not getting this right?! The view just has the fields that are searched on.
As so often, it depends on many things.
The best way to think about views (in the context you describe) is as a stored reusable select statement. It doesn't have to have all the columns needed by the final results, you can still join them with the view, just as if the view would be a table. And this is what your DB team seams to suggest.
If the missing column are part of the tables that are used in the construction of the view, you probably take a performance hit, because you will join the table twice, instead of once. This would probably be a bad idea.
In case of doubt do an experiment:
Write the statement as it would look, if you use the view.
Then write the statement as you would use to create the view you seem to prefer.
Ensure both produce the exact same result.
Measure the time needed by both statements. Make sure you measure the time need to get all results, not just the first 20 or something, because often with large results, the access path that delivers the first rows fast, takes much longer to get all results.