The example in Figure 5-1 illustrates a join of two partitioned tables, orders_tbl and stock_tbl. There are ten worker process available: orders_tbl has ten partitions on ten different physical devices and is the outer table in the join; stock_tbl is nonpartitioned. The worker processes will not have a problem with access contention on orders_tbl, but each worker process must scan stock_tbl. There could be a problem with physical I/O contention if the entire table does not fit into a cache. In the worst case, ten worker processes attempt to access the physical device on which stock_tbl resides. You can 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 5-1: Joining tables on different physical devices
