- Oracle 11g has introduced a new partitioning feature, called Interval Partitioning. It is an enhancement to range partition.
- Automatically creates partition as new data is inserted
- Partitioning is a division of tables, indexes or IOT omtp smaller pieces – these smaller pieces can be managed and accessed independently
- Commonly known as the divide-and-conquer approach to managing large tables and indexes]
- Data loads will fail if one or more records cannot be matched to an existing partitioning
With range partition if partition is created on date – on inserting a particular date which is not part of the partioning, then the insert will fail. Interval partition is simply the data manageability by automatically creating the new partitions as needed. It is enabled in the table’s definition by defining one or more range partitions and including a specified interval.
1 row created.
On inserting into table.
SET INTERVAL (expression);
Restrictions:
1.Cannot be used on IOT
2.Partitioning key must be of must be either DATE / NUMBER datatype
3.domain index cannot be created on interval partitioned
4.Interval partitioning is not supported at the sub-partition level
****************************************************************************
Reference partitioning
It is a new partitioning option in 11g.
- Allows partitioning of two related tables to be based on the same tablespace
- If parent table is partitioned the child table can be made to inherit the partition properties of the parent
Oracle 11g
– Parent table
CREATE TABLE reference_test
(empid number(10) primary key,
joining_date DATE,
loc varchar2(3) not null)
partition by list (loc)
(
partition p_n values (‘Nor’),
partition p_s values (‘Sou’),
partition p_e values (‘Eas’),
partition p_w values (‘Wes’));
– Child table
create table reference_child
(empid number,
empname varchar2(50),
constraint fk_ref foreign key (empid) references reference_test
)
System partitioning
From Oracle 11g you can partition a table directly mapping it to a tablespace without specifying any partition key – SYSTEM partitioning. This is particularly useful for huge table look-ups (say, for validation or otherwise). This is controlled at application level – since, the control is moved from DBA to the application care should be taken to ensure that it is well managed.
Lets quickly compare a normal LIST partition with SYSTEM partition to see whether it has any real performance benefits!!
Oracle 9i
List Partition
CREATE TABLE test_list
(a varchar2(200),
language_spoken varchar2(200))
partition by list (language_spoken)
(partition l1 VALUES (‘English’, ‘French’) tablespace system,
partition l2 VALUES (‘Chinese’, ‘Malay’) tablespace sysaux,
partition l3 VALUES (‘German’, ‘Spanish’) tablespace users,
partition l4 values (default) tablespace users)
INSERT INTO TEST_LIST select SYS_GUID(), ‘English’ from dual connect by level <= 50;
INSERT INTO TEST_LIST select SYS_GUID(), ‘Chinese’ from dual connect by level <= 45;
INSERT INTO TEST_LIST select SYS_GUID(), ‘Malay’ from dual connect by level <= 30;
INSERT INTO TEST_LIST select SYS_GUID(), ‘German’ from dual connect by level <= 35;
INSERT INTO TEST_LIST select SYS_GUID(), ‘Spanish’ from dual connect by level <= 25;
INSERT INTO TEST_LIST select SYS_GUID(), ‘Hindi’ from dual connect by level <= 20;
SELECT table_name, partition_name, high_value, high_value_length, partition_position, tablespace_name from all_tab_partitions where table_name = ‘TEST_LIST’
TEST_LIST L1 ‘English’, ‘French’ 19 1 SYSTEM
TEST_LIST L2 ‘Chinese’, ‘Malay’ 18 2 SYSAUX
TEST_LIST L3 ‘German’, ‘Spanish’ 19 3 USERS
TEST_LIST L4 default 7 4 USERS
select * from test_list where language_spoken = ‘ENGLISH’
————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 204 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 1 | 204 | 3 (0)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | TEST_LIST | 1 | 204 | 3 (0)| 00:00:01 | 4 | 4 |
Predicate Information (identified by operation id):
—————————————————
2 – filter(“LANGUAGE_SPOKEN”=’ENGLISH’)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
745 recursive calls
0 db block gets
155 consistent gets
0 physical reads
0 redo size
342 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
0 rows processed
The above for NO DATA FOUND – 155 consistent gets
select * from test_list where language_spoken = ‘English’
—————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————
| 0 | SELECT STATEMENT | | 50 | 10200 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 50 | 10200 | 2 (0)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | TEST_LIST | 50 | 10200 | 2 (0)| 00:00:01 | 1 | 1 |
—————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – filter(“LANGUAGE_SPOKEN”=’English’)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
2788 bytes sent via SQL*Net to client
449 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed
Oracle 11g
SYSTEM PARTITION
CREATE TABLE test_system
(a varchar2(200),
language_spoken varchar2(200))
partition by system
(partition s1 tablespace system,
partition s2 tablespace sysaux,
partition s3 tablespace users)
INSERT INTO TEST_SYSTEM select SYS_GUID(), ‘English’ from dual connect by level <= 50;
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method
INSERT INTO TEST_SYSTEM partition (S1) select SYS_GUID(), ‘English’ from dual connect by level <= 50;
INSERT INTO TEST_SYSTEM partition (S2) select SYS_GUID(), ‘Chinese’ from dual connect by level <= 45;
INSERT INTO TEST_SYSTEM partition (S3) select SYS_GUID(), ‘Malay’ from dual connect by level <= 30;
INSERT INTO TEST_SYSTEM partition (S1) select SYS_GUID(), ‘German’ from dual connect by level <= 35;
INSERT INTO TEST_SYSTEM partition (S2) select SYS_GUID(), ‘Spanish’ from dual connect by level <= 25;
INSERT INTO TEST_SYSTEM partition (S3) select SYS_GUID(), ‘Hindi’ from dual connect by level <= 20;
SELECT table_name, partition_name, high_value, high_value_length, partition_position, tablespace_name from all_tab_partitions where table_name = ‘TEST_SYSTEM’
TEST_SYSTEM S1 0 1 SYSTEM
TEST_SYSTEM S2 0 2 SYSAUX
TEST_SYSTEM S3 0 3 USERS
select * from test_system where language_spoken = ‘ENGLISH’
—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 204 | 5 (0)| 00:00:01 | | |
| 1 | PARTITION SYSTEM ALL| | 1 | 204 | 5 (0)| 00:00:01 | 1 |3|* 2 | TABLE ACCESS FULL | TEST_SYSTEM | 1 | 204 | 5 (0)| 00:00:01| 1 | 3 |
—————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – filter(“LANGUAGE_SPOKEN”=’ENGLISH’)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
5 recursive calls
0 db block gets
37 consistent gets
0 physical reads
0 redo size
342 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
The above for NO DATA FOUND – 37consistent gets
select * from test_system where language_spoken = ‘English’
—————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————-
| 0 | SELECT STATEMENT | | 50 | 10200 | 5 (0)| 00:00:01 | | |
| 1 | PARTITION SYSTEM ALL| | 50 | 10200 | 5 (0)| 00:00:01 | 1 | 3 |
|* 2 | TABLE ACCESS FULL | TEST_SYSTEM | 50 | 10200 | 5 (0)| 00:00:
01 | 1 | 3 |
—————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – filter(“LANGUAGE_SPOKEN”=’English’)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
2788 bytes sent via SQL*Net to client
449 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed
*** In theory – SYSTEM partition is particularly useful for HUGE table look-ups.
****************************************************************************
Multiple column partitioning
Range and hash partitions can be partitioned on multiple columns.
Range partition on multiple columns -
CREATE TABLE test_range (a number, b varchar2(30), c date, d number)
partition by range (a, b, c)
(partition p1 values less than (1, ‘A’, ’01-Jan-2010′) tablespace system,
partition p2 values less than (2, ‘C’, ’01-Feb-2010′) tablespace users)
Table Created.
Hash partition on multiple columns -
CREATE TABLE test_hash (a number, b varchar2(30), c date, d number)
partition by hash (a, b, c)
partitions 4
store in (system, users)
Table Created.
****************************************************************************
Partition Compression
Individual partitions can be compressed in Oracle 11g. As like normal compression tables – DML operations / add or drop columns / partition level compress or no compress is possible.
Example – You can choose to compress OR uncompress certain partitions -
CREATE TABLE partition_compress (a number, b varchar2(30), c date, d number)
COMPRESS FOR ALL OPERATIONS
partition by range (a, b, c)
(partition p1 values less than (1, ‘A’, ’01-Jan-2010′) tablespace system,
partition p2 values less than (2, ‘C’, ’01-Feb-2010′) tablespace users NOCOMPRESS)
TABLE_NAME PARTITION_NAME COMPRESS
—————————— —————————— ——–
PARTITION_COMPRESS P1 ENABLED
PARTITION_COMPRESS P2 DISABLED
Uncategorized
