DESIGNER_SINGLE_RUN

评估在指定时间跨度内完成执行的所有查询,并返回可用于部署的设计。此设计包含建议用于优化评估查询的投影。除非您重定向输出,否则 DESIGNER_SINGLE_RUN 会将设计返回到 STDOUT。

这是元函数。您必须在顶级 SELECT 语句中调用元函数。

行为类型

易变

语法

DESIGNER_SINGLE_RUN ('interval')
interval
指定元函数调用之前的时间间隔。Database Designer 评估在指定时间间隔内运行完成的所有查询。

特权

超级用户或 DBUSER

示例

-----------------------------------------------------------------------
-- SSBM dataset test
-----------------------------------------------------------------------
-- create ssbm schema
\! $TARGET/bin/vsql -f 'sql/SSBM/SSBM_schema.sql' > /dev/null 2>&1
\! $TARGET/bin/vsql -f 'sql/SSBM/SSBM_constraints.sql' > /dev/null 2>&1
\! $TARGET/bin/vsql -f 'sql/SSBM/SSBM_funcdeps.sql' > /dev/null 2>&1

-- run these queries
\! $TARGET/bin/vsql -f 'sql/SSBM/SSBM_queries.sql' > /dev/null 2>&1
-- Run single API
select designer_single_run('1 minute');

...
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                designer_single_run
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CREATE PROJECTION public.part_DBD_1_rep_SingleDesign /*+createtype(D)*/
(
 p_partkey ENCODING AUTO,
 p_name ENCODING AUTO,
 p_mfgr ENCODING AUTO,
 p_category ENCODING AUTO,
 p_brand1 ENCODING AUTO,
 p_color ENCODING AUTO,
 p_type ENCODING AUTO,
 p_size ENCODING AUTO,
 p_container ENCODING AUTO
)
AS
 SELECT p_partkey,
        p_name,
        p_mfgr,
        p_category,
        p_brand1,
        p_color,
        p_type,
        p_size,
        p_container
 FROM public.part
 ORDER BY p_partkey
UNSEGMENTED ALL NODES;

CREATE PROJECTION public.supplier_DBD_2_rep_SingleDesign /*+createtype(D)*/
(
 s_suppkey ENCODING AUTO,
 s_name ENCODING AUTO,
 s_address ENCODING AUTO,
 s_city ENCODING AUTO,
 s_nation ENCODING AUTO,
 s_region ENCODING AUTO,
 s_phone ENCODING AUTO
)
AS
 SELECT s_suppkey,
        s_name,
        s_address,
        s_city,
        s_nation,
        s_region,
        s_phone
 FROM public.supplier
 ORDER BY s_suppkey
UNSEGMENTED ALL NODES;

CREATE PROJECTION public.customer_DBD_3_rep_SingleDesign /*+createtype(D)*/
(
 c_custkey ENCODING AUTO,
 c_name ENCODING AUTO,
 c_address ENCODING AUTO,
 c_city ENCODING AUTO,
 c_nation ENCODING AUTO,
 c_region ENCODING AUTO,
 c_phone ENCODING AUTO,
 c_mktsegment ENCODING AUTO
)
AS
 SELECT c_custkey,
        c_name,
        c_address,
        c_city,
        c_nation,
        c_region,
        c_phone,
        c_mktsegment
 FROM public.customer
 ORDER BY c_custkey
UNSEGMENTED ALL NODES;

CREATE PROJECTION public.dwdate_DBD_4_rep_SingleDesign /*+createtype(D)*/
(
 d_datekey ENCODING AUTO,
 d_date ENCODING AUTO,
 d_dayofweek ENCODING AUTO,
 d_month ENCODING AUTO,
 d_year ENCODING AUTO,
 d_yearmonthnum ENCODING AUTO,
 d_yearmonth ENCODING AUTO,
 d_daynuminweek ENCODING AUTO,
 d_daynuminmonth ENCODING AUTO,
 d_daynuminyear ENCODING AUTO,
 d_monthnuminyear ENCODING AUTO,
 d_weeknuminyear ENCODING AUTO,
 d_sellingseason ENCODING AUTO,
 d_lastdayinweekfl ENCODING AUTO,
 d_lastdayinmonthfl ENCODING AUTO,
 d_holidayfl ENCODING AUTO,
 d_weekdayfl ENCODING AUTO
)
AS
 SELECT d_datekey,
        d_date,
        d_dayofweek,
        d_month,
        d_year,
        d_yearmonthnum,
        d_yearmonth,
        d_daynuminweek,
        d_daynuminmonth,
        d_daynuminyear,
        d_monthnuminyear,
        d_weeknuminyear,
        d_sellingseason,
        d_lastdayinweekfl,
        d_lastdayinmonthfl,
        d_holidayfl,
        d_weekdayfl
 FROM public.dwdate
 ORDER BY d_datekey
UNSEGMENTED ALL NODES;

CREATE PROJECTION public.lineorder_DBD_5_rep_SingleDesign /*+createtype(D)*/
(
 lo_orderkey ENCODING AUTO,
 lo_linenumber ENCODING AUTO,
 lo_custkey ENCODING AUTO,
 lo_partkey ENCODING AUTO,
 lo_suppkey ENCODING AUTO,
 lo_orderdate ENCODING AUTO,
 lo_orderpriority ENCODING AUTO,
 lo_shippriority ENCODING AUTO,
 lo_quantity ENCODING AUTO,
 lo_extendedprice ENCODING AUTO,
 lo_ordertotalprice ENCODING AUTO,
 lo_discount ENCODING AUTO,
 lo_revenue ENCODING AUTO,
 lo_supplycost ENCODING AUTO,
 lo_tax ENCODING AUTO,
 lo_commitdate ENCODING AUTO,
 lo_shipmode ENCODING AUTO
)
AS
 SELECT lo_orderkey,
        lo_linenumber,
        lo_custkey,
        lo_partkey,
        lo_suppkey,
        lo_orderdate,
        lo_orderpriority,
        lo_shippriority,
        lo_quantity,
        lo_extendedprice,
        lo_ordertotalprice,
        lo_discount,
        lo_revenue,
        lo_supplycost,
        lo_tax,
        lo_commitdate,
        lo_shipmode
 FROM public.lineorder
 ORDER BY lo_suppkey
UNSEGMENTED ALL NODES;

(1 row)