Preliminary understanding of Oracle Partitioning technology

Preliminary understanding of Oracle Partitioning technology

I. partition type

1. Range Partitioning)

Applicable to continuous/time-based data

2. Hash Partitioning)

Suitable for non-continuous/fixed data records

3. Combined partition Range-Hash

Range-List

4. List partition List Partitioning

Suitable for partitioning data in discontinuous Domains

More accurate control of Data Partition Storage

Applicable to location data

Ii. Partition Table maintenance

Preparations

SYS @ ORA11G> create tablespace sales_ts01

2 datafile '/u01/app/Oracle/oradata/ORA11G/sales_ts01_01.dbf' size 10 m;

 

Tablespace created.

 

SYS @ ORA11G> create tablespace sales_ts02

2 datafile '/u01/app/oracle/oradata/ORA11G/sales_ts02_01.dbf' size 10 m;

 

Tablespace created.

 

SYS @ ORA11G> create tablespace sales_ts03

2 datafile '/u01/app/oracle/oradata/ORA11G/sales_ts03_01.dbf' size 10 m;

 

Tablespace created.

 

SYS @ ORA11G> create tablespace sales_ts04

2 datafile '/u01/app/oracle/oradata/ORA11G/sales_ts04_01.dbf' size 10 m;

 

Tablespace created.

 

SYS @ ORA11G> create tablespace sales_ts05

2 datafile '/u01/app/oracle/oradata/ORA11G/sales_ts05_01.dbf' size 10 m;

 

Tablespace created.

 

SYS @ ORA11G> create tablespace sales_ts06

2 datafile '/u01/app/oracle/oradata/ORA11G/sales_ts06_01.dbf' size 10 m;

 

Tablespace created.

 

SYS @ ORA11G>

SYS @ ORA11G>

SYS @ ORA11G>

SYS @ ORA11G> conn tyger/tyger

Connected.

TYGER @ ORA11G>

TYGER @ ORA11G>

TYGER @ ORA11G> CREATE TABLE SALES

(PROD_ID NUMBER,

CUST_ID NUMBER,

TIME_ID DATE,

CHANNEL_ID NUMBER,

PROMO_ID NUMBER,

QUANTITY_SOLD NUMBER (10, 2 ),

AMOUNT_SOLD NUMBER (10, 2)

)

Partition by range (TIME_ID)

(PARTITION sales01 values less than ('01-Feb-2004 ') TABLESPACE SALES_TS01,

PARTITION sales02 values less than ('01-Mar-2004 ') TABLESPACE SALES_TS02,

PARTITION sales03 values less than ('01-Apr-2004 ') TABLESPACE SALES_TS03,

PARTITION sales04 values less than ('01-May-2004 ') TABLESPACE SALES_TS04,

PARTITION sales05 values less than ('01-Jun-2004 ') TABLESPACE SALES_TS05,

PARTITION sales06 values less than ('01-Jul-2004 ') TABLESPACE SALES_TS06

); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

 

Table created.

TYGER @ ORA11G> selectTABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, STATUS

2 from user_part_tables;

 

TABLE_NAME PARTITION SUBPARTIT STATUS

--------------------------------------------------------

SALES RANGE NONE VALID

TYGER @ ORA11G> col table_name for a20

TYGER @ ORA11G> col tablespace_name for a20

TYGER @ ORA11G> l

1 selectTABLE_NAME, PARTITION_NAME, TABLESPACE_NAME

2 * fromuser_tab_partitions

TYGER @ ORA11G>/

 

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

----------------------------------------------------------------------

SALES SALES01 SALES_TS01

SALES SALES02 SALES_TS02

SALES SALES03 SALES_TS03

SALES SALES04 SALES_TS04

SALES SALES05 SALES_TS05

SALES SALES06 SALES_TS06

 

6 rows selected.

 

 

Syntax:

ALTERTABLE ALTER INDEX

• Add partition-DROP PARTITION

• Coalesce partition-MODIFY PARTITION

• Drop partition-MODIFY DEFAULT ATTRIBUTES

• Exchange partition-MODIFY PARTITION COALESCE

• Merge partitions-REBUILD PARTITION

• Modify partition-RENAME PARTITION

• Modify default-SPLIT PARTITION

ATTRIBUTES-UNUSABLE

• MOVE PARTITION

• RENAME PARTITION

• SPLIT PARTITION

 

2.1 Delete table partitions

Alter table sales droppartition sales01;

TYGER @ ORA11G> alter table sales drop partitionsales01;

 

Table altered.

 

TYGER @ ORA11G> selectTABLE_NAME, PARTITION_NAME, TABLESPACE_NAME

2 from user_tab_partitions;

 

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

----------------------------------------------------------------------

SALES SALES02 SALES_TS02

SALES SALES03 SALES_TS03

SALES SALES04 SALES_TS04

SALES SALES05 SALES_TS05

SALES SALES06 SALES_TS06

2.2 Add Table partitions

The range of the added partition must be higher than that of the last partition of the current partition.

TYGER @ ORA11G> alter table sales add partitionsales01 values less than ('01-Feb-2004 ') tablespace sales_ts01;

Alter table sales add partition sales01 values lessthan ('01-Feb-2004 ') tablespace sales_ts01

*

ERROR at line 1:

ORA-14074: partition bound mustcollate higher than that of the last partition

 

 

TYGER @ ORA11G> alter table sales add partitionsales01

2 values less than ('01-Aug-2004 ') tablespacesales_ts01;

 

Table altered.

 

TYGER @ ORA11G> selecttable_name, partition_name, tablespace_name

2 from user_tab_partitions;

 

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

----------------------------------------------------------------------

SALES SALES02 SALES_TS02

SALES SALES03 SALES_TS03

SALES SALES04 SALES_TS04

SALES SALES05 SALES_TS05

SALES SALES06 SALES_TS06

SALES SALES01 SALES_TS01

 

 

2.3 merge partitions

· Partitions must be adjacent range partitions.

· Inherit the largest range Boundary

TYGER @ ORA11G> alter table sales

2 merge partitions sales06, sales01 partition sales07;

 

Table altered.

 

TYGER @ ORA11G> select table_name, partition_name, tablespace_name

2 from user_tab_partitions;

 

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

----------------------------------------------------------------------

SALES SALES07 USERS

SALES SALES02 SALES_TS02

SALES SALES03 SALES_TS03

SALES SALES04 SALES_TS04

SALES SALES05 SALES_TS05

 

2.4 Move Table partitions

· Move partition data to another tablespace

· Reorganizing data to reduce fragmentation

· Changing physical attributes

TYGER @ ORA11G> alter table sales move partitionsales01

2 tablespace sales_ts_move;

 

2.5 split table partitions

Sharding table partitions-a partition becomes too large, resulting in too long backup, recovery, and partition performance operations and re-allocating I/O loads.

TYGER @ ORA11G> alter table sales split partition sales07

2 at ('01-Jul-2004 ') // By which time point split

3 into (partition sales01 tablespacesales_ts01,

4 partition sales06 tablespacesales_ts06 );

 

Table altered.

 

TYGER @ ORA11G> selecttable_name, partition_name, tablespace_name

2 from user_tab_partitions;

 

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

----------------------------------------------------------------------

SALES SALES02 SALES_TS02

SALES SALES03 SALES_TS03

SALES SALES04 SALES_TS04

SALES SALES05 SALES_TS05

SALES SALES06 SALES_TS06

SALES SALES01 SALES_TS01

 

6 rows selected.

For more details, please continue to read the highlights on the next page:

1 2 Next Page