WHERE_IN query with a composite key?

Question

Let's say I have a table with a two-part composite key, and 4 records, like the following:

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, without selecting A,1 or 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.

Answer 1

This syntax works for Oracle and PostgreSQL:

SELECT *   FROM table_name  WHERE (key_part_1, key_part_2) IN ( ('B',1), ('C',2) ); 

Answer 2

Following @Justin Cave's answer, here is a small test case to show that Oracle would do an INDEX RANGE SCAN followed by an INLIST ITERATOR for the following filter predicate:

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 index on key1 and 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# 1 Key-value pair in the same order of the index. Leading key in the lead.

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# 2 Key-value pair in opposite order of the index. Leading key in reverse.

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.