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.