ROWID

LISTAGG(칼럼명, ',') WITHIN GROUP (ORDER BY ROWID ASC) 라는 코드를 봤다.

 

https://gent.tistory.com/328

여기에 listagg within within group 사용법이 잘 나와있다.

여러 행을 하나의 컬럼값으로 보고 싶을 때 사용한다.

칼럼1	칼럼2
과일	사과
과일	귤
과일	바나나
과일	복숭아

라는 테이블이 있다고 할 때 

내가 좋아하는 과일

사과, 귤, 바나나, 복숭아

이렇게 처음에 조회했던 순서대로 하나의 컬럼값을 만들고 싶은데 정렬을 원하는대로 할 수 없다면..

(정렬을 위한 칼럼 추가도 불가하다고 치자.)

 

1. LISTAGG(칼럼2, ', ') WITHIN GROUP (ORDER BY 칼럼2 ASC) as '내가 좋아하는 과일' -- 오름차순

=> 귤, 바나나, 복숭아, 사과

2. LISTAGG(칼럼2, ', ') WITHIN GROUP (ORDER BY 칼럼2 DESC) as '내가 좋아하는 과일' -- 내림차순

=> 사과, 복숭아, 바나나, 귤

3. LISTAGG(칼럼2, ', ') WITHIN GROUP (ORDER BY rowid DESC) as '내가 좋아하는 과일' 

=> 사과, 귤, 바나나, 복숭아  이렇게 처음 조회했던 테이블의 순서대로 만들 수 있다.

 

4. rowid를 사용하지 않고 .. partition by를 사용해서 만들어보기

LISTAGG(칼럼2, ', ') WITHIN GROUP (ORDER BY 칼럼2 ) OVER(PARITION BY 칼럼1) as '내가 좋아하는 과일'

=> 

사과, 귤, 바나나, 복숭아

사과, 귤, 바나나, 복숭아

사과, 귤, 바나나, 복숭아

사과, 귤, 바나나, 복숭아

순서는 같지만 기존의 컬럼 수 만큼 나온다.

 

5. 4번에서 억지로 중복을 제거해보자

DISTINCT(LISTAGG(칼럼2, ', ') WITHIN GROUP (ORDER BY 칼럼2 ) OVER(PARITION BY 칼럼1)) as '내가 좋아하는 과일'

=> 사과, 귤, 바나나, 복숭아

 

 

원하는 결과는 나왔지만 

이제 ROWID를 알게되었으니 .. 이걸 쓰는게 편할 것 같다. 

 

 

 

---

ROWID

-  ROWID는 ORACLE에서 INDEX를 생성하기 위하여 내부적으로 사용하는 PSEUDOCOLUMN 으로 사용자가 임으로 변경하거나 삭제할 수 없다.

- 단지 테이블의 한 컬럼처럼 참조만 가능하며 데이타베이스에 값이 저장되지는 않는다.

- ROWID는 물리적인 ADDRESS를 가지고 있기 때문에 SINGLE BLOCK ACCESS로 찾고자 하는 ROW를 찾을 수 있어 물리적인 ROWID는 주어진 테이블에 가장 빠른 ACCESS 방법을 제공하게 된다.

 

 

 

 

 

https://blog.naver.com/humanang/130011376431

ROWID 와 ROWNUM

 

ROWID 정의

 

▣ ROWID는 ORACLE에서 INDEX를 생성하기 위하여 내부적으로 사용하는 PSEUDOCOLUMN 으로 사용자가 임으로 변경하거나 삭제할 수 없다.

▣ 단지 테이블의 한 컬럼처럼 참조만 가능하며 데이타베이스에 값이 저장되지는 않는다.
▣ ROWID는 물리적인 ADDRESS를 가지고 있기 때문에 SINGLE BLOCK ACCESS로 찾고자 하는 ROW를 찾을 수 있어 물리적인 ROWID는 주어진 테이블에 가장 빠른 ACCESS 방법을 제공하게 된다.

▣ ROWID를 분석해보면
AAAArs AAD AAAAUa AAA
 ------  ---  ------  ---
    1     2      3     4
1) 6자리 : 데이터 오브젝트 번호 (data object number) - 오브젝트의 고유 번호
2) 3자리 : 상대적 파일 번호 (relative file number) - 각각의 데이터파일에 할당되는 번호
3) 6자리 : 블록 번호 (block number) - 데이터 블록의 위치를 알려주는 번호
4) 3자리 : 블록 내의 행 번호 (row number) - 오라클 블록의 헤더에 저장된 row directory slot의 위치를 알려주는 고유 번호

 

ROWID 활용법

▣ 중복제거

 

1. 테이블에서 필드1, 필드2, 필드3 을 불러오는데 필드1에 대해서만 중복을 제거하고 불러와야 할 때

select distinct 필드1, 필드2, 필드3 from 테이블1 where 필드2 = '1' order by 필드1;

위와같이 하면 필드1, 필드2, 필드3 가 모두 중복되어야 제거가 된다.

따라서 이 경우에는 rowid 를 사용해야 한다.

 

SELECT 필드1, 필드2, 필드3 FROM 테이블1 WHERE 필드2 = '1' and rowid in                      (SELECT max(rowid) FROM 테이블1 GROUP BY 필드1);

 

2. 중복된 데이터 중에서 ROWID가 큰 값을 제거
 

DELETE FROM emp a WHERE rowid > (SELECT MIN(rowid) FROM emp b                         WHERE b.empno = a.empno);

 

DELETE FROM emp a WHERE rowid > ANY (SELECT rowid FROM emp b                                WHERE b.empno = a.empno);

 

--> min 그룹함수를 사용하면 수행시 sort 작업이 일어나 수행속도가 떨어지는 반면, any 연산자를 사용하면 하나라도 클 경우 작업이 종료되므로 수행속도가 향상된다.

3. 나중에 들어온 데이터를 살릴 경우
 

DELETE FROM emp a WHERE rowid < (SELECT MAX(rowid) FROM emp b                         WHERE a.empno = b.empno);

 

 

▣ 중복행 찾기

 

 

SELECT * FROM emp a  WHERE rowid > ANY (SELECT rowid FROM emp b                                 WHERE b.code1=a.code1 AND b.code2=a.code2)

 

서브쿼리의 where 절에 기본키 컬럼을 모두 조건으로 기술하면 된다.

위 쿼리는 중복된 행 중에 한개만 찾아주는데 중복된 행을 모두 검색하려면 다음과 같이 한다. 

 

SELECT *

FROM EMP A

         ,(SELECT CODE1, CODE2, COUNT(*)

           FROM EMP B

           GROUP BY CODE1, CODE2

           HAVING COUNT(*) > 1)

WHERE A.CODE1 = B.CODE1

AND     A.CODE2 = B.CODE2

;

 

 

▣ 행을 찾아가는 가장 빠른 방법이 rowid 라는 것을 이용.
update emp set sal = 999
where rowid in (select rowid from emp where ename like 'A%');
==> 따라서 인덱스의 활용과도 연관성이 있을 거라 생각됩니다. 인덱스는 인덱스 컬럼과 rowid를 가지고 있으니까요.

▣ 유일성 부여

데이터가 non-unique 한 환경일 때, 데이터에 "유일성"을 부여할 수 있다.
예를 들면, 이전 페이지에 나왔던 데이터가 다음 페이지에 중복되어 나오는 것을 방지할 수 있다. 이순신이라는 사람이 10건 있다고 할 때, 이전페이지에 7건이 나왔다고 하면 "다음"버튼을 누르면 나머지 3명의 이순신이 나와야 되는 업무가 있을 때 like 를 쓰게 되면 10명 모두 나오게 된다. 이때 rowid 를 적절히 이용하면 이들에게도 유일성을 보장할 수 있다.
select * from emp
where ename || rowid > :ename || :before_rowid

참고로, 이 원리는 MSSQL2000에서도 그대로 반영되고 있다.
MSSQL2000의 인덱스페이지를 살펴보면, unique 일 경우에는 4byte의 "?" 컬럼이 생기지 않지만, non-unique 할 때는 4byte의 "?" 컬럼이 생겨 값들의 유일성을 보장하고 있는 것을 알 수 있다. 결국 unique 라는 옵션 하나만 잘 사용해도 엄청난 수행속도를 보장받을 수 있는 것이다.

 

ROWNUM 정의

결과집합에 대한 가상의 순번

따라서 같은 SQL이라고 하더라도, 다른 rownum 을 가질 수 있다.

 

 

ROWNUM 활용법
 

◈ 선택한 Row만큼만 보여주기

SQL> SELECT name
      FROM storm_board
      WHERE rownum <= 10
---> 이렇게 하면 데이터가 1000건이 있더라도, 1~10건만 보여주게 된다.

◈ copy_t 테이블 생성시
CREATE TABLE qq_t
AS
SELECT rownum no, to_char(rownum, '09') c_no

FROM cdr                  --> cdr 테이블은 최소한 100건 이상
WHERE rownum < 100;

[출처] ROWID 와 ROWNUM|작성자 humanang

 

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=realred0&logNo=10044307198

8. PseudoColumn을 의미하는 것
 8-1) ROWID 컬럼
 8-2) ROWNUM 컬럼

 

8-1) ROWID 컬럼
--------------------------------------------------------------------------------

오라클에서 내부적으로 사용되는 컬럼을 pseudocolumn이라고 하며,
ROWID, ROWNUM등이 있다.
ROWID는 데이터베이스에서 컬럼이 위치한 장소이다.

【예제】
SQL> select rowid from test;

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL> select rowid from test
  2  where rowidtochar(rowid) like '%AABAA%';

ROWID
------------------
AAAHbHAABAAAMXCAAA

SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)
  2  from test;

LENGTHB(ROWIDTONCHAR(ROWID)) ROWIDTONCHAR(ROWID
---------------------------- ------------------
                          36 AAAHbHAABAAAMXCAAA

여기서 rowid의 의미는 다음과 같다.
AAAHbH  AAB  AAAMXC  AAA 객체번호  테이블스페이스번호  블록번호  행번호

 

8-2) ROWNUM 컬럼
--------------------------------------------------------------------------------

오라클에서 내부적으로 사용되는 컬럼을 pseudocolumn이라고 하며, ROWID, ROWNUM등이 있다.
ROWNUM은 테이블에서 select 되어진 일련 번호임
【예제】
SQL> select rownum,ename from emp;
     ROWNUM ENAME
---------- ----------
         1 CLARK
         2 MILLER
         3 JONES
         4 ALLEN
         5 MARTIN
         6 CHAN
6 rows selected.
 
SQL> delete from emp where ename='JONES';
1 row deleted.
 
SQL> select rownum,ename from emp;
    ROWNUM ENAME
---------- ----------
         1 CLARK
         2 MILLER
         3 ALLEN
         4 MARTIN
         5 CHAN