How To Use Table Partition In Oracle

How To Use Table Partition In Oracle

July 06, 2020
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This online tutorial page is based on examples to make it easier to follow. The Oracle Partition option gives you an opportunity to organize your tables that stores huge amount data and split the data between different smaller partitions.

Before you are starting try out the Oracle Partition By option and see if your Oracle database version has all necessary installed for the table partitioning. Most Oracle 9i, 10g and 11g versions have Oracle Partition by default enabled but there are versions that doesn’t have it. Log on as user SYS and run the following SQL select:

 SELECT *
   FROM v$option
  WHERE parameter = 'Partitioning';

database management system database oracle performance tuning online it degree online oracle partition oracle partition by oracle dba oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

For example this Oracle database version has the Partitioning option disabled (“FALSE“) and you would need to install additional database scripts to make it to work. Make sure you will have a backup of your system before running those scripts. Also for protection it is good to shutdown the Oracle listener and to make sure all users have logged out. You can find The Oracle partition scripts can under directory $ORACLE_HOME/rdbms/lib and use the following commands to install them:

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk part_on
$ make -f ins_rdbms.mk ioracle

If your installation was successful you should see the Partitioning parameter showing “TRUE“. Now you are good to continue with the following.

In this first example we are going to use table “SALES“. It contains daily basis sales information and in this sample task we need a data of daily because the sales can be measured depending on the days. In cases like this our Oracle DBA has usually created Oracle table as the following:

CREATE TABLE sales
       ( id         NUMBER(17) NOT NULL,
         sale_date  DATE NOT NULL,
         sale_month VARCHAR2(6) NOT NULL,
         product_id NUMBER(17) NOT NULL
        );

database management system database oracle performance tuning online it degree online oracle partition oracle partition by oracle dba oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

P.S. Our Oracle DBA is trying to keep this example as simple as possible, so we are using only 4 columns. But in a normal situation the table has more columns and it would be classified as a “big” table. Take a look at the script above there is a column named “SALE_MONTH” that is character type and has value from the “SALE_DATE” column and the value is converter to character with Oracle to_char mask as “YYYYMM”.

Since the sale dates are the main segments in this example then creating partitioned table in Oracle would look like following:

CREATE TABLE sales
      ( id         NUMBER(17) NOT NULL,
        sale_date  DATE NOT NULL,
        sale_month VARCHAR2(6) NOT NULL,
        product_id NUMBER(17) NOT NULL
      )
PARTITION BY LIST (sale_month)
 (PARTITION sale_201201 VALUES ('201201'),
  PARTITION sale_201202 VALUES ('201202'),
  PARTITION sale_201203 VALUES ('201203'),
  PARTITION sale_201204 VALUES ('201204') );

database management system database oracle performance tuning online it degree online oracle partition oracle partition by oracle dba oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

In the example above we are using column “SALE_MONTH” and notSALE_DATE“; the reason is in the partitions structure – they are NOT getting created or dropped automatically. You have to do it manually and using a PL/SQL or SQL script. If the amount of lines are per day huge then it makes more sense to use a daily partitioned table. We do recommend to use instead of DATE type VARCHAR2 types and the value is converted from date. The problem lies in the date type that contains time and that makes the partitioning more complicated, because you need to start using ranges.

Important to know:

While you executed the CREATE TABLE statement above and the statement returned error “ORA-00439: feature not enabled: Partitioning” as on the picture below then please go back to the top of this page and try the Partitioning parameter query. If the parameter is TRUE then something went wrong with the installation using scripts other wise try to install the Partitioning again.

database management system database oracle performance tuning online it degree online oracle partition oracle partition by oracle dba oracle ORA-00439: feature not enabled: Partitioning personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

Another common mistake is to forgot add brackets to keywords PARTITION BY LIST (<table_column>). In that case your statement will raise error “ORA-00906: missing left parenthesis” as also on the picture below.

database management system database oracle performance tuning online it degree online oracle partition oracle partition by oracle dba oracle ORA-00906: missing left parenthesis personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

The second common mistake is to forget to declare partition values or misspell keyword VALUES . The error message is “ORA-00926: missing VALUES keyword“. See also the error on the next picture.

database management system database oracle performance tuning online it degree online oracle partition oracle partition by oracle dba oracle ORA-00926: missing VALUES keyword personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

In the next Oracle DBA example we will show that one table partition name can use more than one value in one partition. This method is good when your data amounts are not so huge to need to be in a separate partitions. There is an opportunity to add more then one value into one table partition. Using the table above we realised that four month in one partition is good enough to a good performance.

CREATE TABLE sales
 ( id         NUMBER(17)  NOT NULL,
   sale_date  DATE        NOT NULL,
   sale_month VARCHAR2(6) NOT NULL,
   product_id NUMBER(17)  NOT NULL
 )
PARTITION BY LIST (sale_month)
 (PARTITION sale_month_2012_1 VALUES ('201201','201202','201203','201204'),
  PARTITION sale_month_2012_2 VALUES ('201205','201206','201207','201208'),
  PARTITION sale_month_2012_3 VALUES ('201209','201210','201211','201212'));

database management system database oracle performance tuning online it degree online oracle partition oracle partition by oracle dba oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

Now with the script above we do have left only three partitions in the table and each partition has set four values.

When the data type doesn’t let us to chose certain values like for example date type with the time then the following Oracle DBA example shows us how to use partition ranges. For example we would like to create partitions by product types and every product type starts with a certain number. Food is 1000000001, Drinks 2000000001, Books 3000000001 etc.

CREATE TABLE sales
 ( id         NUMBER(17)  NOT NULL,
   sale_date  DATE        NOT NULL,
   sale_month VARCHAR2(6) NOT NULL,
   product_id NUMBER(17)  NOT NULL
 )
PARTITION BY RANGE (product_id)
 (PARTITION sale_1 VALUES LESS THAN (2000000000),
  PARTITION sale_2 VALUES LESS THAN (3000000000),
  PARTITION sale_3 VALUES LESS THAN (4000000000)
);

database management system database oracle performance tuning online it degree online oracle partition oracle partition by oracle dba oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

The “PRODUCT_ID” column will be added to different partition depending on their value. For example into partition “SALE_1” goes all PRODUCT_IDs who are smaller than 2000000000.

In short, as Oracle DBA described on this page – Oracle table partitions should be created depending on the business needs and the column’s data for partitioning should be taken as more unchangeable or constant. The column data you have chosen to become partitioned shouldn’t be constantly updated or changed. Otherwise it would be better to chose some another column that still covers your business needs.

See Also:
Oracle Select Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*