10)What is a View?
A View is a Database Object which hides the complexity of the Query and it invokes the indexes on the base tables. Any DML Operation on the view applies on the base tables on which the view is based on. We make use of a INSTEAD OF TRIGGER on the view so that any DML operation on the view can applied on the base tables where is no definite correlation between the columns on the view and the columns on the base tables.Also when a View is Queried , then the underlying Query is run at that time.
11)What is a materialized View?
A Materialized View Precomputes the data or executes the Query behind the view and has its results precomputed. This is very different from the normal Oracle Views which executes the Query on run time. Since the Query results are already precomputed, Accessing Data from the Materialized View is much faster when compared to Oracle Views. Materialized Views favours the creation of indexes on the precomputed data which is not possible in the case of Oracle Views which uses the indexes on the base tables on which the Oracle View is created.
The QUERY REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.ENABLE Clause Specify ENABLE to enable the materialized view for query rewrite.
Enabling of query rewrite is subject to the following restrictions:
•You can enable query rewrite only if all user-defined functions in the materialized view are DETERMINISTIC.
•You can enable query rewrite only if expressions in the statement are repeatable. For example, you cannot include CURRENT_TIME or USER, sequence values (such as the CURRVAL or NEXTVAL pseudocolumns), or the SAMPLE clause (which may sample different rows as the contents of the materialized view change).
12)Why should we go for creating a View and a materialized View?
When there is a need to hide the complexity of a Query, then we should go for creating a View for the underlying Query. On the Other hand, when you want the results of the underlying Query to be pre computed , then prolly we should go in for creating a materialized view. Also when there is a need to create indexes on the underlying view apart from the indexes on the underlying tables, we should go in for creating a materialized view.Also Materialized View is a way of replicating data from one database to another.
13)What are the different refresh techniques associated with Materialized Views?
COMPLETE
FAST
FORCE
14)Is there anything like a Parameterized View?
There is nothing like a parameteized view in oracle, but we can replicate the same by making use of Oracle Context.
15)What is Context with respect to Oracle?
There is no concept of a parameterized Views in Oracle as to parameterized Cursors in Oracle. But the Concept of a Parameterized Views can be achived
by way of a context Creation and treat the Attributes in the Context as to parameters which are passed to Parameterized Views. The View is initally created with
the help of the context attribute been used in the where clauses as filter conditions to the data retrieved. Then the calling Program should first set the attribute values
in the context and then make a call to the View.
16)What is a Trigger?
In Order to enforce a business contraint, we make use of a trigger in Oracle.Oracle lets you define procedures called triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. These procedures can be written in PL/SQL or Java and stored in the database, or they can be written as C callouts.
You can write triggers that fire whenever one of the following operations occurs: DML statements on a particular schema object, DDL statements issued within a schema or database, user logon or logoff events, server errors, database startup, or instance shutdown.Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.
17)Will the use of a Trigger hinder the performance of an application?
Yes, because each time when the triggering event occurs, the procedure within the trigger needs to be executed apart from the event which triggered it.The performance hindrance also depends upon whether its a row level or a statement level trigger.
18)What are the different types of Triggers?
Row Triggers and Statement Triggers
BEFORE and AFTER Triggers
INSTEAD OF Triggers
Triggers on System Events and User Events
19)What is meant by an Instead of Trigger?
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.
You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.
20)What are statement level and Row Level Triggers?
A ROW TRIGGER is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.
A STATEMENT TRIGGER is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.
21)Can we commit inside a Trigger?
Yes we can commit inside a trigger. We can make use of the PRAGMA AUTONOMOUS TRANSACTION inside the trigger to state that the transactions made inside the trigger are independent of the calling block.
22)What is meant by Pragma?
PRAGMA is a compiler directive.Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compiler.
A View is a Database Object which hides the complexity of the Query and it invokes the indexes on the base tables. Any DML Operation on the view applies on the base tables on which the view is based on. We make use of a INSTEAD OF TRIGGER on the view so that any DML operation on the view can applied on the base tables where is no definite correlation between the columns on the view and the columns on the base tables.Also when a View is Queried , then the underlying Query is run at that time.
11)What is a materialized View?
A Materialized View Precomputes the data or executes the Query behind the view and has its results precomputed. This is very different from the normal Oracle Views which executes the Query on run time. Since the Query results are already precomputed, Accessing Data from the Materialized View is much faster when compared to Oracle Views. Materialized Views favours the creation of indexes on the precomputed data which is not possible in the case of Oracle Views which uses the indexes on the base tables on which the Oracle View is created.
The QUERY REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.ENABLE Clause Specify ENABLE to enable the materialized view for query rewrite.
Enabling of query rewrite is subject to the following restrictions:
•You can enable query rewrite only if all user-defined functions in the materialized view are DETERMINISTIC.
•You can enable query rewrite only if expressions in the statement are repeatable. For example, you cannot include CURRENT_TIME or USER, sequence values (such as the CURRVAL or NEXTVAL pseudocolumns), or the SAMPLE clause (which may sample different rows as the contents of the materialized view change).
12)Why should we go for creating a View and a materialized View?
When there is a need to hide the complexity of a Query, then we should go for creating a View for the underlying Query. On the Other hand, when you want the results of the underlying Query to be pre computed , then prolly we should go in for creating a materialized view. Also when there is a need to create indexes on the underlying view apart from the indexes on the underlying tables, we should go in for creating a materialized view.Also Materialized View is a way of replicating data from one database to another.
13)What are the different refresh techniques associated with Materialized Views?
COMPLETE
FAST
FORCE
14)Is there anything like a Parameterized View?
There is nothing like a parameteized view in oracle, but we can replicate the same by making use of Oracle Context.
15)What is Context with respect to Oracle?
There is no concept of a parameterized Views in Oracle as to parameterized Cursors in Oracle. But the Concept of a Parameterized Views can be achived
by way of a context Creation and treat the Attributes in the Context as to parameters which are passed to Parameterized Views. The View is initally created with
the help of the context attribute been used in the where clauses as filter conditions to the data retrieved. Then the calling Program should first set the attribute values
in the context and then make a call to the View.
16)What is a Trigger?
In Order to enforce a business contraint, we make use of a trigger in Oracle.Oracle lets you define procedures called triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. These procedures can be written in PL/SQL or Java and stored in the database, or they can be written as C callouts.
You can write triggers that fire whenever one of the following operations occurs: DML statements on a particular schema object, DDL statements issued within a schema or database, user logon or logoff events, server errors, database startup, or instance shutdown.Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.
17)Will the use of a Trigger hinder the performance of an application?
Yes, because each time when the triggering event occurs, the procedure within the trigger needs to be executed apart from the event which triggered it.The performance hindrance also depends upon whether its a row level or a statement level trigger.
18)What are the different types of Triggers?
Row Triggers and Statement Triggers
BEFORE and AFTER Triggers
INSTEAD OF Triggers
Triggers on System Events and User Events
19)What is meant by an Instead of Trigger?
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.
You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.
20)What are statement level and Row Level Triggers?
A ROW TRIGGER is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.
A STATEMENT TRIGGER is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.
21)Can we commit inside a Trigger?
Yes we can commit inside a trigger. We can make use of the PRAGMA AUTONOMOUS TRANSACTION inside the trigger to state that the transactions made inside the trigger are independent of the calling block.
22)What is meant by Pragma?
PRAGMA is a compiler directive.Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compiler.
No comments:
Post a Comment