英文:
Retrieving new oracle table records on insert to process in Java client application
问题
我正在尝试从我的Java客户端应用程序（Spring JMS）中检索表中的新插入记录，以进行一些处理并将它们发送到消息代理。我没有访问Goldengate等CDC工具。我只需要新的插入记录，而不是更新或删除记录。我在寻找一种方法来实现这一点时遇到了困难。是否有一种方法可以做到这一点？我读到可以使用触发器来完成这个任务，但它会对数据库的吞吐量产生很大影响吗？因为这个表每天会有大约5万条记录插入。
提前感谢。
英文:
I am trying to retrieve the new inserts from a table in my java client application (Spring JMS), to do some processing and send them to a message broker. I do not have access to any CDC tool like Goldengate. I only need the new inserts and not the updates or deletes. I am having difficulty finding a way to do this. Is there a way to do this? I read that there is an option to do these with triggers, but will it have a high throughput on the db, because this table gets a lot of inserts in a day (approximately around 50K records inserted in a day).
Thanks in advance
答案1
得分: 2
50,000行每天实际上是相当低的数据量。一些数据仓库表每天都有 5000 万行数据。因此，插入触发器不太可能对加载作业产生明显影响。添加一个日期列（例如 LOAD_DATE），并在插入触发器中分配 :new.LOAD_DATE := SYSDATE。
话虽如此，如果您想避免使用触发器，您可以修改加载作业本身，以在加载时添加一个带有 SYSDATE 的日期列。
无论使用哪种方法，您的检索操作都很简单：每天检索记录时，记录您检索到的 LOAD_DATE 值的最大值。第二天，只检索 LOAD_DATE >= 该值的记录。
英文:
50,000 rows per day is actually rather a low volume. Some data warehouse tables get 50 million rows a day. So an insert trigger is unlikely to make any appreciable difference on the loading job. Add a date column (e.g. LOAD_DATE) and have a before insert trigger assign :new.LOAD_DATE := SYSDATE.
That being said, if you wanted to avoid a trigger, you can modify the loading job itself to load such a date a column with SYSDATE.
With either method, your retrieval is then simple: each day as you retrieve records, record the maximum LOAD_DATE value you retrieved. The next day, pull only records with a LOAD_DATE >= that value.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论