Autonomous Database Select AI uses large language models (LLMs) and metadata from your database schema to automate SQL query generation from your natural language prompts. There are multiple ways to help enhance query accuracy. For example, LLM fine tuning may also improve accuracy but can be prohibitively costly and difficult to keep current as new foundation models are released or business schemas evolve. Similarly direct human feedback can also enable potentially more accurate, relevant, and business-aligned results by offering guidance to the LLM – a kind of prompt tuning – for query refinement. 

User feedback can range from correcting syntax issues, adjusting filters and joins, or clarifying ambiguous natural language inputs, as well as explicitly supplying corrected SQL queries. Such feedback can be provided by experts during application user-acceptance testing but may also be provided by end users through suitable application interfaces. This creates a collaborative loop between humans and AI, helping to improve query results and trust on the part of end-users. Over time, such feedback can lead to queries that meet users’ expectations. 

Select AI Feedback

Select AI introduces a new prompt tuning mechanism that enables users to provide direct feedback on generated SQL queries, allowing the system to learn from user interactions over time. This learning approach can help organizations using Select AI to:

  • Improve accuracy: users can correct inaccurate SQL queries.
  • Enhance user experience: users can confirm correct queries or suggest changes, resulting in more personalized and contextually accurate queries.
  • Facilitate continuous learning: using historical feedback as hints for future query generation, Select AI evolves and adapts to user needs.

The ‘feedback’ action and FEEDBACK procedures in Select AI can assist prompt tuning by collecting and analyzing user feedback – both positive and negative – on generated SQL queries. This enables a dynamic learning loop where the system incrementally improves query accuracy based on user interactions.

  • Positive feedback confirms the accuracy of a given generated SQL query. Select AI stores the confirmed query for future reference.
  • Negative feedback highlights errors or required changes in the generated query. Select AI refines the query using the large language model (LLM) specified in their AI profile and then stores the refined query for future query generation. The stored refined query will be retrieved using semantic similarity search and sent to LLM as a hint in the augmented prompt.

Due to its use of Oracle AI Vector Search, the feedback capability requires Oracle Autonomous Database 23ai.

Semantically relevant prompt examples for LLM

To support this capability, Select AI automatically creates a vector index when the feedback capability is used for the first time. This vector index is associated with the specific AI profile being used and is named “<profile_name>_FEEDBACK_VECINDEX”. Select AI initializes the vector index with default attributes, such as similarity_threshold and match_limit, which can be modified via the DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX procedure. 

User prompts are vectorized using the transformer (embedding model) as specified in the AI profile – either explicitly or using the default transformer for the AI provider. Vectors and the corresponding SQL queries are stored in a vector table. For new prompts using the same AI profile, Select AI searches the vector table for the most similar historical prompts and SQL queries, and it sends the retrieved results in the augmented prompt to the LLM.

Examples using SQL command line

Here, we use the ‘feedback’ action for submitting feedback for the showsql, runsql, and explainsql actions against the SQL query text. In the first example below, we illustrate how providing negative feedback is done, by pointing out that the aggregation operator sum should have been used instead of count. In the second example, the positive feedback simply confirms that the generated query is correct. 

-- Negative feedback example:
SQL > select ai feedback for query "select ai showsql how many watch histories in total", please use sum instead of count;

-- Positive feedback example:
SQL > select ai feedback for query "select ai showsql how many watch histories in total", the sql query generated is correct;


Alternatively, you can provide feedback using a specific SQL ID. You can retrieve the SQL ID by querying the V$MAPPED_SQL view.

-- Negative feedback example:
SQL > select ai feedback please use sum instead of count for sql_id  1v1z68ra6r9zf;

-- Positive feedback example:
SQL > select ai feedback sql query result is correct for sql_id  1v1z68ra6r9zf;

If you don’t have the prompt or the SQL ID, you can also provide feedback using the default LASTAI SQL query. Note: you will need to set server output off. You must also grant the user privileges to access v$session and v$mapped_sql to use LASTAI. 

-- Negative feedback example:
SQL > select ai feedback use ascending sorting for ranking;

-- Positive feedback example:
SQL > select ai feedback the result is correct;

Examples using FEEDBACK PL/SQL procedures

You can use feedback from PL/SQL for ease of integration with application code. There are two such procedures, one that takes a SQL ID and another that takes the SQL text. 

  PROCEDURE feedback(
      profile_name      IN  VARCHAR2,
      sql_id            IN  VARCHAR2,
      feedback_type     IN  VARCHAR2 DEFAULT NULL,
      response          IN  CLOB DEFAULT NULL,
      feedback_content  IN  CLOB DEFAULT NULL,   
      operation         IN  VARCHAR2 DEFAULT 'add'
  );
 
  PROCEDURE feedback(
      profile_name      IN  VARCHAR2,
      sql_text          IN  CLOB,
      feedback_type     IN  VARCHAR2 DEFAULT NULL,
      response          IN  CLOB DEFAULT NULL,
      feedback_content  IN  CLOB DEFAULT NULL,
      operation         IN  VARCHAR2 DEFAULT 'add' 
  );

 

The following examples illustrate how to use a SQL ID, use SQL text, and delete the feedback for a given SQL ID. 

 

EXEC dbms_cloud_ai.feedback(profile_name=>'OCI_FEEDBACK1',
                            sql_id=> '852w8u83gktc1',
                            feedback_type=>'positive',
                            operation=>'add');

EXEC dbms_cloud_ai.feedback(profile_name=>'OCI_FEEDBACK1',
                            sql_text=> 'select ai showsql how many movies',
                            feedback_type=> 'negative',
                            response=>'SELECT SUM(1) FROM "ADMIN"."MOVIES"',
                            feedback_content=>'Use SUM instead of COUNT');

EXEC dbms_cloud_ai.feedback(profile_name=>'OCI_FEEDBACK1',
                            sql_id=> '852w8u83gktc1',
                            operation=>'delete');

 

Resources

For mor information…