Creating VIEW with Partitioning Table

Description

You can define a virtual table by using each partition of a partitioned table. Retrieving data from the virtual table created is possible, but data insert, delete and update operations are not allowed.

Example

The following is an example of creating the participant2 table partitioned based on the participating year, and creating and retrieving a virtual table with the participant2__p__before_2000 partition.

CREATE TABLE participant2 (host_year INT, nation CHAR(3), gold INT, silver INT, bronze INT)
PARTITION BY RANGE (host_year)
( PARTITION before_2000 VALUES LESS THAN (2000),

 PARTITION before_2008 VALUES LESS THAN (2008) );

INSERT INTO participant2 VALUES (1988, 'NZL', 3, 2, 8);

INSERT INTO participant2 VALUES (1988, 'CAN', 3, 2, 5);

INSERT INTO participant2 VALUES (1996, 'KOR', 7, 15, 5);

INSERT INTO participant2 VALUES (2000, 'RUS', 32, 28, 28);

INSERT INTO participant2 VALUES (2004, 'JPN', 16, 9, 12);

CREATE VIEW v_2000 AS

SELECT * FROM participant2__p__before_2000

WHERE host_year = 1988;

    host_year  nation                       gold       silver       bronze

==========================================================================

         1988  'NZL'                           3            2            8

         1988  'CAN'                           3            2            5