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.
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 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.
Get Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
