10.5 Specifying Partitions

When you are writing SQL against partitioned tables, you have the option to treat the partitions as single, virtual tables, or to specify partition names within your SQL statements. If you write DML against a virtual table, the Oracle optimizer determines the partition or partitions that need to be involved. For an INSERT statement, the optimizer uses the values provided for the partition key to determine where to put each row. For UPDATE, DELETE, and SELECT statements, the optimizer uses the conditions from the WHERE clause along with information on local and global indexes to determine the partition or partitions that need to be searched.

If you know that your DML statement will utilize a single partition, and you know the name of the partition, you can use the PARTITION clause to tell the optimizer which partition to use. For example, if you want to summarize all orders for the year 2000, and you know that the cust_order table is range-partitioned by year, you could issue the following query:

SELECT COUNT(*) tot_orders, SUM(sale_price) tot_sales

FROM cust_order PARTITION (orders_2000)

WHERE cancelled_dt IS NULL;

This query's WHERE clause doesn't specify a date range, even though the table contains data spanning multiple years. Because you specified the orders_2000 partition, you know that the query will only summarize orders from 2000, so there is no need to check each order's date.

If your table is composite-partitioned, you can use the SUBPARTITION clause to focus on a single subpartition of the table. For example, the following statement deletes all rows from the orders_2000_s1 subpartition of the range-hash composite-partitioned version of the cust_order table:

DELETE FROM cust_order SUBPARTITION (orders_2000_s1);

You can also use the PARTITION clause to delete the entire set of subpartitions that fall within a given partition:

DELETE FROM cust_order PARTITION (orders_2000);

This statement would delete all rows from the orders_2000_s1, orders_2000_s2, orders_2000_s3, and orders_2000_s4 subpartitions of the cust_order table.

Here are a few additional things to consider when working with partitioned tables:

• If the optimizer determines that two or more partitions are needed to satisfy the WHERE clause of a SELECT, UPDATE, or DELETE statement, the table and/or index partitions may be scanned in parallel. Therefore, depending on the system resources available to Oracle, scanning every partition of a partitioned table could be much faster than scanning an entire unpartitioned table.

• Because hash partitioning spreads data randomly across the partitions,^[1] we don't believe you would ever want to use the PARTITION clause for hash-partitioned tables or the SUBPARTITON clause for range-hash partitioned tables, since you don't know what data you are working on. The only reasonable scenario that comes to mind might be when you want to modify every row in the table, but you don't have enough rollback available to modify every row in a single transaction. In this case, you can perform an UPDATE or DELETE on each partition or subpartition and issue a COMMIT after each statement completes.

  ^[1] It isn't actually random, but it will seem that way to you, since you don't have access to the hash function.

• Partitions can be merged, split, or dropped at any time by the DBA. Therefore, use caution when explicitly naming partitions in your DML statements. Otherwise, you may find your statements failing, or worse, your statements might work on the wrong set of data because partitions have been merged or split without your knowledge. You may want to check with your DBA to determine her policy concerning naming partitions in your DML statements.

If you need to access a single partition or subpartition but don't like having partition names sprinkled throughout your code, consider creating views to hide the partition names, as in the following:

CREATE VIEW cust_order_2000 AS

SELECT *

FROM cust_order PARTITION (orders_2000);

You can then issue your SQL statements against such views:

SELECT order_nbr, cust_nbr, sale_price, order_dt

FROM cust_order_2000

WHERE quantity > 100;