Home › Discussions › Questions & Answers › Anchor Types and Dynamic SQL Optimization

Anchor Types and Dynamic SQL Optimization

Avatar photoCustomer Questions & Answers 7 replies

I see two primary ways to handle optional parameters: the traditional `:bind` variables, and Anchors like `1=1` or `&lexical`. Why does the developer guide recommend using Anchors, especially for optional restrictions?

7 replies

  • Support Enginatics

    The recommendation is driven by performance optimization. Blitz Report creates dynamic SQL at run-time, which allows unused parameters to be dropped entirely from the query. This eliminates a frequent source of non-performant SQL, specifically the widespread coding practice of using the inefficient pattern: `column_name=nvl(:bind_variable, column_name)` for optional parameters.

  • Avatar photoCustomer

    That makes sense for performance. Now, what’s the difference between the `n=n` anchor (like `1=1`) and the `&lexical` anchor, and when should I use one over the other?

  • Support Enginatics

    The `n=n` anchors (e.g., `1=1`) are specifically WHERE clause SQL anchors. When using these, Blitz Report inserts the associated SQL text *before* the anchor, automatically adding the keyword ‘and’ and a line feed, ensuring valid SQL. However, the `n=n` syntax remains in the run-time SQL, so it can only be used for WHERE clauses.

  • Avatar photoCustomer

    So if I need to dynamically change something outside the WHERE clause, like adding columns or an ORDER BY clause, `n=n` won’t work?

  • Support Enginatics

    Exactly. The `&lexical` parameter reference is far more flexible. It works like lexical parameters in standard SQL*Plus or Oracle Reports, meaning Blitz Report replaces the placeholder completely with the parameter SQL text at run-time. This makes `&lexical` suitable for injecting almost any SQL snippet, including Dynamic tables and columns, ORDER BY clauses, GROUP BY clauses, HINTS, or Complete subselects or EXISTS clauses.

  • Avatar photoCustomer

    If I use an `&lexical` parameter and the user leaves the input blank, does the placeholder still remain in the SQL?

  • Support Enginatics

    No. If a parameter value associated with an `&lexical` anchor is left blank, the corresponding reference is removed entirely before SQL execution, which is another aspect of the optimization.

  • You must be logged in to reply to this post.