Partitioning in Oracle

Partitioning in Oracle is a way of distributing table data into separate segments to improve performance of data retrieval. A table with more than 2GB of data volume is generally considered candidate of partitioning. Data is a partitioned table are physically divided and stored into separate physical datafiles, but it is transparent to the end user.

A table can be partitioned in different ways:

--> Range Partition
--> List Partition
--> Hash Partition

Range Partition:

A table can be divided using range partitioning if you want to distribute the data using the range of values of some columns of a table. Generally a data column is considered as a partition key for range partitioning. Like different partitions for Jan-2011, Feb-2011, ......,Dec-2011.

Syntax:

CREATE TABLE SAMPLE
(
COLUMN1 VARCHAR2(10),
COLUMN2 DATE,
COLUMN3 NUMBER(10,2)
)TABLESPACE TABLE1
PARTITION BY RANGE(COLUMN2)
(
PARTITION PART1 VALUES LESS THAN (TO_DATE('01/02/2011','DD/MM/YYYY')),
PARTITION PART2 VALUES LESS THAN (TO_DATE('01/03/2011','DD/MM/YYYY')),
....
....
PARTITION PART12 VALUES LESS THAN (TO_DATE('01/12/2011','DD/MM/YYYY')),
);

List Partition:

A table can be divided using list partitioning if you want to distribute the data using the listed values of some columns of a table. This partitioning method is generally used if most of the time the table is queried using the value of the column.

Syntax:

CREATE TABLE SAMPLE
(
COLUMN1 VARCHAR2(10),
COLUMN2 DATE,
COLUMN3 NUMBER(10,2)
)TABLESPACE TABLE1
PARTITION BY LIST(COLUMN2)
(
PARTITION PART1 VALUES ('A','B'),
PARTITION PART2 VALUES ('C','D','E'),
....
....
PARTITION PART12 VALUES ('G','H','V'),
);

Hash Partition:

Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning.

Syntax:

CREATE TABLE SAMPLE
(
COLUMN1 VARCHAR2(10),
COLUMN2 DATE,
COLUMN3 NUMBER(10,2)
)TABLESPACE TABLE1
PARTITION BY HASH(COLUMN2)
PARTITIONS n
STORE IN (TS1,TS2,....TSn);


Key points while partitioning a table:

1. Table containig LONG and LONG RAW datatype column cannot be partitioned.
2. Pseudo columns like LEVEL,ROWID,MLSLABEl cannot be used as a partition key.
3. Partition key columns can contain columns that are nullable.
4. In case of range or hash partitioning you can use upto maximum 16 columns a partition key.
5. In case of a partitioned table, you cannot define a unique local index if the partitioning key is not a part of index key.
6. A bitmap index cannot be global index on a partitioned table.

Informatica backend query to get the load type in a session for a task

SELECT distinct subj.SUBJ_NAME AS FOLDERNAME,
task.TASK_NAME AS SESSIONNAME,
swdgt.INSTANCE_NAME AS INSTANCENAME,
DECODE (sessattr.ATTR_VALUE, '0', 'NORMAL', '1', 'BULK', NULL)
AS TARGETLOADTYPE
FROM OPB_SESSION sess,
OPB_SWIDGET_INST swdgt,
OPB_EXTN_ATTR sessattr,
OPB_TASK task,
OPB_SUBJECT subj
WHERE sess.SESSION_ID = swdgt.SESSION_ID
AND sess.SESSION_ID = sessattr.SESSION_ID
AND sessattr.ATTR_ID = 3
AND swdgt.SESS_WIDG_INST_ID = sessattr.SESS_WIDG_INST_ID
AND sess.SESSION_ID = task.TASK_ID
AND task.SUBJECT_ID = subj.SUBJ_ID
and swdgt.widget_type=2
order by subj.SUBJ_NAME ,
task.TASK_NAME ,
swdgt.INSTANCE_NAME

Informatica backend Query to find mapping in informatica using Aggregator transformation.

select distinct map.mapping_name,inst.instance_name from opb_widget wdgt,opb_widget_inst inst,opb_mapping map
where
wdgt.widget_id=inst.widget_id and
inst.mapping_id=map.mapping_id and
wdgt.widget_type=9;

Informatica backend query to find a mapping parameter used in a mapping.

select subj.subj_name,map.mapping_name ,param.pv_name from opb_map_parmvar param,opb_mapping map,opb_subject subj
where param.mapping_id=map.mapping_id
and map.subject_id=subj.subj_id
and param.pv_name='$$PARAMETER';