View Advantage and disadvantage oracle

View Advantage and disadvantage oracle

A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used

Benefits of Oracle Views

Oracle views offer some compelling benefits. These include:

* Commonality of code being used. Since a view is based on one common set of SQL, this means that 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. 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!

* Security. Views have long been 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.

* Predicate pushing. Oracle supports pushing of predicates into a given view. Assume we had a set of layered views, like this:


create view viewsample as
select ct_name,ct_description,first_name
from category,employee group by first_name

select * from viewsample

1)Simple Views:

•The view which is created by selecting only one table.
•A Simple view does not contain functions.
•You can always perform DML operations through Simple views.

2)Complex Views:
•The view which is created by selecting more than one tables.
•A Complex view contains functions.
•You cannot always perform DML through Complex views.

Rules for performing DML Operations on a view:

•You can perform DML operations on Simple views.
•You cannot always perform DML through Complex views.
•You cannot delete a row if the view contains the following:

Group functions such as SUM, MIN, MAX, AVG and ......
A GROUP BY clause.
The DISTINCT keyword.

• You cannot update data in a view if it contains,

Group functions such as SUM, MIN, MAX, AVG and ......
A GROUP BY clause.
The DISTINCT keyword.
Columns defined by expressions such as SALARY*1.2
The ROWNUM pseudo column.

• You cannot insert data in a view if it contains,

Group functions such as SUM, MIN, MAX, AVG and ......
A GROUP BY clause.
The DISTINCT keyword.
Columns defined by expressions such as SALARY*1.2
The ROWNUM pseudo column.
There are NOT NULL columns in the base tables that are not selected by the view.

Types of View:

View can be categorized into two. The differences between simple and complex view also listed below.



1)Simple Views:



•The view which is created by selecting only one table.

•A Simple view does not contain functions.

•You can always perform DML operations through Simple views.



2)Complex Views:


•The view which is created by selecting more than one tables.

•A Complex view contains functions.

•You cannot always perform DML through Complex views.



Rules for performing DML Operations on a view:



•You can perform DML operations on Simple views.

•You cannot always perform DML through Complex views.

•You cannot delete a row if the view contains the following:




Group functions such as SUM, MIN, MAX, AVG and ......

A GROUP BY clause.

The DISTINCT keyword.



• You cannot update data in a view if it contains,



Group functions such as SUM, MIN, MAX, AVG and ......

A GROUP BY clause.

The DISTINCT keyword.

Columns defined by expressions such as SALARY*1.2


The ROWNUM pseudo column.



• You cannot insert data in a view if it contains,



Group functions such as SUM, MIN, MAX, AVG and ......

A GROUP BY clause.

The DISTINCT keyword.

Columns defined by expressions such as SALARY*1.2

The ROWNUM pseudo column.

There are NOT NULL columns in the base tables that are not selected by the view.



http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html

http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html

http://arjudba.blogspot.com/2009/12/oracle-security-practices.html

http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html

http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html


http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html

http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html

http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html

http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html

http://arjudba.blogspot.com/2009/07/sql-decode.html

http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html


http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html

http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html

http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html

http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html

http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html

http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html


http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html

http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html

http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html

http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html

http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html

http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html


http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html

http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html

http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html

http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html

http://arjudba.blogspot.com/2008/06/hints-in-oracle.html

http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html


http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html

http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html

http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html

http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html

http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html

http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html


http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html

http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html

http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html

http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html

http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html

http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html


http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html

http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html

http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html

http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html

http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html

http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html


http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html

http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html

http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html

http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html

http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html

http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html


http://arjudba.blogspot.com/2008/06/history-of-sql.html

http://arjudba.blogspot.com/2008/06/what-is-sql.html