Dev Story..

sum(합을 구하는 함수), decode(치환하는 함수)를 이용해서 그룹별 단순화해보자.

예를 들어, 학과별 자퇴자 명단을 구해보면

select dept, count(sno)

from student

where code = '자퇴'

group by dept;

결과는

근데 주야간으로 구분해야 한다.

select dept, div_dn, count(sno)

from student

where code = '자퇴'

group by dept, div_dn; 으로 조회하면 아래와 같이 나오는데

이런 식으로 같은 과에서 주야별로 나눠져서 나온다. 내가 원한건 아래와 같다.

이렇게 하려면 sum과 decode를 활용하면 쉽다.

sum(컬럼명)은 group으로 묶은 데이터 값의 합을 구하는 함수,

decode(컬럼명, 'A',1,0)은 컬럼에 A값이면 1로 치환 아니면 0으로 치환하는 함수

두 개의 함수를 활용하면

select dept, sum(decode(div_dn,'1',1,0) 주간), sum(decode(div_dn,'1',0,1) 야간), count(sno)

from student

where code = '자퇴'

group by dept, div_dn;

위에 표처럼 출력이 되는데

주야간을 구분하는 div_dn 칼럼은 주간이면 1, 야간이면 2로 입력이 되어 있다.

그래서 decode(div_dn,'1',1,0)을 하면 주간일 경우 1로  치환하고,

decode(div_dn,'1',0,1)을 하면 야간일 경우 1로  치환하고 

sum(decode(div_dn,'1',1,0)을 하면 치환한 1값들을 모두 더해져서

주야간별로 깔끔하게 구할 수 있는 것이다.

'A' DB   --- DB Link --→ 'B' DB

 (로컬)                          (원격지)

1. 로컬 'A' DB 의 tnsnames.ora 파일에 원격지 'B' DB 접속 정보 추가

ex)

B_DB =

  (DESCRIPTION =

    (ADDRESS_LIST =

       (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521))

    )

    (CONNECT_DATA =

       (SID = ORA9)

    )

  )

※ 'B' DB 접속 정보가 올바르게 입력되었는지 확인 : tnsping 명령으로 확인할 수 있다.

tnsping B_DB

2. DB Link 설정 명령

CREATE [PUBLIC] DATABASE LINK DB LINK 이름

CONNECT TO 유저명

IDENTIFIED BY 비밀번호

USING '서비스 이름'

ex)

CREATE DATABASE LINK DBLK_B_DB

CONNECT TO SCOTT

IDENTIFIED BY TIGER

USING 'B_DB' 

※ tnsnames.ora 추가 없이 DB Link 설정하기

tnsnames.ora 에 원격지 'B' DB 정보 추가 없이 아래와 같이 CREATE DATABASE LINK 명령에 직접 기술함으로 추가할 수도 있다.

ex)

CREATE DATABASE LINK DBLK_B_DB

CONNECT TO SCOTT

IDENTIFIED BY TIGER

USING '(DESCRIPTION =

               (ADDRESS_LIST =

                   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))

                )

                (CONNECT_DATA =

                    (SID = ORA9)

                )

            )'; 

※ DB Link 설정하기 위해서는 권한이 필요하다. 만약 권한이 없다면 다음으로 권한 설정을 해준다.

    GRANT CREATE PUBLIC DATABASE LINK, DROP PUBLIC DATABASE LINK TO user_id;

3. DB Link 사용

SELECT * FORM TABLE명@DBLK_B_DB

4. DB Link 삭제

DROP DATABASE LINK DB LINK 이름

ex)

DROP DATABASE LINK DBLK_B_DB

※ DB Link 생성 시 GLOBAL_DB_NAME - REGRESS.RDBMS.DEV.US.ORACLE.COM 이 붙는 현상

DB Link 생성 시 DBLK_B_DB.REGRESS.RDBMS.DEV.US.ORACLE.COM 와 같이 사용자가 정한 DB Link DBLK_B_DB 이름 뒤에 GLOBAL_DB_NAME 이 붙을 때는 GLOBAL_DB_NAME 을 DB_NAME 으로 변경하고 DB_LINK 를 생성하면 된다.

DB Link 이름 뒤에 붙어 있는 GLOBAL_DB_NAME 일반적으로 DB Link 사용 시에 크게 문제되지 않는다.

(그저 보기 지저분할 뿐...)

단, 원격 DB 의 SHOW PARAMETERS GLOBAL_NAMES 의 값(Value) 이 TRUE 인 경우 GLOBAL_DB_NAME 까지 일치해야 한다.

(일반적으로 디폴트 SHOW PARAMETERS GLOBAL_NAME 의 값은 FALSE 이다.)

GLOBAL_DB_NAME 확인

SELECT  *  FROM PROPS$ WHERE NAME = 'GLOBAL_DB_NAME';  (VALUE$ 컬럼 값이 GLOBAL_DB_NAME 이다)

DB_NAME 확인

SHOW PARAMETERS DB_NAME

GLOBAL_DB_NAME 의 변경

UPDATE PROPS$ SET VALUE$ = '<DB_NAME>' WHERE NAME = 'GLOBAL_DB_NAME';

<DB_NAME> 은 SHOW PARAMETERS DB_NAME 으로 확인한 이름값을 넣어준다.

GLOBAL_DB_NAME 변경 후 DB 인스턴스를 재기동 해주어야 반영된다.!!! (shutdown immediate / Startup)

<오라클 9i 이상>

1)두 날짜 사이의 달 구하기

SELECT ROWNUM RM
,TO_CHAR (ADD_MONTHS (TO_DATE ('200511', 'YYYYMM'), LEVEL - 1),'YYYYMM') mon
FROM DUAL
CONNECT BY ADD_MONTHS (TO_DATE ('200511', 'YYYYMM'), LEVEL-1) <=TO_DATE ('200610', 'YYYYMM')

2)두 날짜 사이의 날짜 구하기
SELECT YMD FROM(
SELECT TO_DATE ('20050422', 'YYYYMMDD')+(LEVEL - 1) YMD
FROM DUAL
CONNECT BY TO_DATE ('20050422', 'YYYYMMDD')+(LEVEL-1) <=TO_DATE ('20050521', 'YYYYMMDD'))

또는

SELECT TO_CHAR(TO_DATE(BB.DAY, 'YYYYMMDD') + NUM, 'YYYY-MM-DD') AS DAY
FROM (
SELECT ROWNUM AS NUM 
FROM DICTIONARY A,(
  SELECT B.DAY AS BDAY, C.DAY AS CDAY 
  FROM
   ( SELECT TO_CHAR(SYSDATE,'YYYYMM') || '01' AS DAY FROM DUAL ) B,
   ( SELECT TO_CHAR(LAST_DAY(SYSDATE),'YYYYMMDD') AS DAY FROM DUAL ) C
  ) B
WHERE ROWNUM <= TO_DATE(B.CDAY, 'YYYYMMDD') - TO_DATE(B.BDAY, 'YYYYMMDD')  + 1) AA,
( SELECT TO_CHAR(SYSDATE - TO_NUMBER(TO_CHAR(SYSDATE,'D')) + 1,'YYYYMMDD') AS DAY FROM DUAL ) BB

이거는 원하는 시작일부터 끝일까지 가지고 오는 쿼리입니다.

SELECT TO_CHAR(TO_DATE('[SDATE]', 'YYYY-MM-DD') + NUM-1, 'YYYY-MM-DD') AS DAY
FROM (
 SELECT ROWNUM NUM 
 FROM DICTIONARY
 WHERE ROWNUM <= TO_DATE('[EDATE]', 'YYYY-MM-DD') - TO_DATE('[SDATE]', 'YYYY-MM-DD')  + 1
)

MAX, MIN 이 여러 Row (행) 중에서 해당 Column (혹은 표현)의 최댓값/최솟값을 선택하는 거라면

GREATEST, LEAST는 여러 Column (열 혹은 표현) 중에서 최댓값/최솟값 구하는 함수

SELECT MAX(COL_1), MIN(COL_2)

FROM TABLE_1

요건 한 개의 Row만 리턴... 해당 컬럼의 모든 Row를 대상으로 비교

SELECT GREATEST(COL_1, COL_2, COL_3, ...), LEAST(COL_1, COL_2, COL_3, ...)

FROM TABLE_1

요건 여러 개의 Row 리턴. 최대, 최소는 해당 Row의 컬럼간 비교

SELECT GREATEST(3, 2, 1, 10), LEAST(10, 9, -1, 5)

FROM DUAL

- 최대, 최소값을 가진 로우를 추출한다.

[스포일러]

결론부터 말하자면... 

MyISAM, InnoDB, BDB, MEMORY 엔진을 사용할 경우,

NULL도 INDEX를 탄다 !!!

아래는 그 테스트와 MySQL 리퍼런스 링크에 관련된 내용이다.

[문제 제기]

요즘 나의 궁금중 중 하나가, 'MySQL에서 NULL 값이 인덱스를 탈까?' 라는 것이었다.

참고로,

오라클의 경우에는 NULL 일 경우 인덱스를 타지 않고,

그로 인해 NULL일 경우를 검색해야 한다면, FULL SCAN을 해야 한다고 알고 있다.

그래서인지 주위에서는 억지로 인덱스를 태우기 위해

DEFAULT '' 를 속성의 기본값으로 사용하는 경우를 볼 수 있었다.

흠... 평소에도 쓸데 없이 NULL을 사용하지 않고 ''를 사용하던 것에 불만이었던 나...

게다가 얼마 전에 읽었던, 

The Top 20 Design Tips For MySQL Enterprise Data Architects 라는 짧은 PPT에서는 

'필요 없는 경우에 굳이 NOT NULL 을 쓰는 것은 안 좋은 경우' 라고 얘기했기에,

'그럼 한 번 NULL이 인덱스를 타느냐 안 타느냐를 가지고 테스트 해보자'

라는 생각을 하게 되었다.

[테스트]

1. 테이블 준비 및 값 넣기

 1.1. 임시 테이블 생성

-- ---------------------------------------------------------------

CREATE TABLE `nulltest` (

      `id` int(11) NOT NULL auto_increment,

      `col1` varchar(50) default NULL, 

      `col2` varchar(50) default NULL, 

      PRIMARY KEY(`id`), 

      KEY `nulltest_cols` (`col1`, `col2`) 

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

-- ---------------------------------------------------------------

 1.2. 임시 테이블에 널 값의 교차적인 값 입력

(아래 프로시저는 주석을 모두 제거해야 컴파일 됩니다. ^^;;)

-- ---------------------------------------------------------------

CREATE PROCEDURE `p_nulltest`()

BEGIN

 DECLARE number INT DEFAULT 0; --  반복 회수를 위한 변수

 SET @number = 1;  -- 1로 초기화

  WHILE @number < 100000 DO  -- 100000번 수행

   INSERT INTO nulltest (col1,col2) VALUES (

           -- 2로 나누어질 경우 NULL, 그렇지 않을 경우 랜덤숫자를 생성하여 입력

           (CASE mod(@number,2)  WHEN 0 THEN NULL ELSE CASE(ROUND(RAND()*10000) AS CHAR) END), 

           -- 3로 나누어질 경우 NULL, 그렇지 않을 경우 랜덤숫자를 생성하여 입력

           (CASE MOD(@number,3)  WHEN 0 THEN NULL ELSE CAST(ROUND(RAND()*10000) AS CHAR) END) );

   SET @number = @number+1; -- 변수 1증가

  END WHILE;

 END

-- ---------------------------------------------------------------

 1.3. 테스트 수행 

  1.3.1. col1 이 NULL인 경우 인덱스를 타는가?

EXPLAIN SELECT id FROM nulltest WHERE col1 IS NULL;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE nulltest ref nulltest_cols nulltest_cols 153 const 50278 Using where; Using index

=> 인덱스를 탄다.

 1.3.2. col1, col2 가 NULL인 경우 인덱스를 타는가?

EXPLAIN SELECT id FROM nulltest WHERE col1 IS NULL AND col2 IS NULL;

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE nulltest ref nulltest_cols nulltest_cols 306 const,const 13298 Using where; Using index

=> 역시 인덱스를 탄다.

 1.4. 분석

 위의 실행계획들을 보면, 둘다 인덱스를 이용하는 것을 볼 수 있으며,

 이로 인해 NULL값도 인덱스에 들어갈 수 있다는 것을 알 수 있다.

[참고 문서]

나.. 웬지 괜히 테스트 한 듯...

MySQL 외국 리퍼런스를 보면.. (난 영어 능력이 딸려서, 한글만 검색하는뎅 ㅠ,.ㅠ)

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

In MySQL 5.0 :

 * 오직 MyISAM, InnoDB, BDB, MEMORY 에서만 인덱스에 NULL을 더 할 수 있다.

 * 오직 MyISAM, InnoDB, BDB 에서는 BLOB, TEXT를 INDEX로 사용할 수 있다. 

   (사족이지만 이것도 좋은 정보)

라고 나온다....

으흐흑~ ㅠ.ㅠ 

이넘의 저질 영어 울렁병... 언제나 고칠런지....

담부턴... 영어 리퍼런스도 항상 같이 보도록 노력하자..

root계정으로 접속 후 

su - oracle

sysdba계정으로 sqlplus에 접속 후

sqlplus "/ as sysdba"

Data tablespace 생성

CREATE TABLESPACE LBS_DATA DATAFILE '/home/oracle/ordata/LBS/LBS_DATA.DBF' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

Index tablespace 생성

CREATE TABLESPACE LBS_IDX DATAFILE '/home/oracle/ordata/LBS/LBS_IDX.DBF' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

Temp tablespace 생성

CREATE TEMPORARY TABLESPACE LBS_TMP TEMPFILE '/home/oracle/ordata/LBS/temp/LBS_TMP.DBF' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;

사용자 생성

CREATE USER lbs IDENTIFIED BY 비밀번호입력 DEFAULT TABLESPACE LBS_DATA TEMPORARY TABLESPACE LBS_TMP PROFILE DEFAULT ACCOUNT UNLOCK;

사용자 권한 부여

GRANT RESOURCE TO lbs;

GRANT CONNECT TO lbs;

GRANT CREATE VIEW TO lbs;

GRANT CREATE SYNONYM TO lbs;

ALTER USER USER IDENTIFIED BY "새비밀번호"  REPLACE  "이전비밀번호" ;

ex) ALTER USER BADA  IDENTIFIED BY "1234*abcd"  REPLACE  "abcd*1234" ;

※ 특수문자를 사용하는 비밀번호인 경우 '@'는 사용할 수 없다