Optional Joins

You are looking at an older version of the documentation. The latest version is found here.

If a join is marked as optional, the optimizer will omit the query to the joined table if no fields from it are selected. Using the hint, the performance of queries may significantly be improved. This is especially the case with data models where views contain many tables, but the query patterns vary.

Here is an example of an optional join:

SELECT a.column1, b.column2 FROM sa.a, /*+ optional */ sb.b WHERE a.key = b.key

One drawback is that counting the result set size will have unexpected results because not all tables are considered for counting.

See our documentation for optional joins.