A Round trip with Oracle Particioning


We are going to make a trip arround partiotioning in Oracle, why use partitioning?, types, despartitioning, how check some info about particioned tables…allways usefull tips for use day to day at work.

First of all, you need licence in order to use partitioning, ok, you dont have license dont try unistall(you cant) the option, no need, db core use partitioning,
that no need a license.

You can run the DBMS_FEATURE_USAGE_REPORT package and you will clearly see that there is a distinction between partitioning used by the database and partitioning used by the customer.

There’s no way to uninstall partitioning option from EE installation. So as you can’t uninstall Diagnostic Pack features from EE and Grid Control installation.

Only solution reinstall oracle software. I have “not yet” heard of oracle actually trying to charge someone for partitioning in an enterprise edition install where customer has not partitioned any of their tables, so no worry about it.

Lets check our database, what tables are particioned? simple query:

select owner, table_name, partitioning_type
from dba_part_tables;

if you want check partititons, simple query:

select table_name, partition_name, high_value
from dba_tab_partitions
order by partition_position, table_name;

and size? set segment name you want check check size:

select sum(bytes)/1024/1024 Table_Allocation_MB from dba_segments
where segment_type in (‘TABLE PARTITION’) and
(segment_name=’WRITE_HERE_SEGMENT_NAME’ or segment_name in
(select index_name from user_indexes where table_name=’WRITE_HERE_SEGMENT_NAME’));

What you need to know to implement partitioning, first of all, why you need partitioning? reasons to implement this could be the high volumen of data in table, to speed up access to table, to manage table with partitions in a easy
way.

So you need partiotioning, What are the criteria for partitioning ? two possibilities, for performance or Administration, from there you can choose partitioning type, depends on your needs: Range,hash, compound or list. From here the particioned key will come determie fro the aplication needs.

Partitioning by RANGE:
A table that is partitioned by range is partitioned in such a
way that each partition contains rows for which the partitioning expression value
lies within a given range. Used when there are logical ranges of data. Possible
usage: dates, part numbers, and serial numbers.

Example of particioning by range, a sales table, particioned by month, each month in diferent tablespace.

CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE(’01-APR-2006′,’dd-MON-yyyy’)) TABLESPACE tsa
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE(’01-JUL-2006′,’dd-MON-yyyy’)) TABLESPACE tsb
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE(’01-OCT-2006′,’dd-MON-yyyy’)) TABLESPACE tsc
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE(’01-JAN-2007′,’dd-MON-yyyy’)) TABLESPACE tsd
);

Lets see another example with other type of table another example, and how manage data with this tables.

Create table Employee(emp_no number(2),emp_name varchar(2))
partition by range(emp_no)
(
partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less than(300),
partition p4 values less than(maxvalue)
);

Inserting, selecting, Adding,dropping,Renaming,Truncate,Splitting,Exchanging,Moving a partition:

SQL> Insert into Employee values(101,’a’); — this will go to p1
SQL> Insert into Employee values(201,’b’); — this will go to p2
SQL> Insert into Employee values(301,’c’); — this will go to p3
SQL> Insert into Employee values(401,’d’); — this will go to p4
SQL> Select * from Employee;
SQL> Select * from Employee partition(p1);
SQL> Alter table Employee add partition p5 values less than(400);
SQL> Alter table Employee drop partition p1;
SQL> Alter table Employee rename partition p3 to p6;
SQL> Alter table Employee truncate partition p5;
SQL> Alter table Employee split partition p2 at(120) into
(partition p21,partition p22);
SQL> Alter table Employee exchange partition p2 with table Employee_x;
SQL> Alter table Employee move partition p21 tablespace ABC_TBS;

Partitioning by LIST
List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition.
Used to list together unrelated data into partitions. Possible usage: a number of states list partitioned into a region.

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(‘California’, ‘Hawaii’),
PARTITION sales_east VALUES (‘New York’, ‘Virginia’, ‘Florida’),
PARTITION sales_central VALUES(‘Texas’, ‘Illinois’)
PARTITION sales_other VALUES(DEFAULT)
);
Lets see another example with other type of table another example, and how manage data with this tables.
SQL> Create table Employee (Emp_no number(2),Emp_name
varchar(2))
partition by list(Emp_no)
(partition p1 values(1,2,3,4,5),
partition p2 values(6,7,8,9,10),
partition p3 values(11,12,13,14,15),
partition p4 values(16,17,18,19,20));

Inserting, selecting, Adding,dropping,Renaming,Truncate,Splitting,Exchanging,Moving a partition:
SQL> Insert into Employee values(4,’xxx’); — this will go to p1
SQL> Insert into Employee values(8,’yyy’); — this will go to p2
SQL> Insert into Employee values(14,’zzz’); — this will go to p3
SQL> Insert into Employee values(19,’bbb’); — this will go to p4
SQL> Select * from Employee;
SQL> Select * from Employee partition(p1);
SQL> Alter table Employee add partition p5 values(21,22,23,24,25);
SQL> Alter table Employee drop partition p5;
SQL> Alter table Employee rename partition p5to p1;
SQL> Alter table Employee truncate partition p5;
SQL> Alter table Employee exchange partition p1 with table Employee_x;
SQL> Alter table Employee move partition p2 tablespace ABC_TBS;
Partitioning by HASH
Is automated way for balancing. Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. Used to spread data evenly over partitions.

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
PARTITION BY HASH(deptno) PARTITIONS 16;

oracle automatically gives partition names like
SYS_P1,SYS_P2,SYS_P3,SYS_P4,SYS_P5,…,SYS_P16

You can force name for individual partition or tablespace where locate
each partition>

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
STORAGE (INITIAL 10K)
PARTITION BY HASH(deptno)
(PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2,
PARTITION p3 TABLESPACE ts1, PARTITION p4 TABLESPACE ts3);
Inserting, selecting, Adding,dropping,Renaming,Truncate,Splitting,Exchanging,Moving a partition:
SQL> Insert into Employee values(5,’a’);
SQL> Insert into Employee values(8,’b’);
SQL> Insert into Employee values(14,’c’);
SQL> Insert into Employee values(19,’d’);
SQL> Select *from Employee;
SQL> Select *from Employee partition(SYS_P2);
SQL> Alter table Employee add partition p9;
SQL> Alter table Employee rename partition p9 to p10;
SQL> Alter table Employee truncate partition p9;
SQL> Alter table Employee exchange partition SYS_P1 with table Employee_X;
SQL> Alter table Employee move partition SYS_P1 tablespace ABC_TBS;
Partitioning by INTERVAL (Version 11g)
Partition automatic creation according to need, depending on the interval
CREATE TABLE T_11G(C1 NUMBER(38,0),
C2 VARCHAR2(10),
C3 DATE)
PARTITION BY RANGE (C3) INTERVAL (NUMTOYMINTERVAL(1,’MONTH’))
(PARTITION P0902 VALUES LESS THAN (TO_DATE(‘2009-03-01 00:00:00′,’YYYY-MM-DD HH24:MI:SS’)));
Partitioning by SYSTEM (Version 11g)
In the insert you must indicate partition

create table t (c1 int,
c2 varchar2(10),
c3 date)
partition by system
(partition p1,
partition p2,
partition p3);
insert into t partition (p3) values (1,’A’,sysdate);

in next post we will make a round trip for unpartitioning and Merge

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s