Q-5

1)    What is the difference between view and materialized view?

View - store the SQL statement in the database and let you use it as a table. Every time you access the view the SQL statement executes.

Materialized view - stores the results of the SQL in table form in the database. SQL statement only executes once and after that every time you run the query the stored result set is used. Pros include quick query results.

View: This is a PSEUDO table that is not stored in the database and it is just a query. MATERIALIZED VIEWS: These are similar to a view but these are permanent stored in the database and often refreshed. This is used in optimization for the faster data retrieval and is useful in aggregation and summarization of data.

View is a logical reference to a database table. But Material View is actual table and we can refresh data in time intervals. If you made any change in database table that change will effect into view but not materialize view..

View: View is a logical or virtual memory which is based on select query and the simple view is the view in which we cannot make DML command if the view is created by multiple tables.

Materialize view: It works faster than simple its works as snap shot and used for security purposes and we can make DML command in materialize view.

Materialized view is physical duplicate data in a table. View is logical representation of table.

In view we cannot do DML commands where as it is possible in Materialized view.

 A view has a logical existence but a materialized view has a physical existence. Moreover a materialized view can be indexed, analyzed and so on....that is all the things that we can do with a table can also be done with a materialized view.

view is a logical or virtual table it doesn't have data on its own, but materialized view has a physical structure it stores data in local machine or on its own. Materialized view can be refreshed automatically or manually. But in view, if any changes happened in the base tables, we want to reflect the same in the view means view has to issue the select statement again to the database.

 

2)   What is MV and types of refresh methods used in it.

Materialized Views(also known as snapshots in prior releases) is a pre-computed table comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table.

It improves query performance by pre calculating expensive join and aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request.

 

 Types of Refresh:

 

1)    Complete Refresh

When a complete refresh of a materialized view occurs, all data is removed and

then it is reloaded. Depending on the size of the materialized view, this could be a

time consuming operation.

 

2)     Fast Refresh

Some materialized views could be very large and the time required to regularly

perform a complete refresh may not be available. The alternative is a fast refresh

where only the changes to the detail table are applied against the materialized view.

New data loaded into any table in the warehouse is identified and any materialized

view referencing that table is automatically updated with the new data.

In order to perform a fast refresh operation, changes made to the data must be

recorded and this is achieved in one of two ways. If your data is only ever inserted

into the database using SQL*Loader direct path, then the refresh mechanism will

detect this and identify the new data to be loaded.

 

3)     Partition Change Tracking Refresh

Oracle Database 10g provides a component known as Partition Change Tracking

(PCT) which transparently detects when changes to partitions occur and then

determines whether the operation has made the data in the materialized view

inconsistent.

 

What is srw. package? This package extends reports ,Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements  used to create or Drop  temporary table, Call User Exist, to format width of the columns, to page break the column, to set the colors.

SRW is a package name used while creating a report using rdf. It helps to perform action at runtime. for example  changing the color of the col. according to value fetched by the report   Calling a host program executing dynamic sql printing GUI messages at run time.

 

 

 

What is Ref. Cursor?

A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures functions packages etc.).

Difference between Ref Cursor and Normal Cursor

 

1)      In case of an normal explict cursor, the SQL query has to be defined at the time of declaring the cursor itself.  In case of REF Cursor, the cursor declartion is not associated with any SQL query, it is associated with a query at a later stage this brings in a lot of flexibility as different SQL queries can be associated with the cursor (one at a time, offcourse) programatically.  REF Cursors also provide the feature of passing parameters.  Though there is something dynamic with REF Cursor when compared to a normal explicit cursor, it is not a truly perfect dynamic cursor.  Truly perfect dynamic cursors are the one constructed using DBMS_SQL package.

2)      Generally ref cursor used for passing cursor parameter and also dynamically building the query. Normal cursor are static cursors and also we can't able to pass like parameter.

3)    T he normal cursor is a static cursor i.e we in the normal cursor the query is only assigned at the design time and it can't be change at the run time.

 Ref cursor supports the dynamic change in the cursor. It also help in memory allocation

Oracle autonomous transaction

An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction.

The autonomous transaction must commit or roll back before the autonomous transaction is ended and the parent transaction continues.

An autonomous transactions is available from Oracle 8i.

An autonomous transaction is defined in the declaration of a pl/sql block. This can be an anonymous block, function, procedure, object method or trigger.
This is done by adding the statement 'PRAGMA AUTONOMOUS_TRANSACTION;' anywhere in the declaration block.

There isn't much involved in defining a PL/SQL block as an autonomous transaction. You simply include the following statement in your declaration section:
PRAGMA AUTONOMOUS_TRANSACTION;

 

 

 

 

Sample code:

PROCEDURE test_autonomous

IS

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

   insert ....

   commit;

END test_autonomous;

Autonomous transactions can be used for logging in the database independent of the rollback/commit of the parent transaction.