Choosing the best index type in Oracle with a Performance Comparison

v What are partitioned indexes in Oracle?

v Which type is right for my environment (OLTP vs OLAP/DSS)?

v How do they compare and when should I use which type?

v What are limitations/restrictions on this way?

Index Partitioning

Partitioning indexes has recommendations and considerations in common with partitioning tables.

The rules for partitioning indexes are similar to those for tables:

• An index can be partitioned unless:

– The index is a cluster index.

– The index is defined on a clustered table.

• You can mix partitioned and nonpartitioned indexes with partitioned and nonpartitioned tables:

– A partitioned table can have partitioned or nonpartitioned indexes.

– A nonpartitioned table can have partitioned or nonpartitioned indexes.

• Bitmap indexes on nonpartitioned tables cannot be partitioned.

• A bitmap index on a partitioned table must be a local index.

However, partitioned indexes are more complicated than partitioned tables because there are three types of partitioned indexes:

• Local prefixed

• Local nonprefixed

• Global prefixed

Oracle Database supports all three types. However, there are some restrictions.

For example, a key cannot be an expression when creating a local unique index on a partitioned table.

Local Partitioned Indexes

In a local index, all keys in a particular index partition refer only to rows stored in a single underlying table partition.

A local index is created by specifying the LOCAL attribute.

Oracle constructs the local index so that it is equipartitioned with the underlying table.

Oracle partitions the index on the same columns as the underlying table, creates the same number of partitions or sub-partitions, and gives them the same partition bounds as corresponding partitions of the underlying table.

Oracle also maintains the index partitioning automatically when partitions in the underlying table are added, dropped, merged, or split, or when hash partitions or sub-partitions are added or coalesced.

This ensures that the index remains equipartitioned with the table.

A local index can be created UNIQUE if the partitioning columns form a subset of the index columns.

This restriction guarantees that rows with identical index keys always map into the same partition, where uniqueness violations can be detected.

Local indexes have the following advantages:

• Only one index partition must be rebuilt when a maintenance operation other than SPLIT PARTITION or ADD PARTITION is performed on an underlying table partition.

• The duration of a partition maintenance operation remains proportional to partition size if the partitioned table has only local indexes.

• Local indexes support partition independence.

• Local indexes support smooth roll-out of old data and roll-in of new data in historical tables.

• Oracle can take advantage of the fact that a local index is equipartitioned with the underlying table to generate better query access plans.

• Local indexes simplify the task of tablespace incomplete recovery.

To recover a partition or sub-partition of a table to a point in time, you must also recover the corresponding index entries to the same point in time.

The only way to accomplish this is with a local index.

Then you can recover the corresponding table and index partitions or Sub-partitions.

Local Prefixed Indexes

A local prefixed index is a type of local partitioned index where the table's partition key (or a leftmost combination of columns) is the leading column(s) in the index definition. 

This allows the database to perform partition pruning, where queries specifying the partition key in the WHERE clause can avoid searching non-relevant partitions, making queries more efficient for OLTP (online transaction processing) workloads. 

So, a local index is prefixed if it is partitioned on a left prefix of the index columns and the subpartioning key is included in the index key.

Local prefixed indexes can be unique or nonunique.

For example,

if the sales table and its local index sales_ix are partitioned on the week_num column, then index sales_ix is local prefixed if it is defined on the columns (week_num, xaction_num).

On the other hand, if index sales_ix is defined on column product_num then it is not prefixed.

This figure illustrates another example of a local prefixed index.

Key Characteristics

• Equipartitioned with the Table:

A local prefixed index uses the same partitioning key and method as the underlying table, meaning its partitions align with the table's partitions. 

• Leading Partition Key:

The defining feature is that the partition key is the first column (or the first combination of columns in a composite key) in the index's key list. 

• Supports Partition Pruning:

Because the partition key is at the start of the index, the database can efficiently locate data by only scanning the relevant partition(s) when the partition key is specified in a query's WHERE clause. 

This index type is ideal for queries that filter on the partition key, as it enables the database to quickly identify and access only the required data segments. 

Local Nonprefixed Indexes

A local index is nonprefixed if it is not partitioned on a left prefix of the index columns or if the index key does not include the sub-partitioning key.

You cannot have a unique local nonprefixed index unless the partitioning key is a subset of the index key.

The partition key is not the leading edge of the index. 

While these indexes are still partitioned along with the table (local), they do not support partition pruning for queries filtering on the partition key, as the partition key isn't the first part of the index.

They are distinguished from local prefixed indexes by the relationship between the index columns and the table's partitioning key

This figure illustrates an example of a local nonprefixed index.

Key Characteristics:

• Partitioning Key Location:

The defining characteristic of a local non-prefixed index is that the table's partitioning key columns are not the leading columns of the index definitionThe index may or may not include the partitioning key columns at all.

• Partition Alignment:

Like all local indexes, local non-prefixed indexes are partitioned in the same way as their underlying table, meaning each index partition corresponds to a single table partition.

 

• Maintenance:

Oracle automatically maintains local indexes. When a partition is added to or dropped from the table, the corresponding index partition is also added or dropped.

• Uniqueness:

A local non-prefixed index can be unique, but only if the table's partitioning key is included within the index key columns.

• Partition Pruning:

While local non-prefixed indexes can be used to access data within a specific partition, they do not inherently enable partition pruning based on the index key alone, unlike local prefixed indexes. 

If a query includes a predicate on the partitioning key, Oracle can still perform partition pruning on the table, and then use the local non-prefixed index within the pruned partition.

Local non-prefixed indexes are often utilized in scenarios where queries frequently access data based on columns other than the partitioning key,

  and the benefits of local index management and availability outweigh the potential performance implications of less direct partition pruning. 

They are commonly found in data warehousing environments or historical databases where older partitions are frequently dropped.

Global Partitioned Indexes

In a global partitioned index, the keys in a particular index partition may refer to rows stored in multiple underlying table partitions or sub-partitions.

A global index can be range or hash partitioned, though it can be defined on any type of

partitioned table. A global index is created by specifying the GLOBAL attribute.

The database administrator is responsible for defining the initial partitioning of a global index at creation and for maintaining the partitioning over time.

Index partitions can be merged or split as necessary.

Normally, a global index is not equipartitioned with the underlying table.

There is nothing to prevent an index from being equipartitioned with the underlying table, but Oracle does not take advantage of the equipartitioning when generating query plans or executing partition maintenance operations.

So, an index that is equipartitioned with the underlying table should be created as LOCAL.

A global partitioned index contains a single B-tree with entries for all rows in all partitions.

Each index partition may contain keys that refer to many different partitions or sub-partitions in the table.

The highest partition of a global index must have a partition bound that includes all values

that are MAXVALUE. This insures that all rows in the underlying table can be represented in the index.

 

Prefixed and Nonprefixed Global Partitioned Indexes

Ø  A global partitioned index is prefixed if it is partitioned on a left prefix of the index columns.

Ø  A global partitioned index is nonprefixed if it is not partitioned on a left prefix of the index columns.

Ø  Oracle does not support global nonprefixed partitioned indexes.

Ø  Global prefixed partitioned indexes can be unique or nonunique.

Ø  Nonpartitioned indexes are treated as global prefixed nonpartitioned indexes.

 

 

 

 

 

 

Figure: Global Prefixed Partitioned Index

Management of Global Partitioned Indexes

Management of global partitioned indexes presents several challenges.

Global partitioned indexes are harder to manage than local indexes because of the following:

• When the data in an underlying table partition is moved or removed (SPLIT, MOVE, DROP, or TRUNCATE), all partitions of a global index are affected.

Consequently, global indexes do not support partition independence.

• When an underlying table partition or sub-partition is recovered to a point in time, all corresponding entries in a global index must be recovered to the same point in time.

Because these entries may be scattered across all partitions or sub-partitions of the index, mixed with entries for other partitions or sub-partitions that are not being recovered,                                                                                                                                          there is no way to accomplish this except by re-creating the entire global index.

Summary of Partitioned Index Types

A summary of partitioned index types is provided in this topic.

Following table summarizes the types of partitioned indexes that Oracle supports. The key points are:

• If an index is local, then it is equipartitioned with the underlying table. Otherwise, it is global.

• A prefixed index is partitioned on a left prefix of the index columns. Otherwise, it is nonprefixed.

Table: Types of Partitioned Indexes

^Footnote 1

For a unique local nonprefixed index, the partitioning key must be a subset of the index key and cannot be a partial index.

^Footnote 2

Although a global partitioned index may be equipartitioned with the underlying table, Oracle does not take advantage of the partitioning or maintain equipartitioning after partition maintenance operations such as DROP or SPLIT PARTITION.

The Importance of Nonprefixed Indexes

Nonprefixed indexes are important because they are particularly useful in historical databases.

In a table containing historical data, it is common for an index to be defined on one column to support the requirements of fast access by that column.

However, the index can also be partitioned on another column (the same column as the underlying table) to support the time interval for rolling out old data and rolling in new data.

Consider a sales table partitioned by week. It contains a year's worth of data, divided into 13 partitions.

It is range partitioned on week_no, four weeks to a partition.

You might create a nonprefixed local index sales_ix on sales. The sales_ix index is defined on acct_no because there are queries that need fast access to the data by account number.

However, it is partitioned on week_no to match the sales table. Every four weeks, the oldest partitions of sales and sales_ix are dropped and new ones are added.

Performance Implications of Prefixed and Nonprefixed Indexes

There are performance implications of prefixed and nonprefixed indexes.

With a prefixed index, the likelihood to get partition pruning is much higher than with a nonprefixed index.

If a column is part of an index, then you can assume that the column is used as a filter predicate, which automatically means some level of pruning when a filtered column is a prefixed column.

This result suggests that it is usually less expensive to probe into a prefixed index than to probe into a nonprefixed index.

If an index is prefixed (either local or global) and Oracle is presented with a predicate involving the index columns, then partition pruning can restrict application of the predicate to a subset of the index partitions.

For example, in Figure of Local Prefixed Index, if the predicate is deptno=15, the optimizer knows to apply the predicate only to the second partition of the index.

(If the predicate involves a bind variable, the optimizer does not know exactly which partition but it may still know there is only one partition involved, in which case at run time, only one index partition is accessed.)

When an index is nonprefixed, Oracle often has to apply a predicate involving the index columns to all N index partitions. This is required to look up a single key, or to do

an index range scan. For a range scan, Oracle must also combine information from N index partitions. For example, in Figure of Local Nonprefixed Index, a local index is partitioned on chkdate with an index key on acctno.

If the predicate is acctno=31, Oracle probes all 12 index partitions.

Of course, if there is also a predicate on the partitioning columns, then multiple index probes might not be necessary. Oracle takes advantage of the fact that a local index is

equipartitioned with the underlying table to prune partitions based on the partition key.

For example, if the predicate in Figure of Local Nonprefixed Index is chkdate<3/97, Oracle only has to probe two partitions.

So, for a nonprefixed index, if the partition key is a part of the WHERE clause but not of the index key, then the optimizer determines which index partitions to probe based on the underlying table partition.

When many queries and DML statements using keys of local, nonprefixed, indexes have to probe all index partitions, this effectively reduces the degree of partition independence provided by such indexes.

Table: Comparing Prefixed Local, Nonprefixed Local, and Global Indexes

Guidelines for Partitioning Indexes

There are several guidelines for partitioning indexes. When deciding how to partition indexes on a table, consider the mix of applications that must access the table.

There is a trade-off between performance and availability and manageability.  Here are some guidelines you should consider:

• For OLTP applications:

– Global indexes and local prefixed indexes provide better performance than local nonprefixed indexes because they minimize the number of index partition probes.

– Local indexes support more availability when there are partitions or sub-partitions maintenance operations on the table. Local nonprefixed indexes are very useful for historical databases.

• For DSS applications, local nonprefixed indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key.

For example, a query using the predicate "acctno between 40 and 45" on the table checks of Figure of Local Nonprefixed Index causes parallel scans of all the partitions of the nonprefixed index ix3.

On the other hand, a query using the predicate deptno BETWEEN 40 AND 45 on the table deptno of Figure of Local Prefixed Index cannot be parallelized because it accesses a single partition of the prefixed index ix1.

• For historical tables, indexes should be local if possible. This limits the effect of regularly scheduled drop partition operations.

• Unique indexes on columns other than the partitioning columns must be global because unique local nonprefixed indexes whose keys do not contain the partitioning key are not supported.

• Unusable indexes do not consume space.

Physical Attributes of Index Partitions

The command ALTER INDEX MODIFY DEFAULT ATTRIBUTES is an Oracle Database command used to change the default physical attributes (like TABLESPACE, compression, etc.) that will be inherited by future partitions of a partitioned index. 

It allows you to set new defaults that will be applied to new partitions, while not affecting existing ones, making it useful for managing partitioned index storage and performance over time. 

Default physical attributes are initially specified when a CREATE INDEX statement creates a partitioned index.

Because there is no segment corresponding to the partitioned index itself, these attributes are only used in derivation of physical attributes of member partitions.

Default physical attributes can later be modified using ALTER INDEX MODIFY DEFAULT ATTRIBUTES.

Physical attributes of partitions created by CREATE INDEX are determined as follows:

• Values of physical attributes specified (explicitly or by default) for the index are used whenever the value of a corresponding partition attribute is not specified.                                                                                                                                                  Handling of the TABLESPACE attribute of partitions of a LOCAL index constitutes an important exception to this rule in that in the absence of a user-specified                                                                                                                                  TABLESPACE value (at both partition and index levels), that of the corresponding partition of the underlying table is used.
• Physical attributes (other than TABLESPACE, as explained in the preceding) of partitions of local indexes created in the course of processing ALTER TABLE ADD PARTITION are set to the default physical attributes of each index.

Physical attributes (other than TABLESPACE) of index partitions created by ALTER TABLE SPLIT PARTITION are determined as follows:

• Values of physical attributes of the index partition being split are used.

Physical attributes of an existing index partition can be modified by ALTER INDEX MODIFY PARTITION and ALTER INDEX REBUILD PARTITION. Resulting attributes are determined as follows:

• Values of physical attributes of the partition before the statement was issued are used whenever a new value is not specified. Note that ALTER INDEX REBUILD PARTITION can be used to change the tablespace in which a partition resides.

Physical attributes of global index partitions created by ALTER INDEX SPLIT PARTITION are determined as follows:

• Values of physical attributes of the partition being split are used whenever a new value is not specified.
• Physical attributes of all partitions of an index (along with default values) may be modified by ALTER INDEX, for example, ALTER INDEX index_name NOLOGGING changes the logging mode of all partitions of index_name to NOLOGGING.

 

Modifying Default Attributes of a Table

You can modify the default attributes that are inherited for range, hash, list, interval, or reference partitions using the MODIFY DEFAULT ATTRIBUTES clause of ALTER TABLE.

For hash partitioned tables, only the TABLESPACE attribute can be modified.

Modifying Default Attributes of a Partition

To modify the default attributes inherited when creating subpartitions, use the ALTER TABLE MODIFY DEFAULT ATTRIBUTES FOR PARTITION.

The following statement modifies the TABLESPACE in which future subpartitions of partition p1 in the range-hash partitioned table reside.

SQL> ALTER TABLE employees_subpartitions   MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE ts1;

Because all subpartitions of a range-hash partitioned table must share the same attributes, except TABLESPACE, it is the only attribute that can be changed.

You cannot modify default attributes of interval partitions that have not yet been created.

To change the way in which future subpartitions in an interval-partitioned table are created, you must modify the subpartition template.

Modifying Default Attributes of Index Partitions

In a similar fashion to table partitions, you can alter the default attributes that are inherited by partitions of a range-partitioned global index, or local index partitions of partitioned tables.

For this you use the ALTER INDEX MODIFY DEFAULT ATTRIBUTES statement.

Use the ALTER INDEX MODIFY DEFAULT ATTRIBUTES FOR PARTITION statement if you are altering default attributes to be inherited by subpartitions of a composite partitioned table

 

 

FAQ:

What is the prefixed and non-prefixed index?

The index is called prefixed if the leading column(s) in the index definition is (are) the partition key column(s), otherwise it is called non-prefixed.

Can we subpartition the global index?

No, you cannot subpartition global index, but you can partition it. The only way to end up with index subpartition is to have a sub-partitioned table with local index

How to Find Whether an Index is Global or Local

By querying the column, LOCALITY, in DBA_PART_INDEXES.

 

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