The partitioning feature of Oracle Database enables you to partition stored data segments such as tables and indexes for easier management and improved performance. Oracle Database provides many types of partitioning options, including range, list, hash, range/list, and range/hash.
Partitioning, in a nutshell, stores a data segment such as a table as multiple segments
pname VARCHAR2(20),
trans_amt number,
sales_dt date,
state_code varchar2(2)
)
partition by list (state_code)
(
partition ct values ('AP'),
partition ca values ('TN'),
partition def values (default)
);
How to retrieve the values?
SELECT *
FROM XXSALES PARTITION (CT);
it is also useful for purging operations
Data archiving. If you decide to purge data without retaining it, you can use the technique . Data to purge without retention includes log or debug information, which can simply be dropped. Most information, however, probably needs to be archived for future access. In the TRANS table example, if you wanted to store the contents of the Y05Q1 partition before purging it, you could use the following partition exchange technique:
1. Create a table that is almost identical in structure to the TRANS table, except that it is not partitioned:
create table xxsales_y05q1 as
select * from sales
where 1=2;
2. This creates an empty unpartitioned table XXSALES_Y05Q1, whose structure is identical to that of sales. The 1=2 clause returns false, so no rows of the TRANS table are transferred. Exchange the contents of the partition with this new table:
alter table trans
exchange partition sales
with table xxsales_y05q1;
This operation makes the data inside the Y05q1(which is index on sales table) partition appear inside the XXSALES_Y05Q1 table and empties the partition. The data does not physically move from the partition to the new table. This exchange partition statement merely updates the data dictionary to reset a pointer from the partition to the table and vice versa. Because there is no physical movement of data, this exchange does not generate redo and undo, making it faster and far less likely to impact performance than traditional data-movement approaches such as INSERT.
After the EXCHANGE PARTITION command is executed, the partition is empty and you can drop it, as shown earlier. After the table is created, you can transport it out of the database and archive it for future use.
