非常快速地从NOT IN查询中返回结果
我们已经有了一个系统,其中包含一系列项目(> 100万)以及处理它的几件事情。每个处理器应该只处理每个项目一次,并且处理器具有层次结构。非常快速地从NOT IN查询中返回结果
我们当前的实现是有一个什么样的每个处理器已经做了“处理”表保持跟踪:
CREATE TABLE items (id NUMBER PRIMARY KEY, ...)
CREATE TABLE itemsProcessed(
item NUMBER REFERENCES items(id),
processor NUMBER)
我们的查询是这样的(itemsProcessed
已经得到了它的相关指标) - 我们使用一个NOT IN过滤掉那些已经被当前的处理器处理的项目,或者它的祖先:
SELECT ... FROM items i WHERE <additional queries on items>
AND id NOT IN (SELECT item FROM itemsProcessed WHERE processor IN (1, 2))
当处理表变得非常大,这个查询开始花费很长的时间(几秒钟),因为它做很多过滤bef它开始返回第一个项目(查询计划使用散列抗连接)
我们需要此查询以非常快的速度返回前几个项目 - 理想情况下返回500ms内的第一个项目。这意味着它不能遍历items
并过滤掉itemsProcessed
中的那些。因此,我们需要做的加盟items
和itemsProcessed
否定指标的某些方面(我们已经在蒙戈做到了这一点,但甲骨文似乎好好尝试一下才能够做类似的事情)
这可能与甲骨文?
IMO这是一个设计问题。当您试图包含尚未处理的项目时,您正试图排除已处理的项目。已处理的物品清单将不断增加;待处理项目的清单仍然很小。我建议您创建一个要处理的项目表,然后将它内部连接到查询,并在ITEMS_TO_BE_PROCESSED表处理它们时删除项目,而不是处理已处理的项目表(项目处理)。
祝你好运。
这也是我的首选解决方案,但不幸的是,每个项目创建时都不知道处理器列表。 – thecoop
你可以尝试你查询
SELECT /*+ first_rows (10) */... FROM items i ...
还是尽量先选择未处理项目添加/*+ first_rows */暗示,比做<additional queries on items>
with i_to_process AS
(
SELECT item FROM items
minus
SELECT item FROM itemsProcessed WHERE processor IN (1, 2)
)
select * from i_to_process
where
<additional queries on items>
根据表的更新频率,你可以创建itemsNotProcessed的物化视图。处理将事先完成。你也可以反正规化一点,并向项目表添加一个处理过的标志,并在标志上添加一个位图索引。
如果这能给出更好的结果,你可以试试吗? 'AND ID IN(SELECT item FROM itemsProcessed WHERE processor> 2)'。如果你的'processor'永远不是'NULL',它应该返回相同的结果,但是不用'NOT'表示,在某些情况下可能更好地使用索引 –
你的外键索引 - 是你说你有一个“相关”索引,或者你只索引了'processor'?它们是什么类型的索引?执行计划显示了什么?你是否试过使用'not exists'而不是'rownum'停止键,如果你只想要前几个未处理的rows? –
查询所需的时间还取决于您在中所做的操作。是否在表上使用索引? –