Encoding ECC200 in Font PARTITIONING

Using Barcode drawer for Font Control to generate, create PDF 417 image in Font applications.

www.OnBarcode.com

If you continue this experiment up to 16 partitions, you would see the same effects for the ninth through the fifteenth partitions a skewing of the data to the interior partitions, away from the edges, and then upon hitting the sixteenth partition you would see a flattening-out again. The same would be true again up to 32 partitions, and then 64, and so on. This example just points out the importance of using a power of two as the number of hash partitions.

Using Barcode creation for Font Control to generate, create Data Matrix image in Font applications.

www.OnBarcode.com

List partitioning was a new feature of Oracle9i Release 1. It provides the ability to specify in which partition a row will reside, based on discrete lists of values. It is often useful to be able to partition by some code, such as a state or region code. For example, you might want to pull together in a single partition all records for people in the states of Maine (ME), New Hampshire (NH), Vermont (VT), and Massachusetts (MA), since those states are located next to or near each other, and your application queries data by geographic region. Similarly, you might want to group together Connecticut (CT), Rhode Island (RI), and New York (NY). You cannot use a range partition, since the range for the first partition would be ME through VT, and the second range would be CT through RI. Those ranges overlap. You cannot use hash partitioning since you cannot control which partition any given row goes into; the built-in hash function provided by Oracle does that. With list partitioning, we can accomplish this custom partitioning scheme easily: ops$tkyte@ORA10G> create table list_example 2 ( state_cd varchar2(2), 3 data varchar2(20) 4 ) 5 partition by list(state_cd) 6 ( partition part_1 values ( 'ME', 'NH', 'VT', 'MA' ), 7 partition part_2 values ( 'CT', 'RI', 'NY' ) 8 ) 9 / Table created. Figure 13-3 shows that Oracle will inspect the STATE_CD column and, based on its value, place the row into the correct partition. As we saw for range partitioning, if we try to insert a value that isn t specified in the list partition, Oracle will raise an appropriate error back to the client application. In other words, a list partitioned table without a DEFAULT partition will implicitly impose a constraint much like a check constraint on the table: ops$tkyte@ORA10G> insert into list_example values ( 'VA', 'data' ); insert into list_example values ( 'VA', 'data' ) * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition

Figure 13-3. List partition insert example If you want to segregate these seven states into their separate partitions, as we have, but have all remaining state codes (or, in fact, any other row that happens to be inserted that doesn t have one of these seven codes) go into a third partition, then we can use the VALUES ( DEFAULT ) clause. Here, we ll alter the table to add this partition (we could use this in the CREATE TABLE statement as well): ops$tkyte@ORA10G> alter table list_example 2 add partition 3 part_3 values ( DEFAULT ); Table altered. ops$tkyte@ORA10G> insert into list_example values ( 'VA', 'data' ); 1 row created. All values that are not explicitly in our list of values will go here. A word of caution on the use of DEFAULT: once a list partitioned table has a DEFAULT partition, you cannot add any more partitions to it: ops$tkyte@ORA10G> alter table list_example 2 add partition 3 part_4 values( 'CA', 'NM' ); alter table list_example * ERROR at line 1: ORA-14323: cannot add partition when DEFAULT partition exists

We would have to remove the DEFAULT partition, then add PART_4, and then put the DEFAULT partition back. The reason behind this is that the DEFAULT partition could have had rows with the list partition key value of CA or NM they would not belong in the DEFAULT partition after adding PART_4.