新数据到达时自动更新外部表键(OracleDB)

rxztt3cl 于 2023-06-22 发布在 Oracle

关注(0)|答案(2)|浏览(113)

OracleDB中是否有自动生成和维护外部表的技术？为了简单起见，假设我有一个包含两列的表：名字和国家如果我像这样创建这个表MyData：

FirstName varchar2(200)
    Country varchar2(100)

如果我有数百万条记录，那将占用太多的空间，而且肯定会有少量独特的国家和名字。更好的方法是创建外部表：

FirstName_TB: ID unique number, FirstName varchar2(200)
    Country_TB: ID unique number, Contry varchar2(100)
    MyData: FirstName number, Country number.

从理论上讲，这很简单。当要创建新记录时，我将查找外部表以获得正确的ID并将其放入MyTable。如果外部表中没有记录，则将创建新记录。
想象一下，我有几十个列和几百万条记录，在数据加载器部分，我需要在内存中保存所有的外键值，在并行运行时遇到麻烦等。
所以我的问题是，OracleDB中有没有什么技术可以实现这一点？类似于：

INSERT INTO MyTable (FirstName, Country) VALUES ("Tomas","Netherland")

**但不是将原始字符串插入MyTable，而是在MyTable中查找/创建外部表键并仅使用ID？**我在考虑一些插入触发器之前做所有的繁重工作，但我猜这会很慢。这也是一个很常见的任务，我敢打赌有一些技术或过程可以在数据库级别以最佳性能实现这一点。

通过谷歌和stackoverflow寻找建议，没有任何运气。

oracle

来源：https://stackoverflow.com/questions/76443850/auto-updated-foreign-table-keys-when-new-data-arrives-oracledb

2条答案

按热度按时间

1mrurvl11#

您可以使用以下步骤：

CREATE PROCEDURE add_mydata(
  i_firstname IN FirstNames.FirstName%TYPE,
  i_country   IN Countries.Country%TYPE
)
IS
  v_fn_id FirstNames.ID%TYPE;
  v_c_id  Countries.ID%TYPE;
BEGIN
  BEGIN
    SELECT id
    INTO   v_fn_id
    FROM   FirstNames
    WHERE  FirstName = i_firstname;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO FirstNames (FirstName)
      VALUES (i_firstname)
      RETURNING id INTO v_fn_id;
  END;

  BEGIN
    SELECT id
    INTO   v_c_id
    FROM   Countries
    WHERE  Country = i_country;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO Countries (Country)
      VALUES (i_country)
      RETURNING id INTO v_c_id;
  END;

  INSERT INTO MyData (FirstName, Country)
  VALUES (v_fn_id, v_c_id);
END;
/

其中，对于表：

CREATE TABLE FirstNames (
  ID        NUMBER
            GENERATED ALWAYS AS IDENTITY
            PRIMARY KEY,
  FirstName varchar2(200)
            UNIQUE
            NOT NULL
);

CREATE TABLE Countries (
  ID        NUMBER
            GENERATED ALWAYS AS IDENTITY
            PRIMARY KEY,
  Country   varchar2(100)
            UNIQUE
            NOT NULL
);

CREATE TABLE MyData (
  FirstName REFERENCES FirstNames,
  Country   REFERENCES Countries
);

然后，调用该过程：

BEGIN
  add_mydata('Alice', 'Austria');
  add_mydata('Betty', 'Brazil');
  add_mydata('Carol', 'Cambodia');
  add_mydata('Alice', 'Brazil');
  add_mydata('Carol', 'Austria');
END;
/

表中的结果包含：

SELECT m.*, f.firstname, c.country
FROM   mydata m
       INNER JOIN FirstNames f
       ON (m.firstname = f.id)
       INNER JOIN Countries c
       ON (m.country = c.id);

| 名字|国家|名字|国家|
| - -----|- -----|- -----|- -----|
| 1| 1|爱丽丝|奥地利|
| 3| 1|卡罗尔|奥地利|
| 2| 2|贝蒂|巴西|
| 1| 2|爱丽丝|巴西|
| 3| 3|卡罗尔|柬埔寨|
fiddle

赞(0）回复(0）举报 2023-06-22

qnakjoqk2#

这就是PL/SQL擅长的。没有必要将所有这些数据从数据库中取出，并在客户端/加载器代码中进行查找。为了获得最佳性能：
1.创建临时表。如果使用SQL*Loader，它应该是一个真实的的表（尽管有一个使用临时表的解决方案，但这超出了这个答案的范围）。如果您编写了自己的加载器，并且能够在加载完成后调用与数据加载相同的会话中的过程，那么您应该创建一个临时表。
1.将数据加载到staging表中
1.调用PL/SQL过程来处理数据。它将读取staging表并将数据转换为规范化结构。
3a.对于您的查找表，最佳执行方法是对维表使用MERGE，以便使用在临时表中找到的新值/更改值更新维表。它只需要对每个维度表执行一次SQL，因为您一次处理所有行。
3b.然后使用INSERT（或另一个MERGE）来加载事实表，该查询连接所有维度以获取它们的键。所有的键都会出现，因为你在步骤3a中加载了新的键。
对于大数据来说，这比在每个维度键上逐行查找的游标要好得多。这些操作还可以利用PQ、PDML、日志记录和直接路径，所有这些都不可用于逐行查找。
最后，不要过度规范化。规范化country可能是有意义的（如果您有关于每个国家的任何其他属性），但如果这是您唯一的个人数据，我不会规范化名或姓。然而，如果你的事实表低于一个人的纹理（例如，同一个人可能多次参与的可重复事件），您肯定希望将所有的人属性（如姓名、地址、电子邮件等）从事实表中提取到人维度中。规范化更多的是关于拥有一个真实的来源和保护数据关系，而不是节省空间。

赞(0）回复(0）举报 2023-06-22

我来回答

新数据到达时自动更新外部表键(OracleDB)

2条答案

相关问题

热门标签

最新问答