복합 키를 사용하여 WHERE_IN 쿼리를 수행하시겠습니까?

복합 키를 사용하여 WHERE_IN 쿼리를 수행하시겠습니까?

두 부분으로 구성된 복합 키와 다음과 같은 4개의 레코드가 있는 테이블이 있다고 가정해 보겠습니다.

KEY_PART_1 KEY_PART_2
A          1
B          1
C          2
C          3

A,1 또는 C,3을 선택하지 않고 "WHERE IN" 절을 사용하여 레코드 B,1,C,2만 선택할 수 있도록 동적 SQL을 작성하고자 합니다.

이것을 온도 테이블 없이 할 수 있는 방법이 있습니까?

중요한 것은 아니지만 현재 Oracle을 사용하고 있으며 Postgre로 이동하기를 희망합니다.곧 SQL입니다.

이 구문은 Oracle 및 Postgre에 적용됩니다.SQL:

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

@Justin Cave의 답변에 이어 Oracle이 다음 필터 서술어에 대해 INLITE INTERATOR에 이어 INDEX RANGE SCAN을 수행한다는 것을 보여주는 작은 테스트 사례가 있습니다.

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>

키 1 및 키 2의 복합 인덱스:

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'

언급URL : https://stackoverflow.com/questions/29479891/where-in-query-with-a-composite-key