索引与包含 IN 语句的 OUTER JOIN 的使用

发布于 2024-08-14 17:13:56 字数 386 浏览 12 评论 0原文

SELECT a.*, b.* 
FROM a
    LEFT OUTER JOIN b 
        ON b.user IN (:userlist) 
        AND b.key = a.fk_to_b
WHERE 
a.user IN (:userlist) 
OR b.user IN (:userlist)
  • 表 b 的索引为:(user, key)

仅当 :userlist 参数包含单个值时,数据库才使用该索引。当 :users 包含多个值(内部扩展为多个 OR 语句?)时,不使用索引并执行(b 的)表扫描。

当提供多个 :userlist 值时,为什么数据库不使用索引?

有谁知道这个查询的更优化版本?

SELECT a.*, b.* 
FROM a
    LEFT OUTER JOIN b 
        ON b.user IN (:userlist) 
        AND b.key = a.fk_to_b
WHERE 
a.user IN (:userlist) 
OR b.user IN (:userlist)
  • Table b has an index of: (user, key)

The database only uses the index when the :userlist parameter contains a single value. When :users contains multiple values (which internally expands to multiple OR statements?) the index is not used and a table scan (of b) is performed.

Why isn't the database using the index when multiple :userlist values are supplied?

Does anyone know of a more optimal version of this query?

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

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

发布评论

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

评论(3

凌乱心跳 2024-08-21 17:13:56

此查询适用于所有主要系统,并且可能会更高效:

SELECT  a.*, NULL
FROM    a
WHERE   a.user IN (:userlist)
        AND a.fk_to_b NOT IN
        (
        SELECT  key
        FROM    b
        )
UNION ALL
SELECT  a.*, b.id
FROM    a
JOIN    b
ON      b.key = a.fk_to_b
WHERE   b.user IN (:userlist)

您能告诉我您使用的是哪个 RDBMS 吗?

This query will work in all major systems and probably will be more efficient:

SELECT  a.*, NULL
FROM    a
WHERE   a.user IN (:userlist)
        AND a.fk_to_b NOT IN
        (
        SELECT  key
        FROM    b
        )
UNION ALL
SELECT  a.*, b.id
FROM    a
JOIN    b
ON      b.key = a.fk_to_b
WHERE   b.user IN (:userlist)

Could you please tell which RDBMS do you use?

放低过去 2024-08-21 17:13:56

简单的回答是:这取决于情况。

如果您在 :userlist 中指定多个值,那么数据库服务器可能会选择以不同的方式优化查询,例如可能会选择全表扫描。

大多数时候,最好的选择是通过

  1. 在 Oracle 中执行 EXPLAIN PLAN
  2. 在 SQL Server 中显示执行计划来查看查询是如何优化的。

为了给您提供更多帮助,我们确实需要知道您正在使用哪个数据库。

The quick answer is: It depends.

If you specify multiple values in :userlist, then the database server may choose to optimise the query in a different way, it may choose a full table scan for instance.

Most of the time, the best option is to see how the query is optimised, by doing

  1. EXPLAIN PLAN in Oracle
  2. Show Execution Plan in SQL server.

To help you more, we really need to know which database you're using.

时光是把杀猪刀 2024-08-21 17:13:56

IN (:userlist) 扩展为多个 OR 语句。
查询优化器忽略 OR 行/子句。
如果数据库是Oracle,该怎么做

CREATE TABLE userListTable  
(  
   sessionId NUMBER(9),  
   user      NUMBER(9)  
);  

CREATE INDEX userListTableMulti1 ON userListTable(sessionId,user);  

......

CREATE OR REPLACE FUNCTION fn_getUserList(parmUserList VARCHAR2)  
   RETURN NUMBER DETERMINISTIC  
   varUser      NUMBER(9);  
   varSessionId NUMBER(9);  
BEGIN  
   varSessionId := sys_context('USERENV','SESSIONID');  

   -- You have to work on a VARCHAR2TOLIST() function  
   FOR varUser IN VARCHAR2TOLIST(parmUserList) LOOP  
      INSERT INTO userListTable(sessionId,user)  
      VALUES(varSessionId, varUser)  
   END LOOP;  

   INSERT INTO resultsTable  
      SELECT  
         varSessionId as sessionId ,  
         a.*                       ,  
         b.*  
      FROM  
         (SELECT a.*  
          FROM a  
             INNER JOIN userListTable  
             ON a.user = userListTable.user AND  
                userListTable.sessionId = varSessionId) a  
         LEFT OUTER JOIN (SELECT b.*  
                          FROM b  
                             INNER JOIN userListTable  
                             ON b.user = userListTable.user AND  
                                userListTable.sessionId = varSessionId) b  
         ON b.key = a.fk_to_b;  

   RETURN varSessionId;  
END;  
/  

// C Client side  
int   varSessionId;  
char* parmUserList;  
char* sqlStr;  

...  

sqlStr = (char*)malloc( strlen(parmUserList) + 17 ) ;  
sprintf(sqlStr,"fn_getUserList(%s)", parmUserList);  

// EXEC_SQL_FUNC_C_MACRO  
// EXEC_SQL_RETURN_QUERY_RESULTS_C_MACRO  
// EXEC_SQL_C_MACRO  
// are all based on the database API C libraries  

// Run the function for this session  
varSessionId = EXEC_SQL_FUNC_C_MACRO(sqlStr);  
free(sqlStr);  

// Get the results  
sqlStr = (char*)malloc(128);  
sprintf(  
   sqlStr,  
   "SELECT * "  
   "FROM resultsTable "  
   "WHERE sessionId=%s",  
   varSessionId);  
EXEC_SQL_RETURN_QUERY_RESULTS_C_MACRO(sqlStr);  
free(sqlStr);  

...  

// Clean up the resultsTable for this session  
sqlStr = (char*)malloc(128);  
sprintf(  
   sqlStr,  
   "DELETE "  
   "FROM resultsTable "  
   "WHERE sessionId=%s",  
   varSessionId);  
EXEC_SQL_C_MACRO(sqlStr);  
free(sqlStr);  

// Clean up the userListTable for this session  
sqlStr = (char*)malloc(128);  
sprintf(  
   sqlStr,  
   "DELETE "  
   "FROM userListTable "  
   "WHERE sessionId=%s",  
   varSessionId);  
EXEC_SQL_C_MACRO(sqlStr);  
free(sqlStr);  

IN (:userlist) expands to multiple OR statements.
The query optimizer disregards OR lines/clauses.
Here's what to do if the DB is Oracle:

CREATE TABLE userListTable  
(  
   sessionId NUMBER(9),  
   user      NUMBER(9)  
);  

CREATE INDEX userListTableMulti1 ON userListTable(sessionId,user);  

...

CREATE OR REPLACE FUNCTION fn_getUserList(parmUserList VARCHAR2)  
   RETURN NUMBER DETERMINISTIC  
   varUser      NUMBER(9);  
   varSessionId NUMBER(9);  
BEGIN  
   varSessionId := sys_context('USERENV','SESSIONID');  

   -- You have to work on a VARCHAR2TOLIST() function  
   FOR varUser IN VARCHAR2TOLIST(parmUserList) LOOP  
      INSERT INTO userListTable(sessionId,user)  
      VALUES(varSessionId, varUser)  
   END LOOP;  

   INSERT INTO resultsTable  
      SELECT  
         varSessionId as sessionId ,  
         a.*                       ,  
         b.*  
      FROM  
         (SELECT a.*  
          FROM a  
             INNER JOIN userListTable  
             ON a.user = userListTable.user AND  
                userListTable.sessionId = varSessionId) a  
         LEFT OUTER JOIN (SELECT b.*  
                          FROM b  
                             INNER JOIN userListTable  
                             ON b.user = userListTable.user AND  
                                userListTable.sessionId = varSessionId) b  
         ON b.key = a.fk_to_b;  

   RETURN varSessionId;  
END;  
/  

...

// C Client side  
int   varSessionId;  
char* parmUserList;  
char* sqlStr;  

...  

sqlStr = (char*)malloc( strlen(parmUserList) + 17 ) ;  
sprintf(sqlStr,"fn_getUserList(%s)", parmUserList);  

// EXEC_SQL_FUNC_C_MACRO  
// EXEC_SQL_RETURN_QUERY_RESULTS_C_MACRO  
// EXEC_SQL_C_MACRO  
// are all based on the database API C libraries  

// Run the function for this session  
varSessionId = EXEC_SQL_FUNC_C_MACRO(sqlStr);  
free(sqlStr);  

// Get the results  
sqlStr = (char*)malloc(128);  
sprintf(  
   sqlStr,  
   "SELECT * "  
   "FROM resultsTable "  
   "WHERE sessionId=%s",  
   varSessionId);  
EXEC_SQL_RETURN_QUERY_RESULTS_C_MACRO(sqlStr);  
free(sqlStr);  

...  

// Clean up the resultsTable for this session  
sqlStr = (char*)malloc(128);  
sprintf(  
   sqlStr,  
   "DELETE "  
   "FROM resultsTable "  
   "WHERE sessionId=%s",  
   varSessionId);  
EXEC_SQL_C_MACRO(sqlStr);  
free(sqlStr);  

// Clean up the userListTable for this session  
sqlStr = (char*)malloc(128);  
sprintf(  
   sqlStr,  
   "DELETE "  
   "FROM userListTable "  
   "WHERE sessionId=%s",  
   varSessionId);  
EXEC_SQL_C_MACRO(sqlStr);  
free(sqlStr);  
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文