结合使用优化器生成的定向查询和自定义定向查询

可以根据优化器所创建的带注释 SQL 来创建自己的自定义定向查询。当评估优化器创建用于处理给定查询的计划以及测试计划修改内容时，此方法特别有用。

例如，您可能希望修改优化器对以下查询的实施方式：

=> SELECT COUNT(customer_name) Total, customer_region Region
    FROM (store_sales s JOIN customer_dimension c ON c.customer_key = s.customer_key)
    JOIN product_dimension p ON s.product_key = p.product_key
    WHERE p.category_description ilike '%Medical%'
      AND p.product_description ilike '%antibiotics%'
      AND c.customer_age <= 30 AND YEAR(s.sales_date)=2017
    GROUP BY customer_region;

对此查询运行 EXPLAIN 时，您会发现优化器将投影 customers_proj_age 用于 customer_dimension 表。此投影按列 customer_age 进行排序。因此，优化器将根据 customer_key 对表 store_sales 和 customer_dimension 进行哈希联接。

分析 customer_dimension 表数据后，您观察到大部分客户的年龄不到 30 岁，因此对按 customer_key 排序的 customer_dimension 表使用投影 customer_proj_id 更有意义：

您可以创建一个用于封装此更改的定向查询，如下所示：

1. 使用 EXPLAIN ANNOTATED 获取由优化器生成的查询注释：

   => \o annotatedQuery
   => EXPLAIN ANNOTATED SELECT COUNT(customer_name) Total, customer_region Region
        FROM (store_sales s JOIN customer_dimension c ON c.customer_key = s.customer_key)
        JOIN product_dimension p ON s.product_key = p.product_key
        WHERE p.category_description ilike '%Medical%'
          AND p.product_description ilike '%antibiotics%'
          AND c.customer_age <= 30 AND YEAR(s.sales_date)=2017
        GROUP BY customer_region;
   => \o
   => \! cat annotatedQuery
   ...
   SELECT /*+syntactic_join,verbatim*/ count(c.customer_name) AS Total, c.customer_region AS Region
    FROM ((public.store_sales AS s/*+projs('public.store_sales_super')*/
       JOIN /*+Distrib(L,B),JType(H)*/ public.customer_dimension AS c/*+projs('public.customers_proj_age')*/
         ON (c.customer_key = s.customer_key))
       JOIN /*+Distrib(L,B),JType(M)*/ public.product_dimension AS p/*+projs('public.product_dimension')*/
         ON (s.product_key = p.product_key))
    WHERE ((date_part('year'::varchar(4), (s.sales_date)::timestamp(0)))::int = 2017)
        AND (c.customer_age <= 30)
        AND ((p.category_description)::varchar(32) ~~* '%Medical%'::varchar(9))
        AND (p.product_description ~~* '%antibiotics%'::varchar(13))
    GROUP BY  /*+GByType(Hash)*/ 2
   (4 rows)
   

2. 修改带注释的查询：

   
   SELECT /*+syntactic_join,verbatim*/ count(c.customer_name) AS Total, c.customer_region AS Region
    FROM ((public.store_sales AS s/*+projs('public.store_sales_super')*/
       JOIN /*+Distrib(L,B),JType(H)*/ public.customer_dimension AS c/*+projs('public.customer_proj_id')*/
         ON (c.customer_key = s.customer_key))
       JOIN /*+Distrib(L,B),JType(H)*/ public.product_dimension AS p/*+projs('public.product_dimension')*/
         ON (s.product_key = p.product_key))
    WHERE ((date_part('year'::varchar(4), (s.sales_date)::timestamp(0)))::int = 2017)
        AND (c.customer_age <= 30)
        AND ((p.category_description)::varchar(32) ~~* '%Medical%'::varchar(9))
        AND (p.product_description ~~* '%antibiotics%'::varchar(13))
    GROUP BY  /*+GByType(Hash)*/ 2
   

3. 使用修改后的带注释查询来创建所需的定向查询：

   • 使用 SAVE QUERY 保存所需的输入查询：

     
     => SAVE QUERY SELECT COUNT(customer_name) Total, customer_region Region
         FROM (store_sales s JOIN customer_dimension c ON c.customer_key = s.customer_key)
         JOIN product_dimension p ON s.product_key = p.product_key
         WHERE p.category_description ilike '%Medical%'
           AND p.product_description ilike '%antibiotics%'
           AND c.customer_age <= 30 AND YEAR(s.sales_date)=2017
         GROUP BY customer_region;
     

   • 创建一个自定义定向查询，将保存的输入查询与修改后的带注释查询相关联：

     
     => CREATE DIRECTED QUERY CUSTOM 'getCustomersUnder31'
        SELECT /*+syntactic_join,verbatim*/ count(c.customer_name) AS Total, c.customer_region AS Region
      FROM ((public.store_sales AS s/*+projs('public.store_sales_super')*/
         JOIN /*+Distrib(L,B),JType(H)*/ public.customer_dimension AS c/*+projs('public.customer_proj_id')*/
           ON (c.customer_key = s.customer_key))
         JOIN /*+Distrib(L,B),JType(H)*/ public.product_dimension AS p/*+projs('public.product_dimension')*/
           ON (s.product_key = p.product_key))
      WHERE ((date_part('year'::varchar(4), (s.sales_date)::timestamp(0)))::int = 2017)
          AND (c.customer_age <= 30)
          AND ((p.category_description)::varchar(32) ~~* '%Medical%'::varchar(9))
          AND (p.product_description ~~* '%antibiotics%'::varchar(13))
      GROUP BY  /*+GByType(Hash)*/ 2;
     CREATE DIRECTED QUERY
     

4. 激活此定向查询：

   => ACTIVATE DIRECTED QUERY getCustomersUnder31;
   ACTIVATE DIRECTED QUERY
   

当优化器处理与此定向查询的输入查询相匹配的查询时，它会使用此定向查询的带注释查询来生成查询计划：

=> EXPLAIN SELECT COUNT(customer_name) Total, customer_region Region
     FROM (store_sales s JOIN customer_dimension c ON c.customer_key = s.customer_key)
     JOIN product_dimension p ON s.product_key = p.product_key
     WHERE p.category_description ilike '%Medical%'
       AND p.product_description ilike '%antibiotics%'
       AND c.customer_age <= 30 AND YEAR(s.sales_date)=2017
     GROUP BY customer_region;

 The following active directed query(query name: getCustomersUnder31) is being executed:
...