测试架构
主库:10.0.0.31:7236 EP02
从库:10.0.0.32:7237 EP02_S1
===连接主库,用global参数:查集群的全部数据
disql SYSDBA/SYSDBA@10.0.0.31:7236#“{mpp_type=global}”
===连接主库,用global参数:只能查EP02节点存储的
disql SYSDBA/SYSDBA@10.0.0.31:7236#“{mpp_type=local}”
===连接从库,用global参数:只能查EP02_1存储的
disql SYSDBA/SYSDBA@10.0.0.32:7237#“{mpp_type=global}”
===连接从库,用local参数:只能查EP02_1存储的
disql SYSDBA/SYSDBA@10.0.0.32:7237#“{mpp_type=local}”
准备数据
drop table if exists t01;
drop table if exists t01_hash;
create table t01 (id int);
create table t01_hash (id int,name varchar2(50)) DISTRIBUTED BY HASH (id);
begin
for i in 1..5000 loop
insert into t01 values (i);
end loop;
commit;
end;
/
begin
for i in 1..5000 loop
insert into t01_hash values (i,'C'||i);
end loop;
commit;
end;
/
cat > c.sql << EOF
select count(*) from t01;
select count(*) from t01_hash;
EOF
测试过程
###primary,global模式
disql SYSDBA/SYSDBA@10.0.0.32:7237#"{mpp_type=global}"
[dmdba@dmdbs1 ~]$
[dmdba@dmdbs1 ~]$ disql SYSDBA/SYSDBA@10.0.0.32:7237#"{mpp_type=global}"
Server[10.0.0.32:7237]:mode is primary, state is open
login used time : 2.870(ms)
disql V8
SQL> `c.sql
SQL> select count(*) from t01;
LINEID COUNT(*)
---------- --------------------
1 5000
used time: 3.951(ms). Execute id is 140815.
SQL> select count(*) from t01_hash;
LINEID COUNT(*)
---------- --------------------
1 5000
used time: 4.036(ms). Execute id is 140817.
SQL>
###primary,local模式
disql SYSDBA/SYSDBA@10.0.0.32:7237#"{mpp_type=local}"
[dmdba@dmdbs1 ~]$
[dmdba@dmdbs1 ~]$ disql SYSDBA/SYSDBA@10.0.0.32:7237#"{mpp_type=local}"
Server[10.0.0.32:7237]:mode is primary, state is open
login used time : 1.850(ms)
disql V8
SQL>
SQL> `c.sql
SQL> select count(*) from t01;
LINEID COUNT(*)
---------- --------------------
1 1284
used time: 0.592(ms). Execute id is 491302.
SQL> select count(*) from t01_hash;
LINEID COUNT(*)
---------- --------------------
1 1250
used time: 0.511(ms). Execute id is 491303.
SQL>
###standby,global模式
disql SYSDBA/SYSDBA@10.0.0.31:7236#"{mpp_type=global}"
[dmdba@dmdbs1 ~]$
[dmdba@dmdbs1 ~]$ disql SYSDBA/SYSDBA@10.0.0.31:7236#"{mpp_type=global}"
Server[10.0.0.31:7236]:mode is standby, state is open
login used time : 2.139(ms)
disql V8
SQL> `c.sql
SQL> select count(*) from t01;
LINEID COUNT(*)
---------- --------------------
1 1284
used time: 1.186(ms). Execute id is 130202.
SQL> select count(*) from t01_hash;
LINEID COUNT(*)
---------- --------------------
1 1250
used time: 0.993(ms). Execute id is 130203.
SQL>
SQL>
SQL>
###standby,local模式
disql SYSDBA/SYSDBA@10.0.0.31:7236#"{mpp_type=local}"
[dmdba@dmdbs1 ~]$ disql SYSDBA/SYSDBA@10.0.0.31:7236#"{mpp_type=local}"
Server[10.0.0.31:7236]:mode is standby, state is open
login used time : 3.776(ms)
disql V8
SQL> `c.sql
SQL> select count(*) from t01;
LINEID COUNT(*)
---------- --------------------
1 1284
used time: 1.053(ms). Execute id is 132302.
SQL> select count(*) from t01_hash;
LINEID COUNT(*)
---------- --------------------
1 1250
used time: 0.764(ms). Execute id is 132303.
SQL>