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.
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;
0 comments:
Post a Comment