数据库性能：在列与单独表之间进行过滤 [英] Database performance: filtering on column vs. separate table
问题描述
我想知道在以下情况下最好的方法是什么：
我在数据库中有一个Orders表，显然包含所有订单。但是这些都是字面上的所有命令，所以包括完整/完成，只是标记为完整。从所有打开的订单，我想计算一些东西（如开放金额，未清项目等）。什么会更好的性能明智：
保留1个订单表与所有的订单，包括完整/存档，并通过过滤完成标志做计算？ p>
或者我应该创建另一个表，例如'orders_Archive'，这样Orders表将只包含我用于计算的未结订单？
这些方法是否有明显的性能差异？ p>
（BTW我在PostgreSQL数据库。）
或者我应该创建另一个表，例如'Orders_Archive'，以便Orders表只包含我用于计算的未结订单？
是的。他们称之为数据仓库。人们做到这一点，因为它加速了事务系统，以消除几乎没有使用的历史。首先，表的物理规模较小，处理速度更快。第二，长期运行的历史报告不干扰事务处理。
这些方法是否有明显的性能差异？
是的。奖金。您可以重新构建历史记录，使其不再位于3NF（用于更新），而是位于星型架构（用于报告）中。
购买Kimball的数据仓库工具包书，了解有关星型模式设计的更多信息，并将活动表中的历史迁移到仓库表中。
I was wondering what the best approach would be for the following situation:
I have an Orders table in a database that obviously contains all orders. But these are literally ALL orders, so including the complete/finished ones that are just flagged as 'complete'. From all the open orders I want to calculate some stuff (like open amount, open items, etc). What would be better performance wise:
Keep 1 Orders table with ALL orders, including the complete/archived ones, and do calculations by filtering the 'complete' flag?
Or should I create another table, e.g. 'Orders_Archive', so that the Orders table would only contain open orders that I use for the calculations?
Is there any (clear) performance difference in these approaches?
(B.T.W. I'm on a PostgreSQL db.)
Or should I create another table, e.g. 'Orders_Archive', so that the Orders table would only contain open orders that I use for the calculations?
Yes. They call that data warehousing. Folks do this because it speeds up the transaction system to eliminate the hardly-used history. First, tables are physically smaller and process faster. Second, a long-running history report doesn't interfere with transactional processing.
Is there any (clear) performance difference in these approaches?
Yes. Bonus. You can restructure your history so that it's no longer in 3NF (for updating) but in a Star Schema (for reporting). The advantages are huge.
Buy Kimball's The Data Warehouse Toolkit book to learn more about star schema design and migrating history out of active tables into warehouse tables.
这篇关于数据库性能：在列与单独表之间进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
