Oracle Data Warehouse Tuning for 10g

6.3 Tuning Queries with Partitioning 153

Chapter 6

6.3 Tuning Queries with Partitioning

This section examines how queries behave when using Oracle Partitioning.

The four areas to be discussed are: understanding EXPLAIN PLAN com-

mand results, parallel processing as applied to partitioning, partition prun-

ing, and partition-wise joins.

6.3.1 Partitioning EXPLAIN PLANs

The EXPLAIN PLAN command produces an assessment of the way in

which a query could be executed by the optimizer,[1] generating results

into a table called PLAN_TABLE, more commonly known as the query

plan table. The four columns OTHER_TAG, PARTITION_START,

PARTITION_STOP, and PARTITION_ID will contain details of query

execution against a partitioned object.

To demonstrate, let’s begin by querying a nonpartitioned table. We can

check partitioning on tables and materialized views by executing the query

as shown in Figure 6.7.

Figure 6.7

Which tables and

materialized views

are partitioned?

154 6.3 Tuning Queries with Partitioning

Now query a nonpartitioned table as shown in Figure 6.8.

6.3.2 Partitioning and Parallel Processing

Parallel processing using partitions is very simple to understand. Partition-

ing can be used to split a table or materialized view into separate physical

chunks. Earlier in this chapter, we created a range partition table of the

SALE table called PSALE_YEAR. The partition key is the

TRANSACTION_DATE column. Figure 6.9 shows the same query state-

ment, but executed against the partitioned table PSALE_YEAR. The query

plan in Figure 6.9 is thus vastly different to that of Figure 6.8.

6.3.3 Partition Pruning

Partition pruning is a fancy name applied to a very simple process in which

a query reads a subset of partitions in a table, quite often a single partition,

as a result of a filter. In other words, the use and definition of partition

pruning is really no big deal. However, the difference in performance can be

phenomenal. Figure 6.10 shows partition pruning in action, using a filter to

reduce the query to reading a single partition.

Figure 6.11 shows the same query as in Figure 6.10, but using the paral-

lel hint once again.

Figure 6.8

A full table scan on

a nonpartitioned

table.

Figure 6.9

Parallel processing

against multiple

partitions.

6.3 Tuning Queries with Partitioning 155

Chapter 6

6.3.4 Partition-Wise Joins

A partition-wise join is a join across partitions where one or both tables in

the join are partitioned. This leads to the possibility that there is potential

for both full partition-wise joins and partial partition-wise joins. Before

examining some example partition-wise joins it is essential to have some-

thing for comparison. Figure 6.12 shows a join between the two nonparti-

tioned tables SALE and TIME.

Full Partition-Wise Joins

A full partition-wise join can be selected by the optimizer when both tables

are partitioned on the same matching column. Now, for the purposes of

demonstration, we will partition both the SALE and TIME tables on the

TIME_ID column:

CREATE TABLE psale_time

PARTITION BY RANGE(time_id)

Figure 6.10

Partition pruning

to read individual

partitions.

Figure 6.11

Partition pruning

to read individual

partitions.

156 6.3 Tuning Queries with Partitioning

(

PARTITION ptime_1 VALUES LESS THAN (51)

TABLESPACE pdata1

, PARTITION ptime_2 VALUES LESS THAN (101)

TABLESPACE pdata2

, PARTITION ptime_3 VALUES LESS THAN (MAXVALUE)

TABLESPACE pdata3

)

AS SELECT * FROM sale;

CREATE TABLE ptime

PARTITION BY RANGE(time_id)

(

PARTITION ptime_1 VALUES LESS THAN (51)

TABLESPACE pdata1

, PARTITION ptime_2 VALUES LESS THAN (101)

TABLESPACE pdata2

, PARTITION ptime_3 VALUES LESS THAN (MAXVALUE)

TABLESPACE pdata3

)

AS SELECT * FROM time;

And create indexes global to entire partitions:

CREATE INDEX gkx1_psale_time ON psale_time(time_id)

TABLESPACE indx;

CREATE INDEX gkx1_ptime ON ptime(time_id) TABLESPACE indx;

Figure 6.12

A join between two

nonpartitioned

tables.

6.3 Tuning Queries with Partitioning 157

Chapter 6

And generate statistics:

ANALYZE TABLE psale_time COMPUTE STATISTICS;

ANALYZE TABLE ptime COMPUTE STATISTICS;

The query plan in Figure 6.13 demonstrates a full partition-wise join,

because both tables have only their first partitions read. In other words, the

join is performed on the pruned partitions as opposed to the entire tables.

Partial Partition-Wise Joins

A partial partition-wise join can be selected by the optimizer when only one

of the tables in a join is partitioned on the joining column. The partitioned

table or materialized view is usually the fact entity. We can demonstrate this

by joining the PSALE_TIME partitioned sales fact table and the nonparti-

tioned TIME table, as shown in Figure 6.14.

Figure 6.13

A full partition-

wise join.

Figure 6.14

A partial partition-

wise join.