DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS

Executes the specified query and evaluates results in the following columns:

QUERY_TEXT (required): Text of potential design queries.
QUERY_WEIGHT (optional): The weight assigned to each query that indicates its importance relative to other queries, a real number >0 and ≤ 1. Database Designer uses this setting when creating the design to prioritize the query. If DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS returns any results that omit this value, Database Designer sets their weight to 1.

After evaluating the queries in QUERY_TEXT, DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS adds all accepted queries to the design. An unlimited number of queries can be added to the design.

Before you add queries to a design, you must add the queried tables with DESIGNER_ADD_DESIGN_TABLES.

Behavior Type

Immutable

Syntax

DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS ( 'design‑name', 'query' )

Parameters

design‑name	Name of the target design.
query	A valid SQL query whose results contain columns named `QUERY_TEXT` and, optionally, `QUERY_WEIGHT`.

Privileges

Non-superuser: design creator with all privileges required to execute the specified query, and all queries returned by this function

Errors

Database Designer returns an error in the following cases:

The query contains illegal syntax.
The query references:
- External or system tables only
- Local temporary or other non-design tables
DELETE or UPDATE query has one or more subqueries.
INSERT query does not include a SELECT clause.
Database Designer cannot optimize the query.

Example

The following example queries the system table QUERY_REQUESTS for all long-running queries (> 1 million microseconds) and adds them to the VMART_DESIGN design. The query returns no information on query weights, so all queries are assigned a weight of 1:

=> SELECT DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS ('VMART_DESIGN', 
   'SELECT request as query_text FROM query_requests where request_duration_ms > 1000000 AND request_type = 
   ''QUERY'';');