OracleDB中自动生成并维护外键表的实现方案咨询

需求背景 #

当MyData表存储百万级以上字符串字段（如FirstName、Country）时，重复字符串会占用大量存储空间。最优方案是将这类字段拆分为独立的外键表（如FirstName_TB、Country_TB），存储唯一字符串对应的ID，MyData仅存储ID。但在数据量达数千万、字段数量多的场景下，应用层处理映射会面临缓存压力、并行冲突等问题，因此需要数据库层面自动完成“插入时查找/创建外键记录、存储ID”的逻辑。

数据库层面的高效实现方案 #

1. 基于MERGE+函数的原子映射（推荐单条/小批量插入） #

利用Oracle的MERGE语句实现“不存在则插入、存在则复用”的原子操作，配合封装函数简化调用：

步骤1：创建外键表、序列与唯一约束

-- 创建FirstName_TB及依赖对象
CREATE TABLE FirstName_TB (
    ID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(200) NOT NULL
);
CREATE SEQUENCE seq_firstname_id START WITH 1 INCREMENT BY 1 CACHE 1000;
ALTER TABLE FirstName_TB ADD CONSTRAINT uq_firstname UNIQUE (FirstName);

-- 创建Country_TB及依赖对象
CREATE TABLE Country_TB (
    ID NUMBER PRIMARY KEY,
    Country VARCHAR2(100) NOT NULL
);
CREATE SEQUENCE seq_country_id START WITH 1 INCREMENT BY 1 CACHE 1000;
ALTER TABLE Country_TB ADD CONSTRAINT uq_country UNIQUE (Country);

-- 创建最终的MyData表
CREATE TABLE MyData (
    FirstName NUMBER REFERENCES FirstName_TB(ID),
    Country NUMBER REFERENCES Country_TB(ID)
);

步骤2：封装映射函数

CREATE OR REPLACE FUNCTION get_firstname_id(p_firstname VARCHAR2) RETURN NUMBER IS
    v_id NUMBER;
BEGIN
    -- 原子性检查并插入唯一记录
    MERGE INTO FirstName_TB t
    USING (SELECT p_firstname AS firstname FROM dual) s
    ON (t.FirstName = s.firstname)
    WHEN NOT MATCHED THEN
        INSERT (ID, FirstName) VALUES (seq_firstname_id.NEXTVAL, s.firstname);
    
    -- 获取对应ID（唯一约束保证查询结果唯一）
    SELECT ID INTO v_id FROM FirstName_TB WHERE FirstName = p_firstname;
    RETURN v_id;
END;
/

CREATE OR REPLACE FUNCTION get_country_id(p_country VARCHAR2) RETURN NUMBER IS
    v_id NUMBER;
BEGIN
    MERGE INTO Country_TB t
    USING (SELECT p_country AS country FROM dual) s
    ON (t.Country = s.country)
    WHEN NOT MATCHED THEN
        INSERT (ID, Country) VALUES (seq_country_id.NEXTVAL, s.country);
    
    SELECT ID INTO v_id FROM Country_TB WHERE Country = p_country;
    RETURN v_id;
END;
/

步骤3：执行插入

直接调用函数完成映射插入：

INSERT INTO MyData (FirstName, Country)
VALUES (get_firstname_id('Tomas'), get_country_id('Netherland'));

2. 视图+INSTEAD OF触发器（保持原插入语句格式） #

若希望沿用原始的INSERT INTO MyTable (FirstName, Country)语句格式，可通过视图和触发器隐藏映射逻辑：

步骤1：创建视图

CREATE VIEW MyTable AS
SELECT 
    ft.FirstName,
    ct.Country
FROM MyData d
JOIN FirstName_TB ft ON d.FirstName = ft.ID
JOIN Country_TB ct ON d.Country = ct.ID;

步骤2：创建INSTEAD OF触发器

CREATE OR REPLACE TRIGGER trg_mytable_insert
INSTEAD OF INSERT ON MyTable
FOR EACH ROW
BEGIN
    INSERT INTO MyData (FirstName, Country)
    VALUES (get_firstname_id(:NEW.FirstName), get_country_id(:NEW.Country));
END;
/

步骤3：执行原始插入语句

INSERT INTO MyTable (FirstName, Country) VALUES ('Tomas', 'Netherland');

3. 批量数据加载优化（千万级数据场景） #

针对批量导入（如SQL*Loader、INSERT ... SELECT），先批量维护外键表，再完成主表插入：

-- 假设staging_table是临时存储原始数据的表
MERGE INTO FirstName_TB t
USING (SELECT DISTINCT FirstName FROM staging_table) s
ON (t.FirstName = s.FirstName)
WHEN NOT MATCHED THEN
    INSERT (ID, FirstName) VALUES (seq_firstname_id.NEXTVAL, s.FirstName);

MERGE INTO Country_TB t
USING (SELECT DISTINCT Country FROM staging_table) s
ON (t.Country = s.Country)
WHEN NOT MATCHED THEN
    INSERT (ID, Country) VALUES (seq_country_id.NEXTVAL, s.Country);

-- 批量插入主表
INSERT INTO MyData (FirstName, Country)
SELECT 
    ft.ID,
    ct.ID
FROM staging_table st
JOIN FirstName_TB ft ON st.FirstName = ft.FirstName
JOIN Country_TB ct ON st.Country = ct.Country;

性能优化要点 #

• 唯一约束/索引：外键表的字符串字段必须加唯一约束，MERGE的匹配条件会走唯一索引扫描，避免全表扫描。
• 序列缓存：序列使用CACHE选项减少磁盘IO，提升插入速度。
• 并行安全：MERGE是原子操作，并行插入时不会出现重复记录，无需额外锁机制。
• 批量加载优化：批量维护外键表后再插入主表，比单条处理效率提升数倍；可临时禁用主表外键约束，加载完成后重新启用。

内容的提问来源于stack exchange，提问作者Alois Jirasek