Monday, April 5, 2010

Difference between Table and View in Oracle

A table is where you store your data. The table actually occupies space on disk. A sample table might be the EMPLOYEE table which has the columns EMPID, EMP_NAME, and SALARY.

A view is a stored query. A sample view might look like the following:

CREATE VIEW emp_view AS SELECT empid,emp_name FROM employee;

Notice that in the view, I have omitted the SALARY column. You can then query the view similar to the following:

SELECT * FROM emp_view;

Oracle will know this is a view and automatically rewrite the query to return only the EMPID and EMP_NAME columns from the EMPLOYEE table.

Views are used primarily to store a common query in the database. Without the view, you might have the same complex query stored in multiple locations in your application code. If you need to make a change, you would have to change the query in all locations. However, if that query were in a view, you would only have to change it in one location.

The other common reason for a view is for security purposes. If I grant a user the ability to query EMP_VIEW and they do not have permissions to query the EMPLOYEE table, then they could never see the SALARY column.