오라클학원, 오라클교육, 오라클 확장 가능한 시퀀스(Oracle Scalable Sequences)

 

오라클학원, 오라클교육, 오라클 확장 가능한 시퀀스(Oracle Scalable Sequences)

 

일반적으로 사용되는 시퀀스는 고유한 정수를 생성하는데 사용하는 스키마 객체schema objects입니다. PK 값을 시퀀스를 이용하여 입력을 하게되는 경우, 시퀀스의 번호는 대부분  1 씩 증가하므로 대량의 데이터가 입력된다면 입력되는 데이터는  B*Tree 인덱스의 맨 오른쪽 리프 블록에 배치되므로 이곳에서 경합이 발생하여 락lock이 발생할 수 있습니다. 오라클 8i 이후 이 문제를 해결하기 위해 역방향 키 인덱스reverse key indexes를 사용했지만 오라클 18C 이후 확장 가능한 시퀀스scalable sequence가 ​​더 나은 해결책 입니다.

http://ojc.asia/bbs/board.php?bo_table=LecOracle&wr_id=291 

오라클 확장 가능한 시퀀스(Oracle Scalable Sequences)

오라클 확장 가능한 시퀀스(Oracle Scalable Sequences)일반적으로 사용되는 시퀀스는 고유한 정수를 생성하는데 사용하는 스키마 객체schema objects입니다. PK 값을 시퀀스를 이용하여 입력을 하게되는

ojc.asia

 

확장 가능한 시퀀스는 6자리 접두사를 추가하는데  접두사는 3자리 인스턴스 offset, 3자리 세션 offset으로 구성됩니다. SQL*Plus 및 SQL Developer의 접속을 달리하면 세션이 변하므로 접두사는 변합니다.

 

[(instance id % 100) + 100] || [session id % 1000]

 

시퀀스 생성시 기본 속성은 NOSCALE이지만 원하는 경우 명시 적으로 지정할 수 있습니다.

 

시퀀스를 생성할 때 SCALE NOEXTEND를 사용하는 것과 동일한 SCALE 속성을 설정하면 MAXVALUE 값을 기반으로 최대 자릿수를 사용하도록 패딩을 사용하여 6자리 접두사가 시퀀스에 연결됩니다. 

 

다음 실습 예제에서는 시퀀스 값을 10자리 길이로 만들기 위해 0으로 만 채웁니다. 따라해 보시기 바랍니다.

실습

 

SCALABLE SEQUENCE를 생성 합니다.

 

minvalue 1, maxvalue 9999999999  값을 가지는 SEQ_EMP_EMPNO_SCALE 확장 가능한 시퀀스를 생성하세요.

 

CREATE SEQUENCE SEQ_EMP_EMPNO_SCALE 

MINVALUE 1

MAXVALUE 9999999999

SCALE;

 

<실행결과>

Sequence SEQ_EMP_EMPNO_SCALE이(가) 생성되었습니다.

 

생성한 SCALABLE SEQUENCE의 NEXTVAL, CURRVAL을 조회해 보겠습니다.

 

DUAL 테이블을 이용하여  SEQ_EMP_EMPNO_SCALE 시퀀스의 NEXTVAL 값을 조회하세요.

 

SELECT SEQ_EMP_EMPNO_SCALE.NEXTVAL AS SEQ FROM DUAL;

 

<실행결과>

	SEQ
1	1013870001

 

시퀀스의 maxvalue가 9999999999 10자리 이고 앞6자리는 접두사가 위치합니다. 나머지 4자리에 시퀀스 값들이 증가하면서 위치 합니다.

 

DUAL 테이블을 이용하여  SEQ_EMP_EMPNO_SCALE 시퀀스의 NEXTVAL 값을 조회하세요.

 

SELECT SEQ_EMP_EMPNO_SCALE.NEXTVAL AS SEQ FROM DUAL;

 

<실행결과>

	SEQ
1	1013870002

 

이전의 NEXTVAL에서 1 증가된 값 입니다.

 

DUAL 테이블을 이용하여  SEQ_EMP_EMPNO_SCALE 시퀀스의 CURRVAL 값을 조회하세요.

 

SELECT SEQ_EMP_EMPNO_SCALE.CURRVAL AS SEQ FROM DUAL;

 

<실행결과>

	SEQ
1	1013870002

 

EXTEND 키워드를 추가하면 시퀀스 번호가 최대 길이까지 0으로 채워지고 접두사 6자리가 연결되므로 최종 시퀀스 번호는 MAXVALUE 값보다  6자리 더 많습니다.

실습

 

SCALABLE SEQUENCE를 삭제 후 재생성 합니다.

 

minvalue 1, maxvalue 9999999999  값을 가지는 SEQ_EMP_EMPNO_SCALE 시퀀스를  SCALE EXTEND 옵션으로 생성하세요.(삭제 후 재생성 하세요)

 

DROP SEQUENCE SEQ_EMP_EMPNO_SCALE;

CREATE SEQUENCE SEQ_EMP_EMPNO_SCALE 

MINVALUE 1

MAXVALUE 9999999999

SCALE EXTEND;

 

<실행결과>

Sequence SEQ_EMP_EMPNO_SCALE이(가) 생성되었습니다.

 

생성한 SCALABLE SEQUENCE의 NEXTVAL, CURRVAL을 조회해 보겠습니다.

 

DUAL 테이블을 이용하여  SEQ_EMP_EMPNO_SCALE 시퀀스의 NEXTVAL 값을 조회하세요.

 

SELECT SEQ_EMP_EMPNO_SCALE.NEXTVAL AS SEQ FROM DUAL;

 

<실행결과>

	SEQ
1	1013870000000001

 

시퀀스의 maxvalue가 9999999999 인데 이자리수보다 6자리 많습니다. maxvalue 까지 10자리를 0으로 채우고 시퀀스 값들이 시작되며, 앞6자리에 추가로 접두사가 위치 합니다. 총16자리 입니다.

 

DUAL 테이블을 이용하여  SEQ_EMP_EMPNO_SCALE 시퀀스의 NEXTVAL 값을 조회하세요.

 

SELECT SEQ_EMP_EMPNO_SCALE.NEXTVAL AS SEQ FROM DUAL;

 

<실행결과>

	SEQ
1	1013870000000002

 

이 전의 NEXTVAL 값에서 1이 증가 되었습니다.

 

DUAL 테이블을 이용하여  SEQ_EMP_EMPNO_SCALE 시퀀스의 CURRVAL 값을 조회하세요.

 

SELECT SEQ_EMP_EMPNO_SCALE.CURRVAL AS SEQ FROM DUAL;

 

<실행결과>

	SEQ
1	1013870000000002

 

확장 가능한 시퀀스를 ALTER SEQUENCE 명령으로 변경해 보겠습니다.

실습

 

확장 가능한 시퀀스를 NOSCALE로 변경합니다.

 

SEQ_EMP_EMPNO_SCALE 확장 가능한 시퀀스 NOSCALE로 변경하세요.

 

ALTER SEQUENCE SEQ_EMP_EMPNO_SCALE NOSCALE;

 

<실행결과>

Sequence SEQ_EMP_EMPNO_SCALE이(가) 변경되었습니다.

 

변경한 SCALABLE SEQUENCE의 CURRVAL을 조회해 보겠습니다.

 

DUAL 테이블을 이용하여  SEQ_EMP_EMPNO_SCALE 시퀀스의 CURRVAL 값을 조회하세요.

 

SELECT SEQ_EMP_EMPNO_SCALE.CURRVAL AS SEQ FROM DUAL;

 

<실행결과>

	SEQ
1	1013870000000002

 

NOSCALE 옵션 시퀀스로 변경 되었으므로 1013870000000002 값이 16자리 시퀀스 값입니다.

 

변경한 SCALABLE SEQUENCE의 NEXTVAL을 조회해 보겠습니다.

 

DUAL 테이블을 이용하여  SEQ_EMP_EMPNO_SCALE 시퀀스의 NEXTVAL 값을 조회하세요.

 

SELECT SEQ_EMP_EMPNO_SCALE.NEXTVAL AS SEQ FROM DUAL;

 

<실행결과>

ORA-08004: 시퀀스 SEQ_EMP_EMPNO_SCALE.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다. 시퀀스의 maxvalue가 9999999999 인데 현재 시퀀스의 CURRVAL은 “1013870000000002“ 이므로 maxvalue 값을 초과하여 오류가 발생합니다.

 

시퀀스를 SCALE NOEXTEND로 변경합니다.

 

SEQ_EMP_EMPNO_SCALE 확장 가능한 시퀀스를 SCALE NOEXTEND로 변경하세요.

 

ALTER SEQUENCE SEQ_EMP_EMPNO_SCALE SCALE NOEXTEND;

 

<실행결과>

Sequence SEQ_EMP_EMPNO_SCALE이(가) 변경되었습니다.

 

원래 시퀀스 값자체가 16자리인데 SCALE NOEXTEND 옵션으로 변경되었으니 앞 부분에 6자리에 접두사가 들어갈 공간이 필요하므로, 이 시퀀스가 NEXTVAL 하기 위해서는 총22자리의 공간이 필요합니다. SCALE NOEXTEND 옵션은 SCALE 옵션과 동일 합니다.

 

변경한 SCALABLE SEQUENCE의 CURRVAL을 조회해 보겠습니다.

 

DUAL 테이블을 이용하여  SEQ_EMP_EMPNO_SCALE 시퀀스의 CURRVAL 값을 조회하세요.

 

SELECT SEQ_EMP_EMPNO_SCALE.CURRVAL AS SEQ FROM DUAL;

 

<실행결과>

	SEQ
1	1013870000000002

 

SCALE NOEXTEND 옵션 시퀀스로 변경 되었으므로 “1013870000000002” 값이 16자리 시퀀스 값입니다. NEXTVAL 하면 앞에 6자리 접두사가 붙으니 자리 수 주의해야 합니다.

 

변경한 SCALABLE SEQUENCE의 NEXTVAL을 조회해 보겠습니다.

 

DUAL 테이블을 이용하여  SEQ_EMP_EMPNO_SCALE 시퀀스의 NEXTVAL 값을 조회하세요.

 

SELECT SEQ_EMP_EMPNO_SCALE.NEXTVAL AS SEQ FROM DUAL;

 

<실행결과>

ORA-08004: 시퀀스 SEQ_EMP_EMPNO_SCALE.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다. 시퀀스의 maxvalue가 9999999999 10자리인데 현재 시퀀스의 CURRVAL은 “1013870000000002” 16자리 이고 NEXTVAL을 하면 앞에 6자리 접두사가 붙어 총 22자리가 필요하므로 maxvalue를 초과하였다는 오류가 발생합니다.

 

시퀀스의 maxvalue를 9999999999999999999999 22자리로 변경합니다. 

SEQ_EMP_EMPNO_SCALE 확장 가능한 시퀀스의 maxvlaue를 9999999999999999999999 22자리 값으로 변경하세요.

 

ALTER SEQUENCE SEQ_EMP_EMPNO_SCALE MAXVALUE 9999999999999999999999;

 

<실행결과>

Sequence SEQ_EMP_EMPNO_SCALE이(가) 변경되었습니다.

 

변경한 SCALABLE SEQUENCE의 NEXTVAL을 조회해 보겠습니다.

 

DUAL 테이블을 이용하여  SEQ_EMP_EMPNO_SCALE 시퀀스의 NEXTVAL 값을 조회하세요.

 

SELECT SEQ_EMP_EMPNO_SCALE.NEXTVAL AS SEQ FROM DUAL;

 

<실행결과>

	SEQ
1	1013871999990000000003

 

총22자리에 시퀀스 값이 할당 되었고 앞 부분 6자리는 접두사 입니다.

 

시퀀스를 SCALE EXTEND로 변경합니다.

 

SEQ_EMP_EMPNO_SCALE 확장 가능한 시퀀스를 SCALE EXTEND로 변경하세요.

 

ALTER SEQUENCE SEQ_EMP_EMPNO_SCALE SCALE EXTEND;

 

<실행결과>

Sequence SEQ_EMP_EMPNO_SCALE이(가) 변경되었습니다.

 

변경한 SCALABLE SEQUENCE의 NEXTVAL을 조회해 보겠습니다.

 

DUAL 테이블을 이용하여  SEQ_EMP_EMPNO_SCALE 시퀀스의 NEXTVAL 값을 조회하세요.

 

SELECT SEQ_EMP_EMPNO_SCALE.NEXTVAL AS SEQ FROM DUAL;

 

<실행결과>

	SEQ
1	1013870000001999990000000004

 

22자리 최대길이에 더해 앞6자리에 새로운 접두사가 붙었습니다. 전체 28자리 입니다.

 

생성한 확장 가능한 시퀀스를 딕셔너리 뷰에서 조회해 보겠습니다.

실습

 

USER_SEQUENCES 뷰는 사용자가 생성한 시퀀스의 정보를 제공하는 뷰 입니다.

 

USER_SEQUENCES 뷰에서 SEQ_EMP_EMPNO_SCALE 시퀀스의 정보를 조회하세요.

 

SELECT SEQUENCE_NAME,

       SCALE_FLAG,

       EXTEND_FLAG

FROM   USER_SEQUENCES

WHERE  SEQUENCE_NAME = 'SEQ_EMP_EMPNO_SCALE';

 

<실행결과>

	SEQUENCE_NAME	SCALE_FLAG	EXTEND_FLAG
1	SEQ_EMP_EMPNO_SCALE	Y	Y

 

SEQ_EMP_EMPNO_SCALE 시퀀스는 지전 실습에서 SCALE EXTEND로 변경을 한 상태 입니다. 그래서 SCALE_FLAG, EXTEND_FLAG 값이 ‘Y’ 입니다.

 

https://www.youtube.com/playlist?list=PLxU-iZCqT52Dlz0zGgFiq1mBz21arFUHS

오라클 SQL 기본

www.youtube.com

#오라클교육, #ORACLE교육, #오라클시퀀스, #ORACLESequence, #시퀀스, #Sequence​, #확장가능한시퀀스

오라클교육, ORACLE교육, 오라클시퀀스, ORACLESequence, 시퀀스, Sequence​, 확장가능한시퀀스