Select AI (natural language to SQL) sounds magical in demos. In production, it requires careful setup to be useful. Here’s what the production experience looks like.
Schema naming is everything:
Select AI uses table and column names to understand your schema. Cryptic names like T_CUST_HDR or AMT_NET_USD_EQUIV confuse the LLM. Before deploying Select AI, audit your schema naming:
-- Bad: obscure naming that defeats NLP
SELECT C_ID, FNM, LNM FROM T_CUST WHERE STAT = 'A';
-- Good: self-documenting naming
SELECT customer_id, first_name, last_name FROM customers WHERE status = 'ACTIVE';If you can’t rename legacy tables/columns, use synonym layers or annotate them:
-- Add annotations as hints for Select AI
ALTER TABLE T_CUST MODIFY (FNM ANNOTATIONS (ADD UILabel 'First Name'));
ALTER TABLE T_CUST ANNOTATIONS (ADD BusinessName 'Customers');Profile refinement with examples:
Select AI profiles support example question-SQL pairs that guide the LLM:
BEGIN
DBMS_CLOUD_AI.ADD_EXAMPLE(
profile_name => 'hr_assistant',
question => 'How many employees are in each department?',
sql_text => 'SELECT d.department_name, COUNT(e.employee_id) AS headcount FROM departments d LEFT JOIN employees e ON e.department_id = d.department_id GROUP BY d.department_name ORDER BY headcount DESC'
);
END;Adding 10-20 well-chosen examples dramatically improves accuracy for domain-specific queries.
Monitor generated SQL:
Always log what Select AI generates before it executes in production:
SELECT AI SHOWSQL 'Top 5 products by revenue last quarter'
USING PROFILE sales_ai;Review and approve generated SQL patterns before exposing Select AI to end users.
