Oracle:=和in有一个选项有什么区别？

所以,这里有两个返回相同结果的语句.

SELECT * FROM USERS WHERE ID = 1;
-----------------------
SELECT * FROM USER WHERE ID IN (1);

有时,从单个项目生成第二个查询更容易,可以选择向列表中添加其他项目.

在Oracle中使用单项列表是否存在任何内在风险？是否可能导致性能问题？

除了语法之外,您显示的两个查询没有区别.比较解释计划:

查询1:

SQL> EXPLAIN PLAN FOR SELECT * FROM dual WHERE dummy = 'X';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

   1 - filter("DUMMY"='X')

13 rows selected.

查询2:

SQL> EXPLAIN PLAN FOR SELECT * FROM dual WHERE dummy IN 'X';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

   1 - filter("DUMMY"='X')

13 rows selected.

在两个查询中,应用的过滤器内部都是过滤器("DUMMY"="X").

但是,当您在IN列表中有多个值时,Oracle会在内部将其解释为多个OR条件.

在列表中

SQL> EXPLAIN PLAN FOR SELECT * FROM dual WHERE dummy IN ('X', 'Y', 'Z');

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

   1 - filter("DUMMY"='X' OR "DUMMY"='Y' OR "DUMMY"='Z')

13 rows selected.

您可以看到Oracle在内部将其解释为过滤器("DUMMY"='X'或"DUMMY"='Y'或"DUMMY"='Z')