Moving data from one Oracle database to another is often a requirement when using distributed databases, or when a user wants to implement multiple copies of the same database in multiple locations to reduce network traffic or increase data availability. You can export data and data dictionaries (metadata) from one database and import them into another. Oracle also offers many other advanced features in this category, including replication, transportable tablespaces, and Advanced Queuing.
This section describes the technology used to move data from one Oracle database to another automatically.
You can use advanced replication in multimaster systems in which any of the databases involved can be updated and conflict-resolution features are needed to resolve inconsistencies in the data. Because there is more than one master database, the same data may be updated on multiple systems at the same time. Conflict resolution is necessary to determine the "true" version of the data. Oracle's advanced replication includes a number of conflict-resolution scenarios and also allows programmers to write their own. We cover replication in more detail in Chapter 12.
Transportable tablespaces were introduced in Oracle8i. Instead of using the export/import process, which dumps data and the structures that contain it into an intermediate file for loading, you simply put the tablespaces in read-only mode, move or copy them from one database to another, and mount them. You must export the data dictionary (metadata) for the tablespace from the source and import it at the target. This feature can save a lot of time during maintenance, because it simplifies the process.
Advanced Queuing (AQ), introduced in Oracle8, provides the means to asynchronously send messages from one Oracle database to another. Because messages are stored in a queue in the database and sent asynchronously when the connection is made, the amount of overhead and network traffic is much lower than it would be using traditional guaranteed delivery through the two-phase commit protocol between source and target. By storing the messages in the database, AQ provides a solution with greater recoverability than other queuing solutions that store messages in filesystems.
Oracle messaging adds the capability to develop and deploy a content-based publish and subscribe solution using a rules engine to determine relevant subscribing applications. As new content is published to a subscriber list, the rules on the list determine which subscribers should receive the content. This approach means that a single list can efficiently serve the needs of different subscriber communities.
Oracle9i AQ adds XML support and Oracle Internet Directory (OID) integration. This technology is leveraged in Oracle Application Interconnect (OAI), which includes adapters to non-Oracle applications, messaging products, and databases.
Although basic replication has been included with both Oracle Standard Edition and Enterprise Edition, advanced features such as advanced replication, transportable tablespaces, and Advanced Queuing have typically required Enterprise Edition.
Oracle includes several features specifically designed to boost performance in certain situations. We've divided the discussion in this section into two categories: database parallelization and data warehousing.
Database tasks implemented in parallel speed up querying, tuning, and maintenance of the database. By breaking up a single task into smaller tasks and assigning each subtask to an independent process, you can dramatically improve the performance of certain types of database operations.
Parallel query features became a standard part of Enterprise Edition beginning with Oracle 7.3. Examples of query features implemented in parallel include:
- Table scans
- Nested loops
- Sort merge joins
- GROUP BYs
- NOT IN subqueries (anti-joins)
- User-defined functions
- Index scans
- Select distinct UNION and UNION ALL
- Hash joins
- ORDER BY and aggregation
- Bitmap star joins
- Partition-wise joins
- Stored procedures (PL/SQL, Java, external routines)
When you're using Oracle, by default the degree of parallelism for any operation is set to twice the number of CPUs. You can adjust this degree automatically for each subsequent query based on the system load. You can also generate statistics for the cost-based optimizer in parallel.
You can perform maintenance functions such as loading (via SQL*Loader), backups, and index builds in parallel in Oracle Enterprise Edition. Oracle Partitioning for the Enterprise Edition enables additional parallel Data Manipulation Language (DML) inserts, updates, and deletes as well as index scans.
The parallel features discussed in the previous section improve the overall performance of the Oracle database. Oracle has also added some performance enhancements that specifically apply to data warehousing applications. For detailed explanations of these and complementary products and features related to data warehousing, see Chapter 9.
Oracle added support for stored bitmap indexes to Oracle 7.3 to provide a fast way of selecting and retrieving certain types of data. Bitmap indexes typically work best for columns that have few different values relative to the overall number of rows in a table.
Rather than storing the actual value, a bitmap index uses an individual bit for each potential value with the bit either "on" (set to 1) to indicate that the row contains the value or "off" (set to 0) to indicate that the row does not contain the value. This storage mechanism can also provide performance improvements for the types of joins typically used in data warehousing. Bitmap indexes are described in more detail in Chapter 4.
Star query optimization
Typical data warehousing queries occur against a large fact table with foreign keys to much smaller dimension tables. Oracle added an optimization for this type of star query to Oracle 7.3. (See Figure 9-2 for an illustration of a typical star schema.) Performance gains are realized through the use of Cartesian product joins of dimension tables with a single join back to the large fact table. Oracle8 introduced a further mechanism called a parallel bitmap star join, which uses bitmap indexes on the foreign keys to the dimension tables to speed star joins involving a large number of dimension tables.
In Oracle, materialized views provide another means of achieving a significant speed-up of query performance. Summary-level information derived from a fact table and grouped along dimension values is stored as a materialized view. Queries that can use this view are directed to the view, transparently to the user and the SQL they submit.
A growing trend in Oracle and other systems is the movement of some functions from decision-support user tools into the database. Oracle8i and Oracle9i feature the addition of ANSI standard OLAP SQL analytic functions for windowing, statistics, CUBE and ROLLUP, and more.
Oracle9i Advanced Analytic Services
Oracle9i Advanced Analytic Services are a combination of what used to be called OLAP Services and Data Mining. The OLAP services provide a Java OLAP API and are typically leveraged to build custom OLAP applications through the use of Oracle's JDeveloper product. Oracle9i Advanced Analytic Services in the database also provide predictive OLAP functions and a multidimensional cache for doing the same kinds of analysis previously possible in Oracle's Express Server.
The Oracle9i database engine also includes data-mining algorithms that are exposed through a Java data-mining API.
Oracle Standard Edition lacks many important data warehousing features available in the Enterprise Edition, such as bitmap indexes and materialized views. Hence, use of Enterprise Edition is recommended for data warehousing projects.
Page 4 of 5