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.