trafodion一次sql语句优化

EXPLAIN OPTIONS ‘f’
select count(*)
from YF.VIEW_TEST t,YF.VIEWS n
where productid = ‘50’
and t.end_date BETWEEN ‘2020-12-01’ AND ‘2020-12-31 23:59:59’
and t.USERID = n.USERID
and n.provinceid=‘38’

语句分析结果，上面都是两张视图查询结果，分别由不同的两张表构成，总共4张表，大表有1亿+数据，小表也有几百万的数据量。经过查询发现主要慢在第二张视图，由于第二张表是两个表做的视图，根据情况做了索引，搜索数据量还是巨大，半小时查询不出结果。，

LC RC OP OPERATOR OPT DESCRIPTION CARD

---

14 . 15 root 1.00E+000
13 . 14 sort_partial_aggr_ro 1.00E+000
12 . 13 esp_exchange 1:2(hash2) 1.00E+000
11 . 12 sort_partial_aggr_le 1.00E+000
10 5 11 hybrid_hash_join 8.96E+003
7 9 10 merge_union 2.22E+004
8 . 9 esp_exchange 2(hash2):19(hash2) 2.07E+004
. . 8 trafodion_index_scan EP_PRODUC 2.07E+004
6 . 7 esp_exchange 2(hash2):9(hash2) 1.50E+003
. . 6 trafodion_index_scan IDX_EPP__USER_P 1.50E+003
2 4 5 merge_union 2.03E+007
3 . 4 esp_exchange 2(hash2):6(hash2) 1.75E+007
. . 3 trafodion_index_scan IDX_PNR_UP__USE 1.75E+007
1 . 2 esp_exchange 2(hash2):10(hash2) 2.84E+006
. . 1 trafodion_index_scan PROVINCE_CODE_TF_F_ 2.84E+006

重新写如下sql，分解视图后，用union all，可以用上主键，跟原来sql条件一致，执行花费3分15秒

EXPLAIN OPTIONS ‘f’
select count()
from YF.USER t, YF.F_USE d
where productid = ‘50’
and t.end_date BETWEEN ‘2020-12-01’ AND ‘2020-12-31 23:59:59’
and t.USERID = d.USERID
and d.provinceid IN (‘38’,‘ZZZZ’)
union all
select count()
from YF.USE_PRODUCT t, U1.TUSE u
where productid = ‘0’
and t.end_date BETWEEN ‘2020-02-01’ AND ‘2020-02-31 23:59:59’
and t.USERID = u.USERID
and u.provinceid=‘38’;
分析结果整体上，要读取的数据量范围已经大大减少
2. LC RC OP OPERATOR OPT DESCRIPTION CARD

---

22 . 23 root 2.00E+000
11 21 22 merge_union 2.00E+000
20 . 21 sort_partial_aggr_ro 1.00E+000
19 . 20 esp_exchange 1:2(hash2) 1.00E+000
18 . 19 sort_partial_aggr_le 1.00E+000
16 17 18 nested_join 2.22E+004
. . 17 trafodion_index_scan UP_TF_F_USER_IDX 1.00E+000
13 15 16 merge_union 2.22E+004
14 . 15 esp_exchange 2(hash2):19(hash2) 2.07E+004
. . 14 trafodion_index_scan EP_ PRODUC 2.07E+004
12 . 13 esp_exchange 2(hash2):9(hash2) 1.50E+003
. . 12 trafodion_index_scan IDX_EPP USER_P 1.50E+003
10 . 11 sort_partial_aggr_ro 1.00E+000
9 . 10 esp_exchange 1:2(hash2) 1.00E+000
8 . 9 sort_partial_aggr_le 1.00E+000
5 7 8 nested_join 2.22E+004
6 . 7 probe_cache 1.00E+000
. . 6 trafodion_vsbb_scan TF_F_USER 1.00E+000
2 4 5 merge_union 2.22E+004
3 . 4 esp_exchange 2(hash2):19(hash2) 2.07E+004
. . 3 trafodion_index_scan EP_ _PRODUC 2.07E+004
1 . 2 esp_exchange 2(hash2):9(hash2) 1.50E+003
. . 1 trafodion_index_scan IDX_EPP_TF_F_USER_P 1.50E+003