17.2 Tricks with Partitions

---

Why is this section entitled "Tricks with Partitions"? Partitions can have things done to them on an individual basis. For instance, without partitioning if one wanted to remove all of the rows for the year 1999 from the GeneralLedger table the entire GeneralLedger table would be have to full table scanned.

DELETE FROM GeneralLedger WHERE TO_CHAR(dte,'YYYY') =    1999;

Using the partition table GLP, created as a date range partition on the GeneralLedger table, one would not even have to full table scan one-fifth of the rows: simply drop the partition. Dropping a single partition one-fifth the size of the table is much faster than removing 20% of the rows from the nonpartitioned table.

What are some other actions which can be performed specifically with partitions, beneficial to general database performance?

• Add, Drop, and Truncate.   Individual partitions can be added, dropped, or even truncated without affecting the rest of the partitions in a table.

• Split and Merge.   A partition can be split into two partitions or two partitions can be merged into a single partition. Once again there is no effect on rows outside of the partitions being split or merged.

• Rename.   Individual partitions can be renamed.

• Move.   A partition can be moved into a separate tablespace with the speed of a Unix mv (move) command, akin to renaming the file pointer for the datafile. Changing a file pointer is a simple tablespace header change. No costly SQL code is involved in transferring data between tablespaces.

• Exchange.   Partitions and subpartitions can be converted into tables and vice versa. Again no costly SQL code is involved in transferring data.

To reiterate, all of these listed actions will not affect the rest of the partitions in a partitioned table since they operate on specific partitions. Only specified partitions are read or accessed.

The important point to note about partitioning is that it is a tuning method in itself. There are obviously ways to tune partitions individually. For instance, different partitions and their related indexes can have individual partition physical storage attributes. Storage tuning is covered in other chapters of this book. Additionally there are actions which can be taken on individual partitions such as moving and exchanging. Equivalent actions on nonpartitioned tables would either be impossible or involve intense SQL activity. These special partition actions cannot be done nearly as efficiently with large nonpartitioned tables. Partitioning has been presented in this chapter as a tuning technique in itself.

This is all that needs to be discussed with respect to partitioning and tuning. This chapter completes the analysis of physical and configuration tuning for Oracle Database. The next chapter will start to examine how to uncover performance problems by examining use of ratios to help detect performance problems.

---