作为单个查询执行缓慢但分两步完成时只需几分之一秒的查询

发布于 2024-11-28 19:53:29 字数 1007 浏览 0 评论 0原文

我在 ORACLE DB 中有两个视图：

The view USERS with columns:
UserId | GroupId | Details

每个用户有 1 条记录，总共 40.000 个，每个组中有大约 1-30 个用户。

每个用户

The view SUMMARY with columns:
UserId | Summary

有 1 条记录

SUMMARY 视图非常复杂，但当我根据用户查询它时仍然相当快。即：

select * from SUMMARY where UserId='some_user_id'

在 0,1 秒内执行

USERS 视图非常简单（它是所有 USERS_TYPE1 和 USERS_TYPE2 表的联合）和类型的查询：

select * from USERS where GroupId='some_group_id'

在 0,02 秒内执行

现在这里有一个问题，当我这样做时：

select * from USERS JOIN SUMMARY 
ON USERS.UserId = SUMMARY.UserId
WHERE USERS.GroupId = 'some_group_id'

我得到了 90 秒的糟糕性能 - 即使组中只有 3 个用户。

如果首先找到用户 ID，然后使用这些用户 ID 查询 SUMMARY 表，则这应该只需要几分之一秒的时间。有什么办法可以提示数据库这样做吗？

我在所有基础表上都有基于 UserId、GroupId 和 (GroupId,UserId) 的索引。

（（这很难以简单的方式重现，因为复杂视图有很多基础表。我有几个版本的 SUMMARY 视图。在某些情况下（取决于 SUMMARY 的构建方式），当我实现了用户视图，但在其他情况下却没有。））

原文

I have two views in an ORACLE DB:

The view USERS with columns:
UserId | GroupId | Details

where there's 1 record for each User, total 40.000, and there c.a. 1-30 users in each Group.

and

The view SUMMARY with columns:
UserId | Summary

where there's 1 record for each User

The SUMMARY view is very complex but is still quite fast when i query it based on user.
That is:

select * from SUMMARY where UserId='some_user_id'

performs in 0,1 sec

The USERS view is pretty simple (it's a union all of USERS_TYPE1 and USERS_TYPE2 tables)
and a query of the type:

select * from USERS where GroupId='some_group_id'

performs in 0,02 sec

Now here's the catch,
when I do:

select * from USERS JOIN SUMMARY 
ON USERS.UserId = SUMMARY.UserId
WHERE USERS.GroupId = 'some_group_id'

I get AWFUL performance of 90seconds - even though there are only 3 users in the group.

This should take only a fraction of a second if the user ID's are found first and then the SUMMARY table is queried with those user ID's.
Is there a way I can hint the DB to do that?

I have indexes based on UserId, GroupId and (GroupId,UserId) on all underlying tables.

((This is pretty hard to reproduce In a simple manner since the complex view has a lot of underlying tables. I have a couple of versions of the SUMMARY view. In some cases (depending on how SUMMARY is built) it stops being awful when i materialize the Users view, but in other cases it does not.))

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

拥抱我好吗 2024-12-05 19:53:29

您可以尝试

  SELECT * 
  FROM
    (select * from USERS
    WHERE USERS.GroupId = 'some_group_id') u,
    summary
  WHERE USERS.UserId = SUMMARY.UserId

同时指定您想要的列，而不是选择*（显然您不需要两个版本的UserId）

它可能值得尝试，

  SELECT * 
  FROM
    (select users.*, rownum rn from USERS
    WHERE USERS.GroupId = 'some_group_id') u,
    summary
  WHERE USERS.UserId = SUMMARY.UserId

这将迫使它首先评估内联视图。

You could try

  SELECT * 
  FROM
    (select * from USERS
    WHERE USERS.GroupId = 'some_group_id') u,
    summary
  WHERE USERS.UserId = SUMMARY.UserId

Also specify exactly what columns you want, rather than select * (you obviously don't need both versions of the UserId)

It MAY be worth trying

  SELECT * 
  FROM
    (select users.*, rownum rn from USERS
    WHERE USERS.GroupId = 'some_group_id') u,
    summary
  WHERE USERS.UserId = SUMMARY.UserId

which will force it to evaluate the inline view first.

回复收藏 0 原文