GBase 8a 数据库执行计划

一条SQL语句的通用的完整执行流程一般是：

smart scan -> scan -> join -> aggregation -> sort -> materialization -> send result

包含聚集和排序操作的时候通常无需单独的物化步骤，物化在聚集和排序过程中已经完成。如果是包含嵌套子查询的复杂SQL，嵌套子查询从内至外递归执行，每一层的执行顺序与上述过程基本相同。

了解SQL预支的执行过程，有助于SQL执行过程中遇到性能问题排查及分析瓶颈，就是查看上面步骤具体是哪个步骤耗时长，定位后再分析I/O因素、buffer大小对性能影响等信息，看如何优化能提高性能。

例如，tpch模型第三条：

select

    l_orderkey,

    sum(l_extendedprice * (1 - l_discount)) as revenue,

    o_orderdate,

    o_shippriority

from

    customer,

    orders,

    lineitem

where

    c_mktsegment = 'HOUSEHOLD'

    and c_custkey = o_custkey

    and l_orderkey = o_orderkey

    and o_orderdate < date '1995-03-16'

    and l_shipdate > date '1995-03-16'

group by

    l_orderkey,

    o_orderdate,

    o_shippriority

order by

    revenue desc,

    o_orderdate

limit 10;

执行计划如下：

*************************** 1. row ***************************

   ID: 02

MOTION: [RESULT]

OPERATION: Step

TABLE: <01>

CONDITION:

*************************** 2. row ***************************

   ID:

MOTION:

OPERATION: ORDER

TABLE:

CONDITION: ORDER BY SUM((l_extendedprice * (1 - l_discount))) DESC, o_orderda..

*************************** 3. row ***************************

   ID:

MOTION:

OPERATION: LIMIT

TABLE:

CONDITION: LIMIT 10

*************************** 4. row ***************************

   ID: 01

MOTION: [GATHER]

OPERATION: INNER JOIN

TABLE:

CONDITION: (c_custkey = o_custkey)

*************************** 5. row ***************************

   ID:

MOTION:

OPERATION: Step

TABLE: <00>

CONDITION:

*************************** 6. row ***************************

   ID:

MOTION:

OPERATION: INNER JOIN

TABLE:

CONDITION: (l_orderkey = o_orderkey)

*************************** 7. row ***************************

   ID:

MOTION:

OPERATION: SCAN

TABLE: orders[o_orderkey]

CONDITION: (o_orderdate{S} < cast('1995-03-16' as date))

*************************** 8. row ***************************

   ID:

MOTION:

OPERATION: SCAN

TABLE: lineitem[l_orderkey]

CONDITION: (l_shipdate{S} > cast('1995-03-16' as date))

*************************** 9. row ***************************

   ID:

MOTION:

OPERATION: GROUP

TABLE:

CONDITION: GROUP BY l_orderkey, o_orderdate, o_shippriority

*************************** 10. row ***************************

   ID:

MOTION:

OPERATION: ORDER

TABLE:

CONDITION: ORDER BY .. DESC, o_orderdate ASC

*************************** 11. row ***************************

   ID:

MOTION:

OPERATION: LIMIT

TABLE:

CONDITION: LIMIT 10

*************************** 12. row ***************************

   ID: 00

MOTION: [BROADCAST]

OPERATION: SCAN

TABLE: customer[c_custkey]

CONDITION: (c_mktsegment{S} = 'HOUSEHOLD')

12 rows in set (Elapsed: 00:00:00.06)

3gfe�,��