oceanbase

1
#owner: peihan.dph
2
#owner group: SQL3
3
# tags: optimizer
4

5
# test blocking operator for material
6
--disable_warnings
7
drop table if exists t1;
8
drop table if exists t2;
9
--enable_warnings
10
create table t1(c1 bigint primary key,c2 bigint) partition by hash (c1) partitions 10;
11
create table t2(c1 bigint primary key,c2 bigint) partition by hash (c1) partitions 6;
12
insert into t1 values(1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(0,1),(7,8),(8,9),(9,10);
13
insert into t2 values(1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(0,1),(7,8),(8,9),(9,10);
14
--sleep 1
15
commit;
16

17
##need add materail for the left child of merge join
18
explain basic
19
select /*+ USE_PX parallel(3) */* from
20
(select c1,count(*) over(partition by c1) c2 from
21
 (select c1,count(c2) c2 from t1 group by c1)c) a join
22
(select c1,count(*) c2 from t2 group by c1)b on a.c1=b.c1 ;
23
--sorted_result
24
select /*+ USE_PX parallel(3) */* from
25
(select c1,count(*) over(partition by c1) c2 from
26
 (select c1,count(c2) c2 from t1 group by c1)c) a join
27
(select c1,count(*) c2 from t2 group by c1)b on a.c1=b.c1;
28

29
##no need material operator for merge sort receice with local order
30
explain basic
31
select a.c2,count(*) from (select /*+ USE_PX parallel(3) PQ_DISTRIBUTE(b HASH HASH) */a.c1,a.c2,b.c1 c3,b.c2 c4 from t1 a join t2 b on a.c1=b.c1)a group by a.c2;
32
--sorted_result
33
select a.c2,count(*) from (select /*+ USE_PX parallel(3) PQ_DISTRIBUTE(b HASH HASH) */a.c1,a.c2,b.c1 c3,b.c2 c4 from t1 a join t2 b on a.c1=b.c1)a group by a.c2;
34

35