WHERE_IN запрос с составным ключом?
Допустим, у меня есть таблица с составным ключом из двух частей и 4 записи, например:
KEY_PART_1 KEY_PART_2
A 1
B 1
C 2
C 3
Я хочу написать некоторый динамический SQL для выбора только записей B,1 и C,2, используя предложение "WHERE IN", не выбирая A,1 или C,3.
Есть ли способ сделать это без временной таблицы?
Не то, чтобы это имело значение, но в настоящее время мы используем Oracle и надеемся вскоре перейти на PostgreSQL.
3 ответа
Этот синтаксис работает для Oracle и PostgreSQL:
SELECT *
FROM table_name
WHERE (key_part_1, key_part_2) IN ( ('B',1), ('C',2) );
После ответа @Justin Cave ниже приведен небольшой тестовый пример, демонстрирующий, что Oracle выполнит сканирование индекса INDEX RANGE, за которым следует INTIST ITERATOR для следующего предиката фильтра:
WHERE (key_part_1, key_part_2) IN ( ('B',1), ('C',2) )
Настроить
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>
Составной индекс для ключей key1 и key2:
SQL> CREATE INDEX t_idx ON t(key1, key2);
Index created.
SQL>
Соберите статистику:
SQL> EXEC DBMS_STATS.gather_table_stats('LALIT', 'T');
PL/SQL procedure successfully completed.
SQL>
Выполнить запрос:
SQL> SELECT * FROM t
2 WHERE (key1, key2) IN ( ('B',1), ('C',2) );
K KEY2
- ----------
B 1
C 2
SQL>
Таким образом, это дает правильный вывод.
Давайте посмотрим план объяснения:
Случай № 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.
Случай № 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.
В любом случае Oracle использует индекс.
Я не уверен, но я думаю, что вы хотите что-то вроде этого, которое работает почти для всех 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'