Databases Reference
In-Depth Information
14.7.1 Oracle
Oracle offers many ways of partitioning data, including by range, by list, and by hash
value. Each partition can reside in its own tablespace. A tablespace in Oracle is a physi-
cal location for storing data.
Partitioning by range allows data to be stored in separate tablespaces, based on
specified value ranges for each partition. For example, we may want to separate histori-
cal data from recent data. If recent data changes frequently, then isolating the recent
data in a smaller partition can improve update performance. The following is the defini-
tion of a materialized view for labor costs by repair date, partitioned into historical and
recent data, with 2006 acting as the dividing point.
CREATE MATERIALIZED VIEW mv_labor_cost_by_repair_date
PARTITION BY RANGE(repair_year)
(PARTITION repair_to_2006 VALUES LESS THAN (2006)
TABLESPACE repairs_historical,
PARTITION repair_recent VALUES LESS THAN (MAXVALUE)
TABLESPACE repairs_recent)
AS
SELECT w.repair_date_id, repair_year, sum(labor_cost)
FROM warranty_claim w, repair_date r
WHERE w.repair_date_id=r.repair_date_id
GROUP BY w.repair_date_id, repair_year;
If the values of a column are discrete, but do not form natural ranges, the rows can
be assigned to partitions according to defined lists of values. Here is a definition for a
materialized view that partitions the rows into east, central, and west, based on value
lists.
CREATE MATERIALIZED VIEW mv_labor_cost_by_location
PARTITION BY LIST(region)
(PARTITION east VALUES('Northeast','Southeast')
TABLESPACE east,
PARTITION central VALUES('Midwest','Westcentral')
TABLESPACE central,
PARTITION west VALUES('West','Southwest')
TABLESPACE west)
AS
SELECT w.loc_id, region, sum(labor_cost)
FROM warranty_claim w, location l
WHERE w.loc_id=l.loc_id
GROUP BY w.loc_id, region;
