Oracle CBO decisions for Join Order along with multiple tables

 

Have you ever wondered how Oracle handles queries with multiple joins?

How Oracle Find Best Orders of Tables in a large SQL statement?

Is there a difference in the order in which tables are written in queries with multiple joins?

Note:

In this topic, I'm going to focus your attention on the inner workings of Oracle in executing and producing a proper execution plan on queries containing a large number of joins.

If, finally, you have a particular query for which the optimizer has produced a logically incorrect plan, you will have to test and examine many items that are beyond the scope of this topic.

 Join order in Oracle Database

The order in which multiple tables are joined together.

For example, for each row in the employees table, the database can read each row in the departments table. In an alternative join order, for each row in the departments table, the database reads each row in the employees table.

To execute a statement that joins more than two tables, Oracle Database joins two of the tables and then joins the resulting row source to the next table. This process continues until all tables are joined into the result.

 

🔹 1. Join Order Permutations

• When Oracle needs to join multiple tables, there are many possible join orders (permutations).
• For example, joining 3 tables (A, B, C) has 6 possible orders (A→B→C, A→C→B, etc.).
• As the number of tables grows, permutations explode factorially:
• 3 tables → 6 permutations
• 4 tables → 24
• 5 tables → 120
• 10+ tables → millions of possible join orders.

Clearly, testing all permutations would be computationally expensive.

🔹 2. How Oracle Reduces the Search Space

Oracle does not test every possible join order blindly. Instead, it applies heuristics and dynamic programming techniques:

• Query block transformation: Oracle may reorder tables for efficiency unless ORDERED or LEADING hints force a sequence.
• Join commutativity: Inner joins are commutative, so Oracle can flip them.
  Example: A JOIN B ≡ B JOIN A.
• Join associativity: Oracle can regroup joins (A JOIN B) JOIN C vs. A JOIN (B JOIN C) if they are associative.
• Pruning bad plans early: Oracle uses heuristics to discard join orders that are unlikely to be optimal.
• Star transformation & bushy joins: For star schemas, Oracle considers special transformations (fact + dimensions) and sometimes bushy join trees.

 

🔹 3. Cost-Based Decision

• Oracle’s CBO assigns a cost to each candidate join order.
• It estimates:
• Cardinality (row counts after filtering)
• Selectivity (filter conditions, histograms, bind peeking)
• Join method (nested loops, hash join, sort merge join)
• I/O, CPU, memory, parallelism
• Then it picks the lowest-cost plan.

 

🔹 4. Controlling Join Order

You can influence join order in Oracle:

• ORDERED hint → Enforces the join order in the SQL text.
• LEADING hint → Specifies which table(s) should appear first in the join order.
• JOIN hints (USE_NL, USE_HASH, USE_MERGE) → Influence join method but also indirectly affect join order.
• Optimizer parameters like optimizer_search_limit (limits permutations in older versions).

 

In short:
Oracle does not brute-force all join order permutations. Instead, it uses CBO-driven pruning, heuristics, and transformations to evaluate only promising join orders and then selects the lowest-cost plan.

 

Effect of Number of Tables on Join Order Permutations

The CBO will evaluate n! different join orders for a particular query where n is the number of tables in the query. In reality the actual number may be 
less than this because the optimization mechanism avoids repeating work and will not continue down paths that it knows are more expensive than the best
it has already found. There are also numerous other cutoffs that try to prevent the optimizer from spending too long evaluating a plan. One of the better known
of these is the cutoff at 80,000 permutations. With large numbers of tables, 80,000 permutations may be an insignificant proportion of the possible number.
This can mean that the best plan has not been found before the optimizer stops evaluation. See below for figures and percentages.

Unfortunately, with large numbers of tables, the time spent evaluating a single permutation can be significantly greater than with fewer tables.

This means that 50,000 permutations with 15 tables can take significantly longer than with 8 tables.

 

The following list is intended to indicate total permutations and what percentage 80,000 is of this number. This may give an idea of how accurate

or not the evaluation of a particular plan may or may not be.

Number of tables	Total number of possible permutations (n!)	Proportion of total represented permutations by 80,000 permutations  (80,000 / n! * 100)
1	1	Not Relevant
2	2	Not Relevant
3	6	Not Relevant
4	24	Not Relevant
5	120	Not Relevant
6	720	Not Relevant
7	5040	Not Relevant
8	40320	Not Relevant
9	362880	22%
10	3628800	2.2%
11	39916800	0.2%
12	479001600	0.016%
13	6226020800	0.001284%
14	87178291200	0.000092%
15	1307674368000	0.000006%

 

Clearly an 80,000 sample is not really sufficient for a 15 table join.

Note that a 10+ table join is highly unlikely to ever have a different starting table in the join order simply because there are >80,000 permutations

for the 9 remaining tables. So, if the first table in the best plan is not the first table in chosen join order, then you cannot achieve the best plan.

To avoid these problems, use techniques as follows:

·         Determine the best access path by testing and then use hints to force the join order (ORDERED hint).

·         Use the PARAMETER: OPTIMIZER_MAX_PERMUTATIONS parameter to change the maximum number of permutations considered from 80,000 to something else.

             Increasing the parameter means that more permutation will be examined.

            Lowering this parameter value changes the internal algorithm to vary the starting table in the join order.

Important Considerations:

• Oracle 10g and later:

The parameter is obsolete starting with Oracle 10g. If upgrading from an older version where it was set, you may need to explicitly set OPTIMIZER_FEATURES_ENABLE to an older version to ensure the same behavior. 

• Parse Time vs. Plan Quality:

The main tradeoff is between parse time and the quality of the execution plan. 

• Recommended Values:

Setting it to a value less than 1000 will usually result in parse times of a few seconds or less but set an optimal value need a comprehensive test. 

• Dynamic:

OPTIMIZER_MAX_PERMUTATIONS is not a dynamic parameter, meaning it can only be changed by modifying the initialization parameter file and restarting the instance. 

• Obsolete in later versions:

While still present in older versions, it's not a primary control for optimization in newer releases. Other parameters like optimizer_features_enable and hints are more relevant in those versions. 

 

• In older releases (like Oracle 8i, 9i), this parameter limited the maximum number of join order permutations that the optimizer would consider when evaluating queries.
• Its purpose was to avoid excessive optimization time when joining many tables.
• Starting from Oracle 10g onward, Oracle removed it and replaced it with smarter join-ordering algorithms (dynamic pruning, join heuristics, and query transformation).

in Oracle Database 19c (and even 12c, 18c):

• OPTIMIZER_MAX_PERMUTATIONS is obsolete (you won’t even find it in v$parameter).
• The optimizer automatically controls join order exploration with internal limits.

 

What to use instead

If you need to control join order in 19c:

• Use join order hints:
• ORDERED (forces the order written in the query)
• LEADING (specifies the join leading table)
• Use join method hints:
• USE_NL, USE_HASH, USE_MERGE
• Restructure queries with query blocks (WITH clause, subqueries).
• For very complex joins, consider SQL Plan Baselines or SQL Profiles, SPM to stabilize the optimizer’s choice.

 

OPTIMIZER_SEARCH_LIMIT:
This parameter specifies the upper limit above which the CBO will not automatically evaluate cartesian products where equality joins are not specified
in the base query. The reason is to prevent the optimizer from spending an inordinate amount of time on join orderings that are likely to be suboptimal.

 

In Oracle, the parameter controlling the number of join permutations the optimizer considers is:

• In older versions (pre-9i): OPTIMIZER_MAX_PERMUTATIONS was used.
• From Oracle 9i onwards, this parameter was deprecated and replaced with OPTIMIZER_SEARCH_LIMIT.

OPTIMIZER_SEARCH_LIMIT in Oracle 19c

• Default: 0 (meaning "let the optimizer decide" with internal limits).
• Range: 0 to 2,147,483,647 (but setting very high values can cause massive parse times).
• Purpose: It limits the number of join permutations the optimizer considers during cost-based join order optimization.

⚠️ Setting it too high can severely impact parsing performance if you have SQL with many joined tables.

Example: Setting the parameter

ALTER SYSTEM SET OPTIMIZER_SEARCH_LIMIT = 2000 SCOPE=BOTH;

-- Or at session level:

ALTER SESSION SET OPTIMIZER_SEARCH_LIMIT = 2000;

Recommendation for new versions as 19c 

Oracle usually manages join order exploration very well internally, so changing this parameter is rarely recommended unless you’re troubleshooting a specific query with too many joins and poor optimizer decisions.

👉 In practice, better approaches include:

• Using SQL Plan Baselines, SPM and other new facilities on 23ai
• Adding appropriate join order hints (LEADING, ORDERED)
• Ensuring good schema design, statistics, histograms, cardinality, selectivity, correct indexing, partitioning, …

 

Ø  if oracle handle automatically Join orders, does need we focused on tables orders in joins SQL statement?

Ø  Do these orders must be attention manually by DBA/Developers? or oracle find best execution plan automatically??

 

🔹 Oracle’s Default Behavior

• Oracle joins two tables first, then adds the next, step by step, until all are joined.
• But which tables it starts with, and in what order, is chosen by the Cost-Based Optimizer (CBO), unless you explicitly force it.
• The CBO:
• Estimates cardinality (rows after filters).
• Chooses join order + join method (NL/Hash/Merge).
• Picks the lowest-cost plan based on statistics, histograms, system resources, etc.

So, by default: Oracle does not simply follow your SQL text’s table order. It will reorder joins automatically for what it thinks is most efficient and CBO parameters in Database can lead to different behaviors in action.

 

🔹 Do DBAs/Devs Need to Worry About Join Order?

👉 Usually, no — if:

• You have accurate statistics (DBMS_STATS gathered, histograms where needed).
• The SQL is well-formed and written with clear join conditions.
• Data distribution is not heavily skewed (or you’ve provided histograms).

In those cases, Oracle will find the best join order automatically.
That’s why in modern Oracle (10g onward), the old OPTIMIZER_MAX_PERMUTATIONS was removed — because the CBO does the heavy lifting.

 

🔹 When You Should Care About Join Order

A DBA/dev needs to manually influence join order when:

1. Bad stats: Optimizer misestimates cardinality (e.g., skewed data, missing histograms).
2. Complex queries: 8–10+ tables with multiple join paths — CBO might prune too aggressively.
3. Specific performance issues: You see a bad plan (e.g., large table joined too early).
4. Star schemas/warehouses: Sometimes CBO misses the optimal dimension join order.
5. Optimizer bugs/edge cases: Rare but real.
6. Bad CBO parameter setting (Check Adaptive Query Plans, exist SQL Profiles, ASH, Redesign schema, …)

 

🔹 Tools to Influence Join Order

If you find Oracle chose badly, you can:

• LEADING (table1 table2 …) hint → explicitly control starting join order.
• ORDERED hint → forces join order as written in SQL.
• USE_NL / USE_HASH / USE_MERGE hints → force join methods, indirectly affecting order.
• Query rewrite / subquery factoring (WITH clause) → isolate blocks to control execution.
• Use ASH report, use SPM, Profiles, redesign schema, check CBO parameters such as Adaptive params

 

Summary

• Oracle 19c CBO: Automatically finds join order → DBA usually does not need to manually set it.
• Focus: Ensure statistics are accurate and representative of the data.
• Exceptions: Very complex queries, skewed data, or specific performance issues → then DBAs/devs should influence join order manually with hints or rewrites.

---

 In practice:
90% of the time → let Oracle decide.
10% of the time (skewed data, huge joins, critical queries, bad indexing, not partitioning on huge tables, bas histograms, chaining/migration rows, CBO params, Adaptive Query Plans and many other variables) → step in and guide it.

See this essay for more info:

https://vldb.org/cidrdb/papers/2021/cidr2021_paper01.pdf