|
|
For many years, data warehouses have been populated using incremental “batch” loading techniques—
at predefined times. For example, a load into the data warehouse of new and modified transactions
within the last 24 hours from source systems might be scheduled at midnight. This approach is common
especially where large data volumes are to be loaded and complex data transformations occur during
the loading process. We’ll discuss Oracle facilities for such data loading and transformations in Chapter
25, “Data Loading and Management.” However, as business decision makers change their focus from
long-term and strategic planning to short-term and tactical judgments, the need for more current near
real-time data grows. The Oracle database provides a number of facilities for data movement that can
enable rapid and very frequent loading for near real-time analysis. These facilities are the subject of this
chapter. Specifically, we’ll focus on the Oracle Database 10g Data Pump, and Streams.
At first blush, the discussion of data movement may seem a little out-of-bounds for a programmers’
book. After all, data movement is usually more often a topic in guides for database administrators or
for data warehousing architects. We provide this discussion here since programmers play an important
role in the determination of data and loading requirements and the building of extraction, transformation,
and loading (ETL) scripts. This chapter and the next attempt to briefly explain what is possible and
what a programmer should know about these processes. We hope that these chapters enable you to
make better choices when selecting deployment strategies that meet your business’ needs while also
better leveraging your current technology infrastructure.
The first topic in this chapter describes export and import, a procedure not typically thought of as
high speed for data movement. For many years, Oracle provided an export/import facility. The
performance of this facility was greatly improved in a new version that first appeared in Oracle
Database 10g and is now sometimes called the Data Pump. We’ll describe how the new export/import
is used and the relative performance that might be expected compared to the previous
export/import.
In some situations, movement of entire tablespaces makes sense for bulk movement of data. Oracle’s
Transportable Tablespaces provide this capability. They are sometimes leveraged in combination
with Oracle partitioning to enable very fast addition of large incremental data loads to very large
data warehouses. For the first time in Oracle Database 10g, Transportable Tablespaces can be moved
from source databases to target databases on platforms that have different operating systems.
Where near real-time data is needed, a trickle feed (a data feed where a little bit of data is loaded on a
nearly continuous basis) can be preferable to traditional batch feeds. Oracle provides a Streams facility
that is based on Advanced Queues and log-based replication enabling such feeds from Oracle sources, or
from other sources (through the use of a Messaging Gateway).
Finally, since setting up and managing data movement is most often a job for a DBA, we’ll briefly describe
Enterprise Manager’s role in Export/Import and Streams setup and management for the benefit of our
audience of programmers. For more detailed explanations, we suggest you or your DBA also consult the
Oracle administration references.Export/Import and the “Data Pump”
Export and import utilities have long provided a means to move existing Oracle database data and
objects (such as tables in Oracle format) from one Oracle database to another. The export/import support
of movement of objects includes the meta data needed to re-create the tables’ indexes, constraints,
grants, and other attributes. At one time, the export and import provided the only set of utilities, aside
from writing code, for moving data among Oracle instances running on different operating systems.
Today there are other additional utilities that help enable this process (Transportable Tables Spaces and
Streams), as we’ll discuss in this chapter.
While useful for their heterogeneous support, export and import were not known for their performance
until the arrival of Oracle Database 10g. The Oracle Database 10g Data Pump version continues to support
the old export/import format such that data and objects from previous Oracle database versions can be
imported into the current version. But the Data Pump adds much higher performing export (expdp) and
import (impdp) utilities that support a new format for data and objects exchange. The Data Pump’s new
export utility is about 60 percent faster in single stream than the old export. It can be used in conjunction
with external tables (described in the next chapter) for high-speed database “unloads” to data files.
A command line example of using the empdp utility to create a dump file follows and would work with
the sample schema and data provided in a standard Oracle Database 10g installation provided the bi
user has proper privileges for exporting sh schema files:
expdp bi/bi TABLES=sh.supplementary_demographics DUMPFILE=d:\my_data\demotable.dmp
NOLOGFILE=Y
This command would export the table supplementary_demographics to the dump file
demotable.dmp in the d:\my_data directory.
In addition to command line access, the Data Pump export facility can also be accessed through Oracle
Enterprise Manager. Figure 24-1 shows the Enterprise Manager interface to the new Data Pump export.
The Data Pump import is about 15 to 20 times faster in single stream than the old import. From the
command line, impdp has a similar syntax for the import of Data Pump dumpfiles to the previously
described empdp. However, since the Data Pump uses an import format that is new and consistent with
Oracle Database 10g exports and there are many pre-Oracle Database 10g export files being created and
in use, Oracle Enterprise Manager must be capable of importing both current and previous export formats.
Hence, the new Enterprise Manager import interface (see Figure 24-2) allows you to specify whether the
import is an Oracle Database 10g version or a pre–Oracle Database 10g version.SapereOnLine |
|
|
|
|
|
