Partitioning Overview-Partitioning
Partitioning facilitates the management of very large tables and indexes using divide and conquer logic. Partitioning introduces the concept of a partition key that is used to segregate data based on a certain range value, a list of specific values, or the value of a hash function. If I were to put the benefits of partitioning in some sort of order, it would be as follows:
\ 1.\ Increases availability of data: This attribute is applicable to all system types, be they OLTP or warehouse systems by nature.
\ 2.\ Eases administration of large segments by removing them from the database: Performing administrative operations on a 100GB table, such as a reorganization to remove migrated rows or to reclaim “whitespace” left in the table after a purge of old information, would be much more onerous than performing the same operation ten times on individual 10GB table partitions. Additionally, using partitions, we might be able to conduct a purge routine without leaving whitespace behind at all, removing the need for a reorganization entirely!
\ 3.\ Improves the performance of certain queries: This is mainly beneficial in a large warehouse environment where we can use partitioning to eliminate large ranges of data from consideration, avoiding accessing this data at all. This will not be as applicable in a transactional system, since we are accessing small volumes of data in that system already.
\ 4.\ May reduce contention on high-volume OLTP systems by spreading out modifications across many separate partitions: If you have a segment experiencing high contention, turning it into many segments could have the side effect of reducing that contention proportionally.
Let’s take a look at each of these potential benefits of using partitioning.
Increased Availability
Increased availability derives from the independence of each partition. The availability (or lack thereof) of a single partition in an object does not mean the object itself is unavailable. The optimizer is aware of the partitioning scheme that is in place and will remove unreferenced partitions from the query plan accordingly. If a single partition is unavailable in a large object, and your query can eliminate this partition from consideration, then Oracle will successfully process the query.
To demonstrate this increased availability, we’ll set up a hash partitioned table with two partitions, each in a separate tablespace. We’ll create an EMP table that specifies a partition key on the EMPNO column; EMPNO will be our partition key. In this case, this structure means that for each row inserted into this table, the value of the EMPNO column is hashed to determine the partition (and hence the tablespace) into which the row will be placed. First, we create two tablespaces (P1 and P2) and then a partitioned table with two partitions (PART_1 and PART_2), with one partition in each tablespace:
Note The tablespaces in this example use Oracle Managed Files with the initialization parameter DB_CREATE_FILE_DEST set to /opt/oracle/oradata.
$ sqlplus eoda/foo@PDB1
SQL> create tablespace p1 datafile size 1m autoextend on next 1m; Tablespace created.
SQL> create tablespace p2 datafile size 1m autoextend on next 1m; Tablespace created.
Next, we insert some data into the EMP table and then, using the partition-extended table name, inspect the contents of each partition:
SQL> insert into emp select empno, ename from scott.emp; 14 rows created.
SQL> select * from emp partition(part_1);
You should note that the data is somewhat randomly assigned. That is by design here. Using hash partitioning, we are asking Oracle to randomly—but hopefully evenly— distribute our data across many partitions. We cannot control the partition into which data goes; Oracle decides that based on hashing the hash key value itself. Later, when we look at range and list partitioning, we’ll see how we can control what partitions receive which data.
Now, we take one of the tablespaces offline (simulating, e.g., a disk failure), thus making unavailable the data in that partition:
SQL> alter tablespace p1 offline;Tablespace altered.
Next, we run a query that hits every partition, and we see that this query fails:
SQL> select * from emp;select * from emp
However, a query that does not access the offline tablespace will function as normal; Oracle will eliminate the offline partition from consideration. I use a bind variable in this particular example just to demonstrate that even though Oracle does not know at query optimization time which partition will be accessed, it is nonetheless able to perform this elimination at runtime:
SQL> variable n number
SQL> exec :n := 7844;
PL/SQL procedure successfully completed.
In summary, when the optimizer can eliminate partitions from the plan, it will. This fact increases availability for those applications that use the partition key in their queries.
Partitions also increase availability by reducing downtime. If you have a 100GB table, for example, and it is partitioned into 50 2GB partitions, then you can recover from errors that much faster. If one of the 2GB partitions is damaged, the time to recover is now the time it takes to restore and recover a 2GB partition, not a 100GB table. So availability is increased in two ways:
•\ Partition elimination by the optimizer means that many users maynever even notice that some of the data was unavailable.
•\ Downtime is reduced in the event of an error because of thesignificantly reduced amount of work that is required to recover.