Enjoy 프로그래밍

 출처 : http://mediakorea.net/sirboard/board_view.php


오라클 백업명령어인 imp/exp에 대한 간단한 예입니다.

1. 백업
[root@web jeous]# exp

Export: Release 8.0.5.1.0 - Production on 화 Sep 18 11:41:26 2001

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Username: leona/leona ( 사용자 어카운트 및 패스워드 )
Connected to: Oracle8 Release 8.0.5.1.0 - Production
PL/SQL Release 8.0.5.1.0 - Production
Enter array fetch buffer size: 4096 > ( 버퍼 사이즈 )

Export file: expdat.dmp >  ( 저장될 바이너리 파일명의 지정 )
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > t  ( 테이블단위로 혹은 유저 단위로 백업할지의 여부 )

Export table data (yes/no): yes > ( no할 경우 스키마만 백업함. )
Compress extents (yes/no): yes > ( 압축 허용 여부 )
Export done in KO16KSC5601 character set and JA16SJISFIXED NCHAR character set

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > test2  ( 테이블명 지정 )

. . exporting table                          TEST2          3 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully without warnings.
[root@web jeous]#
                                         

2. 복구
[root@web jeous]# imp

Import: Release 8.0.5.1.0 - Production on 화 Sep 18 11:36:26 2001

(c) Copyright 1998 Oracle Corporation.  All rights reserved.

Username: leona/leona ( 사용자 어카운트 및 패스워드 )
Connected to: Oracle8 Release 8.0.5.1.0 - Production
PL/SQL Release 8.0.5.1.0 - Production

Import file: expdat.dmp > exp1.dmp (exp1.dmp 파일명을 지정한 경우)
Enter insert buffer size (minimum is 4096) 30720> ( 사이즈가 클수록 수행속도 빨라짐 )
Export file created by EXPORT:V08.00.05 via conventional path
List contents of import file only (yes/no): no > ( 복구되어지는 내용을 보겠는가? )

Ignore create error due to object existence (yes/no): no > ( 수행시의 에러를 보겠는가? )
Import grants (yes/no): yes > (사용자가 가지고 있는 권한까지 다시 생성할지 여부)
Import table data (yes/no): yes > yes (테이블을 만들고 나서 테이터를 저장할 것인지의 여부)
Import entire export file (yes/no): no > yes (백업 파일내의 모든 데이터를 다시 저장하고 싶을때 yes)

. importing DEVELOPER's objects into DEVELOPER
. . importing table                        "TEST2"          3 rows imported
Import terminated successfully without warnings.
[root@web jeous]#

*주의
테이블 백업 시에는 같은 테이블이 존재하면 imp 실행시 에러가 발생합니다.

#### user 데이터 백업의 예
exp예:
# exp userid=nanu/passwd file=/home/oraback/nanu041701.dmp` owner=nanu consistent=n
(consistent=n -> 테이블 단위로 읽기 일관성 보장)

imp예:
# imp nanune/passwd full=n grants=y rows=y commit=y  fromuser=nanu touser=nanu file=nanu041701.dmp ignore=n
(ignore=n -> object가 이미 존재할때 error를 출력함)

어떤 DBMS라도 인덱스를 사용할 수 있죠. 그보다는 인덱스를 사용할 수 없으면 DBMS라고 부르기 힘들져.

mysql에서 index사용법에 대하여 몇가지 썰을 풀어 볼까 합니다.
누구나 다 아는 야그일것 같은데 헛튼짓 하는것 아닌지 모르겟네여.

다음과 같은 member 테이블을 가지고 놀아 봅시다.

CREATE TABLE member (
id CHAR(10),
name CHAR(20),
age int,
level CHAR(10)
);

요 테이블은 index가 잡혀 있지 않죠.

다음과 같은 select문에 대해서 잘 동작합니다.
select * from member where id = 'myid';

별 문제 없는것 같습니다. 그런데 레코드 수가 좀 많아 지면 query해오는데 시간이 점점 늘어 나져. 그 이유는

member 테이블에 있는 모든 레코드에 대하여 id가 'myid' 인지 전부 비교합니다.(full scan) 레코드가 늘어 날수록

시간은 점점 오래 걸리겠죠.

그리고 다음과 같은 select의 경우에는 시간이 오래 걸리져.
select * from member where id id ='myid' order by id;

id란 필드에 대한 값으로 전체를 sort해야 되기 때문이져.

테이블을 변경해 봅시다.

CREATE TABLE member (
id CAHR(10) NOT NULL DEFAULT '',
name CHAR(20),
age INT,
level CHAR(10),
INDEX myindex1 (id)
);

달라진것은 id 필드에 NOT NULL DEFAULT ''이 추가 됬고, 마지막에 index myindex1(id)라는 항목이 추가 됐네여. id

라는 필드에 대하여 인덱스를 설정한 것입니다. myindex1은 인덱스의 이름일 뿐입니다. 사용자가 임의로 결정해 줍니

다.
NOT NULL이 추가된 것은 index로 사용될 필드는 반드시 NOT NULL이어야 하기 때문이고, DEFUALT ''는 입력값이 없을

때 null이 아닌 어떤값을 디펄트로 사용하겠다는 겁니다.

그냥 index로 사용될 필드는 반드시 NOT NULL과 DEFAULT를 설정해야 된다고 알고 있음 되겟네여.

이렇게 index가 설정되면 id의 순서에 따라 정렬된 index를 가지기 때문에 레코드 수의 증가와 거의 관계없이(사실은

관계있지만 없다고 하겠습니다.) query시간이 일정합니다. 그리고 당근 order by id와 같이 정렬이 필요한 경우에도

따로 sort해줄 필요 없기 때문에 퀘리 시간이 짧지여.

select의 키가 되는 필드는 당연히 보다는 반듯이 index를 잡아 주어야 합니다.

이제 다음과 같은 select문을 생각해 봅시다.
select * from member where name='아무개' age > 20 order by id;

name과 age 필드가 where문에서 사용되었네여. 그렇다면 다음과 같이 name과 age도 index로 잡아 줍니다.

CREATE TABLE member (
id CHAR(10) NOT NULL DEFAULT '',
name CHAR(20) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0
level CHAR(10),
INDEX myindex1 (id),
INDEX myindex2 (name),
INDEX myindex3 (age)
);

별로 어렵지 않죠.

그렇다면 위의 CREATE문과 다음의 CREATE문은 어떤 차이가 있을 까여?

CREATE TABLE member (
id CHAR(10) NOT NULL DEFAULT '',
name CHAR(20) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0
level CHAR(10),
INDEX myindex1 (id, name, age)
);

위의 두개의 CREATE문으로 생성된 member 테이블은 다음과 같은 select에 대해서는 똑같은 동작을 합니다.
select * from member where id = 'myid' order by id;
select * from member where id = 'myid' and name='아무개' order by id;

하지만 다음의 select문에대해서는 서로 다르게 동작합니다.

select * from member where name='아무개';

음.. 두번째 member 테이블에서도 name 필드가 인덱스에 포함되었는데 왜 그럴까...

첫번째의 member 테이블에서는 id, name, age가 각기 다른 index로 잡혀 있었습니다. 그런데 두번째 member 테이블에

서는 id, name, age가 하나의 index로 잡혀 있습니다.
두번째 테이블의 경우 id, name, age의 값이 concate된 값이 index로 작용합니다.
name이란 필드 하나는 index의 역활을 하지 못합니다.

그래서 name이나 age가 따로 키값으로 사용되는 select문에서는 인덱스의 덕을 보지 못하져.

하지만 다음과 같이 id, name, age가 순차적으로 검새될 때는 인덱스의 덕을 봅니다.
select * from member where id = 'myid';
select * from member where id = 'myid' and name='아무개';
select * from member where id = 'myid' and name='아무개' and age=20;

위의 sql의 where 부분은 다음과 같이 작용됩니다.(다음 구문은 sql이 아닙니다. 단지 설명을 위해서)
where id = 'myid'
where id+name = 'myid아무개'
where id+name+age = 'myid아무개20'

그렇다면 다음과 같은 select의 경우는 인덱스의 덕을 볼까여?
select * from member where id like 'my%' ane name='아무개';

id like 'my%'구문은 정규표현식과 비슷합니다. id가 'my'로 시작되는 값을 의미합니다.

적용되는 것을 다시 적어 보면
where id+name like 'my%아무개'

일단은 id부분에서는 덕을 봅니다. 하여간에 id부분이 my로 시작되는 레코드에 대해서 검색을 할테니까여 그러나

name이 '아무개'인 부분은 인덱스의 덕을 보지 못합니다. id필드값이 my로 시작되는 모든 레코드에 대하여 역시 풀스

켄합니다.

테이블의 여러 필드가 인덱스로 잡혔을 때 동작하는 원리를 설명하겠습니다.
feild1, field2, field3가 인덱스로 잡혀 있을때 다음과 같은 where문이 있다고 합시다.
where field1 = 'value1' and field2 like 'value2%' and field3 > value3

1. field1의 값이 'value1'인 레코드의 레퍼런스 리스트를 구합니다. 이때 field1의 인덱스를 사용합니다. 요 리스트

는 역시 field2와 field3에 대한 인덱스를 가지고 있지여.
2. 위에서 구한 리스트에서 field2의 값이 'value2'로 시작되는 레코드의 레퍼런스 리스트를 구합니다. 이때도 역시

field2의 인덱스를 사용해서 구합니다.
3. 다시 위에서 구한 리스트에서 field3의 값이 value3보다 큰 레코드의 레퍼런스 리스트를 구합니다.
4. 최종적으로 구한 리스트의 레퍼런스를 가지고 각 레퍼런스가 가리키는 레코드의 값을 반환합니다.

!!! 테이블의 index설정에 따라 시스템의 퍼포먼스가 엄청나게 차이납니다.
!!! 인덱스 설정은 엄!청! 중요합니다.
----------------------------------------------------------------------------------------
 
인덱스 정보 보기
mysql> SHOW INDEX FROM tablename
[출처] MYSQL INDEX에 관하여..|작성자 디플

1.인덱스 정의 1

검색을 빠른 속도로 하기 위해 제공됩니다.

SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해서 생성하는 오라클 객체입니다.

오라클에서의 인덱스의 내부구조는 B트리 형식으로 구성되어 있습니다.

2.인덱스의 생성

1. 테이블 생성
create table e2
as
select * from emp;

 2.인덱스 만들기
create index index_e2_ename
on e2(ename);

 3.검색
select distinct empno,ename from e2 where ename='SCOTT';
     EMPNO ENAME
---------- ----------
      7788 SCOTT

 4.
select index_name,table_name
from user_indexes;

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
INDEX_E2_ENAME                 E2
SYS_C004042                    DEPT15
EMP_DEPT_EMPNO_PK              EMP_DEPT
DEPT09_DEPTNO_PK               DEPT09
EMP08_EMPNO_PK                 EMP08
DEPT08_DEPTNO_PK               DEPT08
DEPT08_DNAME_UQ                DEPT08
DEPT08_LOC_UQ                  DEPT08
DEPT06_DEPTNO_PK               DEPT06
DEPT03_DEPTNO_PK               DEPT03
SYS_C004023                    DEPT02

 5.
select index_name,table_name,column_name
from user_ind_columns
where table_name in('EMP','DEPT','E2');

INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ --------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
INDEX_E2_ENAME E2 ENAME


 6.
drop index index_e2_ename;

3.인덱스의  장/단점,  인덱스  재구성

1)  인덱스를  사용해야하는  경우

ü  테이블에  행의  수가  많을  때

ü  조건절인  where  문에  해당  컬럼이  많이  사용될  때

ü  검색  결과가  데이터의  2%  ~  4%  정도  일  때

ü  join에  자주  사용되는  컬럼

ü  NULL을  포함하는  컬럼이  많은  경우

2)  인덱스를  사용하지  말아야  하는  경우

ü  테이블에  행의  수가  적을  때

ü  where  문에  해당  컬럼이  자주  사용되지  않을  때

ü  검색  결과가  전체  데이터의  10%  ~  15%  이상  높을  때

ü  테이블에  DML  작업이  많은  경우(  입력  수정  삭제  등이  자주  발생할  때)

4. 인덱스가 필요 이유 예시>

select  *  from  e2  where  deptno=10;

테이블에  전체  행의  수는  10000  건이다.
위의  쿼리문을  전체  쿼리문  들  중  95%  사용된다.
쿼리문의  결과에  구해지는  행은  10건  정도이다.

그래서  인덱스가  필요합니다.



create  index  idx_e2_deptno
on  e2(deptno);
생성완료시간:  7.98초


효율이  떨어지면  인덱스를  재  생성해  주어야  합니다.
컬럼의  데이터가  입력,  수정,  삭제될  경우  해당  컬럼에  의해  생성된  인덱스에  대해서  재구성해야  합니다.

5. 인덱스의  재생성

alter  index  idx_e2_deptno  rebuild;

6. 인덱스의 종류

1.   고유인덱스   : -유일한 값을 갖는 컬럼에 대해서 생성하는 인덱스

2.   비고유 인텍스   : - 중복된 데이터를 갖는 컬럼에 대해서 생성하는 인덱스 
3.   단일 인덱스    :  - 컬럼 하나로 만드는것
4.   결합인덱스   :   컬럼이 하나이상으로 만드는것
5.   함수 기반 인덱스

create table d2
as select * from dept;

고유인덱스만들기.
create unique index idx_d2_deptno
on d2(deptno);

결합인덱스    :   복합 컬럼.
create index idx_d2_com
on d2(deptno,dname);

함수기반 인덱스    :   수식, 함수
1.
create table e3
as
select * from emp;

2.
create index idx_e3_annsal
on e3(sal*12);

3.
select index_name,column_name
from user_ind_columns
where table_name='E3';
INDEX_NAME               COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IDX_E3_ANNSAL            SYS_NC00009$

<별도> 함수 기반 인덱스

Oracle Database 8i 부터 Function Based Index가 소개 되었다.

이 Function Based Index를 잘 활용하게 되면,  성능 향상에 많은 도움이 된다.

1. 컬럼의 중간 부분의 검색
    create index from_loc_idx on orders (substr(ship_id,5,3));
    create index repair_loc_idx on orders (substr(ship_id,3,2), ord_date);

2. 조인 연결고리 컬럼이 대응하지 않는 경우의 해결
  ......
    from  item_group x, items y
    where x.class1||x.class2||x.class3 = y.group_cd ......
   
    ......
    from  item_gropu x, items y
    where x.class1 = substr(y.group_cd,1,2)
    and   x.class2 = substr(y.group_cd,3,2)
    and   x.class3 = substr(y.group_cd,5,3)
    ......
 
    create index group_cd_idx_ on item_group (class1||class2||class3);

3. 일자 컬럼이 분할된 경우의 해결
    where sal_yyyy >= '2005' and sal_mm >= '12' and sal_dd >= '10'    (x)
where sal_yyyy||sal_mm||sal_dd >= '20051210' (o) 문제 : Index 사용불가

    create index sal_date_idx on sales (sal_yyyy||sal_mm||sal_dd);
 

4. 데이타 타입이 상이한 조인 컬럼
    create index deptno_idx on emp(to_number(deptno));

5. 조인 컬럼이 경우에 따라 달라지는 경우
    .......
    from  sales s, department d
    where d.deptno = (case when sal_type = 1 then sal_dept else agent_no end)
    and   d.location = 'BUSAN'
    .......
 
    create index deptno_idx on sales
           (case when sal_type = 1 then sal_dept else agent_no end);
          
     
6. 대/소문자나 공백이 혼재된 컬럼의 검색
    create index ename_upper_ix on employees (upper(ename));
 
    create index ename_upper_ix on employees (upper(replace(ename,' '));

7. NULL 값을 치환하여 검색
    .....
    where  :input_date between start_date and nvl(end_date, '99991231');
    .....
   
    create index end_date_idx on account_history (nvl(end_date, '99991231'), start_date);

8. 복잡한 계산 결과의 검색
    create index order_amount_idx on order_items
        (item_cd, (order_price - nvl(order_discount,0)) * order_count));
       
    s-lect /-+ index_desc(x order_amount_idx) *- *
    from   order_items x
    where  item_cd = :b1
    and    rownum <= 100;

9. 기간, 컬럼 길이 검색
    create index item_idx on activities (expire_date - start_date);
   
    create index source_length_idx on print_media (text_length(source_text));

10. 배타적 관계의 유일성 보장
    create unique index official_id_idx on customers
          (case when cust_type = 1 then resident_id else business_id end);
      
    select * from customers
    where (case when cust_type = 1 then resident_id else business_id end) = :b1;

함수기반 인덱스는 버전에 따라 제약사항에 차이가 있으므로 관련 메뉴얼을 참조하세요.

출처 사이트 및 참고 자료 >

새로쓴 대용량 데이타베이스 솔루션

MySQL에서 Oracle의 ROWNUM같은 기능을 사용할려면 어떻게 해야 할까?

다음과 같은 방법으로 손쉽게 사용 가능하다.

SELECT
    @ROWNUM := @ROWNUM + 1 AS ROWNUM,
    TEST_TABLE.*
FROM
    TEST_TABLE,
    (SELECT @ROWNUM := 0) R

ROWNUM을 이용하여 다른 작업을 하기위해서는 다음과 같이 서브쿼리를 이용하면 된다.

SELECT
    A.*
FROM
(
    SELECT
        @ROWNUM := @ROWNUM + 1 AS ROWNUM,
        TEST_TABLE.*
    FROM
        TEST_TABLE,
       (SELECT @ROWNUM := 0) R
) A
WHERE
    A.ROWNUM < 100