增加百万表数据,根据配置、是否索引等情况,可能需要几分钟~几个钟时间不等
oracle
oracle:
1.查看数据库所有表以及表行数
select t.table_name,t.num_rows from user_tables t ORDER BY NUM_ROWS DESC;
2.复制一张表和表数据
create table ORA_DATA1 as select * from HR.ORACLE_ALL_TYPE_SRC_DATA WHERE ID < 100000;
复制表数据:
insert into HR.ORA_DATA1 select * from HR.ORACLE_ALL_TYPE_SRC_DATA WHERE ID < 1000001;
3.去重,并计算数量
SELECT count(DISTINCT oravarchar2 ) FROM HR.ORACLE_ALL_TYPE_SRC_DATA ;
批量新增数据:
DECLARE
str Varchar(10);-- 定义一个字符串变量str
BEGIN
for i in 100000 .. 1000000 loop
INSERT INTO "HR"."ORA_DATA1"("ID", "ORACHAR", "ORAVARCHAR2", "ORANCHAR", "ORANVARCHAR2", "ORADATE", "ORANUMBER", "ORADECIMAL", "ORAINTEGER", "ORAFLOAT", "ORAREAL", "ORANAME") VALUES (i, '固定 ', '可变字符'||i, '固定长水电费水电费 ', '根据字符集而定的可变长度字符串根据字符集而定的可变长度字符串根据字符集而定的可变长度字符串根据字符集而定的可变长度字符串', TO_DATE('2021-09-16 15:17:31', 'SYYYY-MM-DD HH24:MI:SS'), '12', '12.198212315454645464646464132456848', '11'||''||i, i||''||'123123.34500000000', '1212411982639182.0', 'ORANAME1 ');
end loop;
commit;
END;
pg
清空表数据: TRUNCATE TABLE kgtest.postgre_type_hastime_tar_data ; pg批量插入数据: do $$ declare num integer := 1; BEGIN while num < 2000001 loop INSERT INTO kgtest.postgre_type_hastime_tar_data (id, sinteger, sfloat, schar, svarchar, stimestamp, sboolean, sdate, sunique, stime, snull) VALUES(num, 8888, num*0.001, 'char', '字符串'||num, now(), true, now(), 11, now(), '中文'||num); num = num+1; end loop; end $$; 去重查询表: select count(DISTINCT id) from kgtest.postgre_type_hastime_tar_data ;