Since the days of Oracle 6, the optimizer has used three primary ways to join row sources together: the nested loops join, the sort-merge join, and the cluster join. (There is also the favorite of the ad-hoc query user—the Cartesian join.) Oracle 7.3 introduced the hash join, and Oracle 8i introduced the index join, making for a total of five primary join methods. Each method has a unique set of features and limitations. Before you attack a potential join issue, you need to know the answers to the following questions:
- Which table will drive the query (first table accessed), and when will other tables be accessed given the path that is chosen for the query? What are the alternate driving paths?
- What are the Oracle join possibilities (described in this section)? Remember, each join possibility for Oracle can yield different results, depending on the join order, the selectivity of indexes, and the available memory for sorting and/or hashing.
- Which indexes are available, and what is the selectivity of the indexes? The selectivity of an index can not only cause the optimizer to use or suppress an index, but it can also change the way the query drives and may determine the use or suppression of other indexes in the query.
- Which hints provide alternate paths, and which hints suppress or force an index to be used? These hints change the driving order of the tables, and they change how Oracle performs the join and which indexes it uses or suppresses.
- Which version of Oracle are you using? Your choices vary, depending on the version and release of Oracle you are using. The optimizer also works differently, depending on the version.
zv