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. Parallel query became supported in Virtual Private Databases (VPD) with Oracle Database 10g. 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. Parallel operations are described in more detail in Chapter 6.
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 and business intelligence, 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.
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.
Since Oracle8i, materialized views have provided 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 releases featured the addition of ANSI standard OLAP SQL analytic functions for windowing, statistics, CUBE and ROLLUP, and much more. Oracle Database 10g further adds to this SQL library of analytic functions and statistics in the database.
Introduced in Oracle9i, OLAP services in the OLAP Option provide a Java OLAP API and are typically leveraged to build custom OLAP applications through the use of Oracle's JDeveloper product. The OLAP Option may also be accessed via SQL.
Since Oracle9i, popular data-mining algorithms have been embedded in the database through the Data Mining Option and are exposed through a Java data-mining API. Data mining applications are typically custom built using Oracle's JDeveloper with DM4J.
Oracle Standard Edition lacks important data warehousing features available in the Enterprise Edition, such as bitmap indexes and many parallelization features. Enterprise Edition is recommended for data warehousing projects.