Oracle Table Partitioning
1 Introduction
Oracle provides a good feature called Partitioning which enables dividing an Object (table or an Index) into smaller pieces based on particular fields or values. This helps us in maintaining and administering the tables and indexes in a better way when compared to the normal tables. With the help of the partitioning, the availability of the Database objects and the Performance of the DML or DDL operations on those objects can be improved. In the partitioning concept, a single table or an Index is divided into multiple parts of smaller pieces called Partitions. Each of the partition can be given a different name and consider as a part of the object. By this, DBA tasks can be performed on these partitions as a whole or individually also. E.g. backing up a particular portion of the table. Partitioning can also improve the performance of multi-table joins, by using a technique known as partition-wise join. The Oracle Partitioning is an additional feature that is available when Oracle Server is installed. This feature is available only with the Enterprise edition of the Oracle server. If Partitioning is not enabled, then you will face the following error when trying to partition:
ORA-00439: feature not enabled: Partitioning
1.1 Advantages
* Query subsets of data.
* Partitions usually provide enhanced performance when accessing large tables.
* Table reorganizations can be done on a partition level.
* Reduce downtime for scheduled maintenance.
* Reduce downtime due to data failure.
* I/O performance.
1.2 Evolution of Partitioning in Oracle
* Oracle 8 à Range
* Oracle 8i à Range, Hash, Range-Hash
* Oracle 9i à Range, Hash, List, Range-Hash
* Oracle 9i Release 2 à Range, Hash, List, Range-List, Range-Hash
1.3 Decision to Partition Tables
The main thing for an Oracle DBA or a developer is to take a decision on whether to partition a particular table or not. Here are some tips on making a decision for the same:
* For “large” tables i.e. for tables >= 2 Gigs
* If Performance gain outweighs the management of partitioning.
* If Archiving of data is on a schedule and repetitive.
Tip: SQL to identify the size of a table
SELECT B.OWNER,
B.TABLESPACE_NAME,
B.TABLE_NAME,
ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 6) GIGS
FROM SYS.DBA_EXTENTS A,
SYS.DBA_TABLES B
WHERE ((B.TABLESPACE_NAME = A.TABLESPACE_NAME)
AND
(B.OWNER = UPPER ('&OWNER')) AND (B.TABLE_NAME = '&TABLE')
)
GROUP BY B.OWNER, B.TABLESPACE_NAME, B.TABLE_NAME;
2 Partitioning methods
2.1 Range Partitioning
Range partitioning was the first partitioning method supported by Oracle in Oracle 8. Range partitioning was probably the first partition method because data normally has some sort of logical range. For example, business transactions can be partitioned by various versions of date (start date, transaction date, close date, or date of payment). Range partitioning can also be performed on part numbers, serial numbers or any other ranges that can be discovered.
The below shown syntax can be used to implement Range Partitioning:
Example1: Range partition example using a single tablespace.
CREATE TABLE EMP (EMPNO NUMBER (7), NAME VARCHAR2 (50), DESIGNATION VARCHAR2 (10), SALARY NUMBER (9, 3)
PARTITION BY RANGE (SALARY)
(PARTITION SAL_A VALUES LESS THAN (200000),
PARTITION SAL_B VALUES LESS THAN (100000),
PARTITION SAL_C VALUES LESS THAN (50000))
TABLESPACE USERS);
Example2: Range partition example using a Multiple tablespaces. This method provides better performance when table is very big.
CREATE TABLE EMP (EMPNO NUMBER (7), NAME VARCHAR2 (50), DESIGNATION VARCHAR2 (10), SALARY NUMBER (9, 3)
PARTITION BY RANGE (SALARY)
(PARTITION SAL_A VALUES LESS THAN (200000) TABLESPACE EMP1,
PARTITION SAL_B VALUES LESS THAN (100000) TABLESPACE EMP2,
PARTITION SAL_C VALUES LESS THAN (50000) TABLESPACE EMP3);
Important Note on Range Partitions: Range partitions are ordered by defining the lower and upper boundary for a specific partition. It is possible that partition size may differ substantially due to the amount of data that will be mapped to each specific partition. This may cause sub-optimal performance for certain operations like parallel DML. So through analysis of the data is required before deciding on the partition.
2.2 List Partitioning
List partitioning was added as a partitioning method in Oracle 9i Release 1. List partitioning allows for partitions to reflect real-world groupings (e.g. business units and territory regions). List partitioning differs from range partition in that the groupings in list partitioning are not side by side or in a logical range. List partitioning gives the DBA the ability to group together seemingly unrelated data into a specific partition.
The LIST_ME.SQL script provides an example of a list partition table. Note the last partition with the DEFAULT value. This DEFAULT value is new in Oracle 9i Release 2.
CREATE TABLE EMP (EMPNO NUMBER (7), NAME VARCHAR2 (50), DESIGNATION VARCHAR2 (10), SALARY NUMBER (9, 3)
PARTITION BY LIST (DESIGNATION)
(PARTITION DES_A VALUES (‘MANAGER’,’SENIOR MANAGER’),
PARTITION DES_B VALUES (‘ANALYST’,’SENIOR ANALYST’),
PARTITION DES_C VALUES (‘ENGINEER’,’SENIOR ENGINEER’))
TABLESPACE USERS);
2.3 Hash Partitoning
Hash partioning is the method in which the partioning is enabled or implemented by means of a hash range scan and it is best to be implemented in cases where in we do not have the list or range of values befor in-hand for a particular table. The following is the syntax that can be used for the same :
CREATE TABLE EMP
(EMPNO NUMBER (7),
NAME VARCHAR2 (50),
DESIGNATION VARCHAR2 (10),
SALARY NUMBER (9, 3)
PARTITION BY HASH (EMPNO)
(PARTITION STORE IN (P1,P2,P3,P4,P5)) TABLESPACE USERS);
In the above statement the parameter PARTITION number can be changed based on the performance or the throughput of the operations that are performed in the table.
2.4 Composite Range-Hash Partitioning
Composite range-hash partitioning combines both the ease of range partitioning and the benefits of hashing for data placement, striping, and parallelism. Range-hash partitioning is slightly harder to implement. But, with the example provided and a detail explanation of the code one can easily learn how to use this powerful partitioning method.
One suggestion is that when you actually try to build a range-hash partition table that you do it in the following steps:
1. Determine the partition key for the range.
2. Design a range partition table.
3. Determine the partition key for the hash.
4. Create the SUBPARTITION BY HASH clause.
5. Create the SUBPARTITION TEMPLATE.
6. Do Steps 1 and 2 first. Then you can insert the code created in Steps 3 –5 in the range partition table syntax.
CREATE TABLE DEMO ( ID NUMBER,
TXT VARCHAR2(50))
PARTITION BY RANGE (ID)
SUBPARTITION BY HASH (TXT)
SUBPARTITIONS 4 STORE IN (DATA01, DATA02)
(PARTITION KB_LO VALUES LESS THAN (0),
PARTITION KB_HI VALUES LESS THAN (100),
PARTITION KB_MX VALUES LESS THAN (MAXVALUE)
SUBPARTITIONS 2 STORE IN (DATA03));
2.5 Composite Range-List partitioning
Composite range-list partitioning combines both the ease of range partitioning and the benefits of list partitioning at the sub partition level. This is a combination of the Range and the List partitions. Like range-hash partitioning, range-list partitioning needs to be carefully designed. The time used to properly design a range-list partition table pays off during the actual creation of the table.
CREATE TABLE EMPDEMO
(EMPNO NUMBER(7),
NAME VARCHAR2(50),
DESIGNATION VARCHAR2(10),
SALARY NUMBER(9,3)
PARTITION BY RANGE (EMPNO)
SUBPARTITION BY DESIGNATION (
(PARTITION DES_A VALUES(‘MANAGER’,’SENIOR MANAGER’),
PARTITION DES_B VALUES(‘ANALYST’,’SENIOR ANALYST’),
PARTITION DES_C VALUES (ENGINEER,SENIOR ENGINEER))
TABLESPACE USERS);
3 Indexes for partitioned tables
The indexes for partitioned tables are of two types.
* Globally Partitioned Indexes
* Locally Partitioned Indexes
3.1 Globally Partitioned Indexes
There are mainly two types of Globally Partition Indexes available :
* Non-Partitioned
* Partitioned
Globally Non-Partitioned Indexes are “regular” indexes used in OLTP.
Globally Partitioned Indexes are similar in syntax to Range partitioned tables.
CREATE INDEX PARTITION_BY_RANGE_GPI
ON PARTITION_BY_RANGE (BIRTH_YYYY)
GLOBAL PARTITION BY RANGE (BIRTH_YYYY)
(PARTITION DOBS_IN_1971_OR_B4
VALUES LESS THAN (1972)
TABLESPACE ITS01,
PARTITION DOBS_IN_1972_GPI
VALUES LESS THAN (1973)
TABLESPACE ITS02,
. . .
PARTITION DOBS_IN_1975_OR_L8R
VALUES LESS THAN (MAXVALUE)
TABLESPACE ITS05);
3.2 Locally Partitioned Indexes
Locally partitioned indexes are for the most part very straightforward.
Extra time should be allocated when creating locally partitioned indexes on range-hash or range-list partitioned tables. There are a couple reasons that extra time is needed for this type of index. One of the reasons is a decision needs to be made on what the index will be referencing in regards to a range-hash or range-list partitioned tables. A locally partitioned index can be created to point to either partition level or sub partition level.
Maintenance of locally partitioned indexes is much easier than the maintenance of globally partitioned indexes. Whenever there is DDL activity on the underlying indexed table Oracle rebuilds the locally partitioned index.
This automatic rebuilding of locally partitioned indexes is one reason why most DBAs prefer locally partitioned indexes.
4 When to use which partitioning Method
There are five different table partitioning methods (range, hash, list, range-hash and range-list) and three for indexes (global non-partitioned, global partitioned and locally partitioned). The obvious question that comes to mind is: “When do I use which combination of table and index partitioning?” There is no concrete answer for that question. However, here are some general guidelines on mixing and matching table and index partitioning.
1. First, determine if you need to partition the table.
· Refer to the section 1.3 on “When To Partition Tables”.
2. Next, decide which table partitioning method is right for your situation.
· Each method is described under Section 2
3. Determine how volatile the data is.
· How often are there inserts, updates and deletes?
4. Choose your indexing strategy: global or local partitioned indexes.
· Each type has its own maintenance consideration.
These guidelines are good place to start when developing a partitioning solution.
5 Partitioning Existing tables using the dbms_redefinition package
The DBMS_REDEFINITION is an in-built PL/SQL package that is available with Oracle versions starting from version 9.This can be used in order to redefine the meaning of the tables and columns in a table.
The DBMS_REDEFINITION package can be created by executing the dbmshord.sql script available in the following location: $ORACLE_HOME/rdbms/admin/dbmshord.sql
There are different procedures available in the DBMS_REDEFINITION Package that is used for the implementation of the Partitioning concept:
dbms_redefinition.start_redef_table – This is used to Start the Redefinition Process.
dbms_redefinition.sync_interim_table – This procedure will synchronize the data between the source and the intermediate table.
dbms_redefinition.finish_redef_table - This is will do the final touch up of the redefinition process.
dbms_redefinition.can_redef_table – This is used to find whether we can redefine a table or not by using the Redefinition process.
The following are the steps that were carried out for implementing the redefinition process and the test results for the same :
5.1 Range Partition Process Explained
Environment Selected : DCSCAN Production Server.
Original Table Name: CESAR_VEHICLE_HISTORY
Size of the table : 2195 MB
No. Of Rows : 8.23M
For rest of our discussion in this example we use two tables.
* 1. “CESAR_VEHICLE_HISTORY” can be referred as Original Table
2. “CESAR_VEHICLE_HISTORY_INT” can be referred as Intermittent Table.
Step 1: Create an Intermittent table CESAR_VEHICLE_HISTORY_INT
This is an intermittent table and will be dropped later after the partitioning process has been completed. So the desired partitions have to be defined on this table. In this specific example we have decided to Range partition the table based on a filed called “TIMESTAMP_DT” and each partition contains data for a Quarter.
CREATE TABLE MPCI5171.CESAR_VEHICLE_HISTORY_INT
(
VEHICLE_NO_INT VARCHAR2(22 BYTE) NOT NULL,
TIMESTAMP NUMBER(21,7) NOT NULL,
ACTION VARCHAR2(4 BYTE),
VACTION VARCHAR2(4 BYTE),
ACTION_CONTROL_PURCHASE VARCHAR2(4 BYTE),
PURCHASING_STATUS_OLD VARCHAR2(4 BYTE),
PURCHASING_STATUS_NEW VARCHAR2(4 BYTE),
ACTION_CONTROL_SALES VARCHAR2(4 BYTE),
SALES_STATUS_OLD VARCHAR2(4 BYTE),
SALES_STATUS_NEW VARCHAR2(4 BYTE),
VEHICLE_LOCATION VARCHAR2(10 BYTE),
PERSON_CREATED_OBJECT VARCHAR2(12 BYTE),
ALLOCATION_REASON VARCHAR2(40 BYTE),
QUOTE_NO VARCHAR2(10 BYTE),
DEALERFRONTEND_USERID VARCHAR2(50 BYTE),
REASON_CODE VARCHAR2(3 BYTE),
CUSTOMER_NO VARCHAR2(10 BYTE),
SHIP_TO_PARTY VARCHAR2(10 BYTE),
DELIVERY_DATE_REQ DATE,
VEHICLE_USAGE VARCHAR2(3 BYTE),
CUSTOMER_PURCHASE_ORDERNO VARCHAR2(20 BYTE),
KERRIDGE_STOCK_NO VARCHAR2(18 BYTE),
END_CUSTOMER_NAME VARCHAR2(30 BYTE),
SALESPERSON VARCHAR2(10 BYTE),
CHARACTERISTIC_VAL1 VARCHAR2(120 BYTE),
CHARACTERISTIC_VAL2 VARCHAR2(120 BYTE),
CHARACTERISTIC_VAL3 VARCHAR2(120 BYTE),
CHARACTERISTIC_VAL4 VARCHAR2(120 BYTE),
CHARACTERISTIC_VAL5 VARCHAR2(120 BYTE),
CHARACTERISTIC_VAL6 VARCHAR2(120 BYTE),
CONFIGURATION_INT VARCHAR2(18 BYTE),
ADDRESS_NO VARCHAR2(10 BYTE),
CRM_REFERENCE VARCHAR2(18 BYTE),
SHIP_EST_ARRIVAL_DATE DATE,
FACTORD_PLANNED_FIN_DATE DATE,
EARLIER_VEHICLE_REQ VARCHAR2(1 BYTE),
NAME1 VARCHAR2(40 BYTE),
NAME2 VARCHAR2(40 BYTE),
NAME3 VARCHAR2(40 BYTE),
NAME4 VARCHAR2(40 BYTE),
VESSEL_NAME VARCHAR2(20 BYTE),
SHIP_VOYAGE_NO VARCHAR2(18 BYTE),
SHIP_BILL_OF_LADING_NO VARCHAR2(18 BYTE),
SHIP_EST_DEPARTURE_DATE DATE,
PORT_OF_DESTINATION VARCHAR2(10 BYTE),
KERRIDGE_USER VARCHAR2(50 BYTE),
ZZ_PLANNED_DELIVERY_TIME NUMBER(15),
VEH_MANUFACTURER_YEAR VARCHAR2(4 BYTE),
VEH_CONSTRUCTION_MONTH VARCHAR2(2 BYTE),
VEH_CONSTRUCTION_DAY VARCHAR2(2 BYTE),
VIN VARCHAR2(35 BYTE),
PLANNED_DELIVERY_TIME DATE,
PRODUCTION_TIME DATE,
ORDER_TIME DATE,
VEHICLE_LOCATION_FROM VARCHAR2(10 BYTE),
VEHICLE_LOCATION_FROM_TEXT VARCHAR2(30 BYTE),
VEHICLE_LOCATION_TO VARCHAR2(10 BYTE),
VEHICLE_LOCATION_TO_TEXT VARCHAR2(30 BYTE),
VEND_CRED_ACCOUNT_NO VARCHAR2(10 BYTE),
CARRIER_NAME VARCHAR2(35 BYTE),
COMMENTS VARCHAR2(40 BYTE),
PROMISED_TIME DATE,
TIMESTAMP_DT DATE,
COUNTRY_CODE VARCHAR2(4 BYTE),
VEHICLE_NO VARCHAR2(10 BYTE),
FACTORY_ORDER_DATE DATE,
DAYS_SINCE NUMBER(10),
TIMESTAMP_DT_WEEK VARCHAR2(10 BYTE),
DAYS_SINCE_FIRST NUMBER(10),
DAYS_SINCE_LAST NUMBER(10)
)
PARTITION BY RANGE (TIMESTAMP_DT)
(PARTITION CESAR_VEHICLE_HISTORY_2005Q4 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')),
PARTITION CESAR_VEHICLE_HISTORY_2006Q1 VALUES LESS THAN (TO_DATE('31/03/2006', 'DD/MM/YYYY')),
PARTITION CESAR_VEHICLE_HISTORY_2006Q2 VALUES LESS THAN (TO_DATE('30/06/2006', 'DD/MM/YYYY')),
PARTITION CESAR_VEHICLE_HISTORY_2006Q3 VALUES LESS THAN (TO_DATE('30/09/2006', 'DD/MM/YYYY')),
PARTITION CESAR_VEHICLE_HISTORY_2006Q4 VALUES LESS THAN (TO_DATE('31/12/2006', 'DD/MM/YYYY')),
PARTITION CESAR_VEHICLE_HISTORY_2007Q1 VALUES LESS THAN (TO_DATE('31/03/2007', 'DD/MM/YYYY')),
PARTITION CESAR_VEHICLE_HISTORY_2007Q2 VALUES LESS THAN (TO_DATE('30/06/2007', 'DD/MM/YYYY')),
PARTITION CESAR_VEHICLE_HISTORY_2007Q3 VALUES LESS THAN (TO_DATE('30/09/2007', 'DD/MM/YYYY')),
PARTITION CESAR_VEHICLE_HISTORY_2007Q4 VALUES LESS THAN (TO_DATE('31/12/2007', 'DD/MM/YYYY')))
TABLESPACE MPCDATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING;
/
Step 2: Check if the table can be partitioned
Execute the below statement to verify if the original table can be partitioned.
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('MPCI5171', 'CESAR_VEHICLE_HISTORY');
The above step should not return any errors which means that we can go ahead.
Step 3: Start the Redefinition
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'MPCI5171',
ORIG_TABLE => 'CESAR_VEHICLE_HISTORY',
INT_TABLE => 'CESAR_VEHICLE_HISTORY_INT');
END;
/
Step 4: Synchronize the tables
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
UNAME => 'MPCI5171',
ORIG_TABLE => 'CESAR_VEHICLE_HISTORY',
INT_TABLE => 'CESAR_VEHICLE_HISTORY_INT');
END;
/
Step 5: Create the constraints and Indexes on interim table as original Table with new names
CREATE INDEX MPCI5171.CES_VEHIHIST_P_NOINT_IDX01 ON MPCI5171.CESAR_VEHICLE_HISTORY_INT
(VEHICLE_NO_INT)
LOGGING
TABLESPACE MPCINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX MPCI5171.CES_VEHIHIST_P_VACT_IDX ON MPCI5171.CESAR_VEHICLE_HISTORY_INT
(VACTION)
LOGGING
TABLESPACE MPCINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE UNIQUE INDEX MPCI5171.CES_VEHI_HIST_P_PK ON MPCI5171.CESAR_VEHICLE_HISTORY_INT
(VEHICLE_NO_INT, TIMESTAMP)
LOGGING
TABLESPACE MPCINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX MPCI5171.IX1CESAR_VEHICLE_HISTORY_P ON MPCI5171.CESAR_VEHICLE_HISTORY_INT
(ACTION)
LOGGING
TABLESPACE MPCINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX MPCI5171.IX2CESAR_VEHICLE_HISTORY_P ON MPCI5171.CESAR_VEHICLE_HISTORY_INT
(VEHICLE_NO)
LOGGING
TABLESPACE MPCINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE MPCI5171.CESAR_VEHICLE_HISTORY_INT ADD (
CONSTRAINT CES_VEHI_HIST_PK_P PRIMARY KEY (VEHICLE_NO_INT, TIMESTAMP)
USING INDEX
TABLESPACE MPCINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));
Step 6: Gather Statistics on the new table
EXEC DBMS_STATS.GATHER_TABLE_STATS('MPCI5171', 'CESAR_VEHICLE_HISTORY_INT', CASCADE => TRUE);
Step 7: Finish Redefinition Process
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
UNAME => 'MPCI5171',
ORIG_TABLE => 'CESAR_VEHICLE_HISTORY',
INT_TABLE => 'CESAR_VEHICLE_HISTORY_INT');
END;
/
At this point the interim table has become the "real" table and their names have been switched in the data dictionary. All that remains is to perform some cleanup operations
Step 8: Now drop the Interim table CESAR_VEHICLE_HISTORY_INT
DROP TABLE CESAR_VEHICLE_HISTORY_INT;
Step 9:Rename the Constraints and Indexes to match with the Original names
Alter INDEX CES_VEHIHIST_P_NOINT_IDX01 RENAME TO CES_VEHIHIST_T_NOINT_IDX01;
Alter INDEX CES_VEHI_HIST_P_PK RENAME TO CES_VEHI_HIST_T_PK;
Alter INDEX IX1CESAR_VEHICLE_HISTORY_P RENAME TO IX1CESAR_VEHICLE_HISTORY_T;
Alter INDEX IX2CESAR_VEHICLE_HISTORY_P RENAME TO IX2CESAR_VEHICLE_HISTORY_T;
Step 10: Once the Partitioning is implemented, the same can be tested by using the following SQL query :
SELECT PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='CESAR_VEHICLE_HISTORY':
This will return an output saying YES which means that the original table is partitioned:
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'CESAR_VEHICLE_HISTORY';
The above query returns the names of the Partitions for the original table.
5.1.1 Test Results :
In the production environment we have a procedure named “CESAR_MARKETING_MEASURES”. This procedure uses the original table CESAR_VEHICLE_HISTORY in many select statements.
The Execution of the procedure took 5 hrs 31 min without Partitoning
Execution of the same procedure took 4 hrs 45 min after implementing RANGE Partitioning.
5.1.2 Conclusion
The test results reveal that implementing a Range partition on the filed TIMESTAMP_DT is not the right choice in the given scenario. It is simply because the exection time for the procedure “CESAR_MARKETING_MEASURES” has not improved much after implemneting the rage partition also. So we have continued our testing with hash partitioning and is explained in the next section.
5.2 Hash Partitioning Process Explained
Environment selected : DCSCAN Production.
Table : CESAR_VEHICLE_HISTORY
Size of the table : 2195 MB
No.Of Rows : 8.23M
Step 1: Create a Partitioned table CESAR_VEHICLE_HISTORY_INT
CREATE TABLE MPCI5171.CESAR_VEHICLE_HISTORY_INT
(
VEHICLE_NO_INT VARCHAR2(22 BYTE) NOT NULL,
TIMESTAMP NUMBER(21,7) NOT NULL,
ACTION VARCHAR2(4 BYTE),
VACTION VARCHAR2(4 BYTE),
ACTION_CONTROL_PURCHASE VARCHAR2(4 BYTE),
PURCHASING_STATUS_OLD VARCHAR2(4 BYTE),
PURCHASING_STATUS_NEW VARCHAR2(4 BYTE),
ACTION_CONTROL_SALES VARCHAR2(4 BYTE),
SALES_STATUS_OLD VARCHAR2(4 BYTE),
SALES_STATUS_NEW VARCHAR2(4 BYTE),
VEHICLE_LOCATION VARCHAR2(10 BYTE),
PERSON_CREATED_OBJECT VARCHAR2(12 BYTE),
ALLOCATION_REASON VARCHAR2(40 BYTE),
QUOTE_NO VARCHAR2(10 BYTE),
DEALERFRONTEND_USERID VARCHAR2(50 BYTE),
REASON_CODE VARCHAR2(3 BYTE),
CUSTOMER_NO VARCHAR2(10 BYTE),
SHIP_TO_PARTY VARCHAR2(10 BYTE),
DELIVERY_DATE_REQ DATE,
VEHICLE_USAGE VARCHAR2(3 BYTE),
CUSTOMER_PURCHASE_ORDERNO VARCHAR2(20 BYTE),
KERRIDGE_STOCK_NO VARCHAR2(18 BYTE),
END_CUSTOMER_NAME VARCHAR2(30 BYTE),
SALESPERSON VARCHAR2(10 BYTE),
CHARACTERISTIC_VAL1 VARCHAR2(120 BYTE),
CHARACTERISTIC_VAL2 VARCHAR2(120 BYTE),
CHARACTERISTIC_VAL3 VARCHAR2(120 BYTE),
CHARACTERISTIC_VAL4 VARCHAR2(120 BYTE),
CHARACTERISTIC_VAL5 VARCHAR2(120 BYTE),
CHARACTERISTIC_VAL6 VARCHAR2(120 BYTE),
CONFIGURATION_INT VARCHAR2(18 BYTE),
ADDRESS_NO VARCHAR2(10 BYTE),
CRM_REFERENCE VARCHAR2(18 BYTE),
SHIP_EST_ARRIVAL_DATE DATE,
FACTORD_PLANNED_FIN_DATE DATE,
EARLIER_VEHICLE_REQ VARCHAR2(1 BYTE),
NAME1 VARCHAR2(40 BYTE),
NAME2 VARCHAR2(40 BYTE),
NAME3 VARCHAR2(40 BYTE),
NAME4 VARCHAR2(40 BYTE),
VESSEL_NAME VARCHAR2(20 BYTE),
SHIP_VOYAGE_NO VARCHAR2(18 BYTE),
SHIP_BILL_OF_LADING_NO VARCHAR2(18 BYTE),
SHIP_EST_DEPARTURE_DATE DATE,
PORT_OF_DESTINATION VARCHAR2(10 BYTE),
KERRIDGE_USER VARCHAR2(50 BYTE),
ZZ_PLANNED_DELIVERY_TIME NUMBER(15),
VEH_MANUFACTURER_YEAR VARCHAR2(4 BYTE),
VEH_CONSTRUCTION_MONTH VARCHAR2(2 BYTE),
VEH_CONSTRUCTION_DAY VARCHAR2(2 BYTE),
VIN VARCHAR2(35 BYTE),
PLANNED_DELIVERY_TIME DATE,
PRODUCTION_TIME DATE,
ORDER_TIME DATE,
VEHICLE_LOCATION_FROM VARCHAR2(10 BYTE),
VEHICLE_LOCATION_FROM_TEXT VARCHAR2(30 BYTE),
VEHICLE_LOCATION_TO VARCHAR2(10 BYTE),
VEHICLE_LOCATION_TO_TEXT VARCHAR2(30 BYTE),
VEND_CRED_ACCOUNT_NO VARCHAR2(10 BYTE),
CARRIER_NAME VARCHAR2(35 BYTE),
COMMENTS VARCHAR2(40 BYTE),
PROMISED_TIME DATE,
TIMESTAMP_DT DATE,
COUNTRY_CODE VARCHAR2(4 BYTE),
VEHICLE_NO VARCHAR2(10 BYTE),
FACTORY_ORDER_DATE DATE,
DAYS_SINCE NUMBER(10),
TIMESTAMP_DT_WEEK VARCHAR2(10 BYTE),
DAYS_SINCE_FIRST NUMBER(10),
DAYS_SINCE_LAST NUMBER(10)
)
PARTITION BY HASH(VEHICLE_NO_INT)
PARTITIONS 20
TABLESPACE MPCDATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING;
/
In the above example Hash partitioning approach is selected with number of partitions as 20 and all the partitions resides on the one table space named “MPCDATA”. If the table size is too big then it is a good idea to span each partition on a separate table space.
Step 2: Check if the table can be partitioned
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('MPCI5171', 'CESAR_VEHICLE_HISTORY');
The above step should not returns errors which means that we can go ahead.
Step 3: Start the Redefinition
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'MPCI5171',
ORIG_TABLE => 'CESAR_VEHICLE_HISTORY',
INT_TABLE => 'CESAR_VEHICLE_HISTORY_INT');
END;
/
Step 4: Synchronize the tables
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
UNAME => 'MPCI5171',
ORIG_TABLE => 'CESAR_VEHICLE_HISTORY',
INT_TABLE => 'CESAR_VEHICLE_HISTORY_INT');
END;
/
Step 5: Create the constraints and Indexes as Original Table with new names
CREATE INDEX MPCI5171.CES_VEHIHIST_P_NOINT_IDX01 ON MPCI5171.CESAR_VEHICLE_HISTORY_INT
(VEHICLE_NO_INT)
LOGGING
TABLESPACE MPCINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX MPCI5171.CES_VEHIHIST_P_VACT_IDX ON MPCI5171.CESAR_VEHICLE_HISTORY_INT
(VACTION)
LOGGING
TABLESPACE MPCINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE UNIQUE INDEX MPCI5171.CES_VEHI_HIST_P_PK ON MPCI5171.CESAR_VEHICLE_HISTORY_INT
(VEHICLE_NO_INT, TIMESTAMP)
LOGGING
TABLESPACE MPCINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX MPCI5171.IX1CESAR_VEHICLE_HISTORY_P ON MPCI5171.CESAR_VEHICLE_HISTORY_INT
(ACTION)
LOGGING
TABLESPACE MPCINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX MPCI5171.IX2CESAR_VEHICLE_HISTORY_P ON MPCI5171.CESAR_VEHICLE_HISTORY_INT
(VEHICLE_NO)
LOGGING
TABLESPACE MPCINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE MPCI5171.CESAR_VEHICLE_HISTORY_INT ADD (
CONSTRAINT CES_VEHI_HIST_PK_P PRIMARY KEY (VEHICLE_NO_INT, TIMESTAMP)
USING INDEX
TABLESPACE MPCINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));
Step 6: Gather Statistics on Interim table
EXEC DBMS_STATS.GATHER_TABLE_STATS('MPCI5171', 'CESAR_VEHICLE_HISTORY_INT', CASCADE => TRUE);
Step 7: Finish Redefinition Process
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
UNAME => 'MPCI5171',
ORIG_TABLE => 'CESAR_VEHICLE_HISTORY',
INT_TABLE => 'CESAR_VEHICLE_HISTORY_INT');
END;
/
At this point the interim table has become the "real" table and their names have been switched in the data dictionary. All that remains is to perform some cleanup operations
Step 8: Now drop the Interim table CESAR_VEHICLE_HISTORY_INT
DROP TABLE CESAR_VEHICLE_HISTORY_INT;
Step 9:Rename the Constraints and Indexes to match with the Original names
ALTER INDEX CES_VEHIHIST_P_NOINT_IDX01 RENAME TO CES_VEHIHIST_T_NOINT_IDX01;
ALTER INDEX CES_VEHI_HIST_P_PK RENAME TO CES_VEHI_HIST_T_PK;
ALTER INDEX IX1CESAR_VEHICLE_HISTORY_P RENAME TO IX1CESAR_VEHICLE_HISTORY_T;
ALTER INDEX IX2CESAR_VEHICLE_HISTORY_P RENAME TO IX2CESAR_VEHICLE_HISTORY_T;
Step 10: Once the Partitioning is implemented, the same can be tested by using the following SQL query :
SELECT PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='CESAR_VEHICLE_HISTORY':
This will return an output saying YES which means that the original table is partitioned:
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'CESAR_VEHICLE_HISTORY';
The above query returns the names of the Partitions for the original table.
5.2.1 Test Results
In the production environmen
Tuesday, December 30, 2008
Partitioning Using Dbms Redefinition
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment