oracle 带有复合键的 WHERE_IN 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29479891/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
WHERE_IN query with a composite key?
提问by Kevin Pauli
Let's say I have a table with a two-part composite key, and 4 records, like the following:
假设我有一个包含两部分复合键和 4 条记录的表，如下所示：
KEY_PART_1 KEY_PART_2
A 1
B 1
C 2
C 3
I want to write some dynamic SQL to select only the records B,1 and C,2 using a "WHERE IN" clause, withoutselecting A,1 or C,3.
我想编写一些动态 SQL 以使用“WHERE IN”子句仅选择记录 B,1 和 C,2，而不选择 A,1 或 C,3。
Is there some way to do this without a temp table?
有没有办法在没有临时表的情况下做到这一点？
Not that it matters, but we are currently using Oracle, and hoping to move to PostgreSQL soon.
这并不重要，但我们目前正在使用 Oracle，并希望尽快转向 PostgreSQL。
回答by Justin Cave
This syntax works for Oracle and PostgreSQL:
此语法适用于 Oracle 和 PostgreSQL：
SELECT *
FROM table_name
WHERE (key_part_1, key_part_2) IN ( ('B',1), ('C',2) );
回答by Lalit Kumar B
Following @Justin Cave's answer, here is a small test case to show that Oraclewould do an INDEX RANGE SCANfollowed by an INLIST ITERATORfor the following filter predicate:
按照@Justin Cave 的回答，这里有一个小测试用例，它表明Oracle将执行INDEX RANGE SCAN后跟一个INLIST ITERATOR用于以下过滤谓词：
WHERE (key_part_1, key_part_2) IN ( ('B',1), ('C',2) )
Setup
设置
SQL> CREATE TABLE t(key1 VARCHAR2(1), key2 NUMBER);
Table created.
SQL>
SQL> INSERT INTO t VALUES('A', 1);
1 row created.
SQL> INSERT INTO t VALUES('B', 1);
1 row created.
SQL> INSERT INTO t VALUES('C', 2);
1 row created.
SQL> INSERT INTO t VALUES('C', 3);
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
A composite indexon key1 and key2:
key1 和 key2上的复合索引：
SQL> CREATE INDEX t_idx ON t(key1, key2);
Index created.
SQL>
Gather stats:
收集统计数据：
SQL> EXEC DBMS_STATS.gather_table_stats('LALIT', 'T');
PL/SQL procedure successfully completed.
SQL>
Execute the query:
执行查询：
SQL> SELECT * FROM t
2 WHERE (key1, key2) IN ( ('B',1), ('C',2) );
K KEY2
- ----------
B 1
C 2
SQL>
So, it gives the correct output.
所以，它给出了正确的输出。
Let's see the explain plan:
让我们看看解释计划：
Case# 1Key-value pair in the same order of the index. Leading key in the lead.
案例#1与索引相同顺序的键值对。领先关键在领先。
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2301620486
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 10 | 1 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
|* 2 | INDEX RANGE SCAN| T_IDX | 2 | 10 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
2 - access(("KEY1"='B' AND "KEY2"=1 OR "KEY1"='C' AND "KEY2"=2))
14 rows selected.
Case# 2Key-value pair in opposite order of the index. Leading key in reverse.
案例#2键值对与索引的顺序相反。反向引导键。
SQL> EXPLAIN PLAN FOR SELECT * FROM t
2 WHERE (key2, key1) IN ( (1, 'B'), (2, 'C') );
Explained.
SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2301620486
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 10 | 1 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
|* 2 | INDEX RANGE SCAN| T_IDX | 2 | 10 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
2 - access(("KEY1"='B' AND "KEY2"=1 OR "KEY1"='C' AND "KEY2"=2))
14 rows selected.
In either cases, Oracle uses the index.
在任何一种情况下，Oracle 都使用索引。
回答by null
I'm not sure but I think you want something like this which works for almost all RDBMSs:
我不确定，但我认为您想要这样的东西，它几乎适用于所有 RDBMS：
select KEY_PART_1, KEY_PART_2 from your_table where KEY_PART_1='B' and KEY_PART_2 = '1'
UNION
select KEY_PART_1, KEY_PART_2 from your_table where KEY_PART_1='C' and KEY_PART_2 = '2'
