About
Sometimes it may not be possible to define the ranges of—or a set of known values for—each partition.
Articles Related
Syntax
To create the CUST example table with four hash partitions:
create table cust (
cust_id number,
cust_name varchar2(20)
)
partition by hash (cust_id)
partitions 4;
Example
Customer Table
A typical example is a table, CUST, containing customers, with the CUST_ID column as the primary key. The CUST_ID value is an incrementally increasing but rather meaningless number, so a range partition based on these values may also be rather meaningless.
You can partition the CUST table by using hash, where a hash function is applied to the partition key of each row and, based on the output, the row is placed in an appropriate partition. All the hash partitions hold an equal number of rows.
Fact / Dim table join joined on id sequence
If the tables are not joined on some specific date or date fields but rather on some sequential number.
- a list-partitioning strategy (which requires discrete, finite values) can be chosen
- a range partition strategy would be ineffective, because the sequentially increasing values in these two columns would require numerous partitions to be created that might not have any specific meaning.
In such a case, the hash partition is useful. It makes sure the records are evenly spread over all the partitions. You can create the tables as follows:
create table trans (
trans_id number,
trans_dt date,
product_code number,
store_id number,
trans_amount number(12,2)
)
partition by hash (trans_id)
(
partition trans1 tablespace trans1,
partition trans2 tablespace trans2,
partition trans3 tablespace trans3,
partition trans4 tablespace trans4
)
/
create table advertising (
ad_id number,
ad_dt date,
product_code number,
ad_cost number,
channel varchar2(10)
)
partition by hash (ad_id)
(
partition ad1 tablespace ad1,
partition ad2 tablespace ad2,
partition ad3 tablespace ad3,
partition ad4 tablespace ad4
)
/
After the tables are created, check the explain plan to make sure the partitions are used properly.
Results from hash-partitioned tables
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 2303213640
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 11 (19) | 00:00:01 | | |
| 1 | HASH GROUP BY | | 1 | 78 | 11 (19) | 00:00:01 | | |
| * 2 | HASH JOIN | | 1 | 78 | 10 (10) | 00:00:01 | | |
| 3 | PARTITION HASH SINGLE | | 1 | 39 | 4 (0) | 00:00:01 | 4 | 4 |
| * 4 | TABLE ACCESS FULL |TRANS | 1 | 39 | 4 (0) | 00:00:01 | 4 | 4 |
| 5 | PARTITION HASH SINGLE | | 1 | 39 | 5 (0) | 00:00:01 | 4 | 4 |
| * 6 | TABLE ACCESS FULL |ADVERTISING| 1 | 39 | 5 (0) | 00:00:01 | 4 | 4 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------------------------------------------------------------
2 - access("T"."TRANS_ID"="A"."AD_ID" AND "T"."PRODUCT_CODE"="A"."PRODUCT_CODE")
4 - filter("T"."TRANS_ID"=101)
6 - filter("A"."AD_ID"=101)
Note the PSTART and PSTOP columns, which indicate which table partitions were selected. For the line with Id=4, the values of these columns are 4 and 4, which indicates that the fourth partition of the TRANS table was selected, where the record with TRANS_ID=101 resides. Note the line with Id=6, which shows that the optimizer will search only partition 4 of the joined ADVERTISING table as well, not the entire table. In the absence of an index, this hash-partition strategy makes the PM's query much faster than a full-table scan on the entire table, for both the TRANS and ADVERTISING tables.