Transcript Document

CERN/IT/DB
Oracle9i & VLDB
Montse Collados Polidura, IT/DB
Database Workshop - July 2001
CERN/IT/DB
VLDB - Features
Partitioning
Materialized Views
Parallelism
CERN/IT/DB
PARTITIONING
DIVIDE AND CONQUER
 Tables and indexes decomposed into smaller and
more manageable pieces called partitions.
 Same logical attributes, different physical attributes
 Advantages:
 Data management operations at the partition : data loads,
index creation, backup/recovery, etc.
 Improves query performance (partition pruning)
 Possibility of concurrent maintenance operations on different
partitions of the same table/index.
 Partitioning can be implemented without requiring any
modifications to your applications.
CERN/IT/DB
Operations on partitions











Add a partition to an existing table
Split an existing partition into two partitions
Drop a partition
Load data into one table partition
Export data from one table partition
Import a table partition
Move a table partition - move it to another tablespace
Rename a partition
Truncate a table partition
Rebuild an index partition
Modify a partition - change the physical attributes of a
partition
CERN/IT/DB
How does it work?
Partitioned Table
Insert into Table values (‘A’,’B’, ‘3-MAR-2001’, ‘C’)
JAN2001
FEB2001
MAR2001
DEC2001
…
Select column1, column2
from Table
where key = DEC2001;
CERN/IT/DB
Types of Partitioning
CERN/IT/DB
Range Partitioning
CREATE TABLE event_data
(event_id NUMBER(10),
event_data BLOB)
PARTITION BY RANGE(event_id) (
PARTITION event_100000 VALUES LESS THAN(100000)
TABLESPACE tsa,
PARTITION event_200000 VALUES LESS THAN(200000)
TABLESPACE tsb,
PARTITION event_300000 VALUES LESS THAN(300000)
TABLESPACE tsc);
CERN/IT/DB
Hash Partitioning
CREATE TABLE event_data
(event_id
NUMBER(10),
event_data
BLOB)
PARTITION BY HASH(event_id)
PARTITIONS 4
STORE IN (data1, data2, data3, data4);
CERN/IT/DB
Composite Partitioning
CREATE TABLE event_data
(event_id
NUMBER(10),
event_data
BLOB)
PARTITION BY RANGE(event_id)
SUBPARTITION BY HASH(event_id)
SUBPARTITIONS 4
PARTITION event_100000 VALUES LESS THAN(100000)
( SUBPARTITION event_100000_1 TABLESPACE data1,
SUBPARTITION event_100000_2 TABLESPACE data2,
SUBPARTITION event_100000_3 TABLESPACE data3,
SUBPARTITION event_100000_4 TABLESPACE data4),
PARTITION event_200000 VALUES LESS THAN(200000),
( SUBPARTITION event_200000_1 TABLESPACE data1,
SUBPARTITION event_200000_2 TABLESPACE data2,
SUBPARTITION event_200000_3 TABLESPACE data3,
SUBPARTITION event_200000_4 TABLESPACE data4);
CERN/IT/DB
Partitioned Indexes
 Local indexes, automatically linked to a
table’s partitioning method.
 Global indexes, partitioned
independently.
CERN/IT/DB
Materialized Views
Materialized views, also called snapshots, are
schema objects that can be used to
summarize, precompute, replicate, and
distribute data.
Refreshed upon committing, on a recurring
timed basis, or on demand.
Summary table: Query Rewrite mechanism.
Materialized views for distributed computing
CERN/IT/DB
Parallelism
 Server processes that perform parallel
operations.
 Degree of Parallelism (DOP) configured
at database startup
(PARALLEL_MIN_SERVERS,
PARALLEL_MAX_SERVERS) and at the
SQL statement level.
CERN/IT/DB
Parallelism
Parallel execution improves processing:




Queries requiring large table scans and joins
Creation of large indexes
Partitioned index scans
Bulk inserts, updates, and deletes
 Hardware considerations
CERN/IT/DB
Conclusions
 Divide & Conquer to gain
performance and manageability.
 Materialized Views to precompute
and distribute data.
 Parallel Execution to run faster.