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

See Also

Running Database Designer Programmatically