DEUXIGN' [émpti-spéis]

오라클의 경우 COST BASE 인경우 통계 생성을 해주지 않을경우 실행계획에 대한 정보가 표기가 안된다.
이럴경우 등등에 쓰임.
또한 통계생성일자가 오래된경우 다시 실행해줘야 한다.

SqlLoader를 이용하면 텍스트등으로 된 데이터파일을 쉽게 처리할 수 있습니다.
해당 파일의 데이터를 읽어서 지정된 테이블에 Insert를 하게됩니다.
External Table은 지정된 파일을 테이블을 통해 읽어들이는 것이기에 SqlLoader와는 다름니다.

제어, 데이터, 로그, 오류, 폐기 파일들이 필요하지만 모두가 필수는 아닙니다. 기본적으로 제어와 데이터 파일만 있으면 실행이 가능합니다. 필요에 따라 로그와 오류정보 파일은 생성해주면 됩니다.

데이터파일은 일정한 패턴에 맞게 생성되었다라고 가정합니다.
- 데이터 파일

제어 파일은 데이터파일을 읽어들여서 Insert 처리를 합니다. 확장자는 .ctl로 작성합니다.
- 제어 파일

빨간글씨의 데이터경로는 해당 데이터파일의 위치를 입력해주고, 데이터구분값은 위에 데이터파일에서 ^로 구분하였기때문에 ^를 넣어줍니다.

파란글씨의 Append 부분은 다른 설정값을 사용할 수 있습니다.

Replace와 Truncate가 비슷하고, Append와 Insert가 비슷합니다.


실행방법은 직접 입력하거나 Shell에서 실행처리될 파일(sh)을 작성하여 처리합니다.

- 실행구문

데이터파일을 data=데이터파일경로 로 설정해줘도 되지만 제어파일에서 지정해주었기때문에 생략.

실행시킨후에 로그파일을 확인하여 그결과를 알아보면 됩니다.

현재 실행되고 있는 프로세스 목록들을 볼때 사용.

/usr/local/mysql/bin/mysqladmin -u root -p root비번 processlist;

오라클 External 테이블 사용에 대한 예제
External 테이블은 파일데이터를 특정한 경로에 위치시켜놓고 SELECT 할 수 있는 테이블

샘플데이터 (sample.txt)
--------------------------------------------------------
홍길동^천재^2008/01/01 18:35:20
구마적^건달^2008/01/04 22:05:15
--------------------------------------------------------

Oracle에서 디렉토리 설정을 해서 dir_external로 Alias를 지정한다.
DB서버의 /home/test/ 를 dir_external로 지정했다면 sample.txt 파일을 이곳에 위치시킨다.

일단 첫번째 준비 끝.



빨간글자 부분만 주의깊게 보면 된다. mysql load data infile을 해보셨으면 바로 이해가 가능할겁니다.
ext_table은 생성될 테이블 명, dir_external을 서버내 디렉토리 경로를 가르키고, bad나 log파일은 bad가 생겼을 경우 생기는 로그파일이다. 날짜의 경우 해당 파일에 저장되어 있는 날짜 포맷으로 mask 해주어야 한다.

위의 External 생성을 실행한 후 해당 테이블을 SELECT 해본다.
그러면 bad가 있을경우 bad가 발생하게된 데이터파일이 생성되고, log파일도 생성되므로 서버의 파일을 확인해본다.


주의.
External 테이블은 파일내용을 그대로 읽는 것이기 때문에 UPDATE, INSERT, DELETE가 되지 않는다. INDEX도 걸릴수가 없다.
따라서 생성후에 데이터를 다시 똑같은 구조의 테이블을 생성해서 INSERT INTO SELECT해주는게 바람직하다.

- 형식
mysql>
mysql>load data local infile '파일경로' into table insert할 테이블명 fields terminated by '\t';

\t는 탭으로 구분된 것을 의미

- 설명
텍스트 파일등에 탭으로 구분된 데이터가 있으면 순차적으로 읽어들여서 해당 테이블에 인서트한다.

사용은 아래처럼 합니다.
   
EXPLAIN tbl_name  OR  EXPLAIN SELECT select_options

tbl_name은 테이블의 구조를 나타냅니다.

EXPLAIN SELECT select_options

EXPLAIN SELECT select_options 은 SQL의 실행계획을 표시합니다.
결과에 나오는 항목으로는
table : 테이블 명
type : 조인의 타입
    - system : 테이블이 하나의 레코드만 가지는 경우
    - const : 테이블에 조건을 만족하는 레코드가 하나일 때, 상수 취급
    - eq_ref : 인덱스가 UNIQUE이거나 PRIMARY KEY인 경우의 조인으로 const를 제외한 조인 중 가장 좋은 형태
    - ref : eq_ref와 다른 점은 UNIQUE가 아닐 경우 사용한다는 것.
    - range : 조건에 레코드의 범위가 주어진 조인.
    - index : all 형태와 비슷하며, 인덱스를 사용한다.
    - all : 모든 레코드를 스캔한다.
possible_keys : 테이블에서 사용 할 수 있는 키(인덱스)
key : possible_keys에 나온 키 중에서 실제로 사용한 키
key_len : 키의 길이
ref : 인덱스를 사용할 때 어떤 컬럼과 조건이 맞아야 하는지
rows : 조건을 만족하는 레코드를 찾기 위해 몇개의 레코드를 검사해야 하는지
Extra : 부가적인 정보
    - distinct : 조건을 만족하는 레코드를 찾았을 때 같은 조건을 만족하는 또 다른 레코드가 있는지 검사하지 않음.
    - not exist : left join 조건을 만족하는 하나의 레코드를 찾았을 때 다른 레코드의 조합은 더 이상 검사하지 않는다.
    - range checked for each record : 최적의 인덱스가 없는 차선의 인덱스를 사용한다는 의미.
    - using filesort : mysql이 정렬을 빠르게 하기 위해 부가적인 일을 한다.
    - unsing index : select 할때 인덱스 파일만 사용
    - using temporary : 임시 테이블을 사용한다. order by 나 group by 할때 주로 사용
    - where used : 조건을 사용한다는 의미.

ACCESS	ADD	ALL
ALTER	AND	ANY
AS	ASC	AUDIT
BETWEEN	BY	CHAR
CHECK	CLUSTER	CLOUMN
COMMENT	COMPRESS	CONNECT
CREATE	CURRENT	DATE
DECIMAL	DEFAULT	DELETE
DESC	DISTINCT	DROP
ELSE	EXCLUSIVE	EXISTS
FILE	FLOAT	FOR
FROM	GRANT	GROUP
HAVING	IDENTIFIED	IMMEDIATE
IN	INCREMENT	INDEX
INTIAL	INSERT	INTEGER
INTERSECT	INTO	IS
LEVEL	LIKE	LOCK
LONG	MAXEXTENTS	MINUS
MODE	MODIFY	NOAUDIT
NOCOMPRESS	NOT	NOWAIT
NULL	NUMBER	OF
OFFLINE	ON	OPTION
OR	ORDER	PCTREE
PRIOR	PRIVILEGES	PUBLIC
RAW	RENAME	RESOURCE
REVOKE	ROW	ROWID
ROWNUM	ROWS	SELECT
SESSION	SET	SHARE
SIZE	SMALLINT	START
SUCESSFUL	SYNONYM	SYSDATE
TABLE	THEN	TO
TRIGGER	UID	UNION
UNIQUE	UPDATE	USER
VALIDATE	VALUES	VARCHAR
VARCHAR2	VIEW	WHENEVER
WHERE	WITH

오라클 예약어 정리

ACCESS : 데이터 베이스 오브젝트를 사용자가 사용할 수 있게 만든다.
ADD : SQL문에서 열이나 Integrity constraint를 추가하는데 사용된다.
ALL : 연산자를 지정하거나 함수그룹이 지정된 선택세트에서 모든 값들을 참작하도록 한다
.
ALTER : 많은 문장에서 그 문장을 위한 현재값을 재정의 하는데 사용된다.
AND : 논리 연산자.
ANY : 논리 연산자.
AS : 서브쿼리에 의하여 리턴된 행을 테이블 제작시에 테이블에 삽입하기 위하여 Create 명
령과 함께 사용되는 절.

ASC : 오름차순 리스트를 만드는데 예를 들면, 인텍스와 함께 사용된다.
AUDIT : 지정된 명령문이나 오브젝트를 회계할 수 있게 해준다.
BETWEEN : 비교 연산자.
CHAR : 고정된 길이의 CHARACTER 데이터 타입.
CHECK : 명시적으로 조건을 정의하는 제약.
CLUSTER : 그 행에 있는 데이터가 전형적으로 동시에 엑세스되는 일반적인 정보를 포함하
고 있을 때 복수의 테이블로부터 함께 데이터베이스 데이터를 저장하는 수단.
COLUMN : 이름과 특정 데이터 타입을 포함하는 테이블의 세부구분.
COMMENT : 테이블이나 열에 대하여 데이터 디렉토리안에 코멘트절을 삽입하는 명령어.
CONNECT : 현재의 변경을 행하고, 사용자를 오라클로 부터 log off 하고 특정 사용자로서
오라클에 log on 한다.
CREATE : 특정 데이터ㅔ이스 오브젝트를 만드는데 사용된다.
CURRENT : 최근 불러들인 행을 지정하는데 커서와 함께 사용된다.
DATE : 고정된 길이의 데이터와 시간 데이터인 DATA 형의 데이터 타입.
DECIMAL : 고정된 소수점 수만 지정하는 DECIMAL 데이터 타입.
DEFAULT : 다른 대안이 지정되지 않은 경우 사용되는 절이나 옵션값.
DELETE : 테이블로부터, 또는 뷰의 베이스 테이블로부터 행을 제거하는 명령.
DESC : 내림차순 리스트를 만든다. 이 예약어의 전형적인 애플리케이션은 인덱스와 함께 한
다.
DISTINCT : 명령문의 일부로서 독특함을 나타낸다.
DROP : 데이터베이스로부터 오브젝트를 완전히 제거 또는 삭제한다.
ELSE : TRUE로 평가될 수도 있는 2개의 명령문을 평가하는 명령.
EXCLUSIVE : 데이터 베이스를 독점 모드로 마운트하는데 사용된다. 말하자면, 그 데이터베
이스가 한 번에 하나씩의 예가 마운트됨을 의미한다.
EXITS : WHERE 절에서 그 다음에 나오는 서브쿼리가 적어도 한 행을 리턴할 경우 TRUE를 리턴하는 연산자.
FILE : 모든 데이터베이스 데이터를 저장하는데 사용되는 저장 구역.
FLOAT : FLOAT 데이터 타입.
FOR : 반복 논리에서 명령문이 실행되는 회수를 제한하는데 사용된다.
GRANT : 사용자와 역할에 허가를 적용하는데 사용된다.
HAVING : 지정된 조건이 TRUE인 그룹에 리턴되는 행의 그룹을 제한한다.
IMMEDIATE : 테이블 공간 파일을 이용할 수 있음을 보장하지 않으며 체크포인트를 수행하
지 않는 alter tablespace에서 사용된다.
IN : WHERE 절에서 사용되는 논리 연산자.
INCREMENT : 시퀀스 번호간의 간격을 나타내기 위하여 시퀀스를 만드는데 사용된다.
IDENTIFIED : 사용자를 변경하는데 사용되며, 오라클이 사용자 액세스를 허용하는 방법을
나타낸다.
INDEX : 엑세스 속도를 개선하는데 사용되는 데이터베이스 오브젝트.
INITIAL : 처음 저장공간 요구를 지정하는 명령어.
INSERT : 테이블 또는 뷰의 베이스 테이블에 행을 추가하느데 사용된다.
INTEGER : INTEGER 데이터 타입.
INTERSECT : 2개의 서로 다른 행 세트 간에 공통된 행을 리턴하는 세트 연산자.
INTO : 동작을 수령하는 테이블이나 오브젝트를 지정한다.
IS : 값의 실제를 테스트하기 위하여 NULL 연산자와 함께 사용된다.
LEVEL : 유사열으로서 루트 노드는 1, 차일드 노드는 2, 차일드의 차일드 노드는 3이다.
LIKE : 패턴 매칭과 함께 스트링 비교를 수행하는데 사용된다.
LOCK : 특정 오브젝트에 대한 사용자의 엑세스를 제한하는데 사용되는 오라클 메커니즘.
LONG : 최대 길이 2기가 바이트의 변수 길이 문자 데이터인 LONG 데이터 타입.
MAXEXTENTS : 특정 오브젝트를 위하여 할당될 수 있는 전체 확장자 수.
MINUS : 둘째 쿼리에는 없고 첫째 쿼리에 있는 구별되는 모든 행을 리턴하는 세트 연산자.

MODE : 단일 모드나 복수 프로세서 모드로서 프로세스를 설정한다.
MODIFY : 기존의 테이블 열의 정의를 수정한다.
NOAUDIT : 특정 명령문이나 오브젝트에 대한 오디팅 활동을 중단한다.
NULL : 논리 연산자의 효과를 앞에서 부정한다.
NUMBER : 길이가 다양하고 정밀도를 가진 NUMBER 데이터 타입.
OF : 평가할 아이템 리스트 앞에 온다.
OFFLINE : 특정 오브젝트를 오프라인에서 취하며 그에 대한 더 이상의 엑세스를 방지한다.

ON : 명령문이 동작할 오브젝트를 나타낸다.
ONLINE : 특정 오브젝트를 온라인으로 취하여 사용자가 엑세스할 수 있도록 한다.
OPTION : 명령문을 위하여 선택해야 할 선택의 리스트를 사용자에게 제공한다.
OR : 표현식을 TURE나 FALSE 결과와 조합하는 연산자.
ORDER : 수 리스트가 요청된 순서대로 만들어지도록 한다.
PCTFREE : 오브젝트에 대한 미래의 갱신을 위하여 예비된 각 오브젝트에 있어서 공백의 백
분율을 지정하는 명령어.
PRIOR : 계층에서 현재 행의 패어런트 행을 위하여 지정된 표현식을 평가하는 연산자.
PRIVILEGE : 어떤 동작을 수행하도록 사용자에게 주어진 허가.
PUBLIC : 그 데이터베이스의 모든 사용자가 엑세스를 갖게 되는 데이터베이스 그룹 또는 동
의어등과 같이 모든 사용자가 이용할 수 있는 데이터베이스 아이템을 의미할 수도 있다.
RAW : RAW 데이터 타입, 2진 포맷이며 직접 해석되지 않는다.
RENAME : 오브젝트의 이름을 새로운 이름으로 바꾼다.
RESOURCE : 데이터베이스 오브젝트나 물리적 장치를 가리키는 총칭
REVOKE : 사용자와 역할로부터 허가를 철회한다.
ROW : 단일 기록에 응답하는 테이블 열으로부터의 정보의 수집.
ROWID : 테이블에 있어서 행을 위한 논리적 주소를 나타내는 유사 열.
ROWLABLE : 모든 테이블에서 Trusted Oracle에 의하여 자동적으로 만들어진 열.
ROWNUM : 한 세트의 연결된 행의 테이블로부터 오라클이 행을 선택하는 순서를 나타내는
수를 포함하는 유사열.
ROWS : 테이블에서 하나 이상의 데이터 행.
SELECT : 하나 이상의 데이터베이스 오브젝트로 부터 데이터를 검색하는 SQL 명령.
SESSION : 사용자에 의한 데이터베이스와의 특정한 연결.
SET : 지정된 이이템을 유효화하는데 사용된다.
SHARE : 오브젝트나 자원의 공유를 허가한다.
SIZE : 자원의 크기를 바이트수로 지정한다.
SMALLINT : 오라클의 데이터 타입 번호를 위한 DB2로부터 호환가능한 데이터 타입.
START : 데이터베이스를 출발하는 것과 같은 동작을 초기화하느데 사용된다.
SYNONYM : 테이블이나 뷰를 참조하는데 사용될 수도 있는 테이블이나 뷰를 위한 별명.
SYSDATE : 시스템의 날짜와 시간.
TABLE : 오라클 데이터베이스에서 저장의 기초 단위, 행과 열로 구성된다.
THEN : 선행 조건을 그 뒤에 나오는 명령문과 결합시킨다.
TO : 어떤 동작이 발생할 아이템을 확인한다.
TRIGGER : 테이블과 결합된 저장 프로시저이며 지정된 이벤트에서 자동으로 실행된다.
UID : 각 사용자에게 할당된 고유 번호를 포함하는 유사열.
UNION : 두 쿼리의 결과를 조합하는 SQL 함수.
UNIQUE : 다른 유사한 아이템들에 반하여 어떤 아이템이 고유성을 갖도록 지정한다.
UPDATE : 테이블에서 또는 뷰의 베이스 테이블에서 값들을 변경하는 명령.
USER : 사용자의 로그인 사용자명을 포함하는 유사열.
VALUES : 해당 열에 값 리스트를 할당한다.
VARCHAR : 다양한 길이로 최대 200바이트의 길이를 갖는 VARCHAR 데이터 타입.
VARCHAR2 : 다양한 길이로 최소 200바이트의 길이를 갖는 VARCHAR2 데이터 타입.
오라클의 최근 릴리즈(7.2)에서 이 데이터 타입은 VARCHAR와 같은 의미이다.
VIEW : 일종의 데이터베이스 오브젝트이며 하나 이상의 테이블의 논리의 표현이다.
WHENEVER : audit 명령과 함께 사용되며 일련의 명령문에서 오디팅이 일치되도록 만든다.

WHERE : 명령문에서 사용되며 어떤 행이 그 명령에 영향을 받는지를 제한하게 지정하는 명
령이다.
WITH : 그 명령문에 포함할 보충이되는 아이템을 열거할 때 사용하는 명령.

ADD	ALL	ALTER
ANALYZE	AND	AS
ASC	ASENSITIVE	BEFORE
BETWEEN	BIGINT	BINARY
BLOB	BOTH	BY
CALL	CASCADE	CASE
CHANGE	CHAR	CHARACTER
CHECK	COLLATE	COLUMN
CONDITION	CONNECTION	CONSTRAINT
CONTINUE	CONVERT	CREATE
CROSS	CURRENT_DATE	CURRENT_TIME
CURRENT_TIMESTAMP	CURRENT_USER	CURSOR
DATABASE	DATABASES	DAY_HOUR
DAY_MICROSECOND	DAY_MINUTE	DAY_SECOND
DEC	DECIMAL	DECLARE
DEFAULT	DELAYED	DELETE
DESC	DESCRIBE	DETERMINISTIC
DISTINCT	DISTINCTROW	DIV
DOUBLE	DROP	DUAL
EACH	ELSE	ELSEIF
ENCLOSED	ESCAPED	EXISTS
EXIT	EXPLAIN	FALSE
FETCH	FLOAT	FLOAT4
FLOAT8	FOR	FORCE
FOREIGN	FROM	FULLTEXT
GRANT	GROUP	HAVING
HIGH_PRIORITY	HOUR_MICROSECOND	HOUR_MINUTE
HOUR_SECOND	IF	IGNORE
IN	INDEX	INFILE
INNER	INOUT	INSENSITIVE
INSERT	INT	INT1
INT2	INT3	INT4
INT8	INTEGER	INTERVAL
INTO	IS	ITERATE
JOIN	KEY	KEYS
KILL	LEADING	LEAVE
LEFT	LIKE	LIMIT
LINES	LOAD	LOCALTIME
LOCALTIMESTAMP	LOCK	LONG
LONGBLOB	LONGTEXT	LOOP
LOW_PRIORITY	MATCH	MEDIUMBLOB
MEDIUMINT	MEDIUMTEXT	MIDDLEINT
MINUTE_MICROSECOND	MINUTE_SECOND	MOD
MODIFIES	NATURAL	NOT
NO_WRITE_TO_BINLOG	NULL	NUMERIC
ON	OPTIMIZE	OPTION
OPTIONALLY	OR	ORDER
OUT	OUTER	OUTFILE
PRECISION	PRIMARY	PROCEDURE
PURGE	RAID0	READ
READS	REAL	REFERENCES
REGEXP	RELEASE	RENAME
REPEAT	REPLACE	REQUIRE
RESTRICT	RETURN	REVOKE
RIGHT	RLIKE	SCHEMA
SCHEMAS	SECOND_MICROSECOND	SELECT
SENSITIVE	SEPARATOR	SET
SHOW	SMALLINT	SONAME
SPATIAL	SPECIFIC	SQL
SQLEXCEPTION	SQLSTATE	SQLWARNING
SQL_BIG_RESULT	SQL_CALC_FOUND_ROWS	SQL_SMALL_RESULT
SSL	STARTING	STRAIGHT_JOIN
TABLE	TERMINATED	THEN
TINYBLOB	TINYINT	TINYTEXT
TO	TRAILING	TRIGGER
TRUE	UNDO	UNION
UNIQUE	UNLOCK	UNSIGNED
UPDATE	USAGE	USE
USING	UTC_DATE	UTC_TIME
UTC_TIMESTAMP	VALUES	VARBINARY
VARCHAR	VARCHARACTER	VARYING
WHEN	WHERE	WHILE
WITH	WRITE	X509
XOR	YEAR_MONTH	ZEROFILL

Table에 lock을 걸어줌으로써 테이블 작업을 하는 시간동안 Access 등을 막을 수 있기에 그 동안에 일어날 수 있는 PK 변경 등으로 인한 DB구조상의 오류를 사전에 차단할 수 있다.

먼저 테이블을 생성하면서 타입지정이 MyISAM이나 InnoDB로 되어 있어야 한다. 보통은 MyISAM이 기본이지만 고급 트랜잭션 사용을 위해서 InnoDB를 추천한다.

Lock-type으로는 READ와 WRITE가 있다.
READ는 테이블 작업을 수행하는 동안 자신이외에는 읽기만 가능하며 쓰기는 불가능하고, WRITE는 읽기, 쓰기 모두 불가능하다.

락 사용시는

SQL>Lock Tables 테이블네임 Lock-type;

락 해제시에는

SQL>Unlock Tables;


Lock Table시에 작업할 테이블 모두 lock을 걸어줘야 한다.(select, update, insert, delete에 사용되는 테이블 전부)

여러개의 테이블을 Lock 해