When hiring Oracle DBAs and developers, a lot of questions are asked about Oracle view. Although view is only one of Oracle objects and it is not that significant in my opinion, for some reason managers and business users love views, therefore in interview they usually like to ask more questions on Oracle views. The following are some of questions and answers for you to prepare:
1. What is a view and how to create one?
A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used. For example, if you frequently use a query SELECT empid FROM emp, you may create a view as
CREATE VIEW view_emp
SELECT empid FROM emp;
Then every time you query this view, Oracle will go out and execute the SQL behind the view to query the database data.
2. Can Oracle view improve performance? Why or why not?
In most cases view does improve performance because of commonality of code being used. Since a view is based on one common set of SQL, when it is called it’s less likely to require parsing. This is because the basic underlying SQL that is called is always the same. A query without parsing is faster. However, since you can add additional where clauses when calling a view, you still need to use bind variables. Additional where clauses without a bind variable can still cause a hard parse.
3. Explain how you may use view to enhance security and what are view’s limitations for security practice?
It is a two-question in one but people often miss the second one. In any database, view’s limitation for security is the issue of scalability.
Views are commonly used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to. Using views for security on less complex databases is probably not a bad thing. As databases become more complex, this solution becomes harder to scale and other solutions will be needed.
4. In what situation Oracle views can affect performance, and what is Oracle’s solution to this issue?
When you create complex views with many table joins to represent large subsets of query and allow many developers and end users to access these views, you may experience poor performance. In this case, you may consider Oracle’s materialized view.
5. What is materialized view and where is it used?
A materialized view is a snapshot of data that can be refreshed based on a schedule. It is a replica of a target master from a single point in time. Oracle uses materialized views to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment.
6. Can view be indexed in Oracle?
Don’t think Oracle can do everything and quickly answer ‘yes’. SQL Server offers view index, but Oracle does not do it.
Regular Oracle view cannot be indexed, but you can utilize table level indexed to tune underlying query that generates the view.
7. Can you insert and update records in a view?
This question asks if you can execute insert or update statement directly on a view instead of on a table.
You can only perform data manipulation language (DML) operations with simple views. Complex views don’t support DML. view is mainly used for presenting and data and simplifying query. Data insert and update should be done on table level.
8. What’s the different between GRANT CREATE VIEW and CREATE ANY VIEW?
It is not a question about view. This question checks your knowledge of controlling user privileges in Oracle database. Grant Create view gives user’s right to create new views in grantee’s schema only. GRANT CREATE ANY VIEW allow users to create views in any schema.