选择最优化查询
我在Oracle数据库中有一个名为my_table的表,例如。它是日志表的类型。它有一个增量列,其名称为“id”和“registration_number”,它是注册用户的唯一。现在,我想注册用户最新的变化,所以我写了下面的查询来完成这个任务:选择最优化查询
第一个版本:
SELECT t.*
FROM my_table t
WHERE t.id =
(SELECT MAX(id) FROM my_table t_m WHERE t_m.registration_number = t.registration_number
);
第二个版本:
SELECT t.*
FROM my_table t
INNER JOIN
(SELECT MAX(id) m_id FROM my_table GROUP BY registration_number
) t_m
ON t.id = t_m.m_id;
我的第一个问题是上面哪个查询是推荐的,为什么?第二个是如果有时候大约有70.000个插入到这个表中,但是大多数插入行的数量在0到2000之间变化,那么向这个表中添加索引是否合理?
的分析查询可能是得到每个注册用户的最新变化最快的方法:
SELECT registration_number, id
FROM (
SELECT
registration_number,
id,
ROW_NUMBER() OVER (PARTITION BY registration_number ORDER BY id DESC) AS IDRankByUser
FROM my_table
)
WHERE IDRankByUser = 1
至于指标,我假设你已经通过registration_number
有一个索引。 id
上的附加索引将有助于查询,但可能不会太多或可能不足以证明索引。我这样说,因为如果你一次插入70K行,额外的索引将减慢INSERT
。你必须进行实验(并检查执行计划)以确定该索引是否值得。
为了检查更快的查询,你应该检查执行计划和成本,它会给你一个公平的想法。但是我同意Ed Gibbs的解决方案,因为分析使得查询运行得更快。 如果你觉得这个表会变得非常大,那么我会建议分区表和使用本地索引。他们一定会帮助你形成更快的查询。
如果你想插入大量的行,那么索引放慢插入,因为每个插入索引也必须更新[我不会建议在ID上索引]。有两种解决方案我想到这个:
- 您可以在插入之前删除索引,然后在插入后重新创建它。
- 使用反向键索引。检查这个链接:http://oracletoday.blogspot.in/2006/09/there-is-option-to-create-index.html。反向键索引可以影响你的查询,所以会有折衷。
感谢您对索引的建议,我会尽力实现这一点。但不幸的是,使用ROW_NUMBER()的查询是其中最慢的一个 – 2013-04-24 09:05:33
如果你寻找更快的解决方案,有一个真正需要保持过去的活动列表中为每个用户,那么最强大的解决方案是维护单独的表具有独特registration_number
值,并在日志表中创建的最后记录的rowid
。
E.g.(仅用于演示,不检查语法的有效性,序列和触发器省略):
create table my_log(id number not null, registration_number number, action_id varchar2(100))
/
create table last_user_action(refgistration_number number not null, last_action rowid)
/
alter table last_user_action
add constraint pk_last_user_action primary key (registration_number) using index
/
create or replace procedure write_log(p_reg_num number, p_action_id varchar2)
is
v_row_id rowid;
begin
insert into my_log(registration_number, action_id)
values(p_reg_num, p_action_id)
returning rowid into v_row_id;
update last_user_action
set last_action = v_row_id
where registration_number = p_reg_num;
end;
/
有了这样的架构可以为具有良好性能的每一个用户简单的查询,最后的动作:
select
from
last_user_action lua,
my_log l
where
l.rowid (+) = lua.last_action
ROWID是物理存储标识直接寻址存储块,并且在移动到另一台服务器,从备份等恢复后不能使用它。但是如果您需要这种功能,则可以简单地将id
列从my_log
表添加到last_user_action
,并且使用一个或另一个取决于要求。
感谢您的回答。实际上,起初我使用ROW_NUMBER()做了这个,但后来我认为这不是最好的方式,所以我尝试了其他方法来做到这一点。为什么认为它可能会更快? – 2013-04-24 06:05:36
我已经测试过,但速度较慢。感谢您的时间和索引建议 – 2013-04-24 06:25:28
我可以想象一下'max'查询会更快 - 如果oracle在索引上使用'min/max scan'。但是你在那里得到了一个组,并且我不确定oracle可以对组合索引执行最小/最大扫描。值得检查。 – haki 2013-04-24 08:57:41