Not every large table divides naturally by date. Customers divide by region, orders by country, events by type. List partitioning assigns each row to a partition by a list of key values, with a DEFAULT partition for the rest. And when there is no natural key at all, hash partitioning spreads rows evenly over a fixed number of partitions.
Code for This Guide
The main examples are in the examples/special-tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
partition by list (column) ( partition name values (value [, ...]), ... [partition name values (default)]) partition by hash (column) partitions n
List and Hash Partitions
The example creates a customer table partitioned by country into Asia, Europe, and the rest, and a ticket table hashed into four partitions.
Example:
create table customers_by_region (customer_id number, country_code char(2))
partition by list (country_code) (
partition p_asia values ('IN', 'SG', 'HK', 'JP', 'NP'),
partition p_europe values ('GB', 'FR', 'DE', 'NL', 'TR', 'IE'),
partition p_other values (default)
);
insert into customers_by_region select customer_id, country_code from customers;
create table tickets_hashed (ticket_id number, fare number)
partition by hash (ticket_id) partitions 4;
insert into tickets_hashed select ticket_id, fare from tickets;
exec dbms_stats.gather_table_stats(user, 'CUSTOMERS_BY_REGION')
exec dbms_stats.gather_table_stats(user, 'TICKETS_HASHED')
select table_name, partition_name, num_rows
from user_tab_partitions
where table_name in ('CUSTOMERS_BY_REGION', 'TICKETS_HASHED')
order by table_name, partition_position;Output:
Table CUSTOMERS_BY_REGION created. 120 rows inserted. Table TICKETS_HASHED created. 1,069 rows inserted. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. TABLE_NAME PARTITION_NAME NUM_ROWS ______________________ _________________ ___________ CUSTOMERS_BY_REGION P_ASIA 28 CUSTOMERS_BY_REGION P_EUROPE 40 CUSTOMERS_BY_REGION P_OTHER 52 TICKETS_HASHED SYS_P1642 248 TICKETS_HASHED SYS_P1643 260 TICKETS_HASHED SYS_P1644 281 TICKETS_HASHED SYS_P1645 280 7 rows selected.
The list partitions hold 28, 40, and 52 customers. The hash partitions are close to equal, between 248 and 281 tickets each. Drop the tables afterward with drop table customers_by_region purge and drop table tickets_hashed purge.
Without a DEFAULT Partition
A row whose key is in no list has nowhere to go:
Example:
insert into customers_two_regions values (1, 'IN'); insert into customers_two_regions values (2, 'US'); -- no partition for US
Output:
1 row inserted. Error starting at line : 2 In command - insert into customers_two_regions values (2, 'US') Error at Command Line : 2 Column : 13 Error report - SQL Error: ORA-14400: no partition found in table NIMBUS.CUSTOMERS_TWO_REGIONS for inserted partition key "US"
Add a DEFAULT partition, or add the value to a list with ALTER TABLE ... MODIFY PARTITION ... ADD VALUES.
Which Method
| Method | Use it when |
|---|---|
| Range or interval | The key is ordered, usually a date, and queries ask for ranges |
| List | The key has a known set of values, such as regions or types |
| Hash | There is no natural grouping, but the table must be divided evenly |
| Composite | Two levels are useful, such as range by month and hash by customer |
Use a power of two, such as 4, 8, or 16, for the number of hash partitions; other numbers give unevenly sized partitions.
Related Guides
Conclusion
List partitioning assigns rows by explicit values, with DEFAULT for the rest; hash partitioning spreads rows evenly when there is no natural grouping. Choose the method by how queries filter the table, and plan for values that fit no list.
