Best Practices for SQL Generation with Select AI

Improve Select AI text-to-SQL results by enriching schema context, writing specific prompts, and evaluating models against representative questions and data.

Select AI enables natural language-to-SQL (NL2SQL) generation. This allows users to query database objects using natural language prompts instead of writing SQL manually. The quality and accuracy of the generated SQL depend on several factors, including schema design, metadata quality, prompt specificity, and the choice of large language model (LLM).

Use the following best practices to improve SQL generation accuracy and relevance.

When to Use

Choose and use these techniques to give Select AI the business context it needs to generate accurate SQL.

Technique When to Use
Views with readable names Base tables have cryptic names or application-dependent legacy naming
Comments Business meaning, valid values, or usage rules are not obvious from names
Constraints Users ask questions requiring joins across tables
Annotations You need richer semantic hints beyond comments and object names
Pre-joined views Joins are common, complex, or frequently inferred incorrectly
Metric views KPIs or formulas must be consistent across prompts

Enrich Schema Metadata

Select AI uses metadata to augment user prompts with information about the underlying data model to provide context to the LLM during SQL generation. Metadata can include:

You can enrich the metadata available to Select AI in several ways. Each method can be used independently or in combination to provide additional context. Providing descriptive metadata improves the LLM’s ability to interpret database objects and generate appropriate SQL statements.

Enable Metadata in the AI Profile

This profile configuration includes table and column comments, referential-integrity constraints, and annotations in the schema metadata provided to the LLM.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'EMPLOYEE_AI_PROFILE',
    attributes   => '{
      "provider": "oci",
      "credential_name": "OCI_GENAI_CREDENTIAL",
      "comments": true,
      "constraints": true,
      "annotations": true
    }'
  );
END;
/

The comments, constraints, and annotations attributes control the metadata added to the prompt sent to the LLM:

All three attributes default to FALSE. Set them to TRUE when the metadata should be available to Select AI.

Use Meaningful Object Names

Table, view, and column names are an important source of context for Select AI. Meaningful names help the LLM interpret the purpose of database objects and generate more relevant responses.

If object names are cryptic or generic, such as FLEX_FIELD_1, the LLM may not be able to determine their intended meaning. Renaming existing objects is not always practical because applications, reports, or other database objects may depend on them.

As an alternative, you can create views that expose the underlying data using more descriptive names. This approach improves the metadata available to Select AI without requiring changes to existing applications or database objects.

For example, you can create a view over the following table definition to provide more meaningful object and column names:

CREATE TABLE tab1 (
   id NUMBER,
   field1 VARCHAR2(256),
   dt DATE,
   flex_field2 VARCHAR2(4000)
);

CREATE OR REPLACE VIEW employee AS
SELECT id          AS employee_id,
       field1      AS full_name,
       dt          AS hire_date,
       flex_field2 AS home_address
FROM tab1;

The new view definition can be included in your AI profile.

Enhance Metadata with Comments

You can further enrich metadata by defining comments on tables and columns and enabling comment usage in the AI profile. Comments provide descriptive information about database objects, including their purpose, intended usage, valid values, and the meaning of stored data.

When comments are included in the metadata available to Select AI, they provide additional context that can help the LLM interpret database objects and generate more relevant SQL queries.

For example, you can define comments on both tables and columns as follows:

COMMENT ON TABLE my_generic_table IS
  'Contains customer demographic information.';

COMMENT ON COLUMN customer.status IS 'Customer lifecycle status. Valid values are ACTIVE, INACTIVE, PROSPECT, and CHURNED.';

To include comments in the metadata used by Select AI, specify the following attribute in the AI profile: {"comments": "true"}

Considerations

Including comments increases the amount of metadata provided to the LLM. In environments with a large number of tables, columns, or extensive comments, the metadata may consume a significant portion of the available context window. Depending on the selected LLM and its token limits, this can reduce the amount of space available for user prompts and generated responses.

Enhance Metadata with Constraints to Improve Join Generation

Table relationships are often required to generate SQL queries that join data across multiple tables. Although referential integrity constraints, such as foreign keys, define these relationships in the database, they are not included in the metadata sent to the LLM by default.

To include constraint information in the metadata available to Select AI, specify the following attribute in the AI profile: { "constraints": "true" }.

Including constraint metadata enables the LLM to identify relationships between tables and can improve its ability to generate appropriate join conditions.

For example, the following table definition includes a foreign key constraint that relates employees to departments through the dept_no column:

CREATE TABLE employee (
   emp_no   NUMBER PRIMARY KEY,
   emp_name VARCHAR2(50),
   salary   NUMBER,
   dept_no  NUMBER,
   CONSTRAINT emp_dept_fk FOREIGN KEY (dept_no) REFERENCES department(dept_no)
);

Enhance Metadata with Annotations

Oracle AI Database 26ai supports annotations on tables and columns. Annotations provide additional semantic information that can be used to describe database objects and their attributes.

To include annotation metadata in the context provided to Select AI, specify the following attribute in the AI profile: { "annotations": "true" }.

When enabled, Select AI includes annotation information in the metadata sent to the LLM. This additional context can help the model interpret the purpose and meaning of database objects.

For example, the following table definition includes annotations on both the table and its columns:

CREATE TABLE employee (
    emp_no NUMBER
        ANNOTATIONS (
            mandatory,
            system_generated,
            alias 'employee number'
        ),
    emp_name VARCHAR2(50)
        ANNOTATIONS (display_value),
    salary NUMBER,
    dept_no NUMBER
        ANNOTATIONS (alias 'department number')
)
ANNOTATIONS (
    primary_table 'Table storing the main information about employees'
);

Annotations can provide valuable business context that is not captured by object names, comments, or constraints alone. However, like comments and other metadata, annotations increase the amount of context sent to the LLM. In environments with extensive metadata, consider the token limits of the selected LLM when enabling annotations.

Pre-Join Tables Using Views

If certain table joins are used repeatedly to answer user prompts, you can use domain knowledge to define views over those tables. Pre-joining tables can simplify the SQL that Select AI generates and reduce the need for the LLM to infer join relationships at runtime.

As a starting point, you can review previously executed SQL statements in the V$SQL view to identify commonly used join patterns.

Although referential integrity constraints can help the LLM determine relationships between tables, the LLM may still infer joins incorrectly in some cases. By pre-joining tables in a view, you can expose the intended relationship directly and improve the consistency of generated queries.

Define Columns for Common Metrics

If users frequently ask for derived metrics or KPIs, define them as columns in views. This gives Select AI a stable, documented representation of business logic and reduces variation in generated SQL. Defining calculations in a view also avoids relying on the LLM to repeatedly infer formulas or perform mathematical computations.

CREATE OR REPLACE VIEW product_financial_metrics AS
SELECT product_id,
       net_income,
       total_revenue,
       (net_income / NULLIF(total_revenue, 0)) * 100 AS net_profit_margin
FROM finance_summary;

Minimize Metadata Sent to the LLM

Select AI sends database metadata such as schemas, tables, views, and columns to the LLM so it can identify the objects needed to answer a natural-language question. Provide only the metadata relevant to the intended use case.

For example, an AI profile for sales questions should include sales-related schemas or tables, rather than every object in the database. Exclude duplicate, obsolete, test, development, and sparsely populated objects that are unlikely to be useful. You can further reduce metadata by creating views that pre-join commonly used tables or expose only relevant columns.

You can control the metadata available to the LLM in these ways:

Reducing metadata helps the LLM focus on the appropriate database objects. It also lowers token usage and cost and reduces the risk of exceeding the model’s context-window limit.

For large databases, use object_list_mode: "automated" to apply RAG to the metadata and identify objects most relevant to the user’s prompt. Alternatively, create separate AI profiles for business domains, such as Sales, Customer Relationship, Finance, or Inventory. An agentic application can then select the most appropriate profile based on the request. Automated object-list mode can reduce the need to maintain multiple profiles.

Governance consideration: Object names, column names, annotations, and comments may be sent to the AI provider. Avoid sensitive metadata content in the object list. Create views over such objects and sanitize names, comments, annotations, or exposed columns as needed.

Improve Prompts

Use specific prompts to reduce ambiguity and help the LLM generate more accurate SQL. Include the business definition, filters, time period, and other relevant context. Also use the exact values stored in your tables. For example, “How many customers are in the US?” may return no results if the country column stores values such as USA, UNITED STATES, or another variant.

Less Specific Prompt More Specific Prompt
“How many customers do we have?” “How many active customers did we have as of June 30, 2026?”
“Show top products.” “Show the top 10 products by total revenue for calendar year 2025, excluding discontinued products.”
“How many customers are in the US?” “How many customers have country_code = ‘US’?”

When valid values are unclear, document a manageable, stable set in column comments and enable comments in your AI profile. You can also let users discover values before forming a prompt, for example: “List the distinct country names for product X.”

By default, Select AI generates case-insensitive comparisons for string and text values. This lets a prompt that uses “United States” match a stored value such as UNITED STATES. To require case-sensitive comparisons, set the case_sensitive_values attribute to true in the AI profile.

Choose an LLM That Performs Well for SQL Generation

Not all LLMs perform equally well at text-to-SQL generation. Evaluate several models against representative business questions, schemas, and expected SQL for your domain. Compare correctness, join behavior, handling of business terminology and case sensitivity, reliability, latency, and cost before standardizing on a model. Use SQL-generation benchmarks such as BIRD-SQL as one input, but validate results against your own data model and use cases.