当前位置： 文江博客话题详情

预测推入组中每个用户的最大和最小统计数据时出现问题

发布于 2024-11-29 11:35:09 字数 6423 浏览 1 评论 0原文

我在 ORACLE 10g DB 中有两个表：

表 USERS 包含列： 用户 ID |组ID |其他细节 其中每个用户有 1 条记录，总共 400 条，每个组中有大约 1-10 个用户。

以及表 USAGE 的列： 用户 ID |日期 |数量 其中每个用户有 10000 行。 （所以400 * 10000行）

我有用户表在UserId，GroupId和（UserId，GroupId）上索引，表使用情况在（UserId，Date）上索引

我想查看每个用户的最大和总使用量给定的组。 像这样的事情：

select User.UserId, maxAmount, Total from 
Users
JOIN
( select UserId,max(Amount) as maxAmount from USAGE group by UserId ) A
ON User.UserId = A.UserId
JOIN 
(  select UserId,min(Amount) as minAmount from USAGE group by UserId ) B
ON User.UserId = B.UserId
WHERE User.GroupId = 'some_group_id'

但是它非常慢（大约20秒），但是当我单独做它们时（只是最大和最小，但不是同时），就像这样：

select User.UserId, maxAmount, minAmount from 
Users
JOIN
( select UserId,max(Amount) as maxAmount from USAGE group by UserId ) A
ON User.UserId = A.UserId
WHERE User.GroupId = 'some_group_id'

它在一瞬间运行！

我可以非常快地单独加入它们，但是当我将它们全部加入时，速度非常慢，这是没有意义的。

以下是计划，其中我将前面的语句中的内联视图创建为实际视图：

create view usage_min as select user_id, min(amount) from usage group by user_id;
create view usage_max as select user_id, max(amount) from usage group by user_id;




 explain plan for select * from usage_min join users using(user_id) where group_id='1212882339';
 select * from table(dbms_xplan.display);
Plan hash value: 3874246446

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |     1 |   139 |  1162   (1)| 00:00:14 |
|   1 |  HASH GROUP BY                    |                 |     1 |   139 |  1162   (1)| 00:00:14 |
|   2 |   TABLE ACCESS BY INDEX ROWID     | USAGE           |  7977 |   116K|  1157   (1)| 00:00:14 |
|   3 |    NESTED LOOPS                   |                 | 11085 |  1504K|  1160   (1)| 00:00:14 |
|   4 |     MAT_VIEW ACCESS BY INDEX ROWID| USERS           |     1 |   124 |     3   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN             | USERS_KT        |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN              | SYS_C0099818    |  7977 |       |    79   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("USERS"."group_id"='1212882339')
   6 - access("user_id"="USERS"."user_id")


  explain plan for select * from users join  usage_max   using(user_id) where group_id='1212882339';
 select * from table(dbms_xplan.display);

Plan hash value: 2384977958

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |     1 |   145 |   519   (2)| 00:00:07 |
|   1 |  HASH GROUP BY                    |                 |     1 |   145 |   519   (2)| 00:00:07 |
|   2 |   TABLE ACCESS BY INDEX ROWID     | USAGE2          | 18251 |   374K|   512   (1)| 00:00:07 |
|   3 |    NESTED LOOPS                   |                 | 25362 |  3591K|   515   (1)| 00:00:07 |
|   4 |     MAT_VIEW ACCESS BY INDEX ROWID| USERS           |     1 |   124 |     3   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN             | USERS_KT        |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN              | T2_user_id        | 18251 |       |    25   (4)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("USERS"."group_id"='1212882339')
   6 - access("USERS"."user_id"="user_id")

 explain plan for select * from  users  join usage_max using(user_id) join  usage_min using(user_id)  where group_id='1212882339';
 select * from table(dbms_xplan.display);

Plan hash value: 3190011991

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |   100 | 16600 |  6782  (19)| 00:01:22 |
|*  1 |  HASH JOIN                        |                 |   100 | 16600 |  6782  (19)| 00:01:22 |
|   2 |   MERGE JOIN                      |                 |   100 | 14500 |  3176  (19)| 00:00:39 |
|   3 |    SORT JOIN                      |                 |  2920K|    58M|  3172  (19)| 00:00:39 |
|   4 |     VIEW                          | USAGE_MAX       |  2920K|    58M|  3172  (19)| 00:00:39 |
|   5 |      HASH GROUP BY                |                 |  2920K|    58M|  3172  (19)| 00:00:39 |
|   6 |       TABLE ACCESS FULL           | USAGE2          |  2920K|    58M|  2686   (5)| 00:00:33 |
|*  7 |    SORT JOIN                      |                 |     1 |   124 |     4  (25)| 00:00:01 |
|   8 |     MAT_VIEW ACCESS BY INDEX ROWID| USERS           |     1 |   124 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN             | USERS_KT        |     1 |       |     1   (0)| 00:00:01 |
|  10 |   VIEW                            | USAGE_MIN       |   398 |  8358 |  3605  (19)| 00:00:44 |
|  11 |    HASH GROUP BY                  |                 |   398 |  5970 |  3605  (19)| 00:00:44 |
|  12 |     TABLE ACCESS FULL             | USAGE           |  3174K|    45M|  3073   (4)| 00:00:37 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("USAGE_MAX"."user_id"="USAGE_MIN"."user_id")
   7 - access("USERS"."user_id"="USAGE_MAX"."user_id")
       filter("USERS"."user_id"="USAGE_MAX"."user_id")
   9 - access("USERS"."group_id"='1212882339')
原文

I have two tables in an ORACLE 10g DB:

The table USERS with columns:
UserId | GroupId | Other details
where there's 1 record for each User, total 400, and there c.a. 1-10 users in each Group.

and the table USAGE with columns:
UserId | Date | Amount
where there's 10000 rows for each user. (so 400*10000 rows)

I have the Users table indexed on UserId, GroupId and (UserId,GroupId) and the table Usage is indexed on (UserId,Date)

I want to see the Max and Total amount of usage for each user in a given group.
Something like this:

select User.UserId, maxAmount, Total from 
Users
JOIN
( select UserId,max(Amount) as maxAmount from USAGE group by UserId ) A
ON User.UserId = A.UserId
JOIN 
(  select UserId,min(Amount) as minAmount from USAGE group by UserId ) B
ON User.UserId = B.UserId
WHERE User.GroupId = 'some_group_id'

But it is very slow (approx 20 sec), but when I do them separately (just Max and just Min, but not both at once), like this:

select User.UserId, maxAmount, minAmount from 
Users
JOIN
( select UserId,max(Amount) as maxAmount from USAGE group by UserId ) A
ON User.UserId = A.UserId
WHERE User.GroupId = 'some_group_id'

it runs in a flash!

It just makes no sense that I can join them separately very fast, but when I join them all three it's very slow.

Here are the plans, where I created the inline views in the preceeding statements as actual views:

create view usage_min as select user_id, min(amount) from usage group by user_id;
create view usage_max as select user_id, max(amount) from usage group by user_id;




 explain plan for select * from usage_min join users using(user_id) where group_id='1212882339';
 select * from table(dbms_xplan.display);
Plan hash value: 3874246446

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |     1 |   139 |  1162   (1)| 00:00:14 |
|   1 |  HASH GROUP BY                    |                 |     1 |   139 |  1162   (1)| 00:00:14 |
|   2 |   TABLE ACCESS BY INDEX ROWID     | USAGE           |  7977 |   116K|  1157   (1)| 00:00:14 |
|   3 |    NESTED LOOPS                   |                 | 11085 |  1504K|  1160   (1)| 00:00:14 |
|   4 |     MAT_VIEW ACCESS BY INDEX ROWID| USERS           |     1 |   124 |     3   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN             | USERS_KT        |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN              | SYS_C0099818    |  7977 |       |    79   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("USERS"."group_id"='1212882339')
   6 - access("user_id"="USERS"."user_id")


  explain plan for select * from users join  usage_max   using(user_id) where group_id='1212882339';
 select * from table(dbms_xplan.display);

Plan hash value: 2384977958

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |     1 |   145 |   519   (2)| 00:00:07 |
|   1 |  HASH GROUP BY                    |                 |     1 |   145 |   519   (2)| 00:00:07 |
|   2 |   TABLE ACCESS BY INDEX ROWID     | USAGE2          | 18251 |   374K|   512   (1)| 00:00:07 |
|   3 |    NESTED LOOPS                   |                 | 25362 |  3591K|   515   (1)| 00:00:07 |
|   4 |     MAT_VIEW ACCESS BY INDEX ROWID| USERS           |     1 |   124 |     3   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN             | USERS_KT        |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN              | T2_user_id        | 18251 |       |    25   (4)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("USERS"."group_id"='1212882339')
   6 - access("USERS"."user_id"="user_id")

 explain plan for select * from  users  join usage_max using(user_id) join  usage_min using(user_id)  where group_id='1212882339';
 select * from table(dbms_xplan.display);

Plan hash value: 3190011991

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |   100 | 16600 |  6782  (19)| 00:01:22 |
|*  1 |  HASH JOIN                        |                 |   100 | 16600 |  6782  (19)| 00:01:22 |
|   2 |   MERGE JOIN                      |                 |   100 | 14500 |  3176  (19)| 00:00:39 |
|   3 |    SORT JOIN                      |                 |  2920K|    58M|  3172  (19)| 00:00:39 |
|   4 |     VIEW                          | USAGE_MAX       |  2920K|    58M|  3172  (19)| 00:00:39 |
|   5 |      HASH GROUP BY                |                 |  2920K|    58M|  3172  (19)| 00:00:39 |
|   6 |       TABLE ACCESS FULL           | USAGE2          |  2920K|    58M|  2686   (5)| 00:00:33 |
|*  7 |    SORT JOIN                      |                 |     1 |   124 |     4  (25)| 00:00:01 |
|   8 |     MAT_VIEW ACCESS BY INDEX ROWID| USERS           |     1 |   124 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN             | USERS_KT        |     1 |       |     1   (0)| 00:00:01 |
|  10 |   VIEW                            | USAGE_MIN       |   398 |  8358 |  3605  (19)| 00:00:44 |
|  11 |    HASH GROUP BY                  |                 |   398 |  5970 |  3605  (19)| 00:00:44 |
|  12 |     TABLE ACCESS FULL             | USAGE           |  3174K|    45M|  3073   (4)| 00:00:37 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("USAGE_MAX"."user_id"="USAGE_MIN"."user_id")
   7 - access("USERS"."user_id"="USAGE_MAX"."user_id")
       filter("USERS"."user_id"="USAGE_MAX"."user_id")
   9 - access("USERS"."group_id"='1212882339')

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

扫码二维码加入Web技术交流群

发布评论

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

评论（1）

病毒体 2024-12-06 11:35:09
  • 查询计划是什么？
  • 您是否正在测量获取第一行的时间？或者您正在测量获取最后一行的时间？

至少，我想使用类似

select User.UserId, maxAmount, Total from 
Users
JOIN
( select UserId,
         max(Amount) as maxAmount,
         sum(Amount) as Total
    from USAGE group by UserId ) A
ON User.UserId = A.UserId
WHERE User.GroupId = 'some_group_id'

或更简单的方法来访问 USAGE 表一次

SELECT user.userId,
       max(usage.Amount) maxAmount,
       sum(usage.Amount) total
  FROM user
       join usage on (user.userId = usage.userId)
 WHERE user.GroupId = 'some_group_id'
 GROUP BY user.userId
  • What are the query plans?
  • Are you measuring the time to fetch the first row? Or are you measuring the time to fetch the last row?

At a minimum, I'd want to hit the USAGE table just once using something like

select User.UserId, maxAmount, Total from 
Users
JOIN
( select UserId,
         max(Amount) as maxAmount,
         sum(Amount) as Total
    from USAGE group by UserId ) A
ON User.UserId = A.UserId
WHERE User.GroupId = 'some_group_id'

or even more simply

SELECT user.userId,
       max(usage.Amount) maxAmount,
       sum(usage.Amount) total
  FROM user
       join usage on (user.userId = usage.userId)
 WHERE user.GroupId = 'some_group_id'
 GROUP BY user.userId
回复 原文
~没有更多了~

关于作者

意犹

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

苦中寻乐

文章 0 评论 0

lueluelue

文章 0 评论 0

嗼ふ静

文章 0 评论 0

王权女流氓

文章 0 评论 0

与花如笺

文章 0 评论 0

残酷

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文