DESIGN_QUERIES

Contains info about design queries for a given design. The following functions populate this table:

Column Name Column Type Description

DESIGN_ID

INTEGER

Unique id that Database Designer assigned to the design.
DESIGN_NAME VARCHAR

Name that you specified for the design.
DESIGN_QUERY_ID INTEGER Unique id that Database Designer assigned to the design query.
DESIGN_QUERY_ID_INDEX INTEGER Database Designer chunks the query text if it exceeds the maximum attribute size before storing it in this table. Database Designer stored all chunks stored under the same value of DESIGN_QUERY_ID. DESIGN_QUERY_ID_INDEX keeps track of the order of the chunks, starting with 0 and ending in n, the index of the final chunk.
QUERY_TEXT VARCHAR Text of the query chunk, or the entire query text if it does not exceed the maximum attribute size.
WEIGHT FLOAT

A value from 0 to 1 that indicates the importance of that query in creating the design. Assign a higher weight to queries that you run frequently so that Database Designer prioritizes those queries in creating the design. Default: 1.
DESIGN_QUERY_SEARCH_PATH VARCHAR The search path with which the query is to be parsed.
DESIGN_QUERY_SIGNATURE INTEGER Categorizes queries that affect the design that Database Designer creates in the same way. Database Designer assigns a signature to each query, weights one query for each signature group, depending on how many queries there are with that signature, and Database Designer considers that query when creating the design.

Example

Add queries to VMART_DESIGN and query the DESIGN_QUERIES table:

=> SELECT DESIGNER_ADD_DESIGN_QUERIES('VMART_DESIGN', '/tmp/examples/vmart_queries.sql','true');
 DESIGNER_ADD_DESIGN_QUERIES
-----------------------------
Number of accepted queries                      =9
Number of queries referencing non-design tables =0
Number of unsupported queries                   =0
Number of illegal queries                       =0
=> \x
Expanded display is on.
=> SELECT * FROM V_MONITOR.DESIGN.QUERIES
-[ RECORD 1 ]------------+-------------------
design_id                | 45035996273705090
design_name              | vmart_design
design_query_id          | 1
design_query_id_index    | 0
query_text               | SELECT fat_content
FROM (
SELECT DISTINCT fat_content
  FROM product_dimension
  WHERE department_description
  IN ('Dairy') ) AS food
  ORDER BY fat_content
  LIMIT 5;
weight                   | 1
design_query_search_path | v_dbd_vmart_design_vmart_design_ltt, "$user", public, v_catalog, v_monitor, v_internal
design_query_signature   | 45035996273724651

-[ RECORD 2]-------------+-------------------
design_query_id          | 2
design_query_id_index    | 0
query_text               | SELECT order_number, date_ordered
        FROM store.store_orders_fact orders
        WHERE orders.store_key IN (
        SELECT store_key
        FROM store.store_dimension
        WHERE store_state = 'MA')
        AND orders.vendor_key NOT IN (
        SELECT vendor_key
        FROM public.vendor_dimension
        WHERE vendor_state = 'MA')
        AND date_ordered < '2012-03-01';
         
weight                   | 1
design_query_search_path | v_dbd_vmart_design_vmart_design_ltt, "$user", public, v_catalog, v_monitor, v_internal
design_query_signature   | 45035996273724508   
-[ RECORD 3]-------------+-------------------
...