Oracle Illustrated

Feb 21 2010   7:58AM GMT

Migrating from 9i to 11g – Partitioning enhancements

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Yet another exciting topic !!

Interval partitioning

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

Lets look at the current restriction first –

Oracle 9i

Range partitioning

CREATE TABLE range_test
(empid int,
joining_date DATE)
PARTITION BY RANGE(joining_date)
(
PARTITION join_jan2010 VALUES LESS THAN(TO_DATE(’02/01/2010′,’DD/MM/YYYY’)),
PARTITION join_feb2010 VALUES LESS THAN(TO_DATE(’03/01/2010′,’DD/MM/YYYY’)),
PARTITION join_mar2010 VALUES LESS THAN(TO_DATE(’04/01/2010′,’DD/MM/YYYY’)),
PARTITION join_apr2010 VALUES LESS THAN(TO_DATE(’05/01/2010′,’DD/MM/YYYY’))
);

INSERT INTO RANGE_TEST VALUES (1, sysdate)

–inserted on 11-Feb-09

*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

The current problem ORA-1440

CREATE TABLE range_test
(empid int,
joining_date DATE)
PARTITION BY RANGE(joining_date)
INTERVAL (NUMTOYMINTERVAL (1, ‘MONTH’))
(
PARTITION join_jan2010 VALUES LESS THAN(TO_DATE(’02/01/2010′,’DD/MM/YYYY’)),
PARTITION join_feb2010 VALUES LESS THAN(TO_DATE(’03/01/2010′,’DD/MM/YYYY’)),
PARTITION join_mar2010 VALUES LESS THAN(TO_DATE(’04/01/2010′,’DD/MM/YYYY’)),
PARTITION join_apr2010 VALUES LESS THAN(TO_DATE(’05/01/2010′,’DD/MM/YYYY’))
);

INSERT INTO RANGE_TEST VALUES (1, sysdate)

–inserted on 11-Feb-09

SQL> INSERT INTO RANGE_TEST VALUES (1, sysdate);

1 row created.

Elapsed: 00:00:00.39
SQL>

SELECT * FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME = ‘RANGE_TEST’

SYSTEM RANGE_TEST NO JOIN_JAN2010
SYSTEM RANGE_TEST NO JOIN_FEB2010
SYSTEM RANGE_TEST NO JOIN_MAR2010
SYSTEM RANGE_TEST NO JOIN_APR2010
SYSTEM RANGE_TEST NO SYS_P41 -> Automatically created
TO_DATE(‘ 2010-03-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)

On inserting into table.

Distributing partitioning across tablespace

INTERVAL (NUMTOYMINTERVAL (1, ‘MONTH’))
STORE IN (small_data, medium_data, large_data)

Alter script

ALTER 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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: