英文:
SQL takes too long. How can I optimize this query?
问题
这个查询的问题是速度太慢。我不确定如何优化它。
英文:
The issue with this query is that is too slow:
SELECT
*
FROM
(
SELECT
*
FROM
(
SELECT
*
FROM
(
SELECT
*
FROM
(
SELECT
*
FROM
(
SELECT
MIN(UPPER(MSG_USERS.MSU_LOGIN_NAME)) as MSU_LOGIN_NAME_SORT,
MSG_USERS.MSU_USER_ID ID,
MSG_USERS.MSU_LAST_LOGIN_TIME,
MSG_USERS.MSU_CREATED_DATE,
MSG_USERS.MSU_LOGIN_NAME,
MSG_USERS.MSU_UUID,
MSG_USERS.MSU_FIRST_NAME,
MSG_USERS.MSU_LAST_NAME,
MSG_USERS.MSU_LAST_UPDATED,
MSG_USERS.MSU_CUSTOMER_UID
FROM
(
MSG_USERS
left join MSG_ACCOUNT_USERS on MAU_USER_ID = MSU_USER_ID
left join MSG_ACCOUNTS on MSA_ACCOUNT_ID = MAU_ACCOUNT_ID
left join MSG_TENANTS on MTE_ACCOUNT_ID = MAU_ACCOUNT_ID
)
WHERE
(
MSG_USERS.MSU_ROLE_NAME <> 'CUSTOMER_INTEGRATION'
AND MSG_USERS.MSU_ROLE_NAME <> 'CUSTOMER_EXTERNAL_INTEGRATION'
AND MSG_USERS.MSU_ROLE_NAME <> 'INTEGRATION_SUPERUSER'
AND MSG_USERS.MSU_ROLE_NAME <> 'OAUTH2_CLIENT'
)
GROUP BY
MSG_USERS.MSU_USER_ID,
MSG_USERS.MSU_LAST_LOGIN_TIME,
MSG_USERS.MSU_CREATED_DATE,
MSG_USERS.MSU_LOGIN_NAME,
MSG_USERS.MSU_UUID,
MSG_USERS.MSU_FIRST_NAME,
MSG_USERS.MSU_LAST_NAME,
MSG_USERS.MSU_LAST_UPDATED,
MSG_USERS.MSU_CUSTOMER_UID
ORDER BY
MIN(UPPER(MSG_USERS.MSU_LOGIN_NAME)) ASC
)
ORDER BY
UPPER(MSU_LOGIN_NAME_SORT) ASC,
ID ASC
)
WHERE
ROWNUM < 7
)
ORDER BY
UPPER(MSU_LOGIN_NAME_SORT) DESC,
ID DESC
)
WHERE
ROWNUM < 7
)
ORDER BY
UPPER(MSU_LOGIN_NAME_SORT) ASC,
ID ASC;
I am not sure how to optimize it.
答案1
得分: 3
以下是翻译好的代码部分：
原则上，你执行：
选择 ...
从 (((( ...
按顺序
最小(大写(MSG_USERS.MSU_LOGIN_NAME)) 升序
)
按顺序
大写(MSU_LOGIN_NAME_SORT) 升序，
ID 升序
)
在
ROWNUM < 7
)
按顺序
大写(MSU_LOGIN_NAME_SORT) 降序，
ID 降序
)
在
ROWNUM < 7
)
按顺序
大写(MSU_LOGIN_NAME_SORT) 升序，
ID 升序;
这个疯狂的链条的目的是什么？
你在表 MSG_ACCOUNT_USERS, MSG_ACCOUNTS, MSG_TENANTS 上进行了 LEFT JOIN，但它们在你的查询中并未被使用。那么，为什么你要（左）连接它们？它们没有任何影响，我猜你可以从查询中简单地移除它们，然后你甚至不需要 GROUP BY 子句。
我假设你的查询可以简化成像这样的：
选择
MSG_USERS.MSU_USER_ID ID,
MSG_USERS.MSU_LAST_LOGIN_TIME,
MSG_USERS.MSU_CREATED_DATE,
MSG_USERS.MSU_LOGIN_NAME,
MSG_USERS.MSU_UUID,
MSG_USERS.MSU_FIRST_NAME,
MSG_USERS.MSU_LAST_NAME,
MSG_USERS.MSU_LAST_UPDATED,
MSG_USERS.MSU_CUSTOMER_UID
从 MSG_USERS
在 MSG_USERS.MSU_ROLE_NAME 不在 ('CUSTOMER_INTEGRATION' 'CUSTOMER_EXTERNAL_INTEGRATION' 'INTEGRATION_SUPERUSER' 'OAUTH2_CLIENT') 的情况下
按顺序 ...
获取前 7 行;
英文:
In principle you do
select ...
from (((( ...
ORDER BY
MIN(UPPER(MSG_USERS.MSU_LOGIN_NAME)) ASC
)
ORDER BY
UPPER(MSU_LOGIN_NAME_SORT) ASC,
ID ASC
)
WHERE
ROWNUM < 7
)
ORDER BY
UPPER(MSU_LOGIN_NAME_SORT) DESC,
ID DESC
)
WHERE
ROWNUM < 7
)
ORDER BY
UPPER(MSU_LOGIN_NAME_SORT) ASC,
ID ASC;
What is the purpose of this crazy chain?
You make LEFT JOIN with tables MSG_ACCOUNT_USERS, MSG_ACCOUNTS, MSG_TENANTS but they are not used in your query. So, why do you (left) join them? They have no effect, I would guess you can simply remove them from your query and then you even don't need the GROUP BY clause.
I assume your query can be reduced to something like this:
SELECT
MSG_USERS.MSU_USER_ID ID,
MSG_USERS.MSU_LAST_LOGIN_TIME,
MSG_USERS.MSU_CREATED_DATE,
MSG_USERS.MSU_LOGIN_NAME,
MSG_USERS.MSU_UUID,
MSG_USERS.MSU_FIRST_NAME,
MSG_USERS.MSU_LAST_NAME,
MSG_USERS.MSU_LAST_UPDATED,
MSG_USERS.MSU_CUSTOMER_UID
FROM MSG_USERS
WHERE MSG_USERS.MSU_ROLE_NAME NOT IN ('CUSTOMER_INTEGRATION' 'CUSTOMER_EXTERNAL_INTEGRATION' 'INTEGRATION_SUPERUSER' 'OAUTH2_CLIENT')
ORDER BY ...
FETCH FIRST 7 ROWS ONLY;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论