oceanbase

1
#owner: dachuan.sdc
2
#owner group: SQL3
3
# tags: optimizer
4

5
--source mysql_test/test_suite/px/include/exam_data.inc
6

7
## bc2host
8
explain select /*+ USE_PX parallel(2) */ * from stu, score where stu.sid != score.sid and stu.sid < 12;
9
--sorted_result
10
select /*+ USE_PX parallel(2) */ * from stu, score where stu.sid != score.sid and stu.sid < 12;
11

12
# subject 上指定一个选择率，走 NLJ
13
explain select /*+ USE_PX parallel(2) */ * from score, teacher where teacher.subject = score.subject and teacher.tid = 1;
14
--sorted_result
15
select /*+ USE_PX parallel(2) */ * from score, teacher where teacher.subject = score.subject and teacher.tid = 1;
16

17
CREATE TABLE product (pid bigint primary key,pname varchar(50),gmt_modify timestamp(6),gmt_create timestamp(6),thread_id bigint, key k (gmt_modify))duplicate_scope="cluster";
18

19
CREATE TABLE orders (oid bigint primary key,pid bigint,amount int, addr varchar(20),key k (pid) local) partition by hash (oid) partitions 100;
20

21
insert into product values(314265, "abcd", now(), now(), 1234);
22
insert into orders values(314265, 314265, 678, "192.168.1.8");
23

24
insert into product values(314266, "abcd", now(), now(), 1234);
25
insert into orders values(314266, 314266, 678, "192.168.1.8");
26

27
insert into product values(314267, "abcd", now(), now(), 1234);
28
insert into orders values(314267, 314267, 678, "192.168.1.8");
29

30
insert into product values(314268, "abcd", now(), now(), 1234);
31
insert into orders values(314268, 314268, 678, "192.168.1.8");
32

33
#explain select t1.pname,t2.oid,t2.amount from product t1,orders t2 where t2.oid = 314265 and t1.pid=t2.pid;
34
select t1.pname,t2.oid,t2.amount from product t1,orders t2 where t2.oid = 314265 and t1.pid=t2.pid;
35

36

37