Posts Tagged ‘Oracle view’

View can be defined as virtual table created from one or more actual tables underlying in databases.

As mentioned above “virtual table” that means database wont store the data for view instead of this it only stores a definition of the view.

** When I say “only stores a definition of the view” then I am only referring to simple view there

Is exception to that we will discuss later!

Coming back, so when we try to fetch data using a view database engine fetches the data from actual tables using DEFINITIONprovided for particular view

Why views?

A) To hide complexity of database schema from a end users (To make their life simpler)

B) To provide restricted access (Selected columns from a table NOT ALL) to users

Create a simple View

CREATE VIEW vwStudents
AS select Student_Name,Phone,Address

From Students

So, we can see only selected columns from students table are part of vwStudents and not all columns.

Use View

Select * from vwStudents

Indexed Views in SQL Server / Materialized Views in Oracle:

Why Indexed Views/ Materialized Views?

As I have mentioned above when data is requested by using a view, database engine dynamically pulls data from actual tables based on view definition. If data to computed dynamically is too large in size will result in poor performance.

So, rather than dynamically computing if we refresh our view when data in actual tables gets updated!!!

That is why Indexed Views/ Materialized Views.

Here is exception that I was talking point earlier.

An indexed view is a view that has a unique clustered index created on it.so, here row exist as row in database and no longer only definition that is kept!!!

Read Full Post »

%d bloggers like this: