Question

I have a raw table used as a kind of buffer where periodically new data is inserted (average of ~20.000 rows inserted in bulk each ~5 minutes). Then there is a stored PL/SQL proceudre which reads this raw table and insert the information in the distinct tables of the database.
Unfortunately I cannot do a direct bulk insert in my destination table from the rows stored in the raw table, because part of the columns (10 out of 20) are foreign keys in the destination table. This means that before doing the insert I have to resolve all the foreign key id s and then use these to insert the new rows in the destination table.
Moreover, the element in the foreign key table could not be there, so it needs to be inserted in the foreign key table and then its id can be used as foreign key id.

The solution I am going to use is to have a stored procedure that:
1) for each column of the raw table that corresponds to a foreign key in the destination table I select the distinct values of the column and for each one I select the id in the foreign table, and if it doesn t exists I insert it reutrning the relative id;
2) I write out to an empty temporary table (truncated each time before inserting into it) the same rows of the raw table but with the resolved foreign keys;
3) I do a bulk insert in the destination table by selecting the values from the temporary table.

为什么使用临时表格? 因为我想避免在超过50米线的目的地表中逐行插入一行。

(我已掌握了矿石和部分解决方案结构;我甚至不能改变这一结构,因为目前还存在着一套无法改变的监测工具。)

在目的地表格插入新行之前,我如何通过解决外国钥匙来优化守则? 是否有其他更好的扩大规模的解决办法?

我试图总结一下我复杂的工作流程,留下一些细节,并概括地描述情况。

Answer 1

外国钥匙中有多少百分比没有解决?

您可以为这些外国主要主历表做以下工作:

INSERT INTO parent_table (id, name)
SELECT id.nextval, r2.name
FROM (SELECT DISTINCT r.name FROM raw)
WHERE r2.name NOT IN (SELECT name FROM parent_table)

然后,你会知道它们都存在,并且能够这样做。

或者,如果大多数是,请看。页: 1

友情链接