Trafodion 查看执行计划

Trafodion数据库一般通过EXPLAIN命令查看SQL的执行计划，部分使用方法如下，

• explain select ... from ...;

• explain options 'f' select ... from ...;

• prepare s from select .. from ...;     explain {options 'f'} s;

下面通过一个简单的两表关联介绍如何用上述几种方法分别去查看SQL的执行计划。

1 创建测试表并插入样例数据

SQL>create table test1(a int not null, b varchar(10));

--- SQL operation complete.

SQL>create table test2(a int not null, b varchar(10));

--- SQL operation complete.

SQL>insert into test1 values(1,'LIU'),(2,'WANG');

--- 2 row(s) inserted.

SQL>insert into test2 values(1,'ZHANG'),(2,'LI');

--- 2 row(s) inserted.

2 explain + sql 查看执行计划

SQL>explain
+>select test1.a, test1.b, test2.b
+>from test1, test2
+>where test1.a = test2.a;

------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... NOT NAMED
PLAN_ID .................. 212344925683685481
ROWS_OUT ............. 5,000
EST_TOTAL_COST ........... 0.01
STATEMENT ................
select test1.a, test1.b, test2.b
from test1,
                             test2
where test1.a = test2.a;

------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 4        ONLY CHILD 3
REQUESTS_IN .............. 1
ROWS_OUT ............. 5,000
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est ....... 5,000
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  statement_index ........ 0
  affinity_value ......... 0
  est_memory_per_cpu ..... 260 KB
  max_max_cardinality  5,000
  total_overflow_size .... 0.00 KB
  xn_access_mode ......... read_only
  xn_autoabort_interval    0
  auto_query_retry ....... enabled
  plan_version ....... 2,600
  embedded_arkcmp ........ used
  LDAP_USERNAME .......... ZZ
  SHOWCONTROL_SHOW_ALL ... OFF
  GENERATE_EXPLAIN ....... ON
  ObjectUIDs ............. 7186168948089148044, 7186168948089146661
  select_list ............ TRAFODION.SEABASE.TEST1.A,
                             TRAFODION.SEABASE.TEST1.B,
                             TRAFODION.SEABASE.TEST2.B
  input_variables ........ execution_count

HYBRID_HASH_JOIN ==========================  SEQ_NO 3        CHILDREN 2, 1
REQUESTS_IN .............. 1
ROWS_OUT ............. 5,000
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  memory_quota ........... 656 MB
  max_card_est ....... 5,000
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  est_memory_per_cpu ..... 260 KB
  join_type .............. inner
  join_method ............ hash
  hash_join_predicates ... (TRAFODION.SEABASE.TEST1.A =
                             TRAFODION.SEABASE.TEST2.A)

TRAFODION_SCAN ============================  SEQ_NO 2        NO CHILDREN
TABLE_NAME ............... TEST1
REQUESTS_IN .............. 1
ROWS_OUT ............... 100
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est ......... 100
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SEABASE.TEST1
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_size ........... 100
  probes ................. 1
  rows_accessed ........ 100
  column_retrieved ....... #1:1,#1:2,#1:3
  key_columns ............ SYSKEY

TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... TEST2
REQUESTS_IN .............. 1
ROWS_OUT ............... 100
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est ......... 100
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table TRAFODION.SEABASE.TEST2
  object_type ............ Trafodion
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  cache_size ........... 100
  probes ................. 1
  rows_accessed ........ 100
  column_retrieved ....... #1:1,#1:2,#1:3
  key_columns ............ SYSKEY

--- SQL operation complete.

3 explain options 'f' + sql 查看执行计划

SQL>explain options 'f'
+>select test1.a, test1.b, test2.b
+>from test1, test2
+>where test1.a = test2.a;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  5.00E+003
2    1    3    hybrid_hash_join                                      5.00E+003
.    .    2    trafodion_scan                  TEST1                 1.00E+002
.    .    1    trafodion_scan                  TEST2                 1.00E+002

--- SQL operation complete.

4 prepare + explain 查看执行计划

SQL>prepare s from
+>select test1.a, test1.b, test2.b
+>from test1, test2
+>where test1.a = test2.a;

--- SQL command prepared.

SQL>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  5.00E+003
2    1    3    hybrid_hash_join                                      5.00E+003
.    .    2    trafodion_scan                  TEST1                 1.00E+002
.    .    1    trafodion_scan                  TEST2                 1.00E+002

--- SQL operation complete.