EXISTS 와 DISTINCT
SELECT 문장에서 DISTINCT를 사용하는 이유는 중복된 행을 제거 하기 위해서 입니다. 이를 위해 오라클은 SORT를 수행 하며 소트를 위한 시간, 메모리 또는 디스크 공간이 필요 할 수도 있습니다. 그러니깐 가급적이면 사용 안 하는 것이 좋습니다. 추출되는 데이터가 순서에 의해 출력되지 않아도 된다면 아래의 경우처럼 EXISTS를 사용하는 것이 훨씬 효율적 입니다. 예제를 통해 이해 하도록 하겠습니다. 실제 비용은 1/10 정도로 줄게 됩니다.
:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />:NAMESPACE PREFIX = O />
SQL> set autotrace on
SQL> select distinct c.name
2 from s_customer c, s_ord o
3 where c.id = o.customer_id;
NAME
--------------------------------------------------
Beisbol Si!
Big John's Sports Emporium
:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />:NAMESPACE PREFIX = ST1 />Delhi Sports
Futbol Sonora
Hamada Sport
Kam's Sporting Goods
Kuhn's Sports
Muench Sports
OJ Atheletics
Ojibway Retail
Sportique
NAME
--------------------------------------------------
Unisports
Womansport
13 개의 행이 선택되었습니다.
경 과: 00:00:00 .03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'S_ORD'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'S_CUSTOMER'
5 4 INDEX (UNIQUE SCAN) OF 'S_CUSTOMER_ID_PK' (UNIQUE)
이 경우엔 S_ORD 테이블을 전체 스캔 한 데이터와 S_CUSTOMER 테이블의 데이터를 UNIQUE 인덱스를 이용하여 가져온 후 ROWIDD로 찾은 데이터와 비교하여 같은 아이디의 데이터가 있으면 추출하고 아니면 반복하는 구조를 가집니다. 그런 다음 c.name으로 SORT를 하게 되는 거죠…
Statistics
----------------------------------------------------------
80 recursive calls
0 db block gets
57 consistent gets
0 physical reads
0 redo size
651 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13 rows processed
이번엔 EXISTS를 이용하는 예 입니다.
SQL> select c.name
2 from s_customer c
3 where exists (select 1 from s_ord o
4 where o.customer_id = c.id);
NAME
--------------------------------------------------
Unisports
OJ Atheletics
Womansport
Kam's Sporting Goods
Sportique
Muench Sports
Beisbol Si!
Futbol Sonora
Kuhn's Sports
Hamada Sport
NAME
--------------------------------------------------
Big John's Sports Emporium
Ojibway Retail
13 개의 행이 선택되었습니다.
경 과: 00:00:00 .03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'S_CUSTOMER'
3 1 TABLE ACCESS (FULL) OF 'S_ORD'
이 경우엔 S_CUSTOMER를 전체 스캔하고 S_ORD도 전체 스캔하여 필터링(ID가 같은 데이터가 있는지) 하므로 SORT를 이용하지는 않습니다. 그러므로 앞의 쿼리와 추출되는 데이터의 개수는 같지만 순서는 달리 나오는 겁니다.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
651 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
[실습]
create table myemp (
empno number,
ename varchar2(50),
sal number,
addr varchar2(50)
)
empno number,
ename varchar2(50),
sal number,
addr varchar2(50)
)
-- 2000만건
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE (v_counter <= 20000000) LOOP
insert into myemp values (v_counter, '이름'||v_counter, v_counter+30, '서울'||v_counter);
v_counter := v_counter + 1;
END LOOP;
commit;
END;
create index idx_myemp_empno on myemp(empno)
create table sale (
empno number,
goods_no number,
sale_amt number);
-- 1000만건
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE (v_counter <= 10000000) LOOP
insert into sale values (v_counter, v_counter+10, v_counter+30);
v_counter := v_counter + 1;
END LOOP;
commit;
END;
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE (v_counter <= 10000000) LOOP
insert into sale values (v_counter, v_counter+10, v_counter+30);
v_counter := v_counter + 1;
END LOOP;
commit;
END;
SQL> select count(m.ename)
from myemp m, sale s
where m.empno = s.empno;
COUNT(M.ENAME)
----------------------------
13000051
----------------------------
13000051
1 rows selected.
SQL Execution Time > 00:00:10.937
Total Elapsed Time > 00:00:10.937
Total Elapsed Time > 00:00:10.937
SQL> select count(distinct m.ename)
from myemp m, sale s
where m.empno = s.empno;
COUNT(DISTINCTM.ENAME)
--------------------------------------------
12999943
--------------------------------------------
12999943
1 rows selected.
SQL Execution Time > 00:00:36.047
Total Elapsed Time > 00:00:36.047
Total Elapsed Time > 00:00:36.047
SQL> select count(m.ename)
from myemp m
where exists (select 1 from sale s
where s.empno = m.empno);
COUNT(M.ENAME)
----------------------------
12999943
----------------------------
12999943
1 rows selected.
SQL Execution Time > 00:00:11.062
Total Elapsed Time > 00:00:11.062
Total Elapsed Time > 00:00:11.062
위 distinct 사용한 결과와 같다.
댓글 없음:
댓글 쓰기