Databases Reference
In-Depth Information
To accomplish this, the compiler internally generates the additional dimension predi-
cates.
For example, given this query:
SELECT * FROM mdctable WHERE date > "1999/03/03" AND
date < "2000/01/15“
the compiler generates the following additional predicates:
month >= 199903 AND month <= 200001
that can be used as range predicates for a block index scan. This allows the RDBMS to
determine the list of blocks to be scanned, and the original predicates are applied to the
rows in those blocks.
8.6.2 Monotonicity for MDC Exploitation
Using expression-based columns clearly helps improve the density of data within the
MDC cells by reducing the number of cells in the table. How does the database's query
compiler actually manage to perform search operations using these expression-based
columns when the queries themselves do not reference them, and the expressions may
be quite complex? The query compiler performs this magical mapping for MDC by
exploiting qualities of monotonicity and the ability to detect a monotonic relationship
between the base column and the expression-based column. In the next few paragraphs
we'll describe what this means, and how it works.
Simply put, monotonic functions are ones that do not oscillate. For example, for a
function
f
(
x
), as
x
is increased the value of
f
(
x
) will not increase then decrease or decrease
then increase. This is stated more formally in the following two definitions:
•
Definition 1:
A function
f
(
x
) is monotonic increasing if, and only if, for any
two values
x
1 and
x
2, where
x
1 <
x
2, it is always true that
f
(
x
1) <
f
(
x
2).
•
Definition 2:
A function
f
(
x
) is monotonic nonincreasing if, and only if, for
any two values
x
1 and
x
2, where
x
1 <
x
2, it is always true that
f
(
x
1)
≥
f
(
x
2).
The test for monotonicity is performed if needed during the query rewrite phase
of an SQL statement while the query compiler is processing predicate expressions.
When a range predicate expression includes a column that has a corresponding
expression-based column, the monotonicity algorithm tests to see if a substitution is
possible. A substitution is only possible when the expression-based column is mono-
tonic compared to the base column. Without the ability to test for monotonicity of
an expression, expression-based columns would not really be useful for MDC. While
