Figure 1-1 illustrates a join of two tables, orders_tbl and stock_tbl. There are 10 worker process available: orders_tbl has 10 partitions on 10 different physical devices and is the outer table in the join; stock_tbl is nonpartitioned. The worker processes have a problem with access contention on orders_tbl, but each worker process must scan stock_tbl. There may be physical I/O contention if the entire table does not fit into cache. In the worst case, 10 worker processes attempt to access the physical device on which stock_tbl resides. Avoid physical I/O contention by creating a named cache that contains the entire table stock_tbl.
Another way to reduce or eliminate physical I/O contention is to partition both orders_tbl and stock_tbl and distribute those partitions on different physical devices.
Figure 1-1: Joining tables on different physical devices
