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. IfDESIGNER_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
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 |
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
orUPDATE
query has one or more subqueries.INSERT
query does not include aSELECT
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'';');