SQL Tuning   «Prev  Next»

Lesson 2Oracle extensions to standard SQL
ObjectiveExplain why Oracle enhances standard SQL and how those features affect portability and tuning.

Oracle SQL Extensions and Their Tuning Tradeoffs

Oracle AI Database 26ai implements standard SQL capabilities alongside Oracle-specific functions, syntax, and optimizer controls. These features help applications express business rules, transform data, and influence execution. Their value depends on the problem being solved: a vendor-specific expression is not automatically faster, and a portable expression is not automatically a better fit.

SQL is declarative by design. You describe the result, while the optimizer considers how to obtain it. Declarative behavior is therefore not an Oracle extension. As introduced in Lesson 1, the cost-based optimizer evaluates access paths and join methods using available statistics and other information. Developers still need to understand execution plans when observed performance fails to meet requirements.

For example, the optimizer may consider an index, a hash join, or a full table scan. None is inherently the best choice for every query. A plan that works well for a selective search can be unsuitable for a report that reads most rows.

Standard SQL, Built-in Functions, and Oracle Features

The SQL standard is developed through ISO/IEC and is also associated with ANSI. Implementations differ in supported features, versions, syntax, and behavior. Avoid assuming complete portability merely because two databases both support SQL.

A built-in function is supplied by the database. Some built-in functions implement standard SQL; others belong to a vendor's dialect. Optimizer hints and parallel execution controls are different kinds of features, not built-in functions.

Capabilities and portability considerations
CapabilityExamplesWhat to check
Case conversionUPPERA standard SQL function; character mapping and collation still matter.
Conditional and NULL handlingCASE, COALESCE, NVL, DECODECASE and COALESCE are standard constructs. Review Oracle-specific semantics before replacing NVL or DECODE.
Conversion and formattingTO_CHAR, TO_DATECheck format models, datatypes, and session settings. Similarly named functions in other products may behave differently.
Execution controlsOracle hints and parallel execution settingsThese influence processing rather than define a portable result expression. Test their workload impact.

TO_CHAR can format a value as character data; TO_DATE converts character input to a DATE value. Use an explicit format model when converting a known date string rather than relying on the session's default date format. The uppercase function is UPPER, not TO_UPPER.

Portability Requires Semantic Checks

Is DECODE standard SQL? No. DECODE is an Oracle-dialect function, although other products may provide compatibility implementations. Standard CASE expressions often express the same business decision, but a mechanical substitution can change results.

In Oracle, DECODE considers two NULLs equivalent for matching. A simple CASE expression does not make NULL equal to NULL. To preserve a missing-value branch, use an explicit NULL test:

-- Both expressions label a missing value explicitly.
SELECT DECODE(CAST(NULL AS NUMBER), NULL, 'Missing', 'Present') AS decode_label,
       CASE WHEN CAST(NULL AS NUMBER) IS NULL
            THEN 'Missing' ELSE 'Present' END AS case_label
FROM dual;

Likewise, NVL and COALESCE require attention to argument types, conversions, and evaluation behavior. Prefer standard constructs when they satisfy the requirements, and isolate vendor-dependent SQL where it provides a useful capability. Test each supported database instead of assuming a change in syntax preserves meaning.

Functions in Predicates: What Is Being Transformed?

The examples below assume a practice customer table containing customer_id and last_name. They illustrate design choices; no database execution or measured performance result is claimed.

Consider a predicate that transforms a constant:

SELECT customer_id, last_name
FROM customer
WHERE last_name LIKE UPPER('a%');

Here UPPER produces the pattern 'A%'; it does not wrap LAST_NAME. An ordinary index on LAST_NAME is not disqualified simply because UPPER appears in the statement. Whether Oracle chooses a useful index access path depends on the predicate, collation, statistics, and cost. Under binary comparison semantics, this expression also does not make stored mixed-case names match case-insensitively.

Compare that with a function applied to each column value:

SELECT customer_id, last_name
FROM customer
WHERE UPPER(last_name) = 'SMITH';

An ordinary index on LAST_NAME does not directly index UPPER(LAST_NAME). It is therefore not the same access structure as an index on the expression. This does not mean all index use becomes impossible: other predicates, transformations, or index scan strategies may still be relevant. Inspect the chosen plan rather than applying an absolute rule.

UPPER uses the character set's case mapping. It is not a complete solution for every language-sensitive or accent-insensitive search requirement. Select comparison and collation behavior that matches the application before designing its index.

Alternative A: Create a Function-Based Index

A function-based index stores values derived from an expression. The following index gives the optimizer a matching access structure for the uppercase-name predicate:

CREATE INDEX customer_last_name_upper_ix
    ON customer (UPPER(last_name));

SELECT customer_id, last_name
FROM customer
WHERE UPPER(last_name) = 'SMITH';

The optimizer can consider this index; it is not required to choose it. A scan may still be cheaper when many rows match. Evaluate representative values and appropriate optimizer statistics, then compare observed work and response time. The index also consumes storage and adds maintenance work when affected rows change.

Alternative B: Index a Virtual Column

A virtual column exposes the expression through a column name:

ALTER TABLE customer ADD (
    last_name_upper GENERATED ALWAYS AS (UPPER(last_name)) VIRTUAL
);

CREATE INDEX customer_last_name_vc_ix
    ON customer (last_name_upper);

SELECT customer_id, last_name
FROM customer
WHERE last_name_upper = 'SMITH';

The virtual column does not store a separate ordinary column value in each table row. Its index does store entries and requires space and maintenance. Alternatives A and B demonstrate two ways to express the same indexing intention; do not create both equivalent indexes as a default exercise sequence. Use a practice schema and the necessary DDL privileges to evaluate a chosen design.

An index on an arithmetic expression is also a function-based index, not a third indexing technology. Before adding one, consider whether a simpler equivalent predicate is appropriate. For an Oracle NUMBER salary value, salary + 10 = 60000 can be written as salary = 59990. Check datatype and arithmetic semantics before extending this idea to other expressions.

Materialized views are another design option for suitable repeated workloads, but they store results and introduce refresh considerations. They are not the routine first response to a single function predicate.

User-Defined Functions and DETERMINISTIC

A function you create in PL/SQL is user-defined, not a new built-in function. This simplified function applies a fixed multiplier:

CREATE OR REPLACE FUNCTION plus_tax (
    p_book_retail_price IN NUMBER
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
    RETURN p_book_retail_price * 1.07;
END;
/

The example is an arithmetic illustration, not a complete tax or currency-rounding model. NULL input returns NULL through the multiplication expression. The slash is a script terminator used by clients such as SQL*Plus; other execution interfaces may require submitting the PL/SQL statement without it.

DETERMINISTIC asserts that identical inputs produce identical results. It is required when a user-defined function is referenced by a function-based index, but it is not required for every SQL-callable function. Oracle relies on the assertion rather than proving it. Do not mark a function deterministic if its result depends on changing lookup data or session state not represented by its arguments.

The declaration does not create an index or guarantee faster execution. If a function's semantics change, review and rebuild affected function-based indexes as required. For simple calculations, an inline SQL expression may be clearer than adding a function call.

Evaluate the Complete Tradeoff

Start with correct results, including NULL handling, conversion rules, and comparison semantics. Then inspect the actual cursor plan and available runtime statistics, test representative values, and compare read performance with storage and write overhead. Keep a change only when the measured benefit supports the workload's goals. The next lesson introduces the Oracle SQL tuning tool set.

Terms Used in This Lesson

Index [1]
An access structure that can help locate data. It does not guarantee query output order; use ORDER BY when order matters.
Hash join [2]
A join method that builds a hash structure and probes it for matching rows. Its work area generally uses PGA memory in dedicated-server execution and may spill to temporary storage.
Full table scan [3]
An access method that scans table data rather than using an index to locate selected rows. It can be an efficient choice.
ANSI [4]
The American National Standards Institute. SQL standardization is associated with both ANSI and ISO/IEC.
Built-in function (BIF) [5]
A function supplied by the database, whether standard or vendor-specific.

Oracle 26ai References

SEMrush Software 2 SEMrush Banner 2