Join methods in Oracle

.. yet to be finished ...

When a select statement gets data from multiple tables (or, more accuratly called: row sources), the table are said to be joined. Different join methods are possible in Oracle. Such a join method determines the mechanism (or the algorithm, if you will) how such row sources are joined.

Also called sort merge join.

A merge join basically sorts all relevant rows in the first table by the join key , and also sorts the relevant rows in the second table by the join key, and then merges these sorted rows.

Take an example! At a garage sale you can buy 400 books. The deal is to take all or none. You take all. Now, you have to find the books that you already have at home. How would you go about it? Probably, you'd do a merge join: first, you sort your books by the primary key (author, title), then you sort the 400 books by their primary key (auther, title). Now, you start at the top of both piles. If the value of the left piles primary key is higher, then you take a book from the right pile and vice versa. When both values are equal, then you have found a dublicate. To demonstrate a MERGE JOIN, two tables need to be created:

The behaviour of merge joins is influenced by the initialization parameters sort_area_size and db_file_mutliblock_read_count.

The nested loop iterates over all rows of the outer table. If there are conditions in the where clause of the SQL statement that apply to the outer table only, it checks whether those apply. If they do, the corresponding rows (from the where condition) in the joined inner table are searched. These rows from the inner table are either found using an index (if a suitable exists) or by doing a full table scan.

A hash join (ideally) takes the smaller table (or row source), iterates over its rows and performs a hash algorithm on the columns for the where conditions between the tables and stores the result. After it has finished, it iterates over the other table and performes the same hashing algorithm on the joined columns. It then searches the previously built hashed values and if they match, it returns the row.

Used for queries with a not in.