Pro Oracle Database 11g Administration (61 page)

CREATE TABLE inv_et

ORGANIZATION EXTERNAL (

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY dp

ACCESS PARAMETERS (COMPRESSION ENABLED)

LOCATION ('inv1.dmp')

)

AS SELECT * FROM inv;

You should see quite good compression ratios when using this option. In my testing, the output dump file was 10 to 20 times smaller when compressed. Your mileage may vary, depending on the type data being compressed.

Encrypting a Dump File

You can also create an encrypted dump file using an external table. This example uses the ENCRYPTION

option of the ACCESS PARAMETERS clause:

356

CHAPTER 14 ■ EXTERNAL TABLES

CREATE TABLE inv_et

ORGANIZATION EXTERNAL (

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY dp

ACCESS PARAMETERS

(ENCRYPTION ENABLED)

LOCATION ('inv1.dmp')

)

AS SELECT * FROM inv;

For this example to work, you need to have a security wallet in place and open for your database.

■
Note
Using encryption requires an additional license from Oracle. Contact Oracle for details on using the Advanced Security Option.

You enable compression and encryption via the ACCESS PARAMETERS clause. Table 14–2 contains a listing of all access parameters available with the ORACLE_DATAPUMP access driver.

Table 14–2.
Parameters of the ORACLE_DATAPUMP Access Driver
Access Parameter

Description

COMPRESSION

Compresses the dump file. DISABLED is the default

value.

ENCRYPTION

Encrypts the dump file. DISABLED is the default

value.

NOLOGFILE

Suppresses the generation of a log file.

LOGFILE=[directory_object:]logfile_name

Allows you to name a log file.

VERSION

Specifies the minimum version of Oracle that can

read the dump file.

Preprocessing an External Table

Oracle added (in 10.2.0.5 and higher) the ability to preprocess the file on which an external table is based. For example, you can instruct the CREATE TABLE...ORGANIZATION EXTERNAL statement to uncompress a compressed operating-system file before it’s processed.

Here’s a simple example to illustrate this concept. First, create a directory object that contains the location of the compressed file:

SQL> create or replace directory data_dir as '/orahome/oracle/dk/et'; 357

CHAPTER 14 ■ EXTERNAL TABLES

You also need to create a directory object that contains the location of the script that will perform the preprocessing on the data file. In this example, the directory is /bin: SQL> create or replace directory exe_dir as '/bin';

This example compressed the operating-system flat file with the gzip utility. The compressed file is named exa.csv.gz. You instruct the CREATE TABLE...ORGANIZATION EXTERNAL statement to preprocess the compressed file by specifying the PREPROCESSOR clause. Because the CSV file was compressed by the gzip utility, it can be uncompressed by the corresponding uncompress utility, gunzip. Look carefully for the PREPROCESSOR clause in the following listing; it’s nested under ACCESS PARAMETERS: create table exadata_et(

machine_count NUMBER

,hide_flag NUMBER

,oracle NUMBER

,ship_date DATE

,rack_type VARCHAR2(32)

)

organization external (

type oracle_loader

default directory data_dir

access parameters

(

records delimited by newline

preprocessor exe_dir: 'gunzip'

fields terminated by '|'

missing field values are null

(exa_id

,machine_count

,hide_flag

,oracle

,ship_date char date_format date mask "mm/dd/yyyy"

,rack_type)

)

location ('ex.csv.gz')

)

reject limit unlimited;

The advantage of preprocessing the dump file is that it saves you the step of having to first uncompress the file.

■
Note
Oracle doesn’t let you use the PREPROCESSOR clause in databases that have the Database Vault feature installed.

358

CHAPTER 14 ■ EXTERNAL TABLES

Summary

I used to use SQL*Loader for all types of data-loading tasks. In the past few years, I’ve become an external-table convert. Almost anything you can do with SQL*Loader, you can also do with an external table. The external-table approach is advantageous because there are fewer moving parts and the interface is SQL*Plus. Most DBAs and developers find SQL*Plus easier to use than a SQL*Loader control file.

You can easily use an external table to enable SQL*Plus access to operating-system flat files. You simply have to define the structure of the flat file in your CREATE TABLE...ORGANIZATION EXTERNAL

statement. After the external table is created, you can select directly from the flat file as if it were a database table. You can select from an external table, but you can’t insert, update, or delete.

When you create an external table, if required, you can then create regular database tables by using CREATE TABLE AS SELECT from the external table. Doing so provides a fast and effective way to load data stored in external operating-system files.

The external-table feature also allows you to select data from a table and write it to a binary dump file. The external table CREATE TABLE...ORGANIZATION EXTERNAL statement defines which tables and columns are used to unload the data. A dump file created in this manner is platform independent, meaning you can copy it to a server using a different operating system and seamlessly load the data.

Additionally, the dump file can be encrypted and compressed for secure and efficient transportation.

You can also use parallel features to reduce the amount of time it takes to create the dump file.

The next chapter deals with materialized views. These database objects provide you with a flexible, maintainable, and scalable mechanism for aggregating and replicating data.

359

CHAPTER 15 ■ MATERIALIZED VIEWS

After the table is created, you put in a daily process to delete from it at 8:00 pm and completely refresh it:

-- Step 1 delete from daily aggregated sales data:

delete from daily_sales;

--

-- Step 2 repopulate table with a snapshot of aggregated sales table: insert into daily_sales

select

sum(sales_amt)

,sales_dtt

from sales

group by sales_dtt;

You inform the users that they can have subsecond query results by selecting from DAILY_SALES

(instead of running the query that directly selects and aggregates from the master SALES table). This process roughly describes a complete refresh process.

Oracle’s MV technology automates and greatly enhances this process. This chapter describes the procedure for implementing both basic and complex MV features. After reading this chapter and working through the examples, you should be able to create MVs to replicate and aggregate data in a wide variety of situations.

Before delving into the details of creating MVs, it’s useful to cover basic terminology and helpful data-dictionary views related to MVs. The next two subsections briefly describe the various MV features and the many data-dictionary views that contain MV metadata.

■
Note
This chapter doesn’t cover topics like multimaster replication and updateable MVs. See the Oracle Advanced Replication Guide (available on Oracle’s OTN web site) for more details on those topics.

362

CHAPTER 15 ■ MATERIALIZED VIEWS

Materialized View Terminology

A great many terms relate to refreshing MVs. You should be familiar with these terms before delving into how to implement the features. Table 15–1 describes the various terms relevant to MVs.

Table 15–1.
Descriptions of MV Terminology

Term Meaning

Materialized view

Database object used for replicating data and improving

performance.

Materialized view SQL statement

SQL query that defines what data is stored in the underlying

MV base table.

Materialized view underlying table

Database table that has the same name as the MV that stores

the result of the MV SQL query.

Master (or base) table

Table that an MV references in its FROM clause of the MV SQL

statement.

Complete refresh

Process in which an MV is deleted from and completely

refreshed with an MV SQL statement.

Fast refresh

Process during which only DML changes that have occurred

since the last refresh are applied to an MV.

Materialized view log

Database object that tracks DML changes to the MV base

table. An MV log is required for fast refreshes. It can be based

on the primary key, ROWID, or object ID.

Simple MV

MV based on a simple query that can be fast-refreshed.

Complex MV

MV based on a complex query that isn’t eligible for fast

refresh.

Build mode

Mode that specifies whether the MV should be immediately

populated or deferred.

Refresh mode

Mode that specifies whether the MV should be refreshed on

demand, on commit, or never.

Refresh method

Option that specifies whether the MV refresh should be

complete or fast.

Query rewrite

Feature that allows the optimizer to choose to use MVs

(instead of base tables) to fulfill the requirements of a query

(even though the query doesn’t directly reference the MVs).

Local MV

MV that resides in the same database as the base table(s).

Remote MV

MV that resides in a separate database from the base table(s).

Refresh group

Set of MVs refreshed at the same consistent transactional

point.

363

CHAPTER 15 ■ MATERIALIZED VIEWS

Refer back to Table 15–1 as you read the rest of this chapter. These terms and concepts are explained and expounded on in subsequent sections.

Referencing Useful Views

When you’re working with MVs, sometimes it’s hard to remember which data-dictionary view to query under what circumstance. A wide variety of data-dictionary views are available. Table 15–2 contains a description of the MV-related data-dictionary views. Examples of using these views are shown throughout this chapter where appropriate. These views are invaluable for troubleshooting, diagnosing issues, and understanding your MV environment.

Table 15–2.
Materialized View Data-Dictionary View Definitions
Data-Dictionary View

Description

DBA/ALL/USER_MVIEWS

Information about MVs such as owner, base query,

last refresh time, and so on.

DBA/ALL/USER_MVIEW_REFRESH_TIMES

MV last refresh times, MV names, master table, and

master owner.

DBA/ALL/USER_REGISTERED_MVIEWS

All registered MVs. Helps identify which MVs are

using which MV logs.

DBA/ALL/USER_MVIEW_LOGS

MV log information.

DBA/ALL/USER_BASE_TABLE_MVIEWS

Base-table names and last refresh dates for tables

that have MV logs.

DBA/ALL/USER_MVIEW_AGGREGATES

Aggregate functions that appear in SELECT clauses for

MVs.

DBA/ALL/USER_MVIEW_ANALYSIS

Information about MVs. Oracle recommends that

you use DBA/ALL/USER_MVIEWS instead of these views.

DBA/ALL/USER_MVIEW_COMMENTS

Any comments associated with MVs.

DBA/ALL/USER_MVIEW_DETAIL_PARTITION

Partition and freshness information.

DBA/ALL/USER_MVIEW_DETAIL_SUBPARTITION

Subpartition and freshness information.

DBA/ALL/USER_MVIEW_DETAIL_RELATIONS

Local tables and MVs that an MV is dependent on.

DBA/ALL/USER_MVIEW_JOINS

Joins between two columns in the WHERE clause of an

MV definition.

DBA/ALL/USER_MVIEW_KEYS

Columns or expressions in the SELECT clause of an

MV definition.

364

CHAPTER 15 ■ MATERIALIZED VIEWS

Data-Dictionary View

Description

DBA/ALL/USER_TUNE_MVIEW

Result of executing the DBMS_ADVISOR.TUNE_MVIEW

procedure.

V$MVREFRESH

Information about MVs currently being refreshed.

DBA/ALL/USER_REFRESH

Details about MV refresh groups.

DBA_RGROUP

Information about MV refresh groups.

DBA_RCHILD

Children in an MV refresh group.

Creating Basic Materialized Views

This section covers how to create an MV. The two most common configurations used are as follows:

• Creating complete-refresh MVs that are refreshed on demand

• Creating fast-refresh MVs that are refreshed on demand

It’s important to understand these basic configurations. They lay the foundation for everything else you do with an MV. Therefore, this section starts with these basic configurations. Later, the section covers more advanced configurations. Make sure you understand the material in the following two sections before you move on to advanced MV topics.