Oracle Optimizer Hints

What Exactly is an Optimizer Hint?
A hint is a comment placed within an SQL statement (after SELECT, UPDATE, INSERT, MERGE, or DELETE) that instructs the Cost-Based Optimizer (CBO) to use a particular execution approach, even if the CBO might calculate that path to be more "expensive."
Hints are enclosed in special comment syntax: /*+ hint_name(arguments) */.
For example, to prioritize the fastest return of the first 10 rows:
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name FROM employees;
While powerful for testing and troubleshooting, remember: hints are extra code to manage and can become obsolete or even detrimental after database changes. For long-term stability, tools like SQL Plan Baselines are often preferred.

Types of Hints: Scope of Influence
Oracle hints are categorized by the scope of the SQL they affect, determining the specific part of the statement they influence:
    Single-Table Hints: These are specified on a single table or view within the query. They are used to influence operations directly against that specific object. An example is the INDEX hint, which instructs the optimizer to use a particular index when accessing the table.
    Multi-Table Hints: Similar to single-table hints, but they can specify one or more tables or views, often to influence the relationship or order between them. The LEADING hint is a prime example, used to enforce a specific order in which tables are joined. Note that a hint like USE_NL(table1 table2) is often just a shorthand for two single-table hints.
    Query Block Hints: These hints operate on an entire query block, such as the main query or a named subquery. They typically affect transformations or high-level execution strategies for that block. Examples include STAR_TRANSFORMATION and UNNEST.
    Statement Hints: These apply to the entire SQL statement, regardless of complexity or query blocks. They often control global execution parameters. The ALL_ROWS hint, which optimizes for best overall throughput rather than fastest initial response, is a common statement hint.

Hints by Category:
Oracle's many hints are logically grouped into categories, providing granular control over different phases of query optimization and execution:
1. Hints for Optimization Approaches and Goals:
These hints set the overarching objective for the optimizer, overriding the session or system settings:
    ALL_ROWS
    FIRST_ROWS(n)
2.Hints for Enabling Optimizer Features
This hint allows you to maintain plan stability across database versions:
    OPTIMIZER_FEATURES_ENABLE
3.Hints for Access Paths
These hints instruct the optimizer to use a specific method for accessing data in a table:
    FULL
    CLUSTER
    HASH
    INDEX and NO_INDEX
    INDEX_ASC and INDEX_DESC
    INDEX_COMBINE
    INDEX_JOIN
    INDEX_FFS and NO_INDEX_FFS
    INDEX_SS and NO_INDEX_SS
    INDEX_SS_ASC and INDEX_SS_DESC
4.Hints for Join Orders
These hints suggest the order in which tables should be joined:
    LEADING
    ORDERED
5.Hints for Join Operations
These hints instruct the optimizer on the algorithm to use for joining tables:
    USE_NL and NO_USE_NL
    USE_NL_WITH_INDEX
    USE_MERGE and NO_USE_MERGE
    USE_HASH and NO_USE_HASH
6.Hints for Online Application Upgrade
These specialized hints manage concurrency during Edition-Based Redefinition (EBR) upgrades:
    CHANGE_DUPKEY_ERROR_INDEX
    IGNORE_ROW_ON_DUPKEY_INDEX
    RETRY_ON_ROW_CHANGE
7.Hints for Parallel Execution
These hints control whether and how the SQL statement should be executed in parallel:
    PARALLEL and NO_PARALLEL
    PARALLEL_INDEX and NO_PARALLEL_INDEX
    PQ_DISTRIBUTE
8.Hints for Query Transformations
These hints allow you to enable or disable specific logical rewrites of the SQL statement:
    NO_QUERY_TRANSFORMATION
    USE_CONCAT
    NO_EXPAND
    REWRITE and NO_REWRITE
    MERGE and NO_MERGE
    STAR_TRANSFORMATION and NO_STAR_TRANSFORMATION
    FACT and NO_FACT
    UNNEST and NO_UNNEST
9.Additional Hints
This is a collection of hints for various other specific needs and behaviors:
    APPEND, APPEND_VALUES, and NOAPPEND
    CACHE and NOCACHE
    PUSH_PRED and NO_PUSH_PRED
    PUSH_SUBQ and NO_PUSH_SUBQ
    QB_NAME
    CURSOR_SHARING_EXACT
    DRIVING_SITE
    DYNAMIC_SAMPLING
    MODEL_MIN_ANALYSIS

A key addition in Oracle Database 23ai is related to vector data processing, essential for AI-driven similarity searches:
    VECTOR_INDEX_SCAN: This hint instructs the optimizer to explicitly use a Vector Index to satisfy the query. This is vital for accelerating similarity searches on vector data, ensuring the database leverages its specialized vector processing capabilities for these modern data types.