티스토리 뷰

Oracle/Oracle-SQL

Oracle 기본 가이드

니플하임_ 2007.06.15 17:49
출처 : http://www.modelingworld.co.kr/

- SQL 규칙

다음 SQL 구문 작성 규칙을 준수하여 작성 하도록 한다.
1.DBMS에서 제공하는 모든 키워드와 테이블명은 대문자로 표기한다.
- 컬럼, 변수, 기타 USER-DEFINE 부분은 소문자로 표기한다.
2.테이블의 ALIAS 명을 알파벳 소문자 한자리인 a, b, c, d 순으로 부여한다.
3.줄 바꾸기는 키워드와 컬럼에서 하는 것을 원칙으로 한다.
4.SQL문의 정렬은 첫번째 컬럼의 오른쪽 끝에 맞추고, Sub-Query의 경우는 sub-query 절 내에서 정렬을 맞춘다.
5.단어와 단어 사이는 한 칸을 띄운다.
6,(COMMA) 다음에 한 칸을 띄운다.
7.산자 ( +, -, *, /, || ) 앞뒤에 한 칸을 띄운다.
8.교연산자 (=, !=, <, >, <=, >=, <>) 앞뒤에 한 칸을 띄운다.
9.역변수 명은 소문자로 (예- goosd_code), 전역변수 명은 첫 글자를 대문자로(Goods_code)부여한다.
10.DAO (Data Access Objects)의 경우 자동으로 생성되므로 일부 작성 규칙을 예외로 한다.
11. WHERE 절에 부정형 조건보다 긍정형 조건을 사용하도록 한다.
12. 조인의 연결고리가 되는 컬럼의 데이터 타입을 같게 한다.
13. OR 사용을 가급적 제한해서 사용한다.
14. 불필요한 NVL 함수는 사용하지 않도록 한다.
15. 튜닝 시점 이전까지 인덱스는 기본 인덱스(Primary key)만 존재한다
16. 어떤 컬럼 별로 최대 일련번호를 구할 경우에만 INDEX_DESC 힌트를 사용하고 튜닝 시점까지 힌트 사용을 허용하지 않는다.
17. 작성된 SELECT-SQL문에 대한 실행계획을 검증하는 절차를 거치도록 한다.


- select 예제

[예제 1]

SELECT a.prods_cd,a.prod_nm,
DECODE(b.line_cd,‘11’,‘F/F’,‘기타’)
FROM GOODS a, LINE b
WHERE a.line_cd = b.line_cd(+)
                AND a.prod_nm LIKE ‘자동차

[예제 2]

SELECT prod_cd, prod_nm
FROM GOODS
WHERE prod_cd IN (SELECT prod_cd
                                       FROM GOODS
                                       WHERE cost < 50);



- insert 예제

[예제 1]

INSERT INTO GOODS
(prod_cd, prod_nm,line_cd)
VALUES(‘8800661’,‘CAR’,’1’);

[예제 2]

INSERT INTO GOODS
(prod_cd, prod_nm)
SELECT prod_cd, prod_nm
FROM GOODS_GRP
WHERE prod_cd = ‘A12’;


- update 예제

[예제 1]

UPDATE GOODS
SET prod_nm = ‘CAR’,
line_cd = ‘11’
WHERE prod_cd = ‘88006611’;

[예제 2]

UPDATE GOODS
SET prod_nm = ‘CAR’,
line_cd = ‘11’
WHERE prod_cd IN (SELECT goods_cd
                                       FROM GOODS
                                       WHERE prod_cd = ‘A12’);

[예제 1]

DELETE GOODS
WHERE prod_cd = ‘88006611’;

[예제 2]

DELETE GOODS
WHERE prod_cd IN (SELECT prod_cd
                                       FROM GOODS
                                       WHERE prod_cd = ‘A12’)


- 타입선정

7.1 CHAR 타입
1)고정길이 문자 타입으로 최대 2000 Bytes 까지 입력 가능 (Oracle8 이상)
2)모든 데이터가 고정길이를 가지는 경우
3)모든 데이터가 일정한 길이 이상을 가지며 컬럼 길이가 짧은 경우 사용을 고려
4)컬럼의 길이가 한자리인 경우
5)가변길이로 지정할 경우 많은 체인(Chain)이 발생이 우려되는 경우 사용을 고려
6)데이터 길이의 편차가 심한 경우 사용하면 저장공간 낭비와 불필요한 공백까지 네트워크를 타고 이동 등 수행속도가 저하될 수 있음
7)ROW INSERT 시에는 컬럼이 채워지지 않으나 곧 이어 반드시 데이터가 입력되는 경우 사용 고려. CHAR 타입을 사용하더라도 저장공간을 확보해 두려면 테이블 CREATE 시 DEFAULT CONSTRAINT를 ‘ ‘(BLANK)로 지정해 두어야 함

7.2 VARCHAR2 타입
1)가변길이 문자 타입으로 최대 4000 Bytes 까지 입력 가능 (Oracle8 이상)
2)데이터가 가변길이를 가지는 경우
3)데이터 길이의 편차가 심한 경우
4)NULL로 입력되는 경우
5)적절한 PCTFREE를 부여하지 않으면 체인(Chain) 발생 가능성이 높음
6)체인을 감소하기 위해 저장공간을 확보해 두려면 테이블 CREATE 시 DEFAUTL CONSTRAINT를 원하는 만큼의 ‘ ‘(BLANK)로 지정
7)가능한 CHAR 타입 보다 VARCHAR2 타입 사용

7.3 NUMBER 타입
1)NUMBER(p,s)에서 PRECISION 의 범위는 1 ~ 38, SCALE의 범위는 ?84 ~127 까지이며, p 는 s 의 자리수를 포함한 전체 데이터의 길이이고 소수점이하의 값은 반올림되어 저장됨
2)연산이 필요한 컬럼에 사용
3)NUMBER 타입의 컬럼이 문자 값과 비교되면 문자를 숫자로 바꾸어 비교하므로 인덱스를 사용하지 않게 되므로 인덱스로 생성할 컬럼은 가능한 문자타입을 사용
4)NUMBER 타입으로 지정된 컬럼을 LIKE ‘char%로 비교하면 인덱스를 사용할 수 없음 (내부적 변형이 발생)
5)가변길이 이므로 충분하게 지정함
6)기본키(PK)에 포함되는 일련번호는 NUMBER 타입 사용하되 LIKE로 사용되지 않을 경우에만 사용함

7.4 DATE 타입
1)빈번한 날짜 연산이 필요한 컬럼으로 시간을 저장할 필요가 없을 때 사용
2)시간은 입력되지 않고 날짜만 입력되는 경우 사용 고려. 단, DATE 타입으로 지정된 컬럼을 LIKE ‘년월일%’로 비교하면 인덱스를 사용할 수 없으므로(내부적 변형이 발생) BETWEEN 을 사용해야 함.
3)반드시 인덱스를 생성해야 하며 LIKE 나 BETWEEN 등으로 자주 사용되는 컬럼으로 시간까지 관리해야 한다면 각각 컬럼으로 생성하여 일자는 문자타입을 사용하고 시간은 문자타입 또는 NUMBER 타입을 사용
4)주로 날짜 연산에 사용하거나 시간의 로깅이 필요한 경우 사용
5)다른 테이블과 조인시 연결고리로 사용된다면 비교되는 상대 컬럼과 반드시 같은 데이터 타입을 사용

7.5 문자타입의 비교
1)양쪽 모두 CHAR 타입인 경우
-.두 컬럼의 길이를 비교하여 짧은 쪽의 컬럼에 공백을 추가하여 길이를 같게 한 후 비교함
-.서로 다른 문자가 나올 때까지 비교하여 다른 값이 나오면 문자 값이 큰 컬럼이 크다고 판단하고 비교를 종료함
-.값이 같다면 길이에 상관없이 비교는 정상적으로 수행되나 뒤에 공백만 있더라도 길이가 긴 컬럼의 길이만큼 비교작업을 수행하므로 수행속도가 나빠짐
2)어느 한쪽에 VARCHAR 타입이 있는 경우
-.문자 값 비교가 먼저 수행됨
-.서로 다른 문자가 나올 때까지 비교하여 다른 값이 나오면 문자 값이 큰 컬럼이 크다고 판단하고 비교를 종료함
-.값이 같다면 길이가 짧은 컬럼의 길이만큼 비교한 후 각 컬럼의 길이를 비교하여 긴 컬럼이 크다고 판단함
-.CHAR 타입과 VARCHAR 타입 간의 비교작업은 저장된 결과는 같더라도 ‘같지 않다’라는 결과가 나오므로 CHAR 타입은 사용하되 반드시 약속이 선행된 상태에서만 사용
3)양쪽 모두 VARCHAR2 타입인 경우
-.문자 값 비교가 먼저 수행됨
-.서로 다른 문자가 나올 때까지 비교하여 다른 값이 나오면 문자 값이 큰 컬럼이 크다고 판단하고 비교를 종료함
-.값이 같다면 지정된 컬럼의 길이에 관계 없이 동일한 길이를 가지므로 같은 결과가 나옴
4)상수 값과의 비교
-.상수 값은 문자 값이지만 CHAR 인지 VARCHAR2 인지 모르므로 상수 쪽의 데이터 타입을 변수의 데이터 타입과 같아지도록 변화하여 (1)(2)(3) 중 해당하는 경우의 비교법칙이 적용됨
-.서로 비교를 해야 하는 경우(JOIN) 같은 데이터 타입으로 통일하되 VARCHAR2 타입 사용을 권고함


- 인덱스 적용원칙

인덱스가 사용되지 않는 경우는 다음과 같다.
-.비교되기 전에 인덱스 컬럼의 내부적인 변형, 외부적인 변형이 일어나는 경우 -> 비교되는 상대 컬럼(혹은 상수)의 변형하여 인덱스를 사용하게 함
-.부정형(NOT, <>)으로 조건을 기술한 경우
-.긍정형으로 바꾸어 인덱스를 사용하게 함
-.인덱스 컬럼이 NULL로 비교되는 경우
-.컬럼의 값이 NULL 인 로우는 인덱스에 저장되지 않기 때문, 결합 인덱스의 첫번째 컬럼이 아닌 값을 NULL로 비교하는 경우에는 인덱스 사용
-.옵티마이저가 필요에 따라 상기 적용원칙을 준수했음에도 불구하고 특정 인덱스의 사용을 취사 선택하는 경우

8.1 인덱스 컬럼의 변형
1)외부적인 변형
-.사용자가 인덱스를 가진 컬럼을 SQL 함수나 사용자 지정함수, 연산, 결합(||) 등으로 가공을 시킨 후 비교할 때 발생됨
2)내부적인 변형
-.서로 다른 데이터 타입을 비교하고자 할 때 DBMS 가 어느 한 쪽을 기준으로 동일한 타입이 되도록 내부적인 변형을 일으키게 되므로 자신도 모르게 사용제한이 발생된다.
-.내부적인 변형은 원하지 않은 액세스 경로를 만들게 하여 수행속도에 막대한 영향을 미치게 하므로 컬럼의 데이터 타입을 결정할 때 비교해야 하는 컬럼의 타입은 반드시 일치시켜야 한다.
3)부정형의 비교
4)부정형(Not, <>, !=)으로 조건을 기술한 경우에는 인덱스가 사용되지 않으므로 SQL 을 긍정형으로 바꾸어 인덱스를 사용하도록 유도한다
4) NULL 을 사용한 비교
-.NOT NULL
인덱스 첫번째 컬럼이 NULL로 비교되면 사용될 수 없다
-.NULL
(1)NULL 값을 가지는 로우를 찾고자 할 때 위의 NOT NULL 경우처럼 대체할 방법이 없다. 이 경우 컬럼의 값이 NULL 인 로우가 많다면 손해가 되지 않으나 적다면 손해가 크므로 NULL 인 로우가 적을 경우 컬럼의 기본값(default)을 지정해주는 것이 반드시 필요하다.
(2)이와 반대의 경우로 컬럼값을 NULL로 지정하는 것이 효율적인 경우도 있다. 상태 컬럼의 특정값 (예:E)이 넓은 분포도(예:80%)를 가진다면 ‘E’인 경우를 액세스할 때 손익분기점을 훨씬 상회하게 되므로 STATUS 가 ‘E’인 경우를 NULL 로 바꾸면 인덱스 로우수가 줄고 ‘E’인 경우를 액세스할 때 NULL 을 비교하므로 인덱스를 사용하지 않게 되어 오히려 효율적이 된다.
(3)SUM(ordqty)는 SUM(NVL(ordqty, 0))로 코딩한 것과 결과가 동일하나 불필요한 연산이 발생되어 훨씬 불리하므로 NVL 사용할 때 특히 주의해야 한다.
(4)AVG(ordqty + asnqty)는 AVG(NVL(ordqty, 0) + NVL(asnqty, 0))로 코딩한 것과 결과가 다르다.
(5)잘못된 NVL 사용은 불필요한 연산을 발생시키고 컬럼의 변형으로 인해 인덱스를 사용하지 못하는 경우가 생긴다.
(6)값이 없다는 것은 ‘확정은 되었으되 값이 없다’는 경우와 ‘아직 미확정’인 경우다.
(7)‘확정은 되었으되 값이 없다’는 경우는 반드시 디폴트 값으로 문자 타입일 때는 ‘’(Space) 또는 기타 값(‘X’,’A’등)을 필요에 따라 지정하고, 숫자 타입일 때는 ‘0’을 지정하여야 한다.
(8)‘아직 미확정’인 경우는 그대로 두기만 하면 데이터 타입에 관계없이 저절로 NULL 값으로 저장된다.
(9)NULL 값에 대한 일관성을 지키지 않을 때는 많은 부분을 복잡하게 만든다.
(사원테이블에서 현 종업원 수를 찾을 경우 퇴직일을 여러 OR조건을 사용해 비교해야 비로소 정확한 결과를 추출할 수 있음)

8.2 옵티마이저에 의한 취사 선택
1)순위(RANKING)의 차이
-.규칙기준 옵티마이저 환경에서는 좁은 분포도와 넓은 분포도의 구분을 조건 연산자의 순위에 따라 판단한다.
-.비용기준 옵티마이저인 경우에는 순위가 낮더라도 분포도가 더 좋다면 ‘=’로 사용된 인덱스가 무시되고 LIKE 로 사용된 인덱스가 사용된다.
-.옵티마이저 모드가 규칙기준이라면 나중에 인덱스로 생성된 컬럼이 사용되고 비용기준인 경우는 통계정보에 의해 계산된 비용 중에 최소비용의 경로를 택하게 되므로 둘 중에 분포도가 좁은 컬럼의 인덱스가 사용되거나 손익분기점 이상이면 전체 테이블을 스캔한다.
2)낮은 처리비용의 선택
-.비용기준 옵티마이저 환경에서는 최저의 비용을 가진 액세스를 선택하므로 주어진 조건 및 통계정보에 따라 사용되는 경우가 다양하게 나타난다.
3)힌트(HINT)에 의한 선택
-.사용자가 액세스 경로를 변경하기 위해 SQL 내에 힌트를 기술하면 옵티마이저가 액세스 경로를 결정할 때 참조
-.불필요한 힌트는 액세스 경로의 결정에 악영향을 미치는 경우도 많이 있으므로 튜닝을 목적으로 하거나 특정한 목적(인덱스를 역으로 스캔 등)을 위해 사용하는 것이 좋다.

8.3 인덱스 선정
인덱스는 테이블의 특성, 생성시키고자 하는 컬럼의 분포도, 처리범위 등을 정확히 파악하여 지정해야 한다. 특정 애플리케이션에만 영향을 미치는 것이 아니라 그 컬럼을 사용하는 모든 경우에 영향을 미치므로 막연한 추측을 통해 결정해서는 안되며 가능한 실측자료를 토대로 액세스의 빈도, 처리범위의 크기, 분포도, 테이블의 크기, 액세스 유형 등을 감안하여 종합적이고 전략적으로 결정해야 한다.
1)6 블록 이상의 테이블에 적용 (6 블록이하는 연결고리만)
2)컬럼의 분포도가 10~15% 이내인 경우 적용
3)분포도가 범위 이내더라도 절대량이 많은 경우에는 단일 테이블 클러스터링 검토
4)분포도가 범위 이상이더라도 부분범위처리를 목적으로 하는 경우에는 인덱스 적용
5)인덱스만을 사용하여 요구를 해결하고자 하는 경우는 분포도가 나쁘더라도 적용할 수 있음 (손익분기점)
6)손익분기점이란 분포도가 10~15%인 경우와 Full Scan 이 같은 경우 Full Scan 과 비교해서 낫다는 것이지 10~15%가 목표는 아님. 데이터가 아주 많은 경우 분포도가 1%일지라도 Full Scan 이 더 나을 수 있다는 것임

8.4 인덱스 컬럼의 선정
1)분포도와 손익분기점
-.컬럼의 분포도가 10~15% 이내인 경우
-.분포도= (1/컬럼값의 종류) * 100 = (컬럼값의 평균 로우수/테이블의 총 로우수)*100
2)결합 인덱스의 특징
-.col1+col2 순으로 인덱스 생성한 경우 col1 의 분포도가 넓으나 ‘=’로 사용된 col1 이 앞에 위치하고 분포도는 좁으나 ‘BETWEEN’으로 사용된 col2 가 뒤에 위치한 결합 인덱스를 생성시킨 경우
-.col2+col1 순으로 인덱스 생성한 경우 인덱스의 첫번째 컬럼이 ‘=’로 사용했다 하더라도 처리범위는 줄어들지 않는다. ‘=’ 조건은 단지 테이블을 액세스 할 것인지 말 것인지를 판단하는 역할
-.대다수의 액세스 형태를 만족하기 위해 col2+col1 순으로 결합인덱스 생성했다고 가정
-.가끔은 col2 는 LIKE 나 BETWEEN 등으로 사용되고 col1 은 ‘=’로 사용되는 경우가 있다면 ‘col1+col2’로 인덱스를 구성한 경우에 비해 불리
3)결합인덱스의 컬럼 순서 결정 방법
4)손익분기점 이하의 분포도를 가진 컬럼의 인덱스 생성
-.손익분기점( 정도의 분포도를 가진 인덱스를 만들어서는 결코 안됨
-.10~15%의 범위를 인덱스를 경유하여 액세스한 경우가 전체 테이블을 액세스하는 경우와 손익분기점이 된다.
-.분포도(분포의정도)란 단지 비율을 말하는 것이므로 중요한 것은 비율이 아니라 실제 액세스할 로우수가 얼마가 되느냐임 (100 개의 로우의 1/100 은 1 로우이지만 100 만개 로우의 1/100 은 10000 개 로우가 됨)
-.그러므로 단지 분포도만으로 판단해서는 안됨
-.아주 양호한 분포도를 가진 컬럼만 독립적인 인덱스를 생성해야 한다.
-.그렇지 못한 컬럼들은 서로 적절한 결합을 하여 서로의 단점을 보완하도록 해야 함.
5)손익분기점 이상의 분포도를 가진 컬럼의 인덱스 생성
-.어떤 컬럼의 분포도가 10~15% 이상이라면 일반적으로 인덱스가 없는 것이 유리하다
-.부분범위로 유도하기 위해 분포도가 손익분기점 이상인 경우에도 인덱스를 생성시킬 수 있으나 전체범위로 처리하는 SQL 에서는 넓은 분포도의 인덱스가 사용되지 않도록 힌트를 사용하거나 고의적으로 인덱스를 사용하지 않도록 사용제한을 시켜야 한다

8.5 인덱스 선정
인덱스는 특정 애플리케이션이나 특정 액세스를 위해 생성하는 것이 아니므로 현재 발생하고 있는 모든 액세스 형태뿐만 아니라 향후에 예상되는 액세스 형태까지 종합적으로 감안하여 선정하도록 한다.
완료 후 보유할 실제 데이터 양만큼을 가진 테이블과 현재 판단할 수 있는 가능한 최적의 인덱스를 지정해 두고 개발을 진행해야 함.
1) 해당 테이블을 액세스하는 가능한 모든 액세스 형태를 수집
-. 반복 수행되는 액세스 형태를 찾는다.( 자신의 ‘수행속도 * 반복횟수’만큼의 부하)
-. 분포도가 아주 양호한 컬럼들을 발췌하여 액세스 유형을 조사한다.
-. 자주 넓은 범위의 조건이 부여되는 경우를 찾는다.
-. 조건에 자주 사용되는 주요 컬럼들을 추출하여 액세스 유형을 조사한다.
-. 자주 결합되어 사용되는 컬럼들의 조합 및 정렬되는 순서를 조사한다.
-. 역순으로 정렬하여 추출되는 경우를 찾는다.
-. 일련번호를 부여하는 경우를 찾는다.
-. 통계자료 추출을 위한 액세스 유형을 조사한다.
2) 대상 컬럼의 선정 및 분포도 조사
-. 액세스 유형에 자주 등장하는 컬럼
-. 인덱스의 첫번째 컬럼으로 지정해야 할 컬럼
-. 수행속도에 영향을 미칠 것으로 예상되는 컬럼
3) 반복 수행되는 액세스 경로(Critical access path)의 해결
-. 항상 ‘=’로 사용되는 컬럼이 여러 개 있다면 유일성(분포도)이 좋은 컬럼을 먼저 오도록 하는 것.
4) 클러스터링 검토
5) 매우 좋은 분포도를 가진 컬럼을 독립적인 인덱스로 생성
6) 인덱스 컬럼의 조합 및 순서의 결정
7) 시험 생성 및 테스트
8) 수정이 필요한 애플리케이션 조사 및 수정
9) 일괄 적용

댓글
댓글쓰기 폼