What are the advantages and disadvantages of View?

Questions by sbagai2001   answers by sbagai2001

Editorial / Best Answer

Answered by: Mohan

  • Jun 2nd, 2006

Hi,

Advantages of views:

1. View the data without storing the data into the object.

2. Restict the view of a table i.e. can hide some of columns in the tables.

3. Join two or more tables and show it as one object to user.

4. Restict the access of a table so that nobody can insert the rows into the table.

Disadvatages:

1. Can not use DML operations on this.

2. When table is dropped view becomes inactive.. it depends on the table objects.

3. It is an object, so it occupies space.

Pls. add , if I miss any of them.

Thanks,

Mohan

Showing Answers 1 - 38 of 38 Answers

Mohan

  • Jun 2nd, 2006
 

Hi,

Advantages of views:

1. View the data without storing the data into the object.

2. Restict the view of a table i.e. can hide some of columns in the tables.

3. Join two or more tables and show it as one object to user.

4. Restict the access of a table so that nobody can insert the rows into the table.

Disadvatages:

1. Can not use DML operations on this.

2. When table is dropped view becomes inactive.. it depends on the table objects.

3. It is an object, so it occupies space.

Pls. add , if I miss any of them.

Thanks,

Mohan

  Was this answer useful?  Yes

abhinav28

  • Jun 23rd, 2006
 

hi,

Just to add ..Views don't consume space as they are created dynamically ...ex- when you do select * from view_name ;

  Was this answer useful?  Yes

sippsin

  • Jun 28th, 2006
 

You can update (DML) a table through a view if  the view is created using a single table. If the view is created using multiple tables, then if the tables are normalised using constraints, you could update those tables too through the view.

  Was this answer useful?  Yes

sippsin

  • Jun 28th, 2006
 

The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.

select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;

  Was this answer useful?  Yes

dmadhusudhanarao

  • Aug 2nd, 2006
 

advantages

1. hiding the data.

2. you can use two tables data in view.

3. security will be there.

disadvantages

1.when table is not there view will not work.

2. dml is not possible if that is more than one table.

3. it is also database object so it will occupy the space.

  Was this answer useful?  Yes

YOGENDRA SHRIVASTAVA

  • Nov 26th, 2006
 

 hello sir,

            plz clear that. is view has storage space. if not then y u written this

           "it is also database object so it will occupy the space."

  Was this answer useful?  Yes

Thulasidas

  • Nov 26th, 2006
 

another disadvantage of a view is, it affects performance. Querying from view takes more time than directly querying from the table

  Was this answer useful?  Yes

Indira

  • Dec 2nd, 2006
 

Hi,Only the view definition is stored as a select statement in the data dictionary. Each time a view is accessed the select statement is executed and the rows are retrieved. It does not own data of its own ,so occupies negligable amount of space. It is also useful in hiding some columns from the original tables and the user can be restrictedfrom viewing all the columnsIndira

  Was this answer useful?  Yes

DHRUVA SEN GUPTA

  • Apr 26th, 2007
 

View based on multiple tables are up-datable using INSTEAD OF triggers.

  Was this answer useful?  Yes

shalini parekh

  • Nov 14th, 2011
 

hi!
i would like to add another point to the disadvantages of view...


DISADVANTAGES OF VIEW:

A query fired on a view will run slower than a query fired on a base table ,this is because the view definition has to be retrieved from the oracle's system catalog,the base table has to identified and opened in memory and then view has to be constructed on the top of the base table,suitably masking table columns

  Was this answer useful?  Yes

Pralay

  • May 29th, 2012
 

View needs very less space. Only view definition needs the database space where it does not store any data in it.

Sarvesh

  • Aug 24th, 2012
 

Views does not require any space in the database.

  Was this answer useful?  Yes

millar

  • Sep 13th, 2012
 

some view cannot be updated when it contains group by,order by clauses, which are said to be non updatable views, this type of view can be updated using INSTEAD OF TRIGGERS , the purpose of instead of triggers when a dml statement is issued against a view, instead of updating the trigger, instead of trigger will update the bast table containing the view

  Was this answer useful?  Yes

Sadashiv

  • Nov 14th, 2012
 

Views do not actually store the data. So only for the view definition the space is required of the database.

  Was this answer useful?  Yes

vin

  • Nov 22nd, 2012
 

We can perform DML operations on view
and a view does not occupy any physical space in db.

  Was this answer useful?  Yes

purna chandrudu

  • May 13th, 2013
 

DML operation can performed on views.

  Was this answer useful?  Yes

soumya

  • May 17th, 2013
 

View when called always contacts the base table, on which it is built to get the data .so it always goes to the server thats why it degrades the performance of the server.

  Was this answer useful?  Yes

Pooja Thakur

  • Aug 11th, 2013
 

Adv- hide complexity

  Was this answer useful?  Yes

Sanjay salunkhe

  • Mar 13th, 2014
 

You can perform DML operation on view.
All views based on select statement,View is compiled format of select statement.
If table gets deleted then view remains same and if table get drop then view gets deleted.

  Was this answer useful?  Yes

mohan

  • Jul 8th, 2014
 

You can perform DML on view but only one simple view (one table) , If view would join more then one table data you may not perform any DML operations.

  Was this answer useful?  Yes

damodhar

  • Sep 1st, 2014
 

View is virtual tables unlike tables that contain data,views contain queries that dynamically retrieve data when used.
Materialized view: Materialized views is also a view but are disk based.materialized get updated on specific duration,
Base upon the interval specified in the query definition.

Advantages of view:

1--> Views doesnt store data in a physical location
2--> view can be use to hide some of the columns from the table.
3-->Views can provide access restriction since data insertion,update and deletion is not possible on the view

Disadvantages of view:

When table is drop,associated view become irrelevant.
when views are created large tables it occupy more memory.

  Was this answer useful?  Yes

Dan

  • Oct 3rd, 2014
 

You can indeed insert on complex views... and it is a little known fact, so watch out for this trick question in your interview! You can insert into a complex view using an "instead of trigger". You add this trigger on the view. Thats right, you can add a trigger on a view... in this trigger you can define which column of the inserted data does to which table; you basically must map the insert to the corresponding base table

  Was this answer useful?  Yes

jitendra kumars

  • Nov 10th, 2014
 

Disadvantages-Oracle does not allow constraints on views

  Was this answer useful?  Yes

kiyani

  • Jan 28th, 2015
 


we have two types of views: "Materialized"
that act as base table and occupy space in hard disk.

"Non-Materialized" - that are temporarily located in ram just for display as query output.

  Was this answer useful?  Yes

Parveen Dahiya

  • May 8th, 2015
 

When Table gets deleted then view remains with no data.
When table gets dropped the view still remains but cannot be accessed (Could not use view or function View_Name because of binding errors).
Later when we recreate the table which was dropped earlier can be accessed using the view which we created without recreating it.
)

  Was this answer useful?  Yes

anuanu

  • Jul 22nd, 2015
 

Security,
Query Simplicity,
Structural Simplicity,
Consistency,
Data Integrity,
Logical Data Independence

  Was this answer useful?  Yes

SRUTHY

  • Jun 14th, 2016
 

DML is possible in view

  Was this answer useful?  Yes

rabindra

  • Jun 29th, 2016
 

We can perform DML for updatable/writable MV case.


Regards,
Rabindra

  Was this answer useful?  Yes

NAVYA

  • Jul 12th, 2016
 

we can use DML commands. if we create a view for a single table it will allow to use DML commands(insert ,delete,update) but when u go with the multiple tables that means complex view it will not allow to use DML commands.

  Was this answer useful?  Yes

vipin

  • Sep 28th, 2016
 

We can use DML operations on a VIEW. I used an update command on a VIEW.

  Was this answer useful?  Yes

Ghansyam Panigrahi

  • Sep 29th, 2016
 

We Can Write DML Operation But If The Select St Is Having Join Condition Then We Cannot Accept...

  Was this answer useful?  Yes

dax

  • Nov 29th, 2016
 

VIEW is logical table
It is restricted to not allow data insertion.
When sub-query is JOIN then VIEW are not updated.

  Was this answer useful?  Yes

Debendra

  • Mar 21st, 2017
 

We can use DML operation on views also, which will reflect on base table.

  Was this answer useful?  Yes

Selva

  • Apr 4th, 2017
 

We can use DML statement while writing the query in view point.
Like Select command

  Was this answer useful?  Yes

Marcus

  • Apr 6th, 2018
 

The view uses virtual space when in use, this is a temporary space on the server that goes away when not in use.

  Was this answer useful?  Yes

Raj

  • Mar 7th, 2019
 

These are following some advantages
1. Performance
2. Security
3. Easy and Reusable
4. Consistency and independent

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions