The Office — Making It Work Well

Partition Additions

Partitioning Using Dbms Redefinition

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

About the Author

 

I have Windows XP Professional full version?

At the start of a full format and recovery there are 2 partitons: they are c drive and unpatitoned space. I want to create a d drive partition in addition to the c drive and unpartitioned space drives. How can I do it?

Choose the option unpartitioned space and it will guide you from there.

How to Install VirtualBox Guest Additions into Ubuntu and use CompizFusion in the VM!