mysql oracle 유사 명령어

-- 포트

mysql : 3306
oracle : 1521

-- DB 접속

shell > mysql --user=user_name --password=your_password db_name
shell > mysql -u user_name -p password db_name

shell > sqlplus username/password@db_name


-- DB내 존재하는 테이블 검색

mysql> show tables;
oracle> select * from tabs;


-- 결과수 제한

mysql> select * from table where ROWNUM < 10;
oracle > select * from table limit 10;


-- NULL 처리

mysql> select IFNULL(field1, 0) from table;
oracle> select NVL(field1, 0) from table;


-- 현재시간

mysql> select now() from dual;
oracle> select sysdate from dual;


-- if-else 구문

mysql> select if(field=1, 'X','Y') from table;
oracle> select decode(field,1,'X','Y') from table;

테이블 변경 중인 락 확인

-- 락 확인

SELECT CC.SID,CC.SERIAL#
     , BB.OWNER,BB.OBJECT_NAME
     , CC.MACHINE
     , CC.PROGRAM
     , CC.TERMINAL
     , DD.SPID BG_PID
     , CC.PROCESS FG_PID
  FROM V$LOCKED_OBJECT AA
     , ALL_OBJECTS BB
     , V$SESSION CC
     , V$PROCESS DD
 WHERE BB.OBJECT_ID = AA.OBJECT_ID
   AND CC.SID = AA.SESSION_ID
   AND CC.PADDR = DD.ADDR
;
-- KILL

ALTER SYSTEM KILL SESSION '921,3217';

SELECT * FROM V$SESSION WHERE SID='921';

-- 롤백시 남은 레코드및 블럭수
--USED_UBLK(사용된 언두 블럭) USED_UREC(사용된 언두 레코드)
SELECT S.SID
     , S.MACHINE
     , R.NAME ROLLNAME
     , T.USED_UBLK USED_UBLK
     , T.USED_UREC
  FROM V$SESSION S
     , V$ROLLNAME R
     , V$TRANSACTION T
 WHERE S.SADDR=T.SES_ADDR
   AND T.XIDUSN=R.USN
 --AND USED_UBLK > 10
 --AND R.NAME IN ('R18')
   AND S.SID = '921'
 ORDER BY USED_UBLK
;

오라클 테이블스페이스 확인

-- 테이블스페이스 용량 확인

SELECT A.TABLESPACE_NAME
     , ROUND(SUM(A.BYTES) / (1024 * 1024 * 1024), 2) || 'G' "전체"
     , ROUND(SUM(B.FREES) / (1024 * 1024 * 1024), 2) || 'G' "여유"
  FROM ( SELECT FILE_ID, TABLESPACE_NAME, SUM(BYTES) BYTES
            FROM DBA_DATA_FILES
          GROUP BY FILE_ID, TABLESPACE_NAME
       ) A
     , ( SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES) FREES
           FROM DBA_FREE_SPACE
          GROUP BY TABLESPACE_NAME, FILE_ID
       ) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
   AND A.FILE_ID = B.FILE_ID
--   AND A.TABLESPACE_NAME LIKE 'SP_FMC%'
 GROUP BY A.TABLESPACE_NAME;
;

-- 테이블스페이스 데이터 파일 용량 확인

SELECT A.TABLESPACE_NAME                                    "테이블스페이스명"
     , ROUND((A.BYTES - B.FREE) / (1024 * 1024 * 1024), 2)  "사용공간"
     , ROUND(B.FREE / (1024 * 1024 * 1024),2)               "여유 공간"
     , ROUND(A.BYTES / (1024 * 1024 * 1024),2)              "총크기"
     , TO_CHAR((B.FREE / A.BYTES * 100) , '999.99')||'%'    "여유공간"
  FROM ( SELECT FILE_ID,
                TABLESPACE_NAME,
                SUBSTR(FILE_NAME,1,200) FILE_NM,
                SUM(BYTES) BYTES
           FROM DBA_DATA_FILES
          GROUP BY FILE_ID,TABLESPACE_NAME,SUBSTR(FILE_NAME,1,200)
       ) A,
       ( SELECT TABLESPACE_NAME,
                FILE_ID,
                SUM(NVL(BYTES,0)) FREE
           FROM DBA_FREE_SPACE
          GROUP BY TABLESPACE_NAME,FILE_ID
       ) B
 WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
   AND A.FILE_ID = B.FILE_ID
   AND A.TABLESPACE_NAME = 'SP_AS_AORM_DB'
;

데이터타입의 중요성

오라클에서 테이블 생성시 무엇보다 중요한 부분인 데이터타입이 그 중요성만큼 깊은 고려의 대상인 아닌것 같습니다.

많은 개발자들이 그냥 NUMBER 타입을 선언하는 경우도 있고, 코드성의 숫자 형태를 무심코 VARCHAR2로 선언하는 경우도 있습니다.

이렇듯 테이블 생성시 컬럼의 데이터타입을 단순 데이터가 들어가면 된다라는 식으로 선언을 함으로써 데이터베이스 성능에 심각한 문제를 초래할 수 있는 테이블을 생성하고 있는 것을 많이 보았습니다.

데이터타입의 중요성에 앞서 VARCHAR 과 VARCHAR2의 차이점에 대해서 알아보겠습니다.

결과적으로는 VARCHA 과 VARCHAR2 는 아무런 차이가 없습니다. 모두가 4000 자까지 입력이 됩니다.

그럼 왜 오라클에서는 VARCHAR 이 있는데, VARCHAR2 를 만들었을가요? 그것은 ANSI SQL 에서 아직 VARCHAR 에 대한 정의를 내리지 못했기 때문입니다.

이말을 좀 풀어보겠습니다.

만약에 프로그램에서 4000자까지 입력을 받을 수 있는 항목이 있었습니다. 그리고 이 컬럼을 VARCHAR 로 선언을 했습니다.

그런데 ANSI SQL에서 VARCHAR 타입을 2000자 까지로 정한다라고 하면, 오라클에서 이후 버전에서는 VARCHAR 를 2000자 까지로 바꿔야 합니다.

그럼 VARCHAR 타입으로 선언을 한 테이블을 가진 프로그램이 오라클 상위버전으로 변환시 VARCHAR로 선언된 곳에서 에러가 발생한다는 것입니다.

이러한 이유로 오라클에서는 ANSI SQL에서 VARCHAR이 정해지지 전까지는 VARCHAR2를 사용하라는 겁니다.

ANSI SQL에서 VARCHAR를 정한다 하더라도 오라클에서는 VARCHAR2 에 대해서는 바꾸지 않을 것이기 때문입니다.

그럼 데이터타입에 대해서 자세히 알아보겠습니다.

간단히 숫자로 된 데이터를 VARCHAR2로 선언하는 것과 NUMBER로 선언했을 경우의 차이를 알아보겠습니다.

우선 NUMBER 타입은 아무런 값없이 그냥 NUMBER로 선언을 하면 NUMBER(22)로 선언되게 되어 있습니다.

그러므로 NUMBER 타입을 선언시 길이를 선언하는 것이 좋다고 보고, NUMBER 은 최대 38까지 선언 가능합니다.

그럼 VARCHAR2와 NUMBER 타입의 실제 길이에 대해서 알아보겠습니다.

어떤 컬럼에 데이터가 숫자의 형태로만 들어간다고 했을때 그 값이 1234567890 로 10개의 숫자 형태로 들어간다면 이 컬럼의 데이터 타입을 VARCHAR2 와 NUMBER 의 차이는 실제 데이터의 길이에서 차이가 납니다.

우선 VARCHAR2로 선언을 했을 경우 길이만큼인 10 BYTE 길이의 데이터가 들어가게 됩니다.

NUMBER로 선언을 했다면 10/2+1 인 6 BYTE 길이로 데이터가 들어갑니다.

두 타입의 차이로 4 BYTE 의 차이가 발생합니다. 이것이 별 차이가 없을 수도 있습니다.

하지만 데이터가 대용량으로 저장이 된다면, 만약 1000만건의 데이터가 저장이 된다면, 이 하나의 컬럼만으로도 40,000,000 BYTE 의 저장공간이 차이가 납니다.

다시 말해서 데이터 컬럼하나로 인해서 약 40M 의 저장공간이 차이가 난다는 것입니다.

이러한 이유로 데이터베이스의 테이블 설계시 컬럼의 데이터타입은 무엇보다 중요하다 할 수 있습니다.

그러므로 테이블 설계시 컬럼에 대한 데이터타입을 좀더 신중히 효율적으로 선언해야 합니다.

PK와 FK의 필요성

오라클 데이터베이스에서 테이블을 생성에 있어서 가장 기본적으로 고려되는 PK(Primary Key)입니다.

이 PK의 중요성 만큼 FK(Foreign Key)는 중요시 되지 않는것 같아 PK와 더불어 FK에 대해 알아 보면서 FK의 중요성에 대해서 알아 보겠습니다.

데이터베이스의 목적은 말그대로 수 많은 데이터들을 보관 및 관리하는 것입니다.

그것도 효율적이고, 성능면에서 매우 편리하며 신속한 처리를 요하는 것입니다.

이 모든것 보다 우선시 되어야 하는 것이 데이터의 무결성입니다.

아무리 많은 데이터를 아무리 신속하게 처리한다 하더라도 데이터에 결점이 있다면 데이터는 쓸모가 없을 뿐 아니라, 데이터의 결점으로 인하여 잘못된 결과를 초래할 수도 있습니다.

이러한 이유로 데이터 무결성은 매우 중요한 부분이라 하겠습니다.

이 데이터 무결성을 보장해주기 위해서 가장 기본적으로 필요한 것이 PK와 FK입니다.

우선 PK는 테이블에서 오직 한개만 존재할 수 있으며, 이 PK는 테이블에서 데이터의 유일성을 보장해 줍니다.

PK의 구성은 B*tree Index, Unique, Not Null의 구조를 가지고 있습니다.

이러한 구조이므로 테이블에서 PK를 조건으로 조회를 하면 오로지 한개의 값만 나오거나 아니면 값이 나오지 않게 되는 것입니다.

그럼 PK와 FK는 어떠한 관계를 가지고 있는 것일까요?

예를 들어보겠습니다.

학과 테이블이 있습니다. 이 테이블에는 학과코드가 PK입니다.

그리고 학생 테이블이 있습니다. 이 테이블에는 학생번호가 PK입니다.

또한 학생은 하나의 학과에 등록이 되어야 한다는 조건이 있다면, 학생테이블에 학과코드의 테이터가 있어야 하겠지요.

그럼... 만약 학생테이블의 학과코드의 값에 학과테이블의 학과코드에 없는 값이 들어간다면 어떻게 되겠습니까?

잘못된 데이터가 들어가게 되고, 이러한 데이터를 부모를 잃어버렸다라고 합니다.

그러므로 학생테이블에 데이터를 저장 또는 수정시 학과코드 데이터는 학과테이블에 있는 학과코드가 맞는지를 확인해야 합니다.

이것을 자동하여 주는 것이 FK입니다.

그래서 FK는 PK를 대상으로만 만들 수 잇습니다.

이것은 FK가 항상 PK를 참조하게 되는데, 만에 하나 FK가 유일인덱스가 아닌 데이터를 매번 조회하게 되면 성능에 심각한 문제가 발생할 수 있으므로 이러한 문제를 미연에 방지하기 위하여 기본적인 성능을 보장하여야 하는 조건을 강제로 하기 위해 PK를 대상으로만 FK를 만들수 있게 한것입니다.

그러므로 PK와 FK를 이용하여 성능도 고려하면서 데이터 무결성도 보장을 할 수 있다는 것입니다.

b*tree 인덱스의 구조 및 성능 2

전에 알아본 b*tree 인덱스(이하 인덱스)의 기본 구조는 이러합니다.

인덱스의 전체 값은 1,2,3......10000이라는 값이 저장되어 있습니다.

이러한 구조에서 ROOT BLOCK에 값이 100, 200, 300 ...... 10000이라는 100개의 값이 들어가고, 100 에는 1부터 100이 저장되어 있는 LEAF BLOCK의 주소가 연결되어 있습니다.

이러한 구조에서 0 이라는 값을 저장하게 되면 어떻게 될까요.

인덱스는 조회에서 성능향상을 달성하기 위해서 만들어진 구조입니다.

이러한 성능향상을 위해 인덱스는 정렬된 결과의 값들이 순차적으로 저장이 되어 있죠.

이 말은 조회시 정렬을 매번 하는 것이 아니라 정렬된 값들에서 원하는 값을 조회하겠다는 것입니다.

그러한 이유로 저장시 조금의 부하가 발생한다 하더라도 저장되는 값을 정렬되는 구조로 저장을 하는 것입니다.

ROOT BLOCK에는 100, 200, 300 ...... 10000 이라는 값이 모두 저장되어 있고, LEAF BLOCK에는 1 - 100, 101 - 200, 201 - 200 ...... 9001 - 10000이라는 값이 저장되어 있는 구조에서 0 이라는 값은 어디에 저장되 될까요?

우선 ROOT BLOCK는 아무런 변화가 없습니다.

왜냐하면 0은 100 이하이기에 0 이라는 값을 찾는 첫 위치에는 변화가 있을 필요가 없기 때문입니다.

그럼 0 이라는 값을 저장하기 위해서 인덱스는 어떠한 변화를 가져올까요?

1 - 100 이 저장되어 있는 LEAF BLOCK에 변화가 발생됩니다.

1 - 100 이 저장되어 있는 LEAF BLOCK의 값이 50, 100 으로 단 두개의 값만 가지는 블럭으로 바뀌게 됩니다.

그리고 이 블럭은 LEAF BLOCK에서 BRANCH BLOCK으로 바뀌는 것입니다.

그럼 50, 100 이라는 값에는 어떠한 정보를 가리키고 있을까요?

1 - 100 이라는 값에 0 이 들어갔으므로 0 - 50, 51 - 100 의 값이 들어가게 되는 LEAF BLOCK이 2개 생성되게 됩니다.

그리고 이 2개의 BLOCK은 BRANCH BLOCK으로 바뀐 BLOCK의 50, 100 의 값에 연결이 됩니다.

그럼 인덱스 조회로 0 이라는 값을 조회하게 되면 0 은 100 이하이므로 ROOT BLOCK의 100 이 가리키고 있는 BLOCK을 조회하게 되는데, 이 BLOCK이 LEAF BLOCK이 아니고 BRANCH BLOCK으로 바뀌었으므로 50, 100 의 값에서 0 이라는 값이 50 이하이므로 50 이 가리키고 있는 LEAF BLOCK에서 0 의 값을 찾아 DATA BLOCK을 조회하게 됩니다.

이러한 구조의 인덱스를 밸런스가 깨졌다라고 합니다.

모든 LEAF BLOCK에 레벨 2의 구조를 가지고 있지만 하나의 블럭이 레벨 3의 구조를 가지고 있기 때문입니다.

이렇게 하나의 블럭만 밸런스가 틀려진다고 해서 큰 문제가 발생하는 것은 아니지만 상당히 많은 블럭이 밸런스가 깨지면 인덱스를 새로 정리해야 합니다.

이럴때 사용하는 명령어가 ALTER INDEX index_name REBUILD 입니다.

이 명령으로 인덱스의 값들을 모두 다시 정렬하여 저장하게 되므로 인덱스의 밸런스를 맞출수가 있습니다.

인덱스 관리의 기본이라고 할 수 있죠.

인덱스의 통계 생성 명령어는 아래와 같습니다.

ANALYZE INDEX index_name COMPUTE STATISTICS

위의 명령어로 인덱스의 통계를 생성한 후

SELECT * FROM USER_INDEXES 로 해당 인덱스의 구조를 알 수 있습니다.

여기까지 인덱스 구조에 대해서 알아봤습니다.

명령어 및 쿼리의 자세한 내용은 다음에 정리하겠습니다.

b*tree 인덱스의 구조 및 성능 1

b*tree 인덱스(이하 인덱스)는 데이터베이스 성능 개선에서 가장 기본적으로 고려되어야 할 사항입니다.

기본적으로 PK는 인덱스가 생성이 됩니다. 이것은 PK를 대상으로 FK를 생성하였을 경우 FK의 값을 입력시 PK에 FK의 입력될 값이 있는지를 확인하여야 하는데, 이 확인하는 작업에 대해서 최대한의 성능을 고려한것이 PK에 인덱스를 생성하는 것입니다.

그럼 인덱스의 구조에 대해서 알아 보겠습니다.

인덱스는 기본적으로 밸런스의 구조를 가지고 있습니다.

밸런스의 최상위를 ROOT BLOCK 이라 하고, 최하위를 LEAF BLOCK 이라 하며, ROOT BLOCK과 LEAF BLOCK 사이를 BRANCH BLOCK 이라 합니다.

최초 1이라는 값을 입력을 하면 ROOT BLOCK에 1이 입력이 되고, 1이 입력된 LEAF BLOCK의 주소를 지정합니다.

이후 2를 입력하면 ROOT BLOCK에 입력된 1 이 2 로 바뀐뒤 LEAF BLOCK에 2가 추가되고 ROOT BLOCK 의 2에 1,2 가 저장된 LEAF BLOCK 주소가 그대로 지정됩니다.

만약 하나의 LEAF BLOCK에 100개의 값이 들어갈 수 있다고 가정을 하면(일반적으로 그 이상이 들어갑니다.) 1부터 100을 입력하면 ROOT BLOCK에는 100 이라는 값 하나가 있고, LEAF BLOCK에 1부터 100까지가 입력이 됩니다.

그후 101을 입력을 하면 ROOT BLOCK에는 100, 101의 값이 저장되게 되면 LEAF BLOCK은 2 개가 되어 하나에는 1부터 100이 다른 하나에는 101이 저장이 되겠죠.

이런식으로 ROOT BLOCK이 다 채워질려면 100, 200, 300 ......10000 이렇게 100개의 값이 저장되고 LEAF BLOCK는 100개가 저장이 될 것입니다.

이렇게 생성된 인덱스를 이용하여 데이터 조회시 204 의 값을 조회시 ROOT BLOCK에서 204는 200보다 크고 300보다는 작으므로 300이 가리키는 LEAF BLOCK에서 204를 찾게 됩니다.

그러면 단순 비교만 하더라도. 인덱스를 FULL SCAN 했을 경우 100개의 BLOCK를 조회해야 하는데, 인덱스 구조로 조회를 하면 2개의 BLOCK가 조회하면 되므로 조회시 성능에 월등한 향상을 가져올 수 있습니다.

물론 인덱스는 데이터 저장시 어느 정도의 추가적인 작업이 필요합니다.

하지만 이러한 추가적인 작업에 비해 조회시 너무나 월등한 성능을 보여주므로 데이터베이스  성능에 있어서 인덱스를 가장 기본적으로 고려해야 한다는 것입니다.

오늘은 b*tree 인덱스의 기본적인 구조에 대해서 알아보았습니다.

다음은 밸런스가 어떻게 깨지는지에 대해서 알아보겠습니다.