This page is based on examples to be easier to follow. The Oracle Partition method gives you an opportunity to organize your table’s data for big Oracle tables and split the data between different partitions.
Before you are going to start trying out the Oracle Partition By operators make sure your Oracle database version has all necessary installed. Most Oracle 10g and 11g versions have Oracle Partition enabled by default but there are versions where it isn’t. Log on as SYS user and run the following command:
SELECT *
FROM v$option
WHERE parameter = 'Partitioning';
As you see in this Oracle XE version the Partitioning option is disabled (“FALSE“) and you would need to install additional database scripts. Make sure you’ll make a backup before running this scripts. Also it is better to shutdown the Oracle listener and have users logged out. The Oracle partition scripts can be found under directory $ORACLE_HOME/rdbms/lib and you can install them as the next commands:
$ 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 examples.
The first example we are going to use table “SALES“. It contains daily basis sales information and every day we are working with data depending on the sale dates. Usually Oracle DBA has created Oracle table as follow:
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
);
P.S. We are trying to keep the example as simple as possible, so we are using only 4 columns. But in a normal situation the table has much more columns and it would be classified as a “big” table. There is a column named “SALE_MONTH” that is character type and has value as “SALE_DATE” only with using to_char mask “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') );
In this example above we are using column “SALE_MONTH” and not “SALE_DATE“; the reason is in the partitions – they are NOT getting created or dropped automatically. You have to do it manually using a PL/SQL or SQL script. If the amount of lines are huge daily basis then it makes sense to use daily partitioned table. We do recommend to use instead of DATE type VARCHAR2 types and the value is converted from date.
Important to know:
While you executed the CREATE TABLE statement above and it returned error “ORA-00439: feature not enabled: Partitioning” as on the picture below then go back to the top of this page and try the Partitioning parameter. If the parameter is TRUE then something went wrong with the installation scripts other wise try to install the Partitioning scripts.
Another common mistake is to forgot add brackets to the PARTITION BY LIST (<table_column>) keywords. In that case you will see error “ORA-00906: missing left parenthesis”. As on the picture below.
The second common mistake would be to forgot to declare partition values or misspell the VALUES keywords. The error is “ORA-00926: missing VALUES keyword” as on the next picture.
The next example is about showing that the table partitioning can use more than one value in one partition. In case, when data amount is not so big to have a separate partition. There is an opportunity to add more then one value into the table partition. Using the table above we realized that four month in a partition is good enough to 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'));
Now the table has only three partitions and the values are declared for each partition.
When table data doesn’t let us to chose certain values then the following 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)
);
Now column “PRODUCT_ID” 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 Online Tech Support described – Oracle table partitioning should be done depending on business needs and the column’s data for partitioning should be taken as more unchangeable. The column data shouldn’t be constantly updated or it would be better to chose another column that still covers your business needs.
See Also:
Home Oracle Select
