Oracle9i

1
Oracle9i VLDB

• Montse Collados Polidura, IT/DB
• Database Workshop - July 2001

2
VLDB - Features

• Partitioning
• Materialized Views
• Parallelism

3
PARTITIONINGDIVIDE 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.

4
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

5
How does it work?
Partitioned Table
Select column1, column2 from Table where key
DEC2001
6
Types of Partitioning
List
Hash
Range
7
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)
8
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)

9
List Partitioning
CREATE TABLE sales_list (salesman_id NUMBER(5),
salesman_name VARCHAR2(30), sales_state VARCHAR2
(20), sales_amount NUMBER(10),
sales_date DATE) PARTITION BY
LIST(sales_state) ( PARTITION sales_west VALUES
IN('California', 'Hawaii'), PARTITION sales_east
VALUES IN ('New York', 'Virginia',
'Florida'), PARTITION sales_central VALUES
IN('Texas', 'Illinois'), )
10
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)
11
Partitioned Indexes

• Local indexes, automatically linked to a tables
  partitioning method.
• Global indexes, partitioned independently.

12
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

13
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.

14
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

15
Conclusions

• Divide Conquer to gain performance and
  manageability.
• Materialized Views to precompute and distribute
  data.
• Parallel Execution to run faster.