Optimization of joins using random record generation method

Joins are statements that retrieve data from more than one table. A Join is characterized by multiple tables in the FROM clause, and the relationship between the tables is defined through the existence of a Join condition in the WHERE clause. In the case of Very large databases and highly normalized databases frequency of Join queries are high. To perform the Join Query much efficiently, the Join Method the optimizer selects are very vital. Nested Loop Join, Hash Join and Merge Sort Join are primary Join methods available to join tables. When the size of the result set is less than 10,000 the optimizer will perform Nested Loop join. But Nested loop Join steals must of the system resources. In Nested loop Join each record from the outer table will be compared with the inner table to find out a match. This paper proposes a Random Record Join method in which a random record will be picked from the inner table to find a match with the outer table. This method reduces the number of iteration required to Join the table. To perform the Random Record join the optimizer requires statistics of the table. The no. of records containing the distinct Join key attributes value should be maintained by the data dictionary.