The LOOP JOIN hint directs the Optimizer to pick the nested loop join in a select query for all tables listed in the FROM clause. By default, the Optimizer does not pick the nested loop join.
The LOOP JOIN hint loops through both inner and outer tables to find matches between columns in the inner and outer tables. For better performance, the joining columns should be indexed.
Using the loop join when tables are small and fit in memory can offer greater efficiency than using other join algorithms.
Example:
SELECT --(* vendor(SOLID), product(Engine), option(hint) -- LOOP JOIN *)-- * FROM TAB1, TAB2 WHERE TAB1.I >= TAB2.I