数据库版本

03134284488-20260806-340608-20200

总结

有索引时

用时10.622s 插入数据32万数据

用时16.472s 插入数据37万数据

无索引时

删除索引

用时5.145s 插入数据33万数据

创建索引用时0.391s 0.304s

用时5.607s 插入数据39万数据

创建索引用时0.417s 0.456s

附件

创建业务用户：

create tablespace "OUSHIN" datafile '/data/dmdata/DAMENG/OUSHIN01.DBF' size 128 autoextend on maxsize 102400;
create user "OUSHIN" identified by "Dameng123!"  default tablespace "OUSHIN" default INDEX TABLESPACE "OUSHIN";
grant RESOURCE,PUBLIC,VTI,SOI,SVI to "OUSHIN";

-- 1. 创建表（使用 OUSHIN 表空间，参考金蝶ERP创建的普通表，名称TMP开头）
CREATE TABLE "OUSHIN"."TMP_AJE9IRYM4FEO0"
(
"FID" BIGINT,
"FVERSION" INT NOT NULL
)
STORAGE(ON "OUSHIN", CLUSTERBTR);  -- 指定使用 OUSHIN 表空间

查看索引

SELECT INDEX_NAME,TABLE_NAME,TABLESPACE_NAME
FROM DBA_INDEXES
WHERE OWNER='OUSHIN' AND TABLE_NAME='TMP_AJE9IRYM4FEO0';

删除索引

DROP INDEX "OUSHIN"."IDX_TMP_AJE9IRYM4FEO0_FID";
DROP INDEX "OUSHIN"."IDX_TMP_AJE9IRYM4FEO0_FVERSION";

insert 30w-50w数据

SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
    V_START_TIME DATE;
    V_BATCH_SIZE INT := 10000;
    V_TOTAL INT := 0;
    V_TARGET_TOTAL INT;
    V_REMAIN INT;
    V_ACTUAL_BATCH INT;
    V_ELAPSED_SEC NUMBER;
BEGIN
    V_START_TIME := SYSDATE;
    V_TARGET_TOTAL := TRUNC(DBMS_RANDOM.VALUE(300000, 500001));
    DBMS_OUTPUT.PUT_LINE('本次目标插入总行数：' || V_TARGET_TOTAL);

    WHILE V_TOTAL < V_TARGET_TOTAL LOOP
        V_REMAIN := V_TARGET_TOTAL - V_TOTAL;
        IF V_REMAIN >= V_BATCH_SIZE THEN
            V_ACTUAL_BATCH := V_BATCH_SIZE;
        ELSE
            V_ACTUAL_BATCH := V_REMAIN;
        END IF;

        INSERT INTO OUSHIN.TMP_AJE9IRYM4FEO0(FID, FVERSION)
        SELECT
            DBMS_RANDOM.VALUE(1, 9999999999),
            TRUNC(DBMS_RANDOM.VALUE(1, 1000))
        FROM DUAL
        CONNECT BY ROWNUM <= V_ACTUAL_BATCH;

        V_TOTAL := V_TOTAL + V_ACTUAL_BATCH;

        IF MOD(V_TOTAL, 100000) = 0 THEN
            COMMIT;
            V_ELAPSED_SEC := (SYSDATE - V_START_TIME) * 86400;
            DBMS_OUTPUT.PUT_LINE('已插入: ' || V_TOTAL || ' 行，已运行: ' || ROUND(V_ELAPSED_SEC,2) || ' 秒');
        END IF;
    END LOOP;

    COMMIT;
    V_ELAPSED_SEC := (SYSDATE - V_START_TIME) * 86400;
    DBMS_OUTPUT.PUT_LINE('========== 完成 ==========');
    DBMS_OUTPUT.PUT_LINE('总插入行数: ' || V_TOTAL);
    DBMS_OUTPUT.PUT_LINE('总耗时(秒): ' || ROUND(V_ELAPSED_SEC,2));
END;
/

-- 1 删除用户，级联删除该用户全部表、索引、序列
DROP USER "OUSHIN" CASCADE;

-- 2 删除空的表空间，达梦自动删除磁盘dbf文件，不要写INCLUDING DATAFILES
DROP TABLESPACE "OUSHIN";

SELECT owner,segment_name,segment_type FROM dba_segments WHERE tablespace_name='OUSHIN';
SELECT tablespace_name FROM dba_tablespaces WHERE tablespace_name='OUSHIN';
SELECT username FROM dba_users WHERE username='OUSHIN';

https://eco.dameng.com