Databases Reference
In-Depth Information
There's more...
Partitioning a table grants some advantages. When Oracle executes a query, there is an
operation called partition pruning, which avoids having the database consider partitions
not involved in the request. For example, if we execute the following query:
SELECT * FROM SALES_RP
WHERE TIME_ID < TO_DATE('19990630','YYYYMMDD')
AND CUST_ID = 1511;
Oracle will automatically use only the
SALES_BEFORE_2000
partition to retrieve the data
needed. We could also put the tablespaces in which we store other partitions offline, without
affecting the execution of the query.
Splitting the table into more partitions, lets us operate even on single partitions. For example,
we can move the table to other tablespaces one partition at a time.
We can simply drop a partition instead of deleting rows related to certain partition key values.
For example, when we only want to store data of the last period (a quarter, a year, a day)
online, we can partition the table and drop the old partitions, instead of executing a long
operation, such as
DELETE
…
WHERE
TIME
<
X
.
Partitioning is often used in OLAP systems, because it allows us to perform parallel DML
operations, such as massive UPDATEs and INSERTs with a degree of parallelism equal to the
number of partitions of the table.
In OLTP environments, there isn't a great performance gain—and if we implement a bad
partitioning scheme we can have worse performance than without—but we will have easier
maintenance tasks and increased availability, both as important as performance gains.
List partitioning
We have partitioned a table based on the range of values, which is the most common way to
apply partitioning.
We can also partition a table based on a list of values. For example, the
COUNTRY
for a table
of customers or the
CATEGORY
for a products table.
To create a list partitioned table, we can execute a statement similar to the following, in which
we will partition the
SALES_LP
table based on
CHANNEL_ID
values:
CREATE TABLE SALES_LP (
PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
CHANNEL_ID NUMBER NOT NULL,
PROMO_ID NUMBER NOT NULL,
