1、建用户
create user user_name sysadmin password “Sinoregal@2025”;
create user user_name password “Sinoegal@2025”;
create user user_name createdb password “Sinoregal@2025”;
create user user_name with valid begin ‘2025-03-25 00:00:00’ valid until ‘2025-04-25 23:59:59’ identified by ‘Sinoregal@2025’ connecttion limit 100;
2、建库
create database testdb encoding ‘UTF8’ template=template0;
create database testdb;
create database testdb with owner=jack;
3、建schema
create schema ds;
create schema tsk authorization user1;#指定要用户
show search_path;
alter database testdb set search_path to pa_catalog,public;
set search_path = sch_a;
4、建表
create table part (
p_key bigint not null,
p_retailprice decimal(15,2) not null
) with (orientation= row);
alter table part add constraint pk_part_id primary key(p_key);
CREATE TABLE part_tbl1 (a int, b int)
PARTITION BY RANGE(a)
(
PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (100),
PARTITION part3 VALUES LESS THAN (MAXVALUE)
);
CREATE TABLE part_tbl2 (a int, b int)
PARTITION BY RANGE(a)
(
partition part1 START(1) END(100) EVERY(50),
partition part2 END(200),
partition part3 START(200) END(300),
artition part4 start(300)
);
CREATE unique index bmsql_oorder_idx1 ON bmsql_oorder USING btree (o_w_id, o_d_id, o_carrier_id, o_id);
create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
create unique index idx_a on t1(a);
create index idx_pt1_local on pt1(id) local;
create index idx_pt1_glo on pt1(id) global;
==================================================================================================================
\copy t1 from ‘t1.csv’ with ( FORMAT ‘csv’, DELIMITER ‘,’, ENCODING ‘utf8’);
\copy t1 to ‘t1.csv’ with ( FORMAT ‘csv’, DELIMITER ‘,’, ENCODING ‘utf8’);
\copy bmsql_warehouse from ‘/root/tpcc/bmsql_warehouse.csv’ with ( FORMAT ‘csv’, DELIMITER ‘,’, ENCODING ‘utf8’);
==================================================================================================================
因为写存储过程，可能多一行少一行，修改不方便，要怎么写才方便？
create or replace procedure proc_nation() as
declare
res_name varchar(20);
res_region varchar(20);
TYPE ref_cur_type is ref cursor;
my_cur ref_cur_type;
begin
open my_cur for select n_name,n_region from nation;
loop
fetch my_cur into res_name,res_region;
exit when my_cur%NOTFOUND
case res_region
when 0 then
dbe_output.print_line(‘国家：’||res_name||‘，属于亚洲’);
when 1 then
dbe_output.print_line(‘国家：’||res_name||‘，属于非洲’);
else
dbe_output.print_line(‘others.’);
end case;
end loop;
close my_cur;
end;
/
create or replace procedure proc_nan(name in varchar(20), region out varchar(20)) as
begin
case name
when 1 then
region := ‘兖州’;
when 2 then
region := ‘福州’;
end case;
end;
/
==================================================================================================================
t2再次更新，超过120秒，
SELECT pg_sleep(300);
SELECT pid,usename,query FROM pg_stat_activity WHERE QUERY LIKE ‘%sleep%’;
SELECT pg_terminate_backend(pid);
create database db1;
create t1(c1 int, c2 int);
insert into t1 values (1,2),(3,4),(5,6);
t1:
begin;
select pg_sleep(1000);
update t1 set c2=c2+10 where c1=1;
t2:
begin;
update t1 set c2=c2+100 where c1=1;
t3:
SELECT pid FROM pg_stat_activity WHERE datname = ‘db1’ and state=‘idle in transaction’;
SELECT pg_terminate_backend(pid);
==================================================================================================================
select create_wdr_snapshot();
select * from pg_node_env;
SELECT * FROM snapshot.snapshot ORDER BY start_ts DESC LIMIT 10;
\a
\t
\o /root/index.html
SELECT generate_wdr_report(begin_snap_id Oid, end_snap_id Oid, int report_type, int report_scope, int node_name);
SELECT generate_wdr_report(34, 35, ‘all’, ‘cluster’);
SELECT generate_wdr_report(34, 35, ‘all’, ‘node’, ‘dn_6001_6002_6003’);
mv /root/index.html /var/www/html/
http://EIP:80/index.html
all/summary/detail
static final String JDBC_DRIVER = “com.huawei.opengauss.jdbc.Driver”;
static final String DB_URL = “jdbc:opengauss://192.168.0.72:8000/demo”;
static final String USER = “db_dev”;
static final String PASS = “yourpassword”;
static String connection_url = DB_URL + “?user=” + USER + “&password=” + PASS;
javac -d . exptConnection.java
yum install tree
tree
java -cp /opt/expt/db/libs/opengaussjdbc.jar:. expt.db.basic.exptConnection
static final String DB_URL = “jdbc:opengauss://192.168.0.72:8000,192.168.0.71:8000,192.168.0.108:8000/demo”;
static final String PARM = “autoBalance=true”;
static String connection_url = DB_URL + “?user=” + USER + “&password=” + PASS + “&” + PARM;
static final String PARM = “targetServerType=master”;
==================================================================================================================
analyze bmsql_stock;
alter table part add constraint pk_part_id primary key(p_key);
CREATE TABLE part_tbl1 (a int, b int)
PARTITION BY RANGE(a)
(
PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (100),
PARTITION part3 VALUES LESS THAN (MAXVALUE)
);
CREATE TABLE part_tbl2 (a int, b int)
PARTITION BY RANGE(a)
(
partition part1 START(1) END(100) EVERY(50),
partition part2 END(200),
partition part3 START(200) END(300),
artition part4 start(300)
);
CREATE TABLE sales (
order_id INT,
sale_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (sale_date) (
PARTITION p2023 VALUES LESS THAN (‘2024-01-01’),
PARTITION p2024 VALUES LESS THAN (‘2025-01-01’),
PARTITION p_max VALUES LESS THAN (MAXVALUE)
);
CREATE unique index bmsql_oorder_idx1 ON bmsql_oorder USING btree (o_w_id, o_d_id, o_carrier_id, o_id);
create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
create unique index idx_a on t1(a);
create index idx_pt1_local on pt1(id) local;
create index idx_pt1_glo on pt1(id) global;
执行计划：explain select * from tab1;
统计更新：analyze tab1;
SELECT /*+hint hint */ * FROM …
EXPLAIN
SELECT /*+ tablescan(t1) */ t1.a FROM t1 WHERE t1.a = 1;
/*+ [no] tablescan(table) /
/+ [no] indexscan/indexonlyscan(tablename indexname) */
/*+ [no] nestloop/hashjoin/mergejoin(table1 table2) */
/*+ leading(join_table_list) /
/+ leading(t1 (t2 t3)) */