Databases Reference
In-Depth Information
Figure 7.6
Join of range partitioned tables.
In the first case, shown at the top of the figure all dates in the customer orders table are
joined with all dates in the products table. However, if the tables are range partitioned
by date, the compiler can immediately detect that only some months are interesting for
the join based on the predicates in the query (i.e., we assume a date predicate for orders
in March and April in this example), dramatically reducing the number of rows that
needs to be joined. In most cases this is not a big savings because the join columns
should be indexes. However, another extremely important observation here is the
impact of explicit clustering. By storing each range in its own internal tables, the data is
indirectly clustered by range. Therefore, in this join example, the March and April data
is guaranteed to be clustered (i.e., not intermingled with other data on the same storage
pages), resulting in a minimum amount of I/O for the query. This benefit is valuable
even when the join columns are properly indexed.
