Databases Reference
In-Depth Information
In Oracle Database 12
c
, during the compilation of a SQL statement, the optimizer au‐
tomatically decides whether available statistics can generate a good execution plan. If
statistics are missing or out of date, dynamic sampling of tables automatically occurs to
generate new statistics. Query performance is further improved by adaptive execution
plans that make corrections during execution.
How does the optimizer handle a query against a star schema? First, it finds a sales
transactions fact table (shown in
Figure 10-2
) with a lot more entries than the sur‐
rounding dimension tables. This is the clue that a star schema exists. Early databases
would have tried to join each of the dimension tables to the fact table, one at a time.
Because the fact table is usually very large, using the fact table in multiple joins takes a
lot of computer resources.
Cartesian product joins were added to Oracle7 to first join the dimension tables, with
a subsequent single join back to the fact table in the final step. This technique works
relatively well when there are not many dimension tables (typically six or fewer, as a
rule of thumb, to keep the Cartesian product small) and when data is relatively well
populated.
In some situations, there are a fairly large number of dimension tables or the data in the
fact table is sparse. For joining such tables, a parallel bitmap star join may be selected
by the optimizer.
In earlier releases of the Oracle Database, DBAs had to set initialization parameters (e.g.,
STAR_TRANSFORMATION) and gather statistics, enabling the optimizer to recognize
the best methods for solving such queries. Today, needed parameters are preset upon
installation and statistics are automatically gathered by the Oracle Database.
Bitmap Indexes and Parallelism
Bitmap indexes
, described in
Chapter 4
, were first introduced in Oracle7 to speed up
the type of data retrieval and joins in data warehousing queries. Bitmap indexes in Oracle
are typically considered for columns in which the data has low cardinality.
Cardinali‐
ty
is the number of different values in an index divided by the number of rows. There
are various opinions about what low cardinality actually is. Some consider cardinality
as high as 10% to be low, but remember that if a table has a million rows, that “low”
cardinality would mean 100,000 different values in a column!
In a bitmap index, a value of 1 in the index indicates that a value is present in a particular
row, and 0 indicates that the value is not present. A bitmap is built for each of the values
in the indexed columns. Because computers are built on a concept of 1s and 0s, this
technique can greatly speed up data retrieval. In addition, join operations such as AND
become a simple addition operation across multiple bitmaps. A side benefit is that bit‐
map indexes can provide considerable storage savings.
