Problem solve Get help with specific problems with your technologies, process and projects.

Differences between tables and views

What are the differences between tables and views?

What are the differences between tables and views?
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.

Dig Deeper on Oracle DBA jobs, training and certification