오라클SQL교육강좌, 오라클 동의어(Oracle Synonym)

오라클SQL교육강좌, 오라클 동의어(Oracle Synonym)

​

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

 

오라클 동의어(Oracle Synonym)

오라클 동의어(Oracle Synonym)동의어synonym는 테이블, 뷰, 시퀀스, 프로그램 유닛(함수, 프로시저, 패키지)에 대한 별명이며 공용(public), 전용(private) 두가지 형태로 작성 가능 합니다. 공용 동의어publi

ojc.asia

 

 

동의어synonym는 테이블, 뷰, 시퀀스, 프로그램 유닛(함수, 프로시저, 패키지)에 대한 별명이며 공용(public), 전용(private) 두가지 형태로 작성 가능 합니다. 공용 동의어public synonym는 DBA 권한을 가진 사용자만이 생성 가능하고 모든 계정에서 접근 가능 하며, 전용 동의어private synonym는 동의어로 작성 될 객체에 대한 접근 권한을 얻은 사용자가 작성하는 동의어로 해당 USER에서만 사용 가능 합니다.

​

동의어는 단순한 별칭 이므로 데이터 딕셔너리data dictionary의 정의 외 다른 저장 공간이 필요하지는 않습니다.

​

동의어는 보안과 편의성 때문에 사용되는데 객체의 이름 및 소유자를 가릴 수 있습니다. 또 분산 환경에서 원격 객체에 대한 위치 투명성 제공합니다. 동의어로 만들어진 테이블의 이름을 바꾸거나 이동해야하는 경우 동의어 만 다시 정의하면되기 때문에 이 방법이 유용하며 동의어를 기반으로하는 응용 프로그램은 수정없이 계속 사용 가능 합니다.

​

​

​

동의어는 스키마 오브젝트를 직접 참조 합니다. 예를 들어 SCOTT 계정에서 EDU라는 계정의 STUDENT 테이블을 참조할 때 EDU.STUDENT 라고 써주어야 하는데 여러 번 사용되는 경우에는 이름이 길어서 불편 합니다. 이러한 경우 EDU.STUDENT 에 대한 동의어를 만들어서 사용하면 편리 합니다.

​

[기본형식]

CREATE [PUBLIC] SYNONYM [schema.] synonym_name FOR [schema.]object; ​ PUBLIC : 공용 동의어를 정의하며 생략하면 전용 동의어를 정의합니다.

​

​

실제 RABBIT 사용자를 생성하고 테이블을 만들어서 SCOTT 계정에서 동의어를 만들고 이를 딕셔너리 뷰에서 확인 후 삭제해 보겠습니다.

실습

​

실습을 위한 RABBIT 사용자 계정을 생성합니다. 오라클12C 이후 사용자 생성시 앞에 C##을 붙여야만 하는데 이전 방식과 동일한 방식으로 사용자를 생성하기 위해 세션 레벨에서 “_ORACLE_SCRIPT”=TRUE라고 설정합니다.

​

현재 세션에서 오라클 12C 이전 스크립트방식을 지원하면서 사용자를 생성하기 위해 _ORACLE_SCRIPT를 TRUE로 설정하세요.

​

ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE;

​

<실행결과>

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

​

새로운 사용자 계정을 생성 합니다.

​

ID : RABBIT, PASSWORD : RABBIT으로 사용자 계정을 생성하세요.

​

CREATE USER RABBIT IDENTIFIED BY RABBIT;

​

<실행결과>

User RABBIT이(가) 생성되었습니다.

​

오라클에 접속을 하기위한 롤role과 테이블 등을 생성할 수 있는 롤을 부여합니다. 롤은 권한privilege을 여러개 합쳐놓은 것 입니다.

​

생성한 사용자 계정에 CONNECT, RESOURCE 롤을 부여하세요.

​

GRANT CONNECT, RESOURCE TO RABBIT;

​

<실행결과>

Grant을(를) 성공했습니다.

​

CONNECT 롤에는 ALTER SESSION, CREATE SESSION, CREATE CLUSTER, CREATE SYNONYM, CREATE DATABASE LINK, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW 권한이 포함되어 있고, RESOURCE 롤에는 CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE

CREATE TABLE, CREATE TRIGGER, CREATE TYPE 권한이 포함되어 있습니다.

​

오라클에 접속을 하기위한 롤role과 테이블 등을 생성할 수 있는 롤을 부여합니다. 롤은 권한privilege을 여러개 합쳐놓은 것 입니다.

​

생성한 사용자 계정에 CONNECT, RESOURCE 롤을 부여하세요.

​

GRANT CONNECT, RESOURCE TO RABBIT;

​

<실행결과>

Grant을(를) 성공했습니다.

오라클11g 까지는 RESOURCE 롤에 UNLIMITED TABLESPACE 권한이 있어 RESOURCE 롤을 부여후 CREATE TABLE이 가능했지만 12C 이후에는 별도로 부여해야 합니다.

​

생성한 사용자 계정에 UNLIMITED TABLESPACE 권한을 부여하세요.

​

GRANT UNLIMITED TABLESPACE TO RABBIT;

​

<실행결과>

Grant을(를) 성공했습니다.

​

RABBIT 계정으로 명령프롬프트를 이용하여 접속 후 테이블을 생성하고 데이터 2건을 입력 합니다.

​

생성한 사용자 계정에서 테이블 및 데이터를 생성 하세요.

​

​

SQL Developer의 SCOTT 계정에서 RABBIT 계정의 CUSTOMER 테이블을 SELECT 합니다. SCOTT 계정은 DBA 롤을 부여받은 계정이므로 SELECT 되지만 일반 다른 사용자에서는 CUSTOMER 테이블에 대한 SELECT 권한을 부여 받아야 합니다.

​

RABBIT 계정의 CUSTOMER 테이블을 SELECT 하세요.

​

SELECT * FROM RABBIT.CUSTOMER;

​

<실행결과>

	ID	NAME
1	1	1길동
2	2	2길동

​

RABBIT.CUSTOMER에 대한 공용 동의어를 생성합니다. 공용 동의어는 모든 사용자 계정에서 접근 가능 합니다.

​

RABBIT.CUSTOMER에 대한 동의어 RC를 생성 합니다.

​

CREATE PUBLIC SYNONYM RC FOR RABBIT.CUSTOMER;

​

<실행결과>

SYNONYM RC이(가) 생성되었습니다.

​

동의어를 통해 데이터를 조회 합니다.

​

동의어 RC를 SELECT 합니다.

​

SELECT * FROM RC;

​

<실행결과>

	ID	NAME
1	1	1길동
2	2	2길동

​

생성한 동의어를 딕셔너리 뷰에서 조회 합니다.

​

동의어 RC를 SELECT 합니다.

​

SELECT OWNER, SYNONYM_NAME, TABLE_OWNER

FROM DBA_SYNONYMS

WHERE SYNONYM_NAME = 'RC' ;

#오라클교육, #ORACLE교육, #오라클동의어, #동의어, #ORACLE, #synonym, #OracleSynonym

​

<실행결과>

	OWNER	SYNONYM_NAME	TABLE_OWNER
1	PUBLIC	RC	RABBIT

 

​

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

오라클 함수기반 인덱스(Function Based Indexes)

 

오라클 함수기반 인덱스(Function Based Indexes)

EMP 테이블의 ename 컬럼에 인덱스가 생성되어 있을 때 다음과 같은 경우 WHERE 절에 SQL함수(SQL Function)가 사용되어 경우 인덱스 컬럼의 원래 모습에 변형이 생겼다고 이야기 하고 이 경우 원래의 ename 인덱스를 경유하지 못하고 원본 데이터에서 전체 테이블을 FULL SCAN 하면서 일일이 원하는 데이터를 찾게 됩니다.

SELECT EMPNO, ENAME FROM EMP WHERE SUBSTR(ENAME, 0, 1) = 'S';

<실행결과>

	EMPNO	ENAME
1	7788	SCOTT
2	7369	SMITH

실습용 테이블 EMP에는 고작 14건의 데이터 밖에 없어서 데이터를 추출하기 위해 어떤 경로를 경유하는지 별 의미는 없지만 대용량의 테이블에서는 상황이 달라 집니다. 쿼리하나 잘못 사용하면 전체 시스템이 마비 될 수도 있으니 말입니다. 

위 쿼리문의 실행계획을 살펴보면 전체  EMP 테이블의 레코드를 하나씩 처음부터 읽으면서 조건에 맞는 데이터를 찾고 있는것을 확인 할 수 있습니다. 대용량 테이블에서는 이런식으로 데이터를 추출하면 2박3일이 걸릴수도 있다는 점 명심하세요. SQL 튜닝의 대부분은 인덱스를 잘 다루는 것임에 명심하시고 SQL을 처음 배울 때 부터 이런 부분에 대해 신경을 쓴다면 멋진 SQL 개발자가 될 것 입니다. 

실행계획의 단계 단계를 STEP 이라고 하며 각 STEP별로 그 단계에서 어떤 오퍼레이션이 어떻게 수행되었고, 단계에서 반환 할 예상 행의 수(CARDINALITY),이를 위해 얼마만큼의 비용(COST)이 들었는지가 표시됩니다. 더 높은 카디널리티는 더 많은 행을 가져올 것이고 더 많은 작업을 하므로 쿼리가 더 오래 걸리고 따라서 대체로 비용(COST)은 더 높습니다 .

OPTIONS의 FULL은 EMP 테이블을 전체 처음부터 끝까지 한 행씩 읽었다는 뜻 입니다. 이를 FULL TABLE SCAN 이라고 합니다. 대용량 테이블인 경우 이런 FULL SCAN은 대체로 성능상 좋지 않습니다. 아래 테이블 레벨의 Filter Predicates는 한 행씩 데이터를 읽으면서 ename의 첫번째가 ‘S’ 인지를 검사하는 필터링 조건 입니다. 현재 EMP 테이블은 14건 밖에 없어서 성능 차이를 못느끼지만 시스템에 따라 차이가 있지만  데이터가 수천만건, 수억건 이상인 경우 위 쿼리문은 악성 쿼리가 될 확률이 많습니다.

위 쿼리문을 수정하지 않고 쿼리 성능을 개선할 방법은 없을까요?

당연히 있습니다. 바로 이 장에서 학습할 함수기반 인덱스function based indexes 입니다. 함수기반 인덱스는 오라클 8i 이후 도입되었으며 SQL 문장의 WHERE 절에 SQL함수(SQL Function)가 사용되는 경우 인덱스 컬럼에 변형이 생겨 인덱스를 사용하지 못하게 되는데 이를 해결하고자 함수 결과 자체를 통으로 인덱스를 만든 것 입니다. 그래서 함수결과가 어떠한 것을 찾을 때 인덱스를 이용하게 되고 성능상 이점이 있는 것 입니다.

함수기반 인덱스는 칼럼에 대해 SQL함수, 표현식 등에 인덱스를 생성하며 내부적으로 비트맵 인덱스bitmap indexes로 생성됩니다.

작성방법은 기본 인덱스 생성 방법과 동일하며, 단지 테이블명 다음에 함수나 표현식이 들어 옵니다.

[기본형식]

CREATE INDEX index_name ON table_name (function or expression)

앞에서 작성한 쿼리문을 변경하지 않고 함수기반 인덱스를 이용하여 다시 작성하는 실습을 해보고 쿼리문을 변경하여 보다 효율적인 쿼리문을 만들어 보겠습니다. 또한 생성한 함수기반 인덱스를 딕셔너리 뷰에서 확인해 보겠습니다.

실습

함수기반 인덱스를 생성 합니다.

SUBSTR(ENAME, 0, 1)  구문에 대해 함수기반 인덱스를 생성하세요.

CREATE INDEX IDX_SUBSTR_ENAME ON EMP(SUBSTR(ENAME, 0, 1) );

[실행결과]

Index IDX_SUBSTR_ENAME이(가) 생성되었습니다.

앞에서 작성한 SELECT 쿼리문을 다시 실행하고 실행계획을 확인하여 보겠습니다.

SUBSTR(ENAME, 0, 1)  구문에 대해 함수기반 인덱스를 생성하세요.

SELECT EMPNO, ENAME FROM EMP WHERE SUBSTR(ENAME, 0, 1) = 'S';

[실행결과]

	EMPNO	ENAME
1	7788	SCOTT
2	7369	SMITH

[실행계획]

실행계획이 변경 되었습니다. 실행계획을 읽는 방법은 가장 먼저 어디부터 읽어야 할지를 찾아야 하는데, 위에서 아래로 오면서 오른쪽으로 가장 많이 들여쓰기 된 부분부터 한단계씩 상위 단계로 읽는 것입니다. 동일한 들여쓰기가 되어 있는 단계가 있다면  위 단계부터 읽으면 됩니다.

 위 실행 계획의 단계는 TABLE ACCESS, INDEX 두개의 오퍼레이션을 가지는 2가지 단계 입니다. INDEX가 오른쪽으로 가장 많이 들여쓰기 되어 있으므로 먼저 해석을 합니다. 

1. IDX_SUBSTR_ENAME 인덱스를 읽어 조건 SUBSTR(ENAME, 0, 1) =’S’을 만족하는 행에 처음 ‘S’가 나오는 행에 RANDOM ACCESS 하여 인덱스의 일정 범위를 읽는 부분 범위 스캔(RANGE SCAN)을 하여 ‘S’인 레코드를 찾습니다.

2. SELECT 리스트에 empno, ename이 있으므로 이를 추출하기 위해 인덱스에서 찾은 데이터의 ROWID를 이용하여 EMP 원본 테이블에 접근하여 empno, ename을 추출 합니다.

다음 그림을 참조하세요.

[그림 12.2 함수기반 인덱스]

실습

앞에서 WHERE절에 SUBSTR(ENAME, 0, 1)을 사용한 쿼리문을 변형하여 함수기반 인덱스를 사용하지 않고도 ename 컬럼의 인덱스를 이용하는 효율적인 쿼리문으로 바꾸어 보겠습니다. 

SUBSTR(ENAME, 0, 1)을 작성한 쿼리문을 LIKE구를 이용하여 다시 작성하세요.

SELECT EMPNO, ENAME FROM EMP WHERE ENAME LIKE 'S%';   

[실행결과]

	EMPNO	ENAME
1	7788	SCOTT
2	7369	SMITH

[실행계획]

ename 컬럼에 생성된 인덱스 idx_emp_ename 인덱스를 경유하여 데이터를 추출했습니다.

WHERE절의 컬럼에 함수를 사용하게 되면 인덱스를 사용하지 못한다는 것을 반드시 명심해 주세요. 컬럼에 변형을 가하기 보다는 컬럼 오른쪽에 문자나 숫자 상수가 있다면 거기에 함수를 사용해야 합니다. 또한 위 쿼리문처럼 LIKE등을 이용하여 컬럼에 함수를 사용하지 않을 수도 있습니다.

실행계획에서 Access Predicates는 인덱스에 접근(RANDOM ACCESS)할 때 ename이 ‘S’로 시작하는 첫번째 것을 찾아서 접근했다는 것인데, 인덱스에서 노드를 탐색하는 시작 및 중지 조건을 나타 냅니다.  Filter Predicates는 인덱스에서 데이터 순회중에만 적용되며, 처음 읽은 건 다음부터 한 행씩 읽으면서 이름이 ‘S’로 시작되는 것을 필터링 했다는 것을 나타냅니다.

실습

생성한 함수기반 인덱스를 딕셔너리 뷰에서 확인 합니다.

USER_INDEXES 뷰는 사용자가 생성한 인덱스 정보를 제공하는데 생성한 함수기반 인덱스는 index_type 컬럼의 값이 “FUNCTION-BASED NORMAL”로 설정되어 있습니다.

앞에서 생성한 함수기반 인덱스를 USER_INDEXES 딕셔너리 뷰에서 확인하세요.

SELECT INDEX_NAME, INDEX_TYPE FROM USER_INDEXES

WHERE INDEX_TYPE LIKE 'FUNCTION-BASED%';

[실행결과]

	INDEX_NAME	INDEX_TYPE
1	IDX_EMP_JOB_SAL	FUNCTION-BASED NORMAL
2	IDX_SUBSTR_ENAME	FUNCTION-BASED NORMAL

위에서 생성한 IDX_SUBSTR_ENAME 인덱스 이외 IDX_EMP_JON_SAL 인덱스도 조회되는 이유는 인덱스를 생성할 때 내림차순(DESC) 인덱스로 생성하면 함수기반 인덱스로 만들어 지기 때문 입니다. USER_INDEXES 뷰에서는 함수 기반 인덱스가 어떤 컬럼을 기준으로 어떤 형태로 만들어 졌는지를 확인하기는 어렵습니다.

USER_IND_COLUMNS 뷰는 사용자가 생성한 인덱스 컬럼 정보를 제공하는데 생성한 함수기반 인덱스는column_name 컬럼의 값이 “SYS_NCXXXXX$”로 설정되어 있습니다.

앞에서 생성한 함수기반 인덱스를 USER_IND_COLUMNS   딕셔너리 뷰에서 확인하세요.

SELECT INDEX_NAME, COLUMN_NAME FROM USER_IND_COLUMNS 

WHERE TABLE_NAME = 'EMP';

[실행결과]

	INDEX_NAME	COLUMN_TYPE
1	PK_EMP	EMPNO
	…...	…...
6	IDX_EMP_JOB_SAL	SYS_NC00009$
7	IDX_SUBSTR_ENAME	SYS_NC00010$

실습하는 독자들 마다 인덱스 생성한 것에 따라 건수는 다를수 있지만 함수기반 인덱스는 SYS_NCXXXXX$ 형태로 되어 있는 것을 확인할 수 있습니다. USER_IND_COLUMNS 뷰에서도 함수 기반 인덱스가 어떤 컬럼을 기준으로 어떤 형태로 만들어 졌는지를 확인하기는 어렵습니다.

USER_IND_EXPRESSIONS 뷰는 사용자가 생성한 함수기반 인덱스 정보를 제공 합니다. 함수기반 인덱스에 관한 정보를 확인하기 위해서는 USER_IND_EXPRESSIONS 뷰를 조회하는 것이 효율적 입니다.

앞에서 생성한 함수기반 인덱스를 USER_IND_EXPRESSIONS 딕셔너리 뷰에서 확인하세요.

SELECT INDEX_NAME, COLUMN_EXPRESSION, COLUMN_POSITION

FROM    USER_IND_EXPRESSIONS 

WHERE TABLE_NAME = 'EMP';

[실행결과]

	INDEX_NAME	COLUMN_EXPRESSION	COLUMN_POSITION
1	IDX_EMP_JOB_SAL	"SAL"	2
2	IDX_SUBSTR_ENAME	SUBSTR("ENAME",0,1)	1

IDX_EMP_JOB_SAL 인덱스는 job 오름차순, sal 내림차순으로 생성된 복합 인덱스이며 내림차순 인덱스인 경우 함수기반 인덱스로 생성 됩니다. IDX_SUBSTR_ENAME 인덱스의 생성 형태는 column_expression를 확인하면 확인하면 됩니다. column_position은 0부터 시작되며  EMP 테이블은 empno, ename, sal 컬럼 순 입니다.

 

#함수기반인덱스, #인덱스, #오라클인덱스, #functionbasedindex, #ORACLE, #ORACLE교육, #오라클강의, #오라클강좌​