MySql - 테이블 스캔 피하기

* EXPLAIN을 실행하면 MySQL이 쿼리를 해석하기 위해 테이블을 스캔할 때 type 컬럼에 ALL을 보여줌.
이것은 일반적으로 아래 조건 아래에서 발생.
- 테이블이 너무 작아서 키 룩업 (lookup)을 실행하는 것보다 테이블 스캔을 하는 것이 더 빠름. 일반적으로 10개 미만의 짧은 길이의 행을 가진 테이블이 여기에 해당.
- 인덱스된 컬럼에 대해서 사용할 수 있는 제약 사항이 ON 또는 WHERE 구문에 존재하지 않음.
- 인덱스된 컬럼을 상수 값과 비교할 수 있고, MySQL이 테이블 대부분을 커버하고 있는 상수를 계산해서 테이블 스캔이 빠르게 진행되도록 만드는 경우.
- 다른 컬럼을 통해서 낮은 기수 (cardinality)를 가지고 있는 (많은 열이 키 값과 매치가 됨) 행을 사용할 수 있는 경우, MySQL은 많은 키 룩업 (lookup)이 진행이 되고 이에 따라서 테이블 스캔이 보다 빠를 것이라고 가정.

* 작은 테이블의 경우에는 테이블 스캔이 적절할 수도 있을 것이나 대형 테이블의 경우 옵티마이저가 올바르지 않은 테이블 스캔을 선택하지 못하도록 하기 위해서 아래 기법을 사용.
- 스캔이 된 테이블에 대한 키 배포 업데이트 작업은 ANALYZE TABLE tbl_name를 사용.
- 주어진 인덱스를 사용하는 것 보다 테이블 스캔을 하는 것이 보다 비효율적이라고 MySQL에게 지시하기 위해서, 스캔이 된 테이블에 대해서 FORCE INDEX를 사용.
SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;
- 어떠한 키 스캔도 1,000개 이상의 키 검색이 발생하지 않는다고 가정하게끔 옵티마이저를 만들기 위해서, mysqld를 --max-seeks-for-key=1000 옵션과 함께 시작하거나 또는 SET max_seeks_for_key=1000를 사용.

Oracle - COPY명령으로 테이블 복사하기

* SQL*Plus에서 COPY라는 명령어를 이용해서 테이블을 복사 할 수 있는 기능을 제공.
COPY명령어는 SQL*Net이 설치되어 있어야하고, SQL*Plus에서만 사용 가능.

* 문법
COPY FROM 복사해올유저명
    CREATE 생성할 테이블명
    USING 원본 테이블 질의
 
* 사용예
SQL> COPY FROM scott/tiger@oracle
        CREATE emp2 USING SELECT * FROM emp;

Oracle - 테이블, 컬럼 제약 조건의 확인하기

* 테이블에 걸려있는 제약 조건의 확인하기
USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건을 볼 수 있다.
USER_CONSTRAINTS : 유저가 소유한 모든 제약 조건을 불 수 있다.

SELECT 
    SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,
    DECODE(B.CONSTRAINT_TYPE,
        'P','PRIMARY KEY',
        'U','UNIQUE KEY',
        'C','CHECK OR NOT NULL',
        'R','FOREIGN KEY') CONSTRAINT_TYPE,
    A.CONSTRAINT_NAME CONSTRAINT_NAME
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = UPPER('&table_name')
    AND A.TABLE_NAME = B.TABLE_NAME
    AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY 1;



* 테이블의 특정 컬럼에 걸려있는 제약 조건의 확인하기
USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건을 볼 수 있다.

SELECT 
    SUBSTR(TABLE_NAME,1,15) TABLE_NAMES,
    SUBSTR(COLUMN_NAME,1,15) COLUMN_NAME,
    SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&table_name')
    AND COLUMN_NAME = UPPER('&column_name');

Oracle - 테이블 락 확인하기

* 현재 테이블에 락이 걸려있는지 조회할 수 있다.


* 락 테이블 조회하기
SELECT
      B.OBJECT_NAME, B.OWNER, B.OBJECT_TYPE
      , A.XIDUSN, A.SESSION_ID, A.LOCKED_MODE
FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
WHERE A.OBJECT_ID = B.OBJECT_ID
;
 
* 특정 테이블 락 여부 확인하기
SELECT
     A.SID, A.SERIAL#
     , B.TYPE, C.OBJECT_NAME
FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
WHERE A.SID=B.SID
  AND B.ID1=C.OBJECT_ID
  AND B.TYPE='TM'
  AND C.OBJECT_NAME = '테이블명'
;

Oracle - 테이블 컬럼 변경

*** 컬럼 추가 ***
alter table [테이블명] add([컬럼명] [데이터타입]);

ex) alter table USER add(USER_NAME varchar2(50));


*** 컬럼명 변경 ***
alter table [테이블명] rename column [이전컬럼명] to [새컬럼명];

ex) alter table USER rename column USER_NAME to USER_NM;


*** 컬럼 데이터 타입 변경 ***
alter table [테이블명] modify([컬럼명] [데이터타입]);

ex) alter table USER modify(USER_NM varchar2(100));


*** 컬럼 삭제 ***
alter table [테이블명] drop([컬럼명]);

ex)  alter table USER drop(USER_NM);

Oracle - 테이블 복사하기

*** 테이블 복사 (스키미 & 데이터) ***
CREATE  TABLE 생성테이블명 AS SELECT * FROM 복사할테이블명 [WHERE 절]

*** 테이블 구조만 복사 ***

CREATE TABLE 생성테이블명 AS SELECT * FROM 복사할테이블명 WHERE 1=2

*** 데이터만 복사 ***
INSERT INTO 데이터인서트할테이블명 SELECT * FROM 복사할테이블명 [WHERE 절]