Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

1.8 Performance Features

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.

1.8.1 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.

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.

1.8.1.1 Availability

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.

1.8.2 Data Warehousing and Business Intelligence

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.

1.8.2.1 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. 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.

1.8.2.2 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.

1.8.2.3 Materialized views

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.

1.8.2.4 Analytic functions

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.

1.8.2.5 OLAP Option

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.

1.8.2.6 Data Mining Option

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.

1.8.2.7 Availability

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.