是否激活标志? [英] `active' flag or not?

问题描述

好的，因此实际上每个基于数据库的应用程序都必须处理非活动"记录.软删除或将某些内容标记为要忽略".我对活动"列(或状态列)上是否有任何激进的替代想法感到好奇.

OK, so practically every database based application has to deal with "non-active" records. Either, soft-deletions or marking something as "to be ignored". I'm curious as to whether there are any radical alternatives thoughts on an `active' column (or a status column).

例如，如果我有一个人列表

For example, if I had a list of people

CREATE TABLE people (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(100),
  active   BOOLEAN,
  ...
);

这意味着要获取活跃人群的列表，您需要使用

That means to get a list of active people, you need to use

SELECT * FROM people WHERE active=True;

是否有人建议将非活动记录移至单独的表，并在适当的位置将UNION合并为两个?

Does anyone suggest that non active records would be moved off to a separate table and where appropiate a UNION is done to join the two?

好奇心...

编辑:我应该明确一点，我是从纯粹的角度出发.我可以看到对于大量数据可能需要进行数据归档，但这不是我要从哪里来的.如果您执行SELECT * FROM people，那么对我来说这些条目在某种意义上是活跃的"

I should make clear, I'm coming at this from a purist perspective. I can see how data archiving might be necessary for large amounts of data, but that is not where I'm coming from. If you do a SELECT * FROM people it would make sense to me that those entries are in a sense "active"

谢谢

推荐答案

您在活动标记上对表进行分区，以便活动记录位于一个分区中，而非活动记录位于另一个分区中.然后，为每个自动创建活动过滤器的表创建一个活动视图.数据库查询引擎会自动将查询限制为其中具有活动记录的分区，这比使用该标志上的索引要快得多.

You partition the table on the active flag, so that active records are in one partition, and inactive records are in the other partition. Then you create an active view for each table which automatically has the active filter on it. The database query engine automatically restricts the query to the partition that has the active records in it, which is much faster than even using an index on that flag.

这里是一个如何在Oracle中创建分区表的示例. Oracle没有布尔类型的列，因此出于Oracle的目的，我已经修改了您的表结构.

Here is an example of how to create a partitioned table in Oracle. Oracle doesn't have boolean column types, so I've modified your table structure for Oracle purposes.

CREATE TABLE people
(
   id       NUMBER(10),
   name     VARCHAR2(100),
   active   NUMBER(1)
)
PARTITION BY LIST(active)
(
   PARTITION active_records VALUES (0)
   PARTITION inactive_records VALUES (1)
);

如果需要，可以将每个分区放在不同的表空间中.您还可以对索引进行分区.

If you wanted to you could put each partition in different tablespaces. You can also partition your indexes as well.

顺便说一句，这似乎是此问题，作为我的新手，我要处理意外重复的程序是什么?

Incidentally, this seems a repeat of this question, as a newbie I need to ask, what's the procedure on dealing with unintended duplicates?

根据注释中的要求，提供了一个在Oracle中创建分区表的示例

As requested in comments, provided an example for creating a partitioned table in Oracle

这篇关于是否激活标志?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！