"What I have written below is actually the notes I have taken when I tried to learn Oracle Partitioning, Table/Tablespace Compression and Partition Pruning. I know its not a technically advanced write up, but I guess it will help beginners like me to get started with this heavily vast topic"
Disclaimer:
Most of what written below has been taken from other websites. The examples mentioned in this blog worked out for me but I give no guarantee that it will work on another machine without proper study.
TABLE and TABLESPACE Compression
Table compression was introduced in Oracle 9i as a space saving feature for data warehousing projects. In 11g it is now considered a mainstream feature that is acceptable for OLTP databases. In addition to saving storage space, compression can result in increased I/O performance and reduced memory use in the buffer cache.
TABLESPACE with OLTP Compression
sql> CREATE TABLESPACE TB_COMPRESSED datafile '/home/oracle/app/oracle/oradata/TB_COMPRESSED.dbf' size 10000M DEFAULT COMPRESS FOR ALL OPERATIONS;
User Creation:
1. Since we created the tablespace with compression enabled, we can create an user on that tablespace.
sql> create user ORAUSER identified by perfomix_test default tablespace TB_COMPRESSED;
2. Once the user is created, necessary grants needs to be provided for the user for normal operations.
sql> grant CREATE TABLE, CREATE SESSION, CREATE TRIGGER, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE to ORAUSER;
3. Setting quota for the user.
sql> alter user ORAUSER quota unlimited on TB_COMPRESSED;
Oracle Partitioning
Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics.
From the perspective of a database administrator, a partitioned object has multiple pieces that can be managed either collectively or individually. This gives the administrator considerable flexibility in managing partitioned objects. However, from the perspective of the application, a partitioned table is identical to a non-partitioned table; no modifications are necessary when accessing a partitioned table using SQL queries and DML statements.
Partitioning can provide tremendous benefit to a wide variety of applications by improving performance, manageability, and availability. It is not unusual for partitioning to improve the performance of certain queries or maintenance operations by an order of magnitude. Moreover, partitioning can greatly simplify common administration tasks.
Oracle Partitioning offers three fundamental data distribution methods as basic partitioning strategies that control how data is placed into individual partitions:
Range Partitioning: This maps data to partitions based on ranges of values of the partitioning key that you establish for each partition. It is the most common type of partitioning and is often used with dates.
Hash Partitioning: This maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.
List Partitioning: This enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.
Here I am going to discuss more about RANGE partitioning in this blog.
Creating a Partitioned Table (Range Partitioning) with OLTP Compression:
Below shown is an example of Range Partitioning in Oracle. The CREATE query will have the clause "PARTITION BY RANGE(
field_name)" and the partitions are defined one-by-one with a distinctive name. A clause "VALUES LESS THAN" is mandatory for defining the partition end-points.
Example:
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER)
PARTITION BY RANGE(ORDER_DATE)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('01-JAN-2010', 'DD-MON-YYYY')) COMPRESS FOR ALL OPERATIONS,
PARTITION P2 VALUES LESS THAN(TO_DATE('01-FEB-2010', 'DD-MON-YYYY')) COMPRESS FOR ALL OPERATIONS,
PARTITION P3 VALUES LESS THAN(TO_DATE('01-MAR-2010', 'DD-MON-YYYY')) COMPRESS FOR ALL OPERATIONS,
PARTITION P4 VALUES LESS THAN(TO_DATE('01-APR-2010', 'DD-MON-YYYY')) COMPRESS FOR ALL OPERATIONS,
PARTITION P5 VALUES LESS THAN(TO_DATE('01-MAY-2010', 'DD-MON-YYYY')) COMPRESS FOR ALL OPERATIONS,
PARTITION P6 VALUES LESS THAN(TO_DATE('01-JUN-2010', 'DD-MON-YYYY')) COMPRESS FOR ALL OPERATIONS,
PARTITION P7 VALUES LESS THAN(TO_DATE('01-JUL-2010', 'DD-MON-YYYY')) COMPRESS FOR ALL OPERATIONS,
PARTITION P8 VALUES LESS THAN(TO_DATE('01-AUG-2010', 'DD-MON-YYYY')) COMPRESS FOR ALL OPERATIONS,
PARTITION P9 VALUES LESS THAN(TO_DATE('01-SEP-2010', 'DD-MON-YYYY')) COMPRESS FOR ALL OPERATIONS
);
This should create a table with 9 range partitions along with enabling OLTP compression.
Now we can insert some dummy values to the table for testing. As you can see, there is no changes with the INSERT queries even if its done against a partitioned table. Oracle manages the input data and store them into respective partitions.
sql> INSERT INTO SAMPLE_ORDERS values ('200', '05-FEB-2010', '200', '2000', '2222', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('300', '05-MAR-2010', '300', '3000', '3333', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('400', '05-APR-2010', '400', '4000', '4444', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('500', '05-MAY-2010', '500', '5000', '5555', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('600', '05-JUN-2010', '600', '6000', '6666', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('700', '05-JUL-2010', '700', '7000', '7777', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('800', '05-AUG-2010', '800', '8000', '8888', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('101', '02-JAN-2010', '100', '1000', '1111', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('201', '02-FEB-2010', '200', '2000', '2222', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('301', '02-MAR-2010', '300', '3000', '3333', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('401', '02-APR-2010', '400', '4000', '4444', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('501', '02-MAY-2010', '500', '5000', '5555', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('601', '02-JUN-2010', '600', '6000', '6666', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('701', '02-JUL-2010', '700', '7000', '7777', '1111');
sql> INSERT INTO SAMPLE_ORDERS values ('801', '02-AUG-2010', '800', '8000', '8888', '1111');
SELECT Queries:
Just like the INSERT queries, the SELECT queries can also be used without any kind of modifications relation to partitioning.
SQL> select * from SAMPLE_ORDERS
How ever, its also possible to SELECT data from specific partitions by giving the partition name.
SQL> select * from SAMPLE_ORDERS PARTITION(P2);
ORDER_NUMBER ORDER_DAT CUST_NUM TOTAL_PRICE TOTAL_TAX TOTAL_SHIPPING
------------ --------- ---------- ----------- ---------- --------------
101 02-JAN-10 100 1000 1111 1111
SQL> select * from SAMPLE_ORDERS PARTITION(P3);
ORDER_NUMBER ORDER_DAT CUST_NUM TOTAL_PRICE TOTAL_TAX TOTAL_SHIPPING
------------ --------- ---------- ----------- ---------- --------------
200 05-FEB-10 200 2000 2222 1111
201 02-FEB-10 200 2000 2222 1111
Checking on Partition and Compression Details
Since we created the partitions using "COMPRESS FOR ALL OPERATIONS", its ideal for heavy-transaction databases like OLTP. We can find the details of the Partitions and Compression details using the below query:
SQL> SELECT partition_name, compression, compress_for FROM user_tab_partitions;
PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
P1 ENABLED OLTP
P2 ENABLED OLTP
P3 ENABLED OLTP
P4 ENABLED OLTP
P5 ENABLED OLTP
P6 ENABLED OLTP
P7 ENABLED OLTP
P8 ENABLED OLTP
P9 ENABLED OLTP
Modifying Partitions in a table
Adding a new partition:
sql> alter table tempabc add partition p2 values less than ('C%');
Note:
- You cannot move a partition with sub-partitions
- Each subpartition must be individually moved.
- We can add a partition to the high end of the table (after the last existing partition). If the first element of the partition bound of the high partition is MAXVALUE, you cannot add a partition to the table. You must split the high partition.
- You can add a partition to a table even if one or more of the table indexes or index partitions are marked UNUSABLE.
- You must use the SPLIT PARTITION clause to add a partition at the beginning or the middle of the table.
- ALTER TABLE DROP PARTITION drops a partition and its data. If you want to drop a partition but keep its data in the table, you must merge the partition into one of the adjacent partitions.
- If you drop a partition and later insert a row that would have belonged to the dropped partition, the row will be stored in the next higher partition. However, if you drop the highest partition, the insert will fail because the range of values represented by the dropped partition is no longer valid for the table.
Ways to partition an existing table
Method 1:
- create a PARTITIONED table
- load the NON-PARTITIONED data into the PARTITIONED table
- drop the NON-PARTITIONED table
- rename PARTITIONED table to whatever is appropriate
Method 2:
- export table and data
- drop table
- create new partitioned table the way you want it
- import table data into new partitioned table (this should also create all the necessary indexes and constraints from your original table)
Partition Pruning
Semantics-based partitioning can allow Adaptive Server to eliminate certain partitions when performing a search. Range-based partitions, for example, contain rows whose partitioning keys are discrete value sets. When a query predicate—a where clause—is based on those partitioning keys, Adaptive Server can quickly ascertain whether rows in a particular partition can satisfy the query. This behavior is called partition pruning, or partition elimination, and it can save considerable time and resources during execution.
For range and list partitioning – Adaptive Server can apply partition pruning on equality (=) and range (>, >=, <, and <=) predicates on partition-key columns on a single table.
Sample Queries:
Example 1:
sql> select * from sample_orders WHERE ORDER_DATE BETWEEN TO_DATE ('01-JAN-2010','DD-MON-YYYY') AND TO_DATE('01-MAR-2010','DD-MON-YYYY');
Result:
ORDER_NUMBER ORDER_DAT CUST_NUM TOTAL_PRICE TOTAL_TAX TOTAL_SHIPPING
------------ --------- ---------- ----------- ---------- --------------
100 05-JAN-10 100 1000 1111 1111
101 02-JAN-10 100 1000 1111 1111
200 05-FEB-10 200 2000 2222 1111
201 02-FEB-10 200 2000 2222 1111
Example 2:
SQL> select * from sample_orders where ORDER_DATE > ('01-JUN-2010');
Result:
ORDER_NUMBER ORDER_DAT CUST_NUM TOTAL_PRICE TOTAL_TAX TOTAL_SHIPPING
------------ --------- ---------- ----------- ---------- --------------
600 05-JUN-10 600 6000 6666 1111
601 02-JUN-10 600 6000 6666 1111
700 05-JUL-10 700 7000 7777 1111
701 02-JUL-10 700 7000 7777 1111
800 05-AUG-10 800 8000 8888 1111
801 02-AUG-10 800 8000 8888 1111
More Examples:
Partition on a numeric value range:
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(30),
sal NUMBER
)
PARTITION BY RANGE(empno) (
partition e1 values less than (1000) tablespace ts1,
partition e2 values less than (2000) tablespace ts2,
partition e3 values less than (MAXVALUE) tablespace ts3
);
Partition on a VARCHAR2 string:
CREATE TABLE emp
( id NUMBER(5) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
phone VARCHAR2(15),
email VARCHAR2(100)
)
PARTITION BY RANGE ( name )
(
PARTITION p1 VALUES LESS THAN ('L') TABLESPACE ts1,
PARTITION p2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts2
);
Time based range partitioning:
CREATE TABLE t1 (id NUMBER, c1 DATE)
PARTITION BY RANGE (c1)
(PARTITION t1p1 VALUES LESS THAN (TO_DATE('2007-11-01', 'YYYY-MM-DD')),
PARTITION t1p2 VALUES LESS THAN (TO_DATE('2007-12-01', 'YYYY-MM-DD')),
PARTITION t1p3 VALUES LESS THAN (TO_DATE('2008-01-01', 'YYYY-MM-DD')),
PARTITION t1p4 VALUES LESS THAN (MAXVALUE)
);
