It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

RANGE Partitioning Explored

Range partitioning is useful when you have distinct ranges of data you want to store together. The classic example of this is the use of dates. Partitioning a table using date ranges allows all data of a similar age to be stored in same partition. Once historical data is no longer needed the whole partition can be removed. If the table is indexed correctly search criteria can limit the search to the partitions that hold data of a correct age.

In this post I will be exploring various method of creation and management of a range partition table.

1. Create table:
CREATE TABLE Range_part_T
(ID NUMBER(5),
HIREDATE DATE,
JOB VARCHAR(15)
)
partition by range (ID)
( partition P1 VALUES LESS THAN (10),
partition P2 VALUES LESS THAN (20),
partition p3 VALUES LESS than (35))
ENABLE ROW MOVEMENT;

2. Insert Value:
INSERT INTO Range_part_T VALUES(12, SYSDATE-100, 'CLERK');
INSERT INTO Range_part_T VALUES(22, SYSDATE-200, 'ASSISTANT');
INSERT INTO Range_part_T VALUES(9, SYSDATE-50, 'SALESMAN');

3. Checking value in each partition
SQL> SELECT * FROM Range_part_T PARTITION (p1);

        ID HIREDATE  JOB
---------- --------- ---------------
         9 19-AUG-13 SALESMAN

SQL> SELECT * FROM Range_part_T PARTITION (p2);

        ID HIREDATE  JOB
---------- --------- ---------------
        12 30-JUN-13 CLERK

SQL> SELECT * FROM Range_part_T PARTITION (p3);

        ID HIREDATE  JOB
---------- --------- ---------------
        22 22-MAR-13 ASSISTANT

4. Updating a record in partition and check if it is updated:
UPDATE Range_part_T
SET ID=8
WHERE ID=12;

We have updated the key column value from 12 to 8. Now as we have 'ENABLE ROW MOVEMENT' parameter enabled, it will move to other partition whose range it follows now after update.

SQL> SELECT * FROM Range_part_T PARTITION (p1);

        ID HIREDATE  JOB
---------- --------- ---------------
         9 19-AUG-13 SALESMAN
         8 30-JUN-13 CLERK


5. Inserting a value which is beyond the range of defined partitions:
SQL> INSERT INTO Range_part_T VALUES(45, SYSDATE-15, 'SHOPEKEEPER');
INSERT INTO Range_part_T VALUES(45, SYSDATE-15, 'SHOPEKEEPER')
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

We cannot add any such value in a partition table whose key range is not defined.
 
6. If we try to update a record in key, to a value that doesnt exist:
UPDATE Range_part_T
SET ID=55
WHERE ID=8;

SQL> UPDATE     Range_part_T
  2  SET ID=55
  3  WHERE ID=8;
UPDATE  Range_part_T
        *
ERROR at line 1:
ORA-14406: updated partition key is beyond highest legal partition key


7. Adding a new partition for defining a new Range:

ALTER TABLE Range_part_T
ADD PARTITION P4 VALUES LESS THAN (50);

8. Truncate Partition:
ALTER TABLE Range_part_T TRUNCATE PARTITION p1, p3;

9. Drop partition:
ALTER TABLE Range_part_T DROP PARTITION p4;

10. Checking all partition and table properties:

A). Displays partitioning information for partitioned tables.
SELECT PARTITION_NAME, HIGH_VALUE, PARTITION_POSITION, TABLESPACE_NAME, COMPRESSION, LOGGING, NUM_ROWS
FROM user_tab_partitions
WHERE TABLE_NAME=UPPER('Range_part_T');

PARTITION_NAME                 HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME                COMPRESS LOGGING   NUM_ROWS
------------------------------ ---------- ------------------ ------------------------------ -------- ------- ----------
P1                             10                          1 USERS                          DISABLED YES              2
P2                             20                          2 USERS                          DISABLED YES              0
P3                             35                          3 USERS                          DISABLED YES              1

B). Display partition-level partitioning information, partition storage parameters, and partition statistics generated by the DBMS_STATS package or the ANALYZE statement.
SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT,PARTITIONING_KEY_COUNT, STATUS, DEF_TABLESPACE_NAME
    FROM USER_PART_TABLES;

TABLE_NAME                     PARTITION SUBPARTIT PARTITION_COUNT PARTITIONING_KEY_COUNT STATUS   DEF_TABLESPACE_NAME
------------------------------ --------- --------- --------------- ---------------------- -------- ------------------------------
RANGE_PART_T                   RANGE     NONE                    3                      1 VALID    USERS

C). Display the partitioning key columns for partitioned tables.
SELECT * FROM USER_PART_KEY_COLUMNS;
NAME                           OBJECT_TYPE     COLUMN_NAME     COLUMN_POSITION
------------------------------ --------------- --------------- ---------------
RANGE_PART_T                   TABLE           ID                            1

D). Display the subpartitioning key columns for composite-partitioned tables
SELECT * FROM USER_SUBPART_KEY_COLUMNS


Example:
CREATE TABLE DW.ACCOUNT_EVENTS_STG_MAIN
(
  AIR_DR_TYPE                    NUMBER(2),
  AIR_ID                         VARCHAR2(45 BYTE) NOT NULL,
  DATA_RECORD_ID                 VARCHAR2(20 BYTE) NOT NULL,
  UTC_TIMESTAMP                  DATE           NOT NULL,
  PARTITION_KEY                  NUMBER(2)      NOT NULL,
  SUBSCRIBER_NUMBER              VARCHAR2(30 BYTE),
  ACCOUNT_NUMBER                 VARCHAR2(30 BYTE),
  SERVICE_CLASS_CURRENT          NUMBER(4),
  ACCOUNT_GROUP_ID               NUMBER(10),
  ORIG_NODE_TYPE                 VARCHAR2(20 BYTE),
  ORIG_NODE_ID                   VARCHAR2(45 BYTE),
  ORIG_TRANSACTION_ID            VARCHAR2(20 BYTE),
  ORIG_TRANSACTION_TIMESTAMP     DATE,
  ORIG_OPERATOR_ID               VARCHAR2(255 BYTE),
  TRANSACTION_TYPE               VARCHAR2(30 BYTE),
  TRANSACTION_CODE               VARCHAR2(30 BYTE),
  TRANSACTION_AMOUNT             NUMBER(27,6),
  TRANSACTION_CURRENCY           NUMBER(1),
  VOUCHER_GROUP                  VARCHAR2(4 BYTE),
  VOUCHER_SERIAL_NUMBER          VARCHAR2(20 BYTE),
  AGENT                          VARCHAR2(8 BYTE),
  SUP_EXP_DATE_BEFORE            DATE,
  SUP_EXP_DATE_AFTER             DATE,
  SERVICE_EXP_DATE_BEFORE        DATE,
  SERVICE_EXP_DATE_AFTER         DATE,
  VV_EXTENTION_PERIOD            NUMBER(4),
  TRANS_SERVICE_CLASS            NUMBER(4),
  VV_SERVICE_CLASS_BEFORE        NUMBER(4),
  SERVICE_CLASS_ORIGINAL         NUMBER(4),
  ACCOUNT_CURRENCY               NUMBER(1),
  MAIN_AMOUNT                    NUMBER(27,6),
  MAIN_BALANCE                   NUMBER(27,6),
  PROM_MAIN_AMOUNT               NUMBER(27,6),
  PROMOTION_PLAN_BEFORE          VARCHAR2(4 BYTE),
  PROMOTION_PLAN_AFTER           VARCHAR2(4 BYTE),
  OPERATION                      NUMBER(2),
  ERROR_CODE                     NUMBER(3),
  ACTIVATION_CODE                VARCHAR2(20 BYTE),
  REFILL_FRAUD_COUNTER           NUMBER(9),
  REFILL_UNBAR_UTC_TIMESTAMP     DATE,
  WM_LUW_ID                      NUMBER(18),
  DWS_CREATION_DATE              DATE           DEFAULT sysdate,
  EXTERNAL_DATA_1                VARCHAR2(120 BYTE),
  EXTERNAL_DATA_2                VARCHAR2(120 BYTE),
  REFILL_TYPE_ID                 NUMBER(3),
  LOCATION_NUMBER                VARCHAR2(20 BYTE),
  SEGMENTATION_ID                VARCHAR2(10 BYTE),
  EXTERNAL_DATA_3                VARCHAR2(255 BYTE),
  EXTERNAL_DATA_4                VARCHAR2(255 BYTE),
  VOUCHER_BASED_REFILL           NUMBER(1),
  VOUCHER_REGION                 NUMBER(5),
  SUBSCRIBER_REGION              NUMBER(5),
  SELECTED_OPTION                NUMBER(5),
  MODULE                         VARCHAR2(20 BYTE),
  TRANSACTION_ID                 NUMBER(18),
  LOCAL_TIMESTAMP                DATE,
  ORGIN_NODE_ORIGINAL_TIMESTAMP  DATE,
  ACCOUNT_FLAGS_AFTER            VARCHAR2(8 BYTE),
  NEW_SERVICE_CLASS              NUMBER(4),
  CC_GRACE_PERIOD                NUMBER(8),
  SERV_REMOVAL_GRACE_PERIOD      NUMBER(8),
  VVEXP_DATE_BEFORE              DATE,
  VVEXP_DATE_AFTER               DATE,
  OLD_TEMP_SERVICE_CLASS         NUMBER(4),
  NEW_TEMP_SERVICE_CLASS         NUMBER(4),
  OLD_SERVICE_OFFERINGS          NUMBER(10),
  NEW_SERVICE_OFFERINGS          NUMBER(10),
  NEW_COMMUNITY_ID_1             NUMBER(7),
  NEW_COMMUNITY_ID_2             NUMBER(7),
  NEW_COMMUNITY_ID_3             NUMBER(7),
  ACCOUNT_HOME_REGION            NUMBER(5),
  TRANSACTION_DETAILS            VARCHAR2(10 BYTE),
  CELL_IDENTIFIER                VARCHAR2(50 BYTE),
  ADJUSTED_TRANSACTION_AMT       NUMBER(27,6),
  SUPPLIER_ID                    VARCHAR2(10 BYTE)
)
TABLESPACE AIR_ACC_EVENTS_DATA
PCTUSED    0
PCTFREE    5
INITRANS   1
MAXTRANS   255
PARTITION BY RANGE (PARTITION_KEY)
(  
  PARTITION AIR_ACCOUNT_EVENTS_STG_S_PP_0 VALUES LESS THAN (1)
    LOGGING
    NOCOMPRESS
   TABLESPACE AIR_ACC_EVENTS_DATA
    PCTFREE    5
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
               INITIAL          160M
                NEXT             160M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               BUFFER_POOL      DEFAULT
              ),  
  PARTITION AIR_ACCOUNT_EVENTS_STG_S_PP_1 VALUES LESS THAN (2)
    LOGGING
    NOCOMPRESS
    TABLESPACE AIR_ACC_EVENTS_DATA
    PCTFREE    5
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          160M
                NEXT             160M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION AIR_ACCOUNT_EVENTS_STG_S_PP_2 VALUES LESS THAN (3)
    LOGGING
    NOCOMPRESS
    TABLESPACE AIR_ACC_EVENTS_DATA
    PCTFREE    5
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
               INITIAL          160M
                NEXT             160M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
              ),  
  PARTITION AIR_ACCOUNT_EVENTS_STG_S_PP_3 VALUES LESS THAN (4)
    LOGGING
    NOCOMPRESS
    TABLESPACE AIR_ACC_EVENTS_DATA
    PCTFREE    5
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          160M
                NEXT             160M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
               PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION AIR_ACCOUNT_EVENTS_STG_S_PP_4 VALUES LESS THAN (5)
    LOGGING
    NOCOMPRESS
    TABLESPACE AIR_ACC_EVENTS_DATA
    PCTFREE    5
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          160M
                NEXT             160M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
              ),  
  PARTITION AIR_ACCOUNT_EVENTS_STG_S_PP_5 VALUES LESS THAN (6)
    LOGGING
    NOCOMPRESS
    TABLESPACE AIR_ACC_EVENTS_DATA
    PCTFREE    5
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          160M
                NEXT             160M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION AIR_ACCOUNT_EVENTS_STG_S_PP_6 VALUES LESS THAN (7)
    LOGGING
    NOCOMPRESS
    TABLESPACE AIR_ACC_EVENTS_DATA
    PCTFREE    5
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          160M
                NEXT             160M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION AIR_ACCOUNT_EVENTS_STG_S_PP_7 VALUES LESS THAN (8)
    LOGGING
    NOCOMPRESS
    TABLESPACE AIR_ACC_EVENTS_DATA
    PCTFREE    5
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          160M
                NEXT             160M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

2 comments:

Trying to find the Ultimate Dating Site? Join and find your perfect match.

 

eToro is the #1 forex trading platform for beginning and pro traders.

 

You Might Also Like

Related Posts with Thumbnails

Pages