Month: March 2026

The ALIAS clause enables some surprisingly elegant query patterns. Let’s look at the cases where it makes the biggest difference.

Multi-step financial calculations:

SELECT
    order_id,
    quantity * unit_price                      AS gross_amount    ALIAS gross_amount,
    gross_amount * tax_rate / 100              AS tax_amount       ALIAS tax_amount,
    gross_amount + tax_amount                  AS total_amount     ALIAS total_amount,
    total_amount * discount_pct / 100          AS discount_amount  ALIAS discount_amount,
    total_amount - discount_amount             AS net_payable
FROM order_lines
WHERE gross_amount > 1000;

Without ALIAS, you’d either repeat the expressions (error-prone) or use a subquery to materialize intermediate results. Now it’s a single, readable SELECT.

Analytics with computed breakpoints:

SELECT
    customer_id,
    SUM(order_value)                          AS lifetime_value   ALIAS lifetime_value,
    CASE WHEN lifetime_value >= 10000 THEN 'PLATINUM'
         WHEN lifetime_value >= 5000  THEN 'GOLD'
         WHEN lifetime_value >= 1000  THEN 'SILVER'
         ELSE 'BRONZE'
    END                                       AS customer_tier    ALIAS customer_tier,
    COUNT(*) FILTER (WHERE order_date >= ADD_MONTHS(SYSDATE, -12)) AS orders_last_year
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;

ALIAS in WHERE clause:

SELECT
    employee_id,
    salary * (1 + bonus_pct/100)              AS total_comp  ALIAS total_comp,
    TRUNC(total_comp / 1000) * 1000           AS comp_band
FROM employees
WHERE total_comp > 60000    -- references the alias directly
ORDER BY total_comp DESC;

Previously this WHERE clause would require a subquery or repeating the expression. ALIAS makes the intent clear and the query maintainable.

SQL Firewall is powerful, but deploying it in production without a proper plan can cause application outages. Here’s a safe, phased deployment guide.

Phase 1: Observation (2-4 weeks)

Enable SQL Firewall in capture mode for all application users. Let it observe the full range of SQL generated by your application — including end-of-month batch jobs, reporting queries, and administrative scripts.

EXEC DBMS_SQL_FIREWALL.ENABLE;
EXEC DBMS_SQL_FIREWALL.START_CAPTURE('APP_USER');
EXEC DBMS_SQL_FIREWALL.START_CAPTURE('REPORT_USER');
EXEC DBMS_SQL_FIREWALL.START_CAPTURE('ETL_USER');

Phase 2: Allow-list review

After the observation period, review what was captured:

SELECT sql_text, capture_count, first_seen, last_seen
FROM   dba_sql_firewall_allowed_sql
WHERE  username = 'APP_USER'
ORDER BY last_seen DESC;

Remove any SQL that shouldn’t be on the allow-list (e.g., ad-hoc queries a developer ran during the capture period):

EXEC DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL('APP_USER', :sql_id);

Phase 3: Enable in LOG mode (not BLOCK)

EXEC DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST('APP_USER', DBMS_SQL_FIREWALL.ENFORCE_SQL, FALSE);
-- FALSE = log violations but don't block

Monitor DBA_SQL_FIREWALL_VIOLATIONS for 1-2 weeks. Any legitimate application SQL that triggers violations needs to be added to the allow-list.

Phase 4: Enable BLOCK mode

Only after violations are zero (or only known attack patterns):

EXEC DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST('APP_USER', DBMS_SQL_FIREWALL.ENFORCE_SQL, TRUE);

Ongoing maintenance: When application code changes, update the allow-list before deploying to production. A CI/CD step that runs a short capture against a staging environment and updates the allow-list is ideal.

Direct JOIN syntax in UPDATE and DELETE (covered in February 2025) is cleaner to write, but does it perform better? The answer depends on the query, and understanding the execution plan differences helps you make the right choice.

Checking execution plans for both approaches:

-- Approach 1: Correlated subquery (classic)
EXPLAIN PLAN FOR
UPDATE employees e
SET    e.department_name = (SELECT d.department_name FROM departments d WHERE d.id = e.dept_id)
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id AND d.active = 1);

-- Approach 2: Direct JOIN (23ai)
EXPLAIN PLAN FOR
UPDATE employees e
JOIN   departments d ON d.id = e.dept_id AND d.active = 1
SET    e.department_name = d.department_name;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

What you’ll typically see:

The optimizer often generates the same or equivalent plan for both syntaxes. The direct JOIN form gives the optimizer clearer join semantics, which can lead to better cardinality estimates and more accurate statistics usage.

When direct JOIN is measurably better:

• When the join column has a usable index and the correlated subquery form was defeating the index due to function wrapping
• When the number of rows to update is a small fraction of the target table (the JOIN can use NESTED LOOPS more efficiently)
• When Oracle was previously choosing FILTER operations (which can be slow) for correlated EXISTS subqueries

When they’re equivalent:

For well-written correlated subqueries with proper indexes, the optimizer typically produces identical plans. The direct JOIN benefit is primarily in readability and maintainability, not necessarily raw performance.

General recommendation: Use direct JOIN for clarity. If you observe a performance regression (unlikely but possible on older statistics), compare plans and potentially hint the join type.