leading hint influences the optimizer when choosing the order in which tables are joined. | Query Transformer | If advantageous, the query transformer rewrites the original statement into a semantically equivalent statement (for example from … where col in (17, 42) to … where col = 17 … union all … where col = 42). |
| Estimator | Uses statistics to estimate plans' cost. |
| Plan Generator | Compares estimated costs and chooses plan with least cost; passes this plan to the row source generator. |
dbms_stats
| OR-expansion | where tab.col_1 = 'foo' or tab.col_2 = 'bar' -> where tab.col_1 = 'foo' … UNION ALL … where tab.col_2 = 'bar' |
| Simple view merging | Merging select-project-join views |
| Complex view merging | Merging views that contain group by and distinct clauses |
| Predicate pushing | Push a predicate that is formulated on the outer query into the inner query. |
| Subquery unnesting | Transform a subquery into a join operation. |
| Query rewriting | Use a materialized view to improve performance of a query |
| Star tranformation | Avoid full table scans of fact tables in a star schema. |
| In-memory aggregation (vector group by) | |
| Cursor duration temporary tables | Temporarily store result of a query blocks that are used multiple times. See also the with clause. |
| Table expansion | See also the expand_table hint |
| Join factorization | Factorize common computations from branches of a union all query. |
no_query_transformation hint. optimizer_features_enable (which corresponds to a relase) determines which features are enabled | Release | |
| Adaptive cursor sharing | 11.1.0.6 |
| Join predicate pushdown | 11.1.0.6 |
| Use extended statistics to estimate selectivity | 11.1.0.6 |
| Use native implementation for full outer joins | 11.1.0.6 |
| Partition pruning using join filtering | 11.1.0.6 |
| Group by placement optimization | 11.1.0.6 |
| Null aware antijoins | 11.1.0.6 |
| Join predicate pushdown | 11.1.0.6 |
| Join Factorization | 11.2.0.1 |
| Cardinality Feedback | 11.2.0.1 |
| Subquery Unnesting | 11.2.0.1 |
| Subquery Coalescing | 11.2.0.1 |
| Table Expansion | 11.2.0.1 |
| Filtering Join Elimination | 11.2.0.1 |
| Dynamic statistics enhancements | 11.2.0.4 |
| Adaptive Query Optimization | 12.1.0.1 |
| Online statistics gathering for bulk loads | 12.1.0.1 |
| Session level statistics for Global Temporary Tables | 12.1.0.1 |
| Multi-table left outer joins | 12.1.0.1 |
| Lateral views | 12.1.0.1 |
| Batch table access by rowid | 12.1.0.1 |
| Null accepting semi joins | 12.1.0.1 |
| Scalar subquery unnesting | 12.1.0.1 |
| Conversion of joins that produce unnecessary duplicates to semi-joins | 12.1.0.1 |
| Parallel Union and Parallel Union All operations | 12.1.0.1 |
| Enhance Auto DOP | 12.1.0.1 |
| Approximate count distinct | 12.1.0.2 |
| Support for Oracle Database In-Memory | 12.1.0.2 |
| Group-by and aggregation elimination | 12.1.0.2 |
| Query rewrite for approximate query processing | 12.2.0.1 |
| Statistics advisor | 12.2.0.1 |
| Support for sharded databases | 12.2.0.1 |
| Expression tracking | 12.2.0.1 |
| Space-saving algorithm for partition synopses | 12.2.0.1 |
| Oracle In-Memory Database statistics | 12.2.0.1 |
| Support for sharding | 12.2.0.1 |
| Cost-based OR expansion | 12.2.0.1 |
| Sub-query elimination | 12.2.0.1 |
| Multi-column key join elimination | 12.2.0.1 |
| SQL Quarantine | 19c |
| Gathering and use of real-time statistics | 19c |
| Use of automatic indexes | 19c |
| WITH clause filter predicate pushdown | 21c |
| Number of distinct value (NDV) modeling for real time statistics | 21c |
sys.dual is a single-row table. set autotrace on explain in SQL*Plus. alter session set events '10053 trace name context forever';
aux_stats$, compare with dbms_stats.gather_system_stats. dual has only one record. create table as select …) do not undergo SQL statement optimization.