Databases Reference
In-Depth Information
QUANTITY_SOLD NUMBER(10,2) NOT NULL,
AMOUNT_SOLD NUMBER(10,2) NOT NULL)
PARTITION BY LIST (CHANNEL_ID)
(
PARTITION DIRECT_SALES VALUES (3,9) TABLESPACE EXAMPLE,
PARTITION INDIRECT_SALES VALUES (4,5) TABLESPACE EXAMPLE,
PARTITION OTHER_SALES VALUES (DEFAULT) TABLESPACE EXAMPLE
);
We have defined two partitions to store direct and indirect sales, respectively and a default
partition to store the rows whose partition key is not listed in the partitions mentioned earlier.
We can add partitions to a table, but if we have defined a default partition when applying list
partitioning, we have to split the default partition instead of adding a new partition:
ALTER TABLE SALES_LP SPLIT PARTITION OTHER_SALES
VALUES (2) INTO (PARTITION PARTNERS, PARTITION OTHER_SALES);
With the previous statement, we have split the
OTHER_SALES
partition, creating a new
PARTNERS
partition to store the sales made by partners, leaving the other kind of sales in the
default
OTHER_SALES
partition. To test this assertion, we can insert two rows and query the
single partition of the table to see if each row is in the correct partition, as follows:
INSERT INTO SALES_LP VALUES (1,2,SYSDATE,2,4,5,6);
INSERT INTO SALES_LP VALUES (1,2,SYSDATE,19,4,5,6);
SELECT * FROM SALES_LP PARTITION (PARTNERS);
SELECT * FROM SALES_LP PARTITION (OTHER_SALES);
Hash partitioning
We have seen list and range partitioning. The first fits well when we have a discrete number
of values in the partition key, so we can easily split the table based on this criteria. The
second one helps us when we have distinct range of values—for example, for historical data
partitioned by a date, as in our example.
But can we partition a table when we aren't in the earlier mentioned situations? The answer
is yes, using hash partitioning.
This time we divide our table based on a hash function computed over the partition key, to
distribute table values without using a policy inherent in the data, ensuring only that the data
will be distributed in partitions.
To obtain the best uniform data distribution, it's better to choose a number of partitions which
is a power of 2, having a unique or near partition key.
