Są 4 operatory zbiorowe

Operatory zbiorowe (Set Operations) są dobrze ilustrowane przez diagramy Venna (w przeciwieństwie do JOINów)
Wspomniałem już o tym w tym poście

set_operators_055.png

Zasady odnośnie pisania Set Operations:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT col_1
      ,1
--      ,2
  FROM test_1
-- ORDER BY col_1
 UNION ALL
SELECT col_2
      ,2
  FROM test_2
 ORDER BY col_1
--         ,col_2
;

set_operators_060.png

TYP DANYCH vs GRUPA TYPÓW DANYCH

Jedna z “grup typów danych” to Character data types.

Należą do niej:
CHAR
NCHAR
VARCHAR2
NVARCHAR2

W obrębie danej grupy, Set Operators dokonują niejawnej konwersji (np. rezultat łączenia CHAR z VARCHAR2 to VARCHAR2).
Jeśli spróbujemy wykonać operację zbiorową (np. MINUS) dla kolumn z różnych grup typów, to Oracle zwróci błąd.

Więcej informacji (np. o pierwszenństwie typów danych podczas konwersji) w dokumentacji

UNION vs UNION ALL

UNION robi wszystko to, co UNION ALL, ale dotatkowo usuwa duplikaty. Zabieg usunięcia duplikatów niesie za sobą pewne konsekwencje.

1. Query wykona się wolniej

Stwórzmy 2 testowe tabele z danymi.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP TABLE test_1;
DROP TABLE test_2;
/
CREATE TABLE test_1 AS 
    SELECT level AS num
      FROM dual
   CONNECT BY level <= 4;
/
CREATE TABLE test_2 AS
    SELECT level AS num
      FROM dual
     WHERE MOD(level,2) = 0
   CONNECT BY level <= 4;
/

Zbieramy statystyki dla planu wykonania query z użyciem UNION ALL

1
2
3
4
5
6
7
8
9
10
SELECT /*+ gather_plan_statistics */ 
       *
  FROM test_1
 UNION ALL
SELECT *
  FROM test_2;
/
SELECT * 
  FROM dbms_xplan.display_cursor(); 
/

set_operators_065.png

…A teraz dla UNION

1
2
3
4
5
6
7
8
9
10
SELECT /*+ gather_plan_statistics */ 
       *
  FROM test_1
 UNION 
SELECT *
  FROM test_2;
/
SELECT * 
  FROM dbms_xplan.display_cursor(); 
/

set_operators_070.png

Aby usunąć duplikaty, wyniki zostały posortowane.
Wynik takiego query będzie identyczny jak SELECT DISTINCT...

1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT /*+ gather_plan_statistics */ 
               *
  FROM (SELECT *
          FROM test_1
         UNION ALL 
        SELECT *
          FROM test_2);
/
SELECT * 
  FROM dbms_xplan.display_cursor(); 
/

…Jednak w drugim przykładzie została użyta bardziej wydajna (zaimplementowana w DBMS 10.2.0) wersja algorytmu sortującego - HASH UNIQUE.

set_operators_075.png

Możemy zabronić użycia tego algorytmu. Wtedy plan wykonania będzie identyczny jak w przypadku UNION

1
2
3
4
5
6
7
8
9
10
11
12
13
ALTER SESSION SET "_gby_hash_aggregation_enabled" = FALSE;
/
SELECT DISTINCT /*+ gather_plan_statistics */ 
               *
  FROM (SELECT *
          FROM test_1
         UNION ALL 
        SELECT *
          FROM test_2);
/
SELECT * 
  FROM dbms_xplan.display_cursor(); 
/

2. Żeby usunąć duplikaty, trzeba najpierw porównać wartości. Oracle nie wspiera tej funkcjonalności dla BLOB I CLOB.

Tak jak poprzednio - tworzymy 2 tabele. W każdej 1 kolumna - tym razem celowo zdeklarowany CLOB.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DROP TABLE test_3;
DROP TABLE test_4;
/
CREATE TABLE test_3 (
   large_object CLOB);
/
CREATE TABLE test_4 (
   large_object CLOB);
/
INSERT INTO test_3 (large_object) VALUES (1);
INSERT INTO test_3 (large_object) VALUES (2);
INSERT INTO test_3 (large_object) VALUES (3);
INSERT INTO test_3 (large_object) VALUES (4);
INSERT INTO test_4 (large_object) VALUES (2);
INSERT INTO test_4 (large_object) VALUES (4);
--   ***********************************
SELECT /*+ gather_plan_statistics */ 
       *
  FROM test_3
 UNION ALL
SELECT *
  FROM test_4;
/
SELECT * 
  FROM dbms_xplan.display_cursor(); 
/
SELECT /*+ gather_plan_statistics */ 
       *
  FROM test_3
 UNION 
SELECT *
  FROM test_4;
/
SELECT * 
  FROM dbms_xplan.display_cursor(); 
/

O ile UNION ALL nie sprawia problemu, to UNION już nie przejdzie.

set_operators_080.png

Moglibyśmy porównać zawartość tych kolumn np. za pomocą funkcji TO_CHAR, która zawsze zwraca VARCHAR2.

1
2
3
4
5
6
7
8
9
10
SELECT /*+ gather_plan_statistics */ 
       TO_CHAR(large_object)
  FROM test_3
 UNION 
SELECT TO_CHAR(large_object)
  FROM test_4;
/
SELECT * 
  FROM dbms_xplan.display_cursor(); 
/

Źródła:

  1. https://docs.oracle.com/en/database/oracle/or…
  2. https://asktom.oracle.com/pls/asktom/f?p=100:…
  3. https://docs.oracle.com/cd/B19306_01/server.1…