Oracle Essentials Oracle Database 11g by Rick Greenwald Read Free Book Online Page B

release of Oracle9 i , AQ became part of Oracle Streams. Streams has three major components: log-based replication for data capture, queuing for data staging, and user-defined rules for data consumption. Since Oracle Database 10 g , Streams also includes support for change data capture and file transfer solutions.
    Streams is managed through Oracle Enterprise Manager and described in more detail
    in Chapter 13.
    Extraction, Transformation, and Loading
    Oracle Warehouse Builder (OWB) is a tool used in the design of target databases, especially data warehouses, and provides a metadata repository. However, it is best known as a GUI-based tool used in building source-to-target maps and for generating extraction, transformation, and loading (ETL) scripts. OWB leverages key embedded ETL features in the Oracle database first made available in Oracle9 i .
    OWB is included with the Oracle database as of Oracle Database 10 g Release 2. We
    describe it further in Chapter 10.
    Optionally, Oracle also offers a data integration tool, Oracle Data Integrator (ODI), that is not as Oracle database-centric as OWB (although the Oracle database can be a source and/or target). Oracle Data Integrator is based on a product and company that Oracle acquired named Sunopsis. In addition to providing ETL capabilities, ODI can generate code as web services for SOA deployment and is a key part of Oracle’s SOA integration strategy.
    Data Movement Features
    |
    19

    Database Performance Features
    Oracle includes several features specifically designed to boost performance in certain situations. We’ve divided the discussion in the following subsections into two categories: database parallelization and data warehousing.
    Database Parallelization
    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. 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)
    In addition to parallel query, many other Oracle features and capabilities are parallel-
    ized. Parallel operations are further identified and described in Chapter 7.
    Data Warehousing and Business Intelligence
    While parallel features improve the overall performance of the Oracle database, Oracle also has particular performance enhancements for business intelligence and data warehousing applications. We introduce many of them here, but see Chapter 10 for more detailed explanations of products and features specific to data warehousing and business intelligence.
    20
    |
    Chapter 1: Introducing Oracle

    Bitmap indexes
    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. 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 in Oracle 7.3. Performance gains are realized through the use of Cartesian product joins of dimension tables with a single join back to the large fact