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

Superuser
Design creator with all privileges that pertain to the specified query and all queries that this function returns (see Privileges Required for Common Database Operations).

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'';');