PL/SQL -Q&A - 2011 - Part 3

What is difference between Materialized View and Materialized Log?

Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse or in multi-master replication environment.

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases.

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.


Materialized View Log - When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.


When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must re-execute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.

To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.


Difference between Bigfile & Smallfile?

Big and small file tablespaces in the Oracle 10g database
It is the Oracle Database 10g feature. A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size and on the other hand, a smallfile tablespace can contain many data files.
The size of a bigfile can reach to 128TB depending on the Oracle block size.

An Oracle database can contain both bigfile and smallfile tablespaces.
You can change the default tablespace type to BIGFILE or SMALLFILE.


How to sorting (Order By) LOB column data?
DBMS_LOB.SUBSTR is used to sort the LOB column


Can you give an example for the above?
Yes.

SQL> create table tab1(col1 number, col2 clob);
Table created.


SQL> insert into tab1 values (1,'this is a test record fro clob column');
1 row created.


SQL> insert into tab1 values (2, 'alangottai');
1 row created.


SQL>  insert into tab1 values (3,'mannargudi');
1 row created.


SQL> select * from tab1 order by col2;
select * from tab1 order by col2
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes


SQL> select col1, dbms_lob.substr(col2,50,1) from tab1 order by 2;
         col1 DBMS_LOB.SUBSTR(col2,50,1)
---------- ----------------------------------------------
         2 alangottai
         3 mannargudi
         1 this is a test record fro clob column
 

What are the uses of database trigger?

• Automatically generate derived column values
• Enforce complex security authorizations
• Prevent invalid transactions
• Enforce referential integrity across nodes in a distributed database
• Enforce complex business rules
• Provide transparent event logging
• Provide auditing
• Maintain synchronous table replicates
• Gather statistics on table access
• Modify table data when DML statements are issued against views

Difference between Row Level Trigger and Statement Trigger?

Row Level Trigger:
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.

Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. For example, Figure 22-2 illustrates a row trigger that uses the values of each row affected by the triggering statement.

Statement Triggers
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.

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:

Make a complex security check on the current time or working hours, weekday, or user
Generate a single audit record.

People who read this post also read :