oceanbase

1
# owner: link.zt
2
# owner group: sql1
3
# tags: optimizer
4
# description:
5
#
6

7
--disable_query_log
8
set @@session.explicit_defaults_for_timestamp=off;
9
--enable_query_log
10

11
--disable_warnings
12
drop database if exists hualong;
13
--enable_warnings
14
create database hualong;
15
use hualong;
16

17
#
18
CREATE TABLE `cb_dep_acct_54` ( `acctnbr` bigint(20) NOT NULL, `curracctstatcd` varchar(4) NOT NULL, PRIMARY KEY (`acctnbr`)) partition by hash(acctnbr) partitions 5;
19
CREATE TABLE `cb_dep_acctbal_54` ( `acctnbr` bigint(20) NOT NULL, `balcatcd` varchar(4) NOT NULL, `baltypcd` varchar(4) NOT NULL, PRIMARY KEY (`acctnbr`, `balcatcd`, `baltypcd`));
20
CREATE TABLE `cb_dep_rxtnbal_54` ( `acctnbr` varchar(34) NOT NULL, `rtxnnbr` bigint(20) NOT NULL, `balcatcd` varchar(4) NOT NULL, `baltypcd` varchar(4) NOT NULL);
21

22
insert into cb_dep_acct_54 values (1, 'CLS');
23
insert into cb_dep_acct_54 values (2, 'CLS');
24
insert into cb_dep_acct_54 values (3, 'CCC');
25

26
insert into cb_dep_acctbal_54 values (1, 3, 4);
27
insert into cb_dep_acctbal_54 values (3, 7, 9);
28
insert into cb_dep_acctbal_54 values (5, 6, 2333);
29

30
insert into cb_dep_rxtnbal_54 values (1, 4, 3, 2);
31

32
##select * from cb_dep_acctbal_54 s,  cb_dep_acct_54 a where s.acctnbr = a.acctnbr and (a.curracctstatcd != 'CLS' or (a.curracctstatcd = 'CLS' and exists(select 1 from cb_dep_rxtnbal_54 r where r.acctnbr = a.acctnbr)));
33
explain select * from cb_dep_acctbal_54 s,  cb_dep_acct_54 a where s.acctnbr = a.acctnbr and (a.curracctstatcd != 'CLS' or (a.curracctstatcd = 'CLS' and exists(select 1 from cb_dep_rxtnbal_54 r where r.acctnbr = a.acctnbr)));
34
##select * from cb_dep_acctbal_54 s,  cb_dep_acct_54 a where s.acctnbr = a.acctnbr and (a.curracctstatcd != 'CLS' or (a.curracctstatcd = 'CLS' and exists(select 1 from cb_dep_rxtnbal_54 r where r.acctnbr = a.acctnbr) and exists(select 1 from cb_dep_rxtnbal_54 r where r.acctnbr != a.acctnbr)));
35
explain select * from cb_dep_acctbal_54 s,  cb_dep_acct_54 a where s.acctnbr = a.acctnbr and (a.curracctstatcd != 'CLS' or (a.curracctstatcd = 'CLS' and exists(select 1 from cb_dep_rxtnbal_54 r where r.acctnbr = a.acctnbr) and exists(select 1 from cb_dep_rxtnbal_54 r where r.acctnbr != a.acctnbr)));
36
##select * from cb_dep_acctbal_54 s, cb_dep_acct_54 a where s.acctnbr = a.acctnbr and ((a.curracctstatcd != 'CLS' and exists(select 1 from cb_dep_acctbal_54 r where r.acctnbr = s.acctnbr)) or (a.curracctstatcd = 'CLS' and exists(select 1 from cb_dep_rxtnbal_54 r where r.acctnbr = a.acctnbr)));
37
explain select * from cb_dep_acctbal_54 s, cb_dep_acct_54 a where s.acctnbr = a.acctnbr and ((a.curracctstatcd != 'CLS' and exists(select 1 from cb_dep_acctbal_54 r where r.acctnbr = s.acctnbr)) or (a.curracctstatcd = 'CLS' and exists(select 1 from cb_dep_rxtnbal_54 r where r.acctnbr = a.acctnbr)));
38
##select * from cb_dep_acctbal_54 s,  cb_dep_acct_54 a where s.acctnbr = a.acctnbr and (a.curracctstatcd != 'CLS' or (a.curracctstatcd = 'CLS' and exists(select 1 from cb_dep_rxtnbal_54 r where r.acctnbr = a.acctnbr))) group by s.acctnbr;
39
explain select * from cb_dep_acctbal_54 s,  cb_dep_acct_54 a where s.acctnbr = a.acctnbr and (a.curracctstatcd != 'CLS' or (a.curracctstatcd = 'CLS' and exists(select 1 from cb_dep_rxtnbal_54 r where r.acctnbr = a.acctnbr))) group by s.acctnbr;
40
##select /*+ no_use_hash_aggregation */ * from cb_dep_acctbal_54 s,  cb_dep_acct_54 a where s.acctnbr = a.acctnbr and (a.curracctstatcd != 'CLS' or (a.curracctstatcd = 'CLS' and exists(select 1 from cb_dep_rxtnbal_54 r where r.acctnbr = a.acctnbr))) group by a.acctnbr;
41
explain select /*+ no_use_hash_aggregation */ * from cb_dep_acctbal_54 s,  cb_dep_acct_54 a where s.acctnbr = a.acctnbr and (a.curracctstatcd != 'CLS' or (a.curracctstatcd = 'CLS' and exists(select 1 from cb_dep_rxtnbal_54 r where r.acctnbr = a.acctnbr))) group by a.acctnbr;
42

43
drop database hualong;
44