[오라클] SQL - UNION에 관한 SQL 규칙

Head First SQL 책에 있는 UNION에 관한 내용을 보다가 이 내용을 참고로 다시 정리해 봅니다.

1. UNION에서 ORDER BY 사용

- UNION에서 ORDER BY가 여러 번 나오면 해석하지 못한다는 에러가 발생한다.

- UNION은 문장의 끝에 한 개의 ORDER BY만을 받아 들일 수 있다.

이유는 UNION이 여러 SELECT문의 결과를 합친 후 결과를 만들기 때문이다.

예제 1) 두 select문에 order by 가 모두 있을 경우 에러가 발생한다.

select 10 as a1, 20 as a2 from dual order by a1 union select 30 as a3, 40 as a4 from dual order by a1

예제 2) 두 select문에 order by 가 마지막 select 문에만 있어야 한다.

select 10 as a1, 20 as a2 from dual union select 30 as a3, 40 as a4 from dual order by a1

- ORDER BY 절에 컬럼명은 첫번째 질의(select문)의 컬럼명만 사용이 가능하다.

예제 3) 이런 경우는 두번째 select문의 컬럼명을 사용했기 때문에 에러가 발생한다.

select 10 as a1, 20 as a2 from dual union select 30 as a3, 40 as a4 from dual order by a3

예제 4) 첫번째 select문의 컬럼명을 사용하였기에 정상 처리

select 10 as a1, 20 as a2 from dual union select 30 as a3, 40 as a4 from dual order by a1

2. 각 SELECT문의 열(컬럼) 수는 같아야 한다.

- 첫번째 SELECT문에서 두 열을 선택하고 두번째에서는 하나의 열을 선택 할 수 없다.

예제) 위 내용처럼 각 SELECT문의 열의 수가 다를 경우 에러가 발생한다.

select 10 as a1, 20 as a2 from dual union select 10 as a3 from dual

- 컬럼 수가 일치하지 않아 에러가 발생할 경우 강제로 일치 시키는 방법은 상수값 또는 NULL을 사용한다.

select 10 as a1, 20 as a2 from dual union select 10 as a3, NULL from dual

3. 각 SELECT문에 표현식과 집계 함수도 같아야 한다.

4. SELECT문의 순서는 중요하지 않다. 순서는 결과에 영향을 미치지 않는다

보기 1) select 10 as a1, 20 as a2 from dual union select 30 as a1, 40 as a2 from dual

보기 2) select 30 as a1, 40 as a2 from dual union select 10 as a1, 20 as a2 from dual

보기1)과 보기2)는 SELECT문의 순서가 달라도 결과는 같다는 뜻이다.

5. UNION의 결과에서 중복값은 하나로 나오는 것이 디폴트이다.

중복값이 모두 나오게 하려면 UNION ALL를 쓰면 된다.

6. 열의 데이터 타입은 같거나 서로 변환 가능한 값이어야 한다.

여기서 좀더 생각할 것은 데이터 타입만 같으며 되며 데이터 사이즈까지 같을 필요는 없다는 것이다.

예를 들어 같은 열에서 하나는 varchar2(100) 이고 다른 하나는 varchar2(200)인 경우라도 문제가 없다.

7. 컬럼명은 일치하지 않아도 된다. 그래서 강제로 컬럼명을 맞춘다고 해도 무조건 순서대로 컬럼명을 맞추기 때문에 이점을 유념하고 있어야 한다. 컬럼명이 다를 경우 첫번째 질의에 있는 컬럼명이 헤딩된다.

예제 1) 컬럼 순서를 바꾸고 강제로 alias를 준다고 해도 sum(a1)은 "50"이 아니라 "40"이 나온다.

select sum(a1), sum(a2) from ( select 10 as a1, 20 as a2 from dual union select 30 as a2, 40 as a1 from dual )

8. UNION은 내부적으로 중복된 값을 제거하는 과정에서 SORT기능(정렬)이 작동하며,

UNION ALL은 중복여부 관계없이 전체가 보여주므로 SORT가 일어나지 않는다.

고로 UNION ALL이 빠르다.

오라클에서 rownum 은 쿼리가 실행될 때 결과 레코드에 번호를 나타내어 주는 필드이다.
물론 table을 만들 때 rownum을 만들어줄 필요는 없다.

어떠한 테이블이라도
"select rownum from boardtable" 의 형태로 쿼리를 날리면 레코드에 번호가 나타내어 주는 것을 볼 수 있을 것이다...

따라서 페이징을 위한 쿼리에서 우리는 rownum이 10 보다 크고 20보다 작은...
이런 식의 조건을 주어.. 원하는 범위의 레코드만 쿼리 할 수 있다..

select * from boardtable where rownum > 10 and rownum <= 20

그래서 위와 같이 쿼라하면 어떨까?
결과가 하나도 않나올 것이다.. 왜냐하면.. 쿼리가 실행되면 where 절 부분이 먼저 실행 될 것이고.. select 를 하기전에는 rownum이 없기 때문이다.
따라서, rownum을 긁어오는 쿼리를 한번 미리 해줄 필요가 잇다..

select * from (select rownum numrow , boardtable.* from boardtable) where numrow > 10 and numrow <= 20

order by 절을 사용하지 않는다면 이것만으로 우리는 행복할 수 있다.

select * from (select rownum numrow , boardtable.* from boardtable order by reg_date desc) where numrow > 10 and numrow <= 20

결과는 원하는 대로 되지 않음을 볼 수 있다.
이유는 아마도.... order by 가 rownum 값을 만드는데 영향을 못주는 것이라고 생각되는데.. 즉.. order by를 지정한다고 해도.. rownum은 최초의 order by 가 없는 상태로 만들어진다고 추측할 수 있다..
select rownum numrow , boardtable.* from boardtable order by reg_date desc
select rownum numrow , boardtable.* from boardtable
위의 두 쿼리는 실제로 rownum 값을 똑같이 만들어준다.. 물론 뿌리는 순서는 다르지만..

따라서 원하는대로 정렬을 할려면..

select rownum numrow , aa.* from ( select * from boardtable order by reg_date desc ) aa

위의 형태로 쿼리를 바꾸어 줘야 한다.
그런데 또 우리는 범위를 지정해야 하니까..

select rownum numrow , aa.* from ( select * from boardtable order by reg_date desc ) aa where numrow > 10 and numrow <= 20

하면 아무것도 안나오는 이유는 이미 설명했고..

select rownum numrow , aa.* from ( select * from boardtable order by reg_date desc ) aa where numrow > 10 and numrow <= 20

따라서...

select * from ( select rownum numrow , aa.* from ( select * from boardtable order by reg_date desc ) aa ) where numrow > 10 and numrow <= 20

위와 같이 해주면 되겟다..
그런데 좀 복잡하다.. 더구나 검색을 한번... 두번... 세번 을 하니...
데이타 양이 많다면 쿼리실행속도가 느려질 가능성이 있다..
물론 속도에 가장 큰 영향을 주는 것은 order by 이지만 ...
그래서 index를 만들고 hint 절을 써야할 이유가 생기는 것이다..
인덱스는 order by 를 하는 필드에 주면 된다..
따라서.. reg_date 필드에 인덱스를 주면 되고...
그러나 hint 절을 사용하려면 유니크 속성이 있어야 한다..
따라서.. 인덱스를 줄 때 reg_date 필드와 프라이머리 키 필드를 같이 걸어주면 유닉속성을 이용할 수 있다..

create unique index idx_board_reg_date on boardtable (reg_date, idx)

인덱스가 생성된다..

select /*+ index_desc(boardtable idx_board_reg_date) */ rownum, tvulist.* from tvulist

order by 없이 rownum이 제대로 긁어지나?

select * from (select /*+ index_desc(tvulist idx_board_reg_date) */ rownum as numrow, boardtable.* from boardtable ) where numrow > 10 and numrow <= 20

이제 위와 같이하면...
원하는 범위의 데이타를 가져올 수 있겠다..

만일 인덱스를 사용하지 않앗는데... 데이타가 튀어나오는 시간이 좀 늦는다 싶으면..
인덱스를 사용하라.. 행복할 것이다..

select distinct field1

from tb_test

이렇게 하면 field1에 있는 데이터 중 중복을 제거하고 유니크 한 값들만 나온다.

그러나 시간 개념이 들어가서 최신의 데이터에 해당하는 row의 값을 다 가져 오고 싶다면?

distinct에 시간까지 걸면 모든 데이터가 다 유니크 해버린다.

oracle db 데이터 중 특정 필드의 중복을 제거하고 최신의 데이터 1개만 가져오기.

숨어 있던 rowid가 이때 도움을 준다.

select *

from tb_test

where reg_tm between start_tm and end_tm

and rowid in (select max(rowid) from tb_test group by field1)

중복이 되면 안되는 필드를 group by 해서 rowid를 큰 것 순으로 뽑아내면 된다.

...라고 썼는데, 생각해보니.. scan 범위를 더 줄일 수 있을 것 같다.

물론 이건 환경과 쓰는 사람 마음에 따라 다르다. 내가 찾은 예제에선 저렇게 되어 있었고..

나는 시간 범위 내에서 중복된 값 중 최신의 것만 찾아내면 되니까.

select *

from tb_test

where rowid in (select max(rowid) from tb_test where reg_tm between start_tm and end_tm group by field1)

요렇게 작성하면 스캔 범위가 줄어들어 위의 쿼리보단 성능향상에 도움이 될 수 있을 것 같다.

ps. 스크랩 하시는 분들이 많아져서 추가 사실을 덧붙입니다.

이 구문은 rowid가 숫자일 경우에만 유효합니다

http://wiki.kldp.org/HOWTO/html/Adv-Bash-Scr-HOWTO/index.html