[오라클파] 파티션 테이블

데이터베이스/오라클 튜닝 2018. 6. 13. 15:03

오라클 파티션 정의

파티션 개요

오늘날 기업에서 관리하는 데이터는 수백테라 바이트에 이르는 데이터베이스를 관리합니다. 하지만 이런 데이터들 중 몇몇의 Big Transaction Table이 거의 모든 데이터를 가지고 있고 나머지 테이블들은 이 Big Transaction Table을 경유하여 액세스하는 용도로 사용됩니다. 이렇게 데이터 크기가 크고 중요한 Big Transaction Table을 관리하는 부분에서 Troubleshooting이 발생될 경우 데이터베이스의 성능 및 관리작업에 심각한 영향을 받을 수 있습니다.


이러한 리스크가 있는 Big Transaction Table을 보다 효율적으로 관리하기 위해 Table을 작은 단위로 나눔으로써 데이터 액세스 작업의 성능 향상을 유도하고 데이터 관리를 보다 수월하게 하고자 하는 개념입니다.


파티션 테이블의 장점

1) 개선된 가용성

 - 파티션은 독립적으로 관리된다.

 - Backup and Restore을 파티션별로 작업할 수 있다.

 - 같은 테이블에서 Unavailable한 파티션은 다른 파티션에 영향을 주지 않는다.

2) 관리의 용이성

 - 사용자가 지정한 값으로 파티션이 가능하다.

 - 테이블스페이스간에 파티션 이동이 가능하다.

 - 파티션 레벨에서 SELECT, DELETE, UPDATE가 가능하다.

3) 개선된 성능

 - 데이터를 액세스할 때 액세스하는 범위를 줄여 퍼포먼스 향상을 가져올 수 있다.

 - RAC(Real Application Clusters) 환경에서 인스턴스간 Block Connection을 감소시킨다.



파티션 테이블 사용시 주의할 점

1) 관리적인 관점

     - 하나의 테이블을 세분화하여 관리하기 때문에 보다 세심한 관리가 요구된다.

     - 파티션을 잘못 구성 또는 관리하여 IU(Index Unusable)에 빠지는 것을 주의해야 한다.

2) 사용하는 관점

     - 파티션 키로 나누어져 있는 테이블에 파티션 키를 조건으로 주지 않아 전체 파티션을 액세스하지 않도록 주의해야 한다.



파티션 테이블의 특징

- 파티션 테이블은 파티션 키 값에 의해 구성되며, 한 테이블 당 가능한 파티션은 이론적으로 65,535개를 지원하나 실질적으로는 10,000개까지만 생성 가능합니다(Oracle Ver 10.2.0.3 테스트)

- 모든 파티션 테이블(또는 인덱스)는 같은 Logical Attribute를 가져야 합니다. 

   ex) Columns, Data Types, Constraints ...

- 모든 파티션 테이블(또는 인덱스)는 다른 Physical Attribute를 가져야 합니다.

   ex) PCTFREE, PCTUSED, INITTRANS, MAXTRANS, TABLESPACE, STORAGE ...

- 파티션 테이블은 'KEY', 'VALUES LESS THAN Literal', 'Physical Attributes'로 구성된다.

- 'VALUES LESS THAN Literal' 절에서 'Literal' 값에는 SQL Function을 지원한다.

- Composite Column 구성은 16개까지 가능합니다.



파티션 종류

오라클 버전에 따른 파티션


1) Oracle Ver 7.3

- Partition View를 처음으로 도입하였다.

- 당시 Partition View는 큰 테이블을 동일한 템플릿을 가진 여러 개의 다른 테이블로 분할하고 UNION ALL을 사용하여 View로 묶은 형태이다.

- 그러나 이 방식은 관리의 어려움, 활용성의 부족, 성능 등에 대한 이슈로 인하여 Oracle Ver 9i에서는 더이상 지원하지 않는다.

2) Oracle Ver 8.0

- 컬럼 값의 Range 기반으로 된 Range Partition이 최초로 도입되었고, 비로소 Partition의 모습을 갖추었다.

- 각 파티션은 각기 다른 테이블 스페이스, Segment에 저장이 가능한다.

3) Oracle Ver 8i

- 컬럼 값의 Hash 기반으로 된 hash partition과 Sub Partition을 할 수 있는 Composite Partition이 추가되었다.

- 이 당시 Composite Partition은 Range-Hash로만 구성 가능함.

4) Oracle Ver 9i

- 리스트 값으로 파티션을 할 수 있는 List Partition이 추가되었다.

- Composite Partition에서는 Range-Hash 이외에 Range-List가 추가 지원되었다.

5) Oracle Ver 10g

- 10T 파티션이 추가되었다.

6) Oracle Ver 11g

- Composite Partition에서 확장된 Extended Composite Partition이 지원된다.

    -> Range-Range, List-Range, List-Hash, List-List

- Reference Partition 추가

- Interval Partition 추가

- System Partition 추가

- Virtual Column Partition 추가



Partition Table


1) Range Partition

- Column Value의 범위를 기준으로 하여 행을 분할하는 형태이다.

- Range Partition에서 Table은 단지 논리적인 구조이며 실제 데이터가 물리적으로 저장되는 곳은 Partition으로 나누어진 Tablespace에 저장이 된다.

- PARTITION BY RANGE ( column_list ) : 기본 Table에서 어느 Column을 기준으로 분할할지를 정함

- VALUES LESS THAN ( value_list ) : 각 Partition이 어떤 값의 범위를 포함할지 Upper Bound를 정함.


- 구문 Sample

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE TABLE SALES_DATA_2008
(
  COLUMN_1 NUMBER   NOT NULL,
  COLUMN_2 VARCHAR2(4),
  COLUMN_3 VARCHAR2(4),
  COLUMN_4 VARCHAR2(2),
  COLUMN_5 VARCHAR2(2),
  COLUMN_6 NUMBER
)
TABLESPACE TABLE_SPACE_DATA_1
PCTFREE 5
PCTUSED 40
INITRANS 11
MAXTRANS 255
STORAGE
(
  INITIAL 2048K
  NEXT 1024K
  PCTINCREASE 0
  MINEXTENTS 1
  MAXEXTENTS 121
)
PARTITION BY RANGE ( COLUMN_3, COLUMN_4, COLUMN_5 )
(
  PARTITION P_200801 VALUES LESS THAN ('2008', '04', '01'),
  PARTITION P_200802 VALUES LESS THAN ('2008', '07', '01'),
  PARTITION P_200803 VALUES LESS THAN ('2008', '10', '01'),
  PARTITION P_200804 VALUES LESS THAN ('2009', '01', '01'),
  PARTITION P_5    VALUES LESS THAN (MAXVALUE)
  TABLESPACE TABLE_SPACE_DATA_2
  PCTFREE 5
  PCTUSED 40
  INITRANS 11
  MAXTRANS 255
  STORAGE
  (
    INITIAL 1M
    NEXT 1M
    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS 121
  )
);


2) Hash Partition

- Partitioning column의 Partitioning Key 값에 Hash 함수를 적용하여 Data를 분할하는 방식

- 데이터 이력관리의 목적보다 성능 향상의 목적으로 나온 개념이다.

 Hash Partition은 Range Partition에서 범위를 기반으로 나누었을 경우 특정범위의 분포도가 몰려서 각기 Size가 다르게 되는 것을 보완하여, 일정한 분포를 가진 파티션으로 나누고 균등한 데이터 분포도를 이용한 병렬처리로 퍼포먼스를 보다 향상시킬 수 있다.

- Hash Partition에서 Table은 단지 논리적인 구조이며 실제 데이터가 물리적으로 저장되는 곳은 Partition으로 나누어진 Tablespace에 저장이 된다.


- 구문 Sample

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE TABLE SALES_DATA_2008
(
  COLUMN_1 NUMBER   NOT NULL,
  COLUMN_2 VARCHAR2(4),
  COLUMN_3 VARCHAR2(4),
  COLUMN_4 VARCHAR2(2),
  COLUMN_5 VARCHAR2(2),
  COLUMN_6 NUMBER
)
TABLESPACE TABLE_SPACE_DATA_1
PCTFREE 5
PCTUSED 40
INITRANS 11
MAXTRANS 255
STORAGE
(
  INITIAL 2048K
  NEXT 1024K
  PCTINCREASE 0
  MINEXTENTS 1
  MAXEXTENTS 121
)
PARTITION BY HASH (COLUMN_3, COLUMN_4, COLUMN_5)
(
  PARTITION P_200801,
  PARTITION P_200802,
  PARTITION P_200803,
  PARTITION P_200804,
  PARTITION P_5 VALUES LESS THAN (MAXVALUE)
)
);



3) Composite(Sub) Partition

- 파티션의 칼럼을 Main-Sub 관계로 나누어 분할하는 방식.

- Composite Partition이 아닌 다른 파티션에서 물리적인 데이터가 저장되는 곳은 Table이 아닌 Partition Table에 저장이 되는 것처럼, Composite Partition에서는 Main Partition이 아닌 Sub Partition에 저장된다.

- Composite Partition의 조합 구성은 Oracle의 버전이 올라갈수록 조합하는 방식을 다양하게 지원한다.



- 구문 Sample

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE TABLE SALES_DATA_2008
(
  COLUMN_1 NUMBER   NOT NULL,
  COLUMN_2 VARCHAR2(4),
  COLUMN_3 VARCHAR2(4),
  COLUMN_4 VARCHAR2(2),
  COLUMN_5 VARCHAR2(2),
  COLUMN_6 NUMBER
)
TABLESPACE TABLE_SPACE_DATA_1
PCTFREE 5
PCTUSED 40
INITRANS 11
MAXTRANS 255
STORAGE
(
  INITIAL 2048K
  NEXT 1024K
  PCTINCREASE 0
  MINEXTENTS 1
  MAXEXTENTS 121
)
PARTITION BY RANGE ( COLUMN_3, COLUMN_4 )
SUBPARTITION BY HASH ( COLUMN_5 )
(
  PARTITION P_200801 VALUES LESS THAN ('2008', '04'),
  PARTITION P_200802 VALUES LESS THAN ('2008', '07'),
  PARTITION P_200803 VALUES LESS THAN ('2008', '10')
    (SUBPARTITIONS P_200803_S1 TABLESPACE TABLE_SPACE_DATA_1_1,
     SUBPARTITIONS P_200803_S2 TABLESPACE TABLE_SPACE_DATA_1_2,
     SUBPARTITIONS P_200803_S3 TABLESPACE TABLE_SPACE_DATA_1_3,
     SUBPARTITIONS P_200803_S4 TABLEPSACE TABLE_SPACE_DATA_1_4,
     SUBPARTITIONS P_200803_S5 TABLEPSACE TABLE_SPACE_DATA_1_5,
     SUBPARTITIONS P_200803_S6 TABLEPSACE TABLE_SPACE_DATA_1_6,
     SUBPARTITIONS P_200803_S7 TABLEPSACE TABLE_SPACE_DATA_1_7,
     SUBPARTITIONS P_200803_S8 TABLEPSACE TABLE_SPACE_DATA_1_8,
    ),
  PARTITION P_200804 VALUES LESS THAN ('2009', '01')
 
);


4) List Partition

- Partitioning Column의 특정 값으로 분할하는 방식

- 데이터 분포도가 낮지 않고, 균등하게 분포되어 있을때 유용하다.

- Composite Partition에서 'Range-List'일 경우 그 효율이 더욱 높아진다.

- 다른 파티션 방식처럼 다중 컬럼을 지원하지 않고 단일 컬럼만 가능하다.


- 구문 Sample

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE SALES_DATA_2008
(
  COLUMN_1 NUMBER   NOT NULL,
  COLUMN_2 VARCHAR2(4),
  COLUMN_3 VARCHAR2(4),
  COLUMN_4 VARCHAR2(2),
  COLUMN_5 VARCHAR2(2),
  COLUMN_6 NUMBER
)
TABLESPACE TABLE_SPACE_DATA_1
PCTFREE 5
PCTUSED 40
INITRANS 11
MAXTRANS 255
STORAGE
(
  INITIAL 2048K
  NEXT 1024K
  PCTINCREASE 0
  MINEXTENTS 1
  MAXEXTENTS 121
)
PARTITION BY LIST(COLUMN_2)
(
  PARTITION RS VALUES('A') TABLESPACE TABLE_SPACE_DATA_2,
  PARTITION RM VALUES('B') TABLESPACE TABLE_SPACE_DATA_3,
  PARTITION RN VALUES('C') TABLESPACE TABLE_SPACE_DATA_4,
);


5) Reference Partition

- Reference Key로 지정된 경우 부모 테이블의 컬럼이 존재하지 않아도 부모의 Partition Key로 분할하는 방식


- 구문 Sample

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE CUSTOMERS
(
  CUST_ID   NUMBER    PRIMARY KEY,
  CUST_NAME VARCHAR2(200),
  RATING    VARCHAR2(1)   NOT NULL
)
PARTITION BY LIST(RATING)
(
  PARTITION PA VALUES('A'),
  PARTITION PB VALUES('B')
);
-- Detail Table
CREATE TABLE SALES
(
  SALES_ID   NUMBER   PRIMARY KEY,
  CUST_ID    NUMBER   NOT NULL,
  SALES_AMT  NUMBER,
  CONSTRAINT FK_SALES_01 FOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS
)
PARTITION BY REFERENCE (FK_SALES_01);

- 제약조건

   1) Foreign Key 제약조건이 설정되어 있어야 한다.

   2) 상속받는 테이블의 Key값이 NOT NULL 이어야 한다.


- 테스트

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Normal
SELECT  *
FROM  SALE_TMP  A,
  CUSTOMERS B
WHERE A.CUST_ID = B.CUST_ID
  AND B.RATING = 'A';
 
Rows     Row Source Operation
-------  ------------------------------------
     0    STATEMENT
    28    HASH JOIN 
    28     PARTITION LIST SINGLE PARTITION: 1
    28      TABLE ACCESS FULL CUSTOMERS PARTITION: 1
    56     TABLE ACCESS FULL SALE_TMP
 
 
-- Reference Partition
SELECT  *
FROM  SALES     A,
  CUSTOMERS B
WHERE   A.CUST_ID = B.CUST_ID
  AND B.RATING = 'A';
 
Rows     Row Source Operation
-------  -------------------------------------        
     0    STATEMENT
    28    PARTITION LIST SINGLE PARTITION: 1
    28     HASH JOIN
    28      TABLE ACCESS FULL CUSTOMERS PARTITION: 1
    28      TABLE ACCESS FULL SALES PARTITION: 1


6) Interval Partition

- Range Partition에서 특정 범위를 지정하고 관리할때는 미리 Range를 만들어주어야 하고 생성 이후 분할 또는 병합을 할 때는 추가적인 작업을 해주어야 한다.

- 하지만 'Interval Partition'에서는 각 파티션을 미리 정의함으로써 파티션 생성을 오라클이 직접 해주는 방법입니다.


- 예제 Sample

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- 1. Range Partition 생성
CREATE TABLE SALES6
(
  SALES_ID NUMBER,
  SALES_DT DATE
)
PARTITION BY RANGE(SALES_DT)
(
  PARTITION P0701 VALUES LESS THAN (TO_DATE('20070201', 'YYYYMMDD')),
  PARTITION P0701 VALUES LESS THAN (TO_DATE('20070301', 'YYYYMMDD'))
);
 
-- 2. Partition Key 값의 범위에 없는 값으로 Insert
INSERT INTO SALES6 VALUES(1, TO_DATE('20070401', 'YYYYMMDD'));
 
-- Error
ORA-14400: inserted partition key does not map to any PARTITON
 
-- 3. Intrval Partition 생성
CREATE TABLE SALES6
(
  SALES_ID NUMBER,
  SALES_DT DATE
)
PARTITION BY RANGE(SALES_DT) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION P0701 VALUES LESS THAN(TO_DATE('20080201', 'YYYYMMDD'))
);
 
-- 4. Partition Key 값의 범위에 없는 값으로 Insert
INSERT INTO SALES6 VALUES(1, TO_DATE('20070601', 'YYYYMMDD'));
 
-- No Error
1 row created.

- 파티션을 특정 테이블 스페이스에 저장하고 싶다면 STORE IN 구문으로 가능하다.

    -> INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) STORE IN (TS1, TS2, TS3)

- 애플리케이션 개발자가 특정 파티션에 접하고 싶다면 다음의 구문으로 가능하다.

    -> SELECT * FROM SALES6 PARTITION FOR(TO_DATE('20080501', 'YYYYMMDD'));



7) System Partition

- 테이블 생성시 파티션 구간을 미리 설정하는 것이 아니라 임의로 나눈 파티션에 대해 사용자가 원하는 파티션에 데이터를 저장하는 방식.

- 이 방식은 사용자가 'System Partition'으로 되어 있는 테이블의 데이터를 DML하고자 할 때 직접 파티션을 지정하여 해주어야 한다.

- 로컬 인덱스 생성 시, 인덱스도 동일한 방법으로 파티셔닝된다.


- 예제 Sample

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE SALES3
(
  SALES_ID  NUMBER,
  PRODUCT_CODE  NUMBER,
  STATE_CODE  NUMBER
)
PARTITION BY SYSTEM
(
  PARTITION P1 TABLESPACE USERS,
  PARTITION P2 TABLESPACE USERS
);


- Insert 할 때는 반드시 파티션을 지정해 주어야 한다.

1
2
3
4
5
6
7
8
9
10
11
12
-- Insert할 때 테이블의 파티션을 지정하지 않을 경우
INSERT INTO SALES3 VALUES(1, 101, 1);
 
-- Error
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be sued for DMLs on tables partitioned by the System method
 
-- Insert을 할 때 테이블의 파티션을 지정한 경우
INSERT INTO SALES3 PARTITION(P1) VALUES (1, 101, 1);
 
-- No Error
1 row created.


- Delete, Update 할 때는 필수는 아니지만 파티션을 지정하지 않을 경우 모든 파티션을 찾아다녀야 하므로 이 경우에도 가급적 파티션을 지정해 주어야 한다.

1
DELETE SALES3 PARTITION(P1) WHERE STATUS_CODE = 1;


8) Virtual Column Partition

- 파티션으로 나누고자 하는 칼럼이 테이블에서 가공되어 얻을 수 있는 칼럼일 경우 11g 이전에서는 새로운 칼럼을 추가하고 트리거를 이용하여 칼럼 값을 생성하는 방법을 사용하여 많은 오버헤드를 감수하였으나, 11g에서는 'Virtual Column Partition'을 원하여 실제로 저장되지 않는 칼럼을 런타임에 계산하여 생성할 수 있다. 또한 가상 컬럼에 파티션을 적용하는 것도 가능하다.


- 예제 Sample

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Virtual Partition 생성
CREATE TABLE SALES
(
  SALES_ID  NUMBER,
  CUST_ID   NUMBER,
  SALE_CATEGORY   VARCHAR2(6)
  GENERATED ALWAYS AS
  (
    CASE WHEN SALES_AMT <= 10000 THEN 'LOW'
      WHEN SALES_AMT BETWEEN 10000 AND 100000 THEN CASE WHEN CUST_ID < 101 THEN 'LOW'
                  WHEN BETWEEN 101 AND 200 THEN 'MEDIUM'
                  ELSE 'LOW' END
      WHEN SALES_AMT BETWEEN 100000 AND 1000000 THEN CASE WHEN CUST_ID < 1010  THEN 'MEDIUM'
                  WHEN BETWEEN 101 AND 200 THEN 'MEDIUM'
                  ELSE 'ULTRA' END
      ELSE 'ULTRA' END
  ) VIRTUAL
PARTITION BY LIST(SALES_CATEGORY)
(
  PARTITION P_LOW   VALUES ('LOW'),
  PARTITION P_MEDIUM  VALUES ('MEDIUM'),
  PARTITION P_HIGH  VALUES ('HIGH'),
  PARTITION P_ULTRA VALUES ('ULTRA')
);
 
-- Insert 테스트
INSERT INTO SALES(SALES_ID, CUST_ID, SALES_AMT) VALUES (1, 1, 100);
 
-- No Error
1 row created.


Partition Index


1) Local Index

- 인덱스를 생성한 인덱스와 파티션된 인덱스가 동일하게 파티션된 형태를 말합니다.

- 인덱스와 테이블은 같은 칼럼에 의해 파티션되며, 하나의 인덱스 파티션이 테이블 파티션 하나와 대응되며, 대응되는 인덱스 파티션과 테이블 파티션은 각각 같은 범위를 갖게 됩니다.

- 결국 특정한 하나의 인덱스에 포함된 모든 Key들은 하나의 테이블 파티션 내의 데이타만 가리키게 됩니다.


1-1) Local Prefixed Index

- 인덱스의 맨 앞에 있는 컬럼에 의해 파티션되는 방식입니다.

- Local Prefixed Index에서 칼럼은 Unique/Non-Unique를 모두 허용합니다.

- Base Table 의 파티션이 변경되면 Local Index의 관련 파티션만 변경이 됩니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE DEPT
(
  DEPT  NUMBER    NOT NULL,
  DNAME   VARCHAR2(10)  NOT NULL,
  LOC VARCHAR2(14)
)
PARTITION BY RANGE (DEPTNO)
(
  PARTITION PART_1 VALUES LESS THAN (30),
  PARTITION PART_2 VALUES LESS THAN (MAXVALUE)
);
 
CREATE INDEX DEPT_N1 ON DEPT(DEPTNO) LOCAL;


1-2) Local Non-Prefixed Index

- Index의 첫번째 Column이 Partition Key가 아닌 형태로 Base Table과 동일한 Partition 구조를 가진 Index입니다(equi-partitioned)

- 빠른 Access가 요구될 때 유용합니다(Base Table의 Partition Key는 제외)

- Partition 단위로 관리할 수 있으므로 Global Index에 비해 운영상 편리합니다.

- OLAP 측면에서 Global Index보다 조회 속도가 저하됩니다.


1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE DEPT
(
  DEPTNO  NUMBER    NOT NULL,
  DNAME VARCHAR2(10)  NOT NULL,
  LOC   VARCHAR2(14)
)
PARTITION BY RANGE (DEPTNO)
(
  PARTITION PART_1 VALUES LESS THAN (30),
  PARTITION PART_2 VALUES LESS THAN (MAXVALUE)
);
 
CREATE INDEX DEPT_N2 ON DEPT(LOC) LOCAL;


2) Global Index

- Global Index는 테이블과 다르게 파티션되는 경우입니다.


2-1) Global Prefixed Index

- Base Table과 비교하여 not equi-partitioned 상태입니다.

- Oracle은 only Index structure만 관리합니다. (Partition은 관리안함)

- 최종 Partition에는 Maxvalue값이 반드시 기술되어야 합다.

- Local index보다 관리하기 힘듭니다.

- 기준 Table의 Partition이 변경되면 global index의 모든 Partition에 영향을 미칩니다.

  (Global Index 재생성 해야함) 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 테이블 생성
CREATE TABLE SALES_DATA_2008
(
  COLUMN_1 NUMBER   NOT NULL,
  COLUMN_2 VARCHAR2(4),
  COLUMN_3 VARCHAR2(4),
  COLUMN_4 VARCHAR2(2),
  COLUMN_5 VARCHAR2(2),
  COLUMN_6 NUMBER
)
PARTITION BY RANGE ( COLUMN_3, COLUMN_4 )
(
  PARTITION P_200801 VALUES LESS THAN ('04', '01'),
  PARTITION P_200802 VALUES LESS THAN ('07', '01'),
  PARTITION P_200803 VALUES LESS THAN ('10', '01'),
  PARTITION P_200804 VALUES LESS THAN ('12', MAXVALUE)
);
 
-- Global Prefixed Index
CREATE UNIQUE INDEX RANGE2_GPX8 ON SALES_DATA_2008(COLUMN_2, COLUMN_1)
TABLESPACE TABLE_SPACE_DATA_1
PCTFREE   10
STORAGE( INITIAL 100K NEXT 100K PCTINCREASE 0 )
GLOBAL PARTITION BY RANGE ( CODE )
(
  PARTITION P_2008_P1 VALUES LESS THAN ('2000'),
  PARTITION P_2008_P2 VALUES LESS THAN ('3000'),
  PARTITION P_2008_P3 VALUES LESS THAN (MAXVALUE)
);


2-2) Non-Partitioned Index

- 파티션과는 아무런 상관없는 Normal Index를 말함.



파티션을 사용할 때 알아야 할 사항들

파티션 테이블 및 인덱스 관리를 위한 명령어들

1) 일반 테이블 파티션 하기


- Export/Import 하는 방법

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<b></b>-- 테이블을 Export 한다.
exp user/password tables=number file=exp.dmp
 
-- 백업받은 테이블을 제거한다.
drop table numbers;
 
-- 파티션 테이블을 생성한다.
CREATE TABLE NUMBER(QTY NUMBER(3), NAME VARCHAR2(15))
PARTITION BY RANGE(QTY)
  ( PARTITION P1 VALUES LESS THAN (501),
    PARTITION P2 VALUES LESS THAN (MAXVALUE));
 
--ignore=y를 사용하여 데이터를 Import한다.
imp user/password tables=number file=ex.dmp ignore=y


- Subquery를 이용한 방법

1
2
3
4
5
6
7
8
-- 파티션 테이블을 생성한다.
CREATE TABLE PARTBL(QTY NUMBER(3), NAME VARCHAR2(15))
PARTITION BY RANGE(QTY)
  ( PARTITION P1 VALUES LESS THAN (501),
    PARTITION P2 VALUES LESS THAN (MAXVALUE));
 
-- Subquery를 이용하여 파티션 테이블에 데이터를 입력한다.
INSERT INTO PARTBL(QTY, NAME) SELECT * FROM ORIGTBL;


- Partition Exchange 명령어를 하는 방법

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- ALTER TABLE EXCHANGE PARTITION은 파티션 테이블을 일반 테이블로,
-- 또는 파티션 되어있지 않은 테이블을 파티션 테이블로 변경시킬 때 사용한다.
 
-- 파티션 테이블 생성
CREATE TABLE P_EMP (SAL NUMBER(7,2))
PARTITION BY RANGE(SAL)
  (PARTITION EMP_P1 VALUES LESS THAN (2000),
   PARTITION EMP_P2 VALUES LESS THAN (4000));
 
-- 첫번째 파티션에 들어갈 데이터
CREATE TABLE DUMMY_Y
SELECT  SAL
FROM  EMP
WHERE   SAL < 2000;
 
-- 두번째 파티션에 들어갈 데이터
CREATE TABLE DUMMY_Z
SELECT  SAL
FROM  EMP
WHERE SAL BETWEEN 2000 AND 3999;
 
ALTER TABLE P_EMP EXCHANGE PARTITION EMP_P1
WITH TABLE DUMMY_Y;
 
ALTER TABLE P_EMP EXCHANGE PARTITION EMP_P2
WITH TABLE DUMMY_Z;


- 여러 개의 파티션으로 분리된 테이블 중 일부의 파티션만 가진 테이블 생성하기

1
2
3
4
5
6
7
8
9
-- 데이타를 Export한 후 필요한 파티션으로 이루어진 테이블을 생성한다.
-- 데이터 생성
CREATE TABLE YEAR(COL1 DATE) PARTITION BY RANGE (COL1)
  ( PARTITION OCTOBER   VALUES LESS THAN ('01-NOV-1999') TABLESPACE OCTOBER,
    PARTITION NOVEMBER  VALUES LESS THAN ('01-DEC-1999') TABLESPACE NOVEMBER,
    PARTITION DECEMBER  VALUES LESS THAN (MAXVALUE) TABLESPACE DECEMBER);
-- 데이터를 Import
IMP USERNAME/PASSWORD FILE=EXPDAT.DMP FROMUSER=<owner> TOUSER=<owner>
TABLES=(YEAR:OCTOBER, YEAR:NOVEMBER, YEAR:DECEMBER)</owner></owner>


- 파티션을 추가하는 방법

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 파티션 테이블 생성
CREATE TABLE PART_TBL
(
  IN_DATE  CHAR(8)  PRIMARY KEY,
  EMPNO    NUMBER,
  ENAME    VARCHAR2(20),
  JOB      VARCHAR2(20)
)
PARTITION BY RANGE (IN_DATE)
(
  PARTITION PART_TBL_03 VALUE LESS THAN ('20000331') TABLESPACE PTS_03,
  PARTITION PART_TBL_04 VALUE LESS THAN ('20000430') TABLESPACE PTS_04,
  PARTITION PART_TBL_05 VALUE LESS THAN ('20000531') TABLESPACE PTS_05,
  PARTITION PART_TBL_06 VALUE LESS THAN ('20000630') TABLESPACE PTS_06,
  PARTITION PART_TBL_07 VALUE LESS THAN ('20000731') TABLESPACE PTS_07,
  PARTITION PART_TBL_08 VALUE LESS THAN ('20000831') TABLESPACE PTS_08,
  PARTITION PART_TBL_09 VALUE LESS THAN ('20000930') TABLESPACE PTS_09,
  PARTITION PART_TBL_10 VALUE LESS THAN ('20001031') TABLESPACE PTS_10
);
 
-- 파티션 추가
ALTER TABLE PART_TBL ADD PARTITION PART_TBL_11 VALUES LESS THAN ('20001130') TABLESPACE PTS_11;
ALTER TABLE PART_TBL ADD PARTITION PART_TBL_12 VALUES LESS THAN ('20001231') TABLESPACE PTS_12;


- 특정 파티션을 삭제하는 방법

특정 파티션 삭제 이후 삭제한 파티션의 값이 들어올 경우 그 다음 VALUES LESS THAN으로 편입됩니다.

1
ALTER TABLE PART_TBL DROP PARTITION PART_TBL_08;


- 파티션을 나누는 방법

만약 3월만 들어가있는 파티션이 있을 경우, 여기서 1, 2월을 추가하려면 파티션에 ADD가 아닌 SPLIT을 해주어야 합니다.

1
2
3
4
5
6
7
8
9
-- 3월 파티션에서 2월과 3월을 SPLIT함.
ALTER TABLE PART_TBL SPLIT PARTITION PART_TBL_03 AT ('20000229')
  INTO (PARTITION PART_TBL_02    TABLESPACE PTS_02,
        PARTITION PART_TBL_03_1  TABLESPACE PTS_03);
 
-- 2월 파티션에서 1월과 2월을 SPLIT함.
ALTER TABLE PART_TBL SPLIT PARTITION PART_TBL_02 AT ('20000131')
  INTO (PARTITION PART_TBL_01    TABLESPACE PTS_01,
        PARTITION PART_TBL_02_1  TABLESPACE PTS_02);


- 파티션 이름을 변경하는 방법

1
ALTER TABLE PART_TBL RENAME PARTITION PART_TBL_02_1 TO PART_TBL_02;


- 파티션의 테이블스페이스를 옮기는 방법

1
ALTER TABLE PART_TBL MOVE PARTITION PART_TBL_10 TABLESPACE PTS_10_1 NOLOGGING;


- 특정 파티션의 데이터를 Truncate하는 방법

Partition의 Data를 모두 삭제하려면 Truncate하는 방법을 사용할 수가 있는데,

Truncate는 Rollback이 불가능하며 특정 Partition 전체를 삭제하므로 주의하여 사용하여야 합니다.

1
ALTER TABLE PART_TBL TRUNCATE PARTITION PART_TBL_02;


- 파티션 테이블의 물리적인 속성 변경하는 방법

Partition Table은 특정 Partition의 속성만 변경할 수 있고, Table의 속성을 변경하여 전체 Partition에 대해 동일한 변경을 할 수 있습니다.

1
2
3
4
-- part_tbl의 모든 Partition의 Next 값이 변경
ALTER TABLE PART_TBL STORAGE (NEXT 10M);
-- part_tbl_05 Partition의 Maxextents 값만 변경
ALTER TABLE APRT_TBL MODIFY PARTITION PART_TBL_05 STORAGE (MAXEXTENTS 1000);


- 인덱스 관리

파티션 테이블 관련 변경작업을 한 후에는 테이블에 걸려있는 Local, Global Index에 대해 반드시 Rebuild를 해주어야 합니다.

1
2
3
4
-- 특정 파티션의 인덱스 Rebuild
ALTER INDEX IND_PART_TBL REBUILD PARTITION I_PART_TBL_02;
-- 글로벌 인덱스 Rebuild
ALTER INDEX PART_TBL_PK REBUILD;


Backup & Recovery


1) Export

 - Table-Level Export

    기존의 Table Export처럼 Table 전체를 Export하는 경우입니다. 이는 Emp Table(Partitioned 또는 Non-Partitioned) 전체를 Export하는 경우입니다.

    $ exp scott/tiger tables=emp file=emp.dmp


 - Partition-Level Export

    이는 Partition Table의 일부 Partition만을 Export하는 것으로, Full Mode의 Export시에는 사용하지 못하고, Table단위의 Export시에만 가능합니다. ':'을 이용하여 Partition 이름을 지정하며 이 경우 Emp Table의 px Partition만을 Export합니다.

    $ exp scott/tiger tables=emp:px file=emp_par.dmp


- 다음과 같이 두가지 경우를 Level을 혼용하여 사용하는 것도 가능합니다.
    Sales Table은 전부를, Emp Table에서는 px Partition만을 Export.
     $ exp scott/tiger tables=(emp:px, sales) file=both.dmp

2) Import

 - Table-Leve Import

    Partitioned 또는 Non-Partitioned Table 전체를 Import 합니다. 모든 Import Mode (full, user, table)에서 사용됩니다. emp table(Partitioned 또는 non-Partitioned) 전체를 Import 합니다.

    $ imp scott/tiger file=wookpark.dmp tables=emp


 - Partition-Level Import

    Export Dump File에서 (full, user, table 중 어떠한 Mode를 이용하여 Export했건간에)

    Partitioned Tabled의 일부 Partition만을 Import합니다.

    Table Import Mode에서만 사용 가능합니다.


    -- emp table의 px Partition만을 Import

    -- ':'을 이용하여 Partition을 지정

     $ imp scott/tiger file=wookpark.dmp tables=emp:px


테이블 단위의 Import시 우선 Table Creation 문장을 행하고 Row Insert문을 수행하는 것과 마찬가지로, Partition-level Import도 우선 Partitioned Table의 생성 문장을 수행하고 Row Insert문을 수행하게 됩니다.


따라서 ignore=y option등을 적절히 사용하면, Non-Partitioned Table과 Partitioned Table간의 Partitioned Table의 구조 변경등을 수행할 수 있게 됩니다.



출처: http://12bme.tistory.com/290?category=749950 [goorm>=5; 길은 가면, 뒤에 있다]

저작자표시 비영리 변경금지

'데이터베이스 > 오라클 튜닝' 카테고리의 다른 글

[오라클]테이블 스페이스와 데이터파일  (0) 2018.06.13
[Oracle] Tablespace(테이블스페이스)란?  (0) 2018.05.21
[오라클] SQL 튜닝 시작하기  (0) 2018.05.21
[Oracle] Select 쿼리 튜닝 순서  (0) 2018.05.21
블로그 이미지

낭만가을

,

[오라클]테이블 스페이스와 데이터파일

데이터베이스/오라클 튜닝 2018. 6. 13. 15:00

* 테이블스페이스: 논리적(메모리)
- 오라클 데이터베이스는 하나 이상의 논리적 저장영역 테이블스페이스가 있고 데이터를 집합적으로 저장합니다.
- 하나 이상의 데이터 파일로 구성되어 있습니다.


1. 테이블 스페이스 유형

1.1. 시스템 테이블스페이스(사용자가 건드릴수 없다)
  - 데이터 베이스와 함께 생성됩니다.
  - 데이터 딕셔너리 포함
  - 시스템 Undo Segment를 포함합니다.

1.2. 비시스템 테이블스페이스(사용자가 필요하면 만들고, 지우면서 사용가능)
- 데이터베이스 관리와 공간 관리를 용이하게 할 수 있습니다.
- Undo Segment, Temporary Segment, 응용 프로그램 데이터 세그먼트 및 인덱스 세그먼트를 분할합니다.
- 사용자에게 할당된 공간의 양을 제어합니다.



2. 테이블스페이스 생성(기본)


SQL> CREATE TABLESPACE <테이블스페이스명>
    DATAFILE '<데이터 파일명>' SIZE <크기>    // 생성하는 파일은 크기에 따른 빈파일이 생성
    AUTOEXTEND ON NEXT 5M MAXSIZE 200M;       // 5M씩 최대 200M까지 자동 증가 200M가 다차면 에러발생
                             MAX SIZE가 없으면 32비트:16기가, 64비트:32기가까지 가능



3. 테이블 스페이스 영역관리

테이블스페이스는 확장영역에 공간을 할당합니다.


3.1. DMT(Dictionary Managed Tablespace): 10g부터는 DMT사용안함.
- 데이터 딕셔너리에서 사용 가능한 확장 영역을 관리. 서버 프로레스가 빈 블록을 찾을때 사용중인 곳과 빈 곳을 리스트로 작성 관리(Uetf, Fetf)하는 데이터 딕셔너리 관리합니다.
- 확장 영역이 할당될시 할당해제시 해당 테이블이 갱신됩니다.
- 개인별로 하나씩은 가능하지만 단번에 벌크로 들어오면 데이터가 선형대기상태로 전환돼 처리가 느립니다.

3.2. LMT(Locally Managed Tablespace): 오라클 9i부터 기본설정되서 안써줘도 됨.
- 많은 데이터가 한꺼번에 들어오는 문제를 해결하기 위해 지역적으로 관리되는 테이블스페이스가 기본 관리 방식으로 추가합니다.
- 자체 Extent에 대한 관리를 각각의 데이터 파일에 비트맵 형식으로 저장하여 관리하는 테이블스페이스입니다.
- 데이터파일을 구성하는 블럭이 비었는지, 사용중인지에 대한 정보를 관리합니다.
- 테이블스페이스 할당량 정보와 같은 특별한 경우를 제외하고 확장 영역 비트맵의 변경사항은 데이터 딕셔너리에 있는 테이블을 갱신하지 않으므로 실행 취소 정보를 생성하지 않습니다.
- 데이터 딕셔너리 테이블에 사용 가능 영역을 기록하지 않으므로 테이블의 경합을 줄입니다.
- 인접한 사용 가능 공간을 자동 추적하므로 사용 가능한 확장 영역을 통합할 필요가 없습니다.


4. Undo Tablespace(실행취소 테이블스페이스)

- 실행취소 세그먼트를 저장하고 다른 객체를 포함할 수 없습니다.
- 확장영역을 지역적으로 관리하며 데이터 파일절 및 Extent Management절만 사용합니다.


SQL> CREATE UNDO TABLESPACE undo1
    DATAFILE '/u01/oradata/undo01.dbf' size 40M;




5. Temporary Tablespaces(임시 테이블스페이스)

- 정렬 작업에 사용하여 공간을 효과적으로 관리할 수 있습니다.
서버 프로세스가 DB 캐시로부터 100만건의 데이터를 받아 PGA(Program Global Area)에서 정렬 작업을 합니다. PGA용량이 충분하다면 정렬이 되겠지만 용량이 작다면 100만건의 데이터가 정렬할때까지 전달하지 못합니다. 이런경우 임시 테이블스페이스를 사용합니다. 저장하는 곳이 아니며 임시로 사용하는 공간입니다. 임시 테이블스페이스도 부족할 경우에는 자신의 용량만큼만 수행을 하고 에러가 발생하고 멈춰버립니다.


SQL> CREATE TEMPORARY TABLESPACE temp
    TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;



5.1. Default Temporary Tablesapce(기본임시 테이블스페이스)
- 모든 데이터베이스를 생성할 경우 System 테이블스페이스는 기본 임시 테이블스페이스로 사용됩니다.
- 기본 임시 테이블스페이스를 생성하면 System 테이블 스페이스는 사용되지 않습니다.
- 하나의 임시 테이블스페이스를 여러곳에서 사용할순 있지만 동시에 사용은 불가능합니다. 이런경우 데이터 처리가 줄을 서게됩니다. 그래서 각각 임시 테이블스페이스를 여러개 만들어 1:1로 할당을 해주면 좋습니다. 하지만 만드는 것도 중요하지만 관리가 더 중요할 것입니다.

기본임시 테이블스페이스 생성 및 찾기

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;   // 생성
SQL> SELECT * FROM DATABASE_PROPERTIES;          // 질의



5.2. 제한 사항
- 새 기본값을 사용할 수 있을때까지 기본 임시 테이블스페이스는 삭제할 수 없습니다. 기본 임시 테이블스페이스 변경은 ALTER DATABASE 명령 사용합니다.
- 오프라인으로 설정 불가능
- 영구 테이블스페이스로 변경 불가능


6. Read Only Tablespaces(읽기 전용 테이블스페이스)


SQL> ALTER TABLESPACE userdata READ ONLY;

- 테이블이 모두 SELECT밖에 되지 않는다. 데이터를 읽을 수만 있다는 것이다.
  다시 반대로 적용하기 위해 Read Write로 쿼리를 입력하면 원상복귀된다.


7. 테이블스페이스 오프라인 설정

- 오프라인 설정을 하면 이후의 SQL문은 이 테이블스페이스에 포함된 객체를 참조할수 없게 된다. 데이터 액세스 불가
- 오프라인 설정 불가 테이블 스페이스 : SYSTEM 테이블스페이스, UNDO 테이블스페이스, DEFAULT TEMPORARY 테이블 스페이스

SQL> ALTER TABLESPACE userdata OFFLINE;        // 오프라인 설정
SQL> ALTER TABLESPACE userdata ONLINE;        // 온라인 설정



8. 테이블스페이스 크기 조정

- 테이블 파일의 크기 변경

CREATE DATABASE
CREATE TABLESPACE
ALTER TABLESPACE ... ADD DATAFILE


자동: AUTOEXTEND를 사용한 자동 변경

SQL > CREATE TABLESPACE user_data
    DATAFILE '/u10/oradata/userdata01.dbf' SIZE 200M
    AUTOEXTEND ON NEXT 10M MAXSIZE 500M;


수동: ALTER DATABASE를 사용한 수동 변경: 에러날때마다 수동으로 늘려야 하는 단점

SQL > ALTER DATABASE
    DATAFILE '/u03/oradata/userdata02.dbf'
    RESIZE 200M;


- ALTER TABLESPACE를 사용하여 데이터 파일 추가(테이블 추가)
    순차 저장이 아닌 분산 저장으로 추가된 데이터파일은 똑같은 양으로 저장이 되며 속도 향상이 된다.

SQL > ALTER TABLESPACE user_data
    ADD DATAFILE '/u01/oradata/userdata03.dbf'
    SIZE 200M;



9. 데이터 파일 이동 방식

ALTER TABLESPACE
 - 테이블스페이스는 오프라인이어야 한다.
 - 대상 데이터 파일은 반드시 존재해야 한다.


SQL > ALTER TABLESPACE user_data RENAME
    DATAFILE '/u01/oradata/userdata01.dbf'        // 원래 있던 경로
    TO '/u02/oradata/userdata01.dbf'        // 변경 경로



* 파일이동시, 따라야할 순서
1. 해당 파일을 사용 안하게 만들어야 한다.(오프라인, Shutdown-오프라인이 안될 경우: Undo, SYSTEM, DEFAULT)
2. OS명령어로 복사/이동
3. 위치정보 변경 - 데이터 파일 정보가 들어있는 ControlFile 수정(컨트롤 파일 수정시 마운트 상태에서 수정)
4. 사용할 수 있도록 설정 : 테이블스페이스 온라인 설정

ALTER DATABASE
- 데이터베이스는 마운트 되어야 한다.
- 대상 데이터 파일이 존재해야 한다.


SQL > ALTER DATABASE RENAME
    FILE '/u01/oradata/system01.dbf'
    TO '/u03/oradata/system01.dbf';



10. 테이블 스페이스 삭제

- 삭제 안되는 경우
    SYSTEM 테이블스페이스인 경우, 테이블스페이스에 사용중인 세그먼트가 있을 경우
- INCLUDING CONTENTS는 세그먼트를 삭제
- INCLUDING CONTENTS AND DATAFILES: 데이터파일을 삭제
- CASCADE CONSTRAINTS: 모든 참조 무결성 제약 조건 삭제


SQL > DROP TABLESPACE userdata
    INCLUDING CONTENTS AND DATAFILES;



11. 테이블스페이스 정보보기

테이블 스페이스: DBA_TABLESPACES, V$TABLESPACE
데이터 파일 정보: DBA_DATA_FILES, V$DATAFILE
임시파일 정보: DBA_TEMP_FILES, V$TEMPFILE


DATA FILE 및 TABLESPACE관련 조회

SQL> select tablespace_name, status, contents,
         extent_management, segment_space_management
         from dba_tablespaces;
SQL> select tablespce_name, bytes, file_name from dba_data_files;



테이브스페이스 생성

SQL> create tablespace < tablespace 명>
         datafile '<data file명>' size <크기>
         segment space management auto;    //10g에서는 사용 안해도 됨
SQL> create tablespace < tablespace 명>
         datafile '<data file명>' size <크기>
         extent management local;
SQL> create undo tablespace < tablespace 명>
         datafile '<data file명>' size <크기>
SQL> create temporary tablespace < tablespace 명>
         datafile '<data file명>' size <크기>
SQL> create tablespace < tablespace 명>
         datafile '<data file명>' size <크기>
         blocksize<크기>
         segment space management auto;


테이블스페이스 확장

SQL> alter tablespace < tablespace 명>
         add datafile '<data file명>' size <크기>;
SQL> alter database datafile
         '<data file명>' resize <크기>;



테이블스페이스 관리

SQL> alter tablespace <tablespace 명> offline;
SQL> alter tablespace <tablespace 명> online;
SQL> alter tablespace <tablespace명> rename
         datafile '<원본data file명>'
         to '<data file명>'
SQL> alter database rename
         datafile '<원본data file명>'
         to '<data file명>'



테이블스페이스 삭제

SQL> drop tablespace <tablespace명>
         including contents and datafiles cascade constraints;



출처: http://12bme.tistory.com/300?category=749950 [goorm>=5; 길은 가면, 뒤에 있다]

저작자표시 비영리 변경금지

'데이터베이스 > 오라클 튜닝' 카테고리의 다른 글

[오라클파] 파티션 테이블  (0) 2018.06.13
[Oracle] Tablespace(테이블스페이스)란?  (0) 2018.05.21
[오라클] SQL 튜닝 시작하기  (0) 2018.05.21
[Oracle] Select 쿼리 튜닝 순서  (0) 2018.05.21
블로그 이미지

낭만가을

,

SQL 문법

데이터베이스/오라클 기본 문법 2018. 6. 13. 14:36

/*************************************************************
  SQL & SQL*PLUS Syntax 정리
**************************************************************/

             ----------------------------------------------------
            -       * syntax 기호                                    -
             -                                                                           -
             -         [] : 대괄호 안의 내용이 생략가능                     -
             -          | : 또는                                                      -
             -        {} : 중괄호 안의 내용이 하나 이상 올 수 있다.     -
             -        ... : 앞 표기와 같은 형식으로 더 올 수 있다.        -
             -          , : 구분자                                                    -
             ---------------------------------------------------- 

* SELECT
SELECT [DISTINCT] {*, COLUMN [ALIAS], . . .}
FROM TABLE_NAME
[WHERE CONDITION]
[ORDER BY {COLUMN, EXPRESSION} [ASC|DESC]];

* 비교연산자
= : 같다.
> : 보다 크다.
>= : 보다 크거나 같다.
< : 보다 작다.
<= : 보다 작거나 같다.
<>, !=, ^= : 같지 않다.
NOT Column_name = : 같지 않다.
NOT Column_name > : 보다 크지 않다.

* SQL연산자
between a and b : a와 b 사이에 있다. (a, b값 포함)
in (list) : list의 값 중 어느 하나와 일치한다.
like : 문자형태와 일치한다. (%, _ 사용)
      %는 값이 없거나 하나이상, _는 하나의 문자
in null : null 값을 가졌다.
not between a and b : a와 b사이에 있지 않다. (a, b값 포함하지 않음)
not in (list) : list의 값과 일치하지 않는다.
not like : 문자 형태와 일치하지 않는다.
not is null : null 값을 갖지 않는다.

* 논리연산자
and : 양쪽 컴포넌트의 조건이 true이면 true를 리턴
or : 한쪽 컴포넌트의 조건만이 true이면 true를 리턴
not : 이후의 조건이 false이면 true를 리턴

* 우선순위 규칙
1) 괄호
2) 수치연산자 > 결합연산자 > 비교연산자
3) is[not] null, like, [not] in > [not] between
4) not > and > or

* 문자형 함수
1) 변환함수
lower : 알파벳값을 소문자로 변환
syntax -> lower(column|expression)
upper : 알파벳 값을 대문자로 변환
syntax -> upper(column|expression)
initcap : 첫번째 글자만 대문자로 변환
syntax -> initcap(column|expression)
2) 문자조작함수
concat : 두 문자열을 연결(합성)        
syntax -> concat(column1|expression1, column2|expression2)
substr : 문자열 중 특정 문자 또는 문자열의 일부분을 선택        
syntax -> substr(column|expression,m,[,n])
length : 문자열의 길이를 구함        
syntax -> length(column|expression)
instr : 명명된 문자의 위치를 구함        
syntax -> instr(column|expression,m[,n])
lpad : 왼쪽 문자자리 채움        
syntax -> lpad(column|expression,n,''''string'''')
rpad : 오른쪽 문자자리 채움        
syntax -> rpad(column|expression,n,''''string'''')
ltrim : 왼쪽 문자를 지움        
syntax -> ltrim(column1|expression1, ''''string'''')
rtrim : 오른쪽 문자를 지움        
syntax -> rtrim(column1|expression1, ''''string'''')
translate : 특정 문자열을 대체        
syntax -> translate(column1|expression1, ''''string1'''', ''''string2'''')
replace : 특정 문자열을 대신        
syntax -> replace(column1|expression1, ''''string1'''', ''''string2'''')

* 숫자형 함수
round : 숫자를 반올림
syntax -> round(column1|expression1, n)
trunc : 숫자를 절삭
syntax -> trunc(column1|expression1, n)
mod : 나머지를 구함
syntax -> mod(column1|expression1, n)
power : 거듭제곱
syntax -> power(column1|expression1, n)
sqrt : 제곱근
syntax -> sqrt(column1|expression, n)
sign : 양수, 음수, 0인지를 구분
syntax -> sign(column1|expression1)
chr : ascii값에 해당하는 문자를 구함
syntax -> chr(column1|expression1)

* 날짜연산
date + number : 결과는 date : 일수를 날짜에 더함
date - number : 결과는 date : 날짜에서 일수를 뺌
date - date : 결과는 일수(숫자) : 어떤 날짜에서 다른 날짜를 뺌
date + number/24 : 결과는 date : 시간을 날짜에 더함

* 날짜함수
months_between : 두 날짜사이의 월수를 계산
syntax -> months_between(date1, date2)
add_months : 월을 날짜에 더함
syntax -> add_months(date1, n)
next_day : 명시된 날짜로부터 다음 요일에 대한 날짜를 나타냄
syntax -> next_day(date1, ''''string''''|n)
last_day : 월의 마지막 날을 계산
syntax -> last_day(date1)
round : 날짜를 반올림
syntax -> round(date1 [,fmt]) -> fmt는 ''''month'''', ''''year''''
trunc : 날짜를 절삭
syntax -> trunc(date1 [,fmt]) 

* 암시적 형변환 (Oracle서버가 자동형변환)
varchar2 or char -> number
varchar2 or char -> date
number -> varchar2
date -> varchar2

* 변환함수
to_char : 숫자나 문자값을 지정한 형식의 varchar2문자열로 변환
syntax1 -> to_char(date, ''''fmt'''') : 날짜를 문자로
syntax2 -> to_char(number, ''''fmt'''') : 숫자값을 문자로
to_number : 숫자를 포함하는 문자열을 숫자로 변환
syntax -> to_number(char) : 숫자를 포함하는 문자열을 숫자로 변환
to_date : 날짜를 나타내는 문자열을 명시된 날짜로 변환
syntax -> to_date(char [,''''fmt'''']) : 날짜를 나타내는 문자열을 명시된 날짜로 변환

* 날짜 형식
scc or cc : 세기(BC날짜에는 -를 붙임)
years indates yyyy of syyyy : 년(BC날짜에는 -를 붙임)
yyy or yy or y : 년의 마지막 3, 2 또는 1자리 수
y,yyy : 콤마가 있는 년
|yyy,|yy,|y,| : ISO표준에 바탕을 둔 4, 3, 2또는 1자리 수
syser or year : 문자고 표현된 년(BC날짜에는 _S를 붙임)
bc or ad : bc/ad 지시자
b.c or a.d : .이 있는 bc/ad지시자
q : 년의 4분의 1
mm : 두자리 값의 월
month : 9자리를 위해 공백을 추가한 월이름
mon : 세자리의 약어로 된 월이름
rm : 로마숫자 월
ww or w : 년이나 월의 주
ddd or dd or d : 년, 월 또는 주의 일
day : 9자리를 위해 공백을 추가한 요일 이름
dy : 세자리 약어로 된 요일 이름
j : Jilian day (bc4713년 12월 31일 이후의 요일 수)

* 시간형식
am or pm : 정오 지시자
a.m or p.m : .이 있는 정오 지시자
hh or hh12 or hh24 : 하루 중 시간(1-12, 0-23)
mi : 분(0-59)
ss : 초(0-59)
sssss : 자정 이후의 초(0-86399)
/ . , : 사용 문자가 결과에 다시 나타남
"문자" : 인용부호내의 문자(들)가 결과에 출력

* 숫자에 영향을 주는 접미사
th : 서수(ddth -> 4th)
sp : 명시한 수(ddsp -> four)
spth or thsp : 명시한 서수(ddspth -> fourth)

* 숫자형식
9 : 9의 수는 출력폭을 지정
0 : 맨 앞에 0을 출력
$ : $ 기호
L : 지역 화폐기호
. : 명시한 위치에 소수점
, : 명시한 위치에 콤마
mi : 우측에 마이너스 기호(음수 값)
pr : 음수를 ()로 묶음
eeee : 과학적인 부호 표기
v : 10을 n번 곱함
b : 0을 0아닌 공백으로 출력

* 기타함수
case, decode : case나 if-then-else-end if 문장의 조건적 조회
1) case
syntax -> case expr when 조건 then 반환값
                             [when 조건 then 반환값]
                              ...
                             else 반환값
               end
2) decode
syntax -> decode(col|expr, search1, result1[,search2, result2, ...][,default])

* 중첩함수 : 여러 단일행함수의 중첩
syntax -> f3(f2(f1()))

* 그룹함수
avg(distinct|all|n) : null값을 제외한 n개 행의 평균값
count(distinct|all|expr|*) : null이 아닌 행의 개수
max(distinct|all|expr) : 최대값
min(distinct|all|expr) : 최소값
stddev(distinct|all|n) : null값을 제외한 n의 표준편차
sum(distinct|all|n) : null값을 제외한 n의 합계
variance(distinct|all|n) : null값을 제외한 n의 분산

* 그룹함수
syntax : select group_function(column) [,group_function(column)...]
            from table_name
            [where condition]
            [ order by column]

* 그룹핑
syntax : select group_function(column) [,group_function(column)...]
            from table_name
            [where condition]
            [group by group_by_expression]
            [having group_condition]
            [ order by column]

* 조인
1) cartesian join : 모든 가능한 행들의 조인 (결과가 의미없는 경우가 대부분)
2) equi join(inner join) : 조인 조건이 정확히 일치하는 경우 사용 (일반적으로 PK, FK사용)
syntax : select table1.column1 [,table2.column2, ...]
            from table1, table2
            where table1.column1 = table2.column2;
3) non-equi join : 조인 조건이 정확히 일치하지 않는 경우에 사용
4) outer join : 조인 조건이 정확히 일치하지 않는 경우에도 모든 행들을 출력
                  조인시킬 값이 없는 쪽에 (+)연산자 적용
syntax : select table1.column1 [,table2.column2, ...]
            from table1, table2
            where table1.column1 = table2.column2(+);
5) self join : 하나의 테이블에서 행들을 조인하고자 할 경우에 사용

* SET연산자
syntax : select *|column1[,column2, column3, ...]
            from table1
            ...
           set operator
            select *|column1[,column2, column3, ...]
            from table2
            ...
           [order by column|expression];
1) union : 각 결과의 합 (합집합:중복되는 값은 한번만 출력)
2) union all : 각 결과의 합 (합집합:중복되는 값 그대로 출력)
3) intersect : 각 결과의 중복되는 부분만 출력 (교집합)
4) minus : 첫번째 결과에서 두번째 결과를 뺌 (차집합)

* 서브쿼리
syntax : select select_list 
            from table
            where expression
            operator
            (select select_list 
            from table
            where expression)

* 서브쿼리에서의 연산자(operator)
1) in : 2개 이상의 값을 리턴하는 서브쿼리에 대해 비교연산자를
       기술하면 에러가 발생. 이런 경우 서브쿼리에서 리턴된 목록의
       각각과 비교하여 쿼리를 수행하는 연산자
2) any : 서브쿼리에서 리턴된 목록의 각각의 값과 비교
3) all : 서브쿼리에서 리턴된 목록의 모든 값과 비교
4) exists: 서브쿼리에서 적어도 1개의 행을 리턴하면 논리식은 참

* 다중열 서브쿼리
syntax : select *|column1[,column2, ...]
            from table
            where (column1, column2 ...) 
            in
            (select column1, column2, ...
            from table
            where condition);

* SQL*PLUS 명령어
a[ppend] text : 현재 편집라인의 끝에 text를 추가
c[hange]/old/new:현재 편집 라인의 old문자를 new문자로 바꿈
del [n] : n라인을 삭제
i[nput] [text] : 현재 편집 라인 다음에 라인을 추가하여 text를 추가
l[ist] [n] : SQL문장을 보여주고, 편집 라인을 이동
n text : n번재 라인을 text로 바꿈
r[un] : buffer에 있는 명령어를 실행한다.(/와 동일)
edit [filename[.ext]] : 지정된 파일의 내용이나 버퍼의 내용을 운영체제의 
                             문자편집기로 불러온다.
sav[e] [filename[.ext]] [rep[lace] | app[end]]
: SQL버퍼의 내용을 파일에 저장하고 기존 파일에 추가하기 위해서 append를, 
기존 파일에 중복해서 쓰려면 replace를 사용한다. 기본적인 파일 확장자는 sql이다.
sta[rt] [filename[.ext]] : 지정된 파일을 수행한다. start라는 명열 대신에 @를 
                                 사용할 수 있다. 파일 확장자가 .sql이 아니면 파일 확장자를 명시
get [filename[.ext]] : SQL버퍼에 파일의 내용을 기록한다. 파일명의 기본적인 확장자는
                             .lis또는 .lst이다
spo[ol] [filename[.ext]] [off | out] : SQL*PLUS의 내용을 파일에 저장
host : sql*plus안에서 호스트 운영체제의 명령어를 실행한다.
! : 운영체제 shell로 나들이
!vi file_name.sql : file_name.sql을 vi편집기로 부름 (unix)

* set : sql*plus의 환경설정
syntax : set 시스템변수 값
- 앞 숫자는 기본값
array[size] {20|n} : 데이터베이스 데이터 패치의 크기를 설정
colsep { | text} : 열 사이에 출력되는 문자를 설정 (디폴트:공백하나)
feed[back] {6|off|on} : 질의가 최소한 n개이어야 row의 수를 출력
hea[ding] {off|on} : 열의 heading을 출력할지의 여부 결정
lin[esize] {80|n} : 라인당 문자의 수
long {80|n} : long값을 출력하기 위해 최대 폭을 설정
pages[ize] {24|n} : page당 line수를 지정
pau[se] {off|on|text} : 화면제어를 함
term[out] {off|on} : 결과를 화면에 출력할지의 여부를 결정
col[umn] [column_option] : 열 포맷을 제어
  syntax : col[umn] [{column|alias} [option]]
  option : cle[ar] : 어떤 열의 형식을 해제
             for[mat] format : 열 데이터의 디스플레이를 변경
             hea[ding] text : 열 헤딩을 설정, 수직 바(|)는 헤딩 라인을 한줄 띄움
             jus[tify] [align] : 열 heading을 정렬(좌, 우, 중간)
            nopri[nt] : 열을 숨김
            nul[l] text : null일때 디스플레이할 텍스트 명시
            pri[nt] : 열을 보여줌
            tru[ncated] : 디스플레이 되는 첫번째 라인의 마지막 문자열을 절삭
            wra[pped] : 문자열이 끝나면 다음 라인으로 이동
tti[tle] [text|off|on] : 리포트의 머리말을 명시
bti[tle] [text|off|on] : 리포트의 꼬리말을 명시
bre[ak] [on report_element] : 중복값을 제거하고 라인 피드로 행들을 단락지음
                                         -> 해제시 clear break
syntax : break on column[|alias|row] [skip n|dup|page] on ... [on report]
            page : break값이 변경될 대 새로운 page로 skip
            skip n : break값이 변경될 때 n만큼 줄을 skip(column, row, page, report)
            duplicate : 중복되는 값을 출력
compute : SQL*PLUS명령어를 이용하여 요약된 계산을 한다.
            해제시 -> clear compute
syntax : compute function of compute_column on break_column
            function : count, num, max, min, sum, avg, std, var중 하나
           compute_column : 계산에 사용되는 column이나 식
           break_column : break명령으로 기술된 column

* 치환변수
& : 리턴되는 데이터를 동적으로 제한, 변수를 인식

* 사용자 변수 정의
1) define variable = value : char데이터형 사용자 변수를 생성하고 값을 할당
2) define variable : 변수, 변수 값, 변수 데이터형을 출력
3) define : 값과 데이터형을 가진 모든 데이터형을 출력
4) accept : 사용자 입력 라인을 읽고 그것을 변수에 저장
  syntax : accept variable [datatype] [for[mat] format] [prompt text] [hide]
              variable : 값을 저장하는 변수의 이름
                            존재하지 않으면 SQL*PLUS가 그것을 생성하여 사용
              datatype : number, char 또는 date, char는 최대 길이 240바이트
                             date는 형식 모델을 다시 검사하고 데이터형은 char
              format : 형식 모델을 명시(예:a10, 9,999)
              text : 사용자가 값을 입력하기 전에 값을 출력
              hide : 사용자 입력을 숨긴다.(예:패스워드)

* 오라클 객체
1) table : 행과 열로 구성된 기본적인 저장 구조
2) view : 하나 이상의 테이블에서 데이터의 부분집합을 논리적으로 표현
3) sequence : 고유한 번호를 자동으로 발생시키는 객체로 주로 PK값 생성에 사용
4) index : 질의(select) 성능을 향상시키기 위하여 사용하는 물리적인 저장 구조
5) synonym : 객체에 대한 이름을 부여

* 테이블 생성
syntax : create table [schema.]table_name
                     (column datatype [default expr] [column_constraint],
                     ...
                     [table_constraint]);

* 오라클 data type
varchar2(n) : 가변길이 문자 데이터 (1~4000byte)
char(n) : 고정 길이 문자 데이터 (1~2000byte)
number(p, s) : 전체 p자리 중 소수점 이하 s자리 (p:1~38, s:-84~127)
date : 7byte(bc4712년 1월1일부터 ad9999년 12월 31일)
long :가변길이 문자 데이터 (1~2Gbyte)
clob : 단일 바이트 가변 길이 문자 데이터 (1~4Gbyte)
raw(n) : n byte의 원시 이진 데이터 (1~2000)
long raw : 가변 길이 원시 이진 데이터 (1~2Gbyte)
blob : 가변 길이 이진 데이터 (1~4Gbyte)
bfile : 가변 길이 외부 파일에 저장된 이진 데이터 (1~4Gbyte)

* constraints (제약)
- primary key(pk) : 유일하게 테이블의 각행을 식별 (not null과 unique 동시 만족)
- foreign key(fk) : 열과 참조된 열 사이의 외래키 관계를 적용하고 설정합니다.
- unique key(uk) : 테이블의 모든 행을 유일하게 하는 값을 가진 열 (null을 허용)
- not null(nn) : 열은 null값을 포함할 수 없습니다.
- check(ck) : 참이어야 하는 조건을 지정함(대부분 업무 규칙을 설정)
1) column level
syntax : column datatype [constraint constraint_name] constraint_type
2) table level
syntax : column datatype,
            ...
            [constraint constraint_name] unique(column1[,column2...])

* subquery를 사용한 테이블 생성
syntax : create table table_name [column1[,column2...]] as subquery

* 테이블 수정 (add 열추가, modify 열수정, drop 열삭제)
syntax : alter table table_name
            add (column datatype [default expr]
            [,column datatype [default expr]...]

* 제약조건 수정 (add 제약추가, drop 제약삭제) : 제약변경은 못함
syntax : alter table table_name
            add [constraint constraint_name] constraint_type (column);
- add자리에 disable, enable로 제약조건활성화 결정할 수 있다.

* 객체 이름 변경
syntax : rename old_name to new_name

* truncate table (테이블 비움:롤백불가)
syntax : truncate table table_name

* 테이블에 주석문 추가
syntax : comment on table table_name | column table.column is ''''text'''';

* 테이블 삭제
syntax : drop table table_name

* DML
insert : 테이블에 새로운 행 추가
syntax : insert into table_name [(column1[, column2, ...])]
            values (value1[, value2 ...]);
update : 테이블의 행 내용을 변경
syntax : update table_name
            set column1 = value1 [,column2 = value2, ...]
            [where condition];
delete : 테이블의 행 삭제
syntax : delete [from] table_name
            [where condition];
merge : 행이 존재하면 update, 새로운 행이면 insert
         merge into table_name as table_alias
         using (table/view/sub_query) as alias
         on (join condition)
         when matched then
                  update set
                  col1 = col1_val1, col2 = col2.val2
         when not matched then
                  insert (column_list) values (column_values);
commit : 저장되지 않은 모든 변경 사항을 Database에 저장
syntax : commit;
savepoint : savepoint 설정
syntax : savepoint name;
rollback : 저장되지 않은 모든 변경 사항을 취소
syntax : rollback [to savepoint name]

* sequence (nextval, currval 사용)
syntax : create sequence sequence_name
                      [increment by n]
                      [start witn n]
                      [{maxvalue n | nomaxvalue}]
                      [{minvalue n | nominvalue}]
                      [{cycle | nocycle}]
                      [{cache | nocache}]
- sequence_name : sequence의 이름입니다.
- increment by n : 정수 값인n으로 sequence번호 사시의 간격을 지정.
                          이 절이 생략되면 sequence는 1씩 증가.
- start with n : 생성하기 위해 첫번째 sequence를 지정.
                     이 절이 생략되면 sequence는 1로 시작.
- maxvalue n : sequence를 생성할 수 있는 최대 값을 지정.
- nomaxvalue : 오름차순용 10^27 최대값과 내림차순용 -1의 최소값을 지정.
- minvalue n : 최소 sequence를 지정.
- nominvalue : 오름차순용 1과 내림차순용 -(10^26)의 최소값을 지정
- cycle | nocycle : 최대 도는 최소갑에 도달한 후에 계속 값을 생성할지의
                           여부를 지정, nocycle이 디폴트.
- cache | nocache : 얼마나 많은 값이 메모리에 오라클 서버가 미리 할당하고
                              유지하는가를 지정. 디폴트로 오라클 서버는 20을 cache

* sequence 수정
syntax : alter sequence sequence_name
            ... (생성과 같다)

* sequence 제거
syntax : drop sequence sequence_name

* view 생성 (simple view)
syntax : create [or replace] [force|noforce] view view_name [(alias[,alias, ...])]
            as subquery
            [with check option [constraint constraint_name]]
            [with read only]
- or replace : 이미 존재한다면 다시 생성한다.
- force : base table유무에 관계없이 view를 만든다.
- noforce : 기본 테이블이 존재할 경우에만 view를 생성한다.
- view_name : view의 이름
- alias : subquery를 통해 선택된 값에 대한 컬럼명이 된다.
- subquery : select 문장을 기술한다.
- with check option : view에 의해 엑세스 될 수 있는 행만이 입력, 갱신될 수 있다.
- constraint : check option 제약 조건에 대해 지정된 이름이다.
- with read only : 이 view에서 DML이 수행될 수 없도록 한다.

* view 생성 (complex view)
syntax : create [or replace] [force|noforce] view view_name [(alias[,alias, ...])]
            as subquery 
            [with check option [constraint constraint_name]]
            [with read only]             
- complex view에서는 둘이상의 테이블이 조인되어 view에 나타난다.

* view 제거
syntax : drop view view_name;

* inline view : from절에 subquery를 사용

* top-n analysis
syntax : select [column_list], rownum
            from (select [column_list] from table order by top-n_column)
            where rownum <= n;

* index 종류
- unique index : 지정된 열의 값이 고유함을 보장
- non-unique index : 데이터를 검색할 때 가장 빠른 결과를 보장
- single column index : 하나의 열만 인덱스에 존재
- composite index : 여러 열을 결합하여 하나의 인덱스를 생성(16개의 열까지)

* index 생성 (사용자정의)
syntax : create index index_name
            on table_name (column1[,column2, ...]);

* index 제거
syntax : drop index index_name;

* synonym 생성
syntax : create [public] synonym synonym_name 
            for object_name;

* synonym 제거
syntax : drop [public] synonym synonym_name;

* user 생성
syntax : create user user_name
            idntified by password;

* 권한부여
syntax : grant system_privilege1[,system_privilege2, ...]
            to user_name1[,user_name2, ...]
            [with admin option]
- with admin option을 주면 부여받은 권한을 다시 부여할 수 있다.

* 권한제거
syntax : revoke system_privilege1[,system_privilege2, ...] | role1[,role2, ...]
            from {user1[,user2, ...] | role1[,role2 ...] | public};

* Role 생성 : Role은 권한(privilege의 모임)
syntax : create role role_name;

* Role 부여
syntax : grant role_name to user_name; 


저작자표시 비영리 변경금지
블로그 이미지

낭만가을

,

[Oracle] Tablespace(테이블스페이스)란?

데이터베이스/오라클 튜닝 2018. 5. 21. 00:36

테이블을 저장하는 공간이란 의미도 틀린것은 아니지만 정확한 의미는 아닙니다.

 

오라클은 데이터베이스 관리 시스템이고 말 그대로 데이터들을 관리합니다. 즉 어딘가에 데이터들을 저장, 추출, 삭제, 변경하는 작업을 할 수 있는 것입니다. 그렇다면 데이터는 어디에 저장되는 것일까요? 물론 파일에 저장됩니다.

 

오라클 데이터베이스는 데이터 파일들을 가지고 있으며, 이 파일들에 데이터가 저장됩니다. 그런데 파일은 데이터가 저장되는 물리적인 공간을 말하는 것입니다.

 



 

 

오라클 내부에서는 데이터 블록(data block), 익스텐트(extent), 세그먼트(segment), 테이블스페이스(tablespace)라는 논리적인 개념으로 데이터 들을 관리합니다.

 

오라클에서 데이터를 저장하는 가장 최소의 논리적인 단위가 데이터 블록이며, 데이터 블록이 모여서 익스텐트가 되고, 익스텐트가 모여서 세그먼트, 세그먼트가 모여서 테이블 스페이스가 됩니다. 실제로 물리적인 데이터 파일(확장자가 dbf 혹은 ora인 파일)은 테이블 스페이스와 대응됩니다.

 

하나의 테이블 스페이스는 최소 1개의 데이터파일로 구성됩니다.

 

테이블 이외에도 인덱스, 프로시저, 뷰 등 다른 여러 가지 오라클 객체들이 저장됩니다. 

저작자표시 비영리 변경금지

'데이터베이스 > 오라클 튜닝' 카테고리의 다른 글

[오라클파] 파티션 테이블  (0) 2018.06.13
[오라클]테이블 스페이스와 데이터파일  (0) 2018.06.13
[오라클] SQL 튜닝 시작하기  (0) 2018.05.21
[Oracle] Select 쿼리 튜닝 순서  (0) 2018.05.21
블로그 이미지

낭만가을

,

[오라클] SQL 튜닝 시작하기

데이터베이스/오라클 튜닝 2018. 5. 21. 00:32

SQL 튜닝은 "SQL + 튜닝"입니다. 즉, SQL 튜닝이란 튜닝 대상이 되는 SQL을 이해하고, SQL이 가진 정보(테이블/인덱스/컬럼의 정보 및 업무적 성격 등)를 치밀하게 분석하여 얻어지는 결과라고 생각합니다. SQL 튜닝을 시작하기 위해서는 SQL에 대한 이해가 선행되어야 한다고 생각합니다. 왜냐하면, SQL의 작성형태에 따라 다양한 성능 문제가 발생되기 때문입니다.

 

SQL 튜닝의 시작은 SQL의 의미(작성 의도)를 제대로 파악하는 것입니다. SQL의 의미를 정확히 파악하지 못한다면, 원본 SQL에서 추출하고자 했던 결과 집합이 아닌 다른 집합을 추출하게 될지도 모릅니다. 이러한 개선안은 개선안이라고 할 수 없습니다. 즉, 원본 SQL의 작성 의도를 제대로 파악하지 않고, 단순히 I/O 발생량을 줄인다거나 수행시간을 단축하기 위해 힌트를 남발하는 것은 상당히 위험한 일입니다. 그러므로 SQL의 의미를 파악하는 것이 성능 문제를 유발하는 SQL에 대한 개선의 시작이라 말할 수 있습니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT *
FROM  (
  SELECT /*+ INDEX_DESC(A IDX_MOX_SENDDATE) */
    a.*,
    ROWNUM as rnum
  FROM   tbs_mbox a
  WHERE userid = :b1
    AND status = :b2
    AND ROWNUM <= :b3
  )
WHERE rnum >= :b4;
 
SELECT STATEMENT - CHOOSE- COST ESTIMATE:3
VIEW
  COUNT STOPKEY
  TABLE ACCESS BY INDEX ROWID :imsi.tbs_mbox
  INDEX RANGE SCAN DESCENDING :imsi.idx_mbox_senddate

 

해당 SQL은 메일 프로그램에서 사용되며, 동시 간대(특히, 업무 시작 시간, 09:00)에 다수의 다용자가 사용하는 특성이 있습니다. 뿐만 아니라, 데이터를 추출하는 과정에서 발생하는 경합으로 인해 DB 서버 전반적인 성능 저하를 유발하는 악성 SQL입니다.

 

해당 SQL은 INDEX_DESC 힌트와 ROWNUM 조건으로 인해 COUNT STOPKEY의 실행계획이 수립되었습니다. 따라서 전체 데이터를 모두 처리하지 않고, RONUM, 조건에 해당하는 일부의 데이터만 처리할 것으로 예상됩니다. 즉, 실행계획 상으로는 SQL의 문제점을 찾기가 쉽지 않습니다. 따라서 인덱스 구성과 힌트 사용이 적절한지에 대한 검토가 필요합니다.

 

INDEX_NAME          COLUMN LIST
-----------------   -----------------
idx_mbox_status     userid, status
idx_mbox_senddate   userid, senddate

 

인덱스 구성만 보면, 해당 SQL은 IDX_MBOX_SENDDATE 인덱스보다는 USER_ID, STATUS 컬럼으로 구성된 IDX_MBOX_STATUS 인덱스를 사용하는 것이 유리해 보입니다.

 

그런데 조건 절에 적합한 인덱스 구성만 확인하고, 해당 SQL의 의미를 파악하지 않은 채 성능 개선을 하려고 한다면, 잘못된 개선안을 도출할 가능성이 높습니다.

 

INDEX_DESC 힌트의 인덱스 명만을 변경하는 것으로 결론을 도출한다면, 아주 치명적인 실수를 저지를 수 있습니다. 왜냐하면, SQL의 작성 의도와는 전혀 다른 데이터가 추출될 수도 있기 때문입니다. 물론, 경우에 따라 USERID, STATUS로 구성된 인덱스를 이용하는 것이 올바른 방법일 수도 있습니다. 그러나 이와 같은 개선안은 "USER_ID, SENDDATE 컬럼으로 정렬이 필요없다"는 전제 조건을 반족해야 합니다. 이러한 업무적인 내용은 개발 담당자 또는 업무 운영자에게 문의할 필요가 있으며, 그렇지 못한 경우라면 SQL 자체에서 작성 의도를 찾아봐야 합니다. 그렇다면, 해당 SQL의 작성 의도를 파악해보겠습니다.

 

- ROWNUM을 사용하였으므로 PAGINATION QUERY일 가능성이 높다.

- INDEX_DESC 힌트는 ORDER BY DESC를 대체하려는 것으로 보인다.

- 인덱스 컬럼 정보와 힌트를 고려해 보았을 때, 의미사응로는 "ORDER BY USER_ID DESC, SEND_DATE DESC"가 내포된 것으로 보인다.

 

앞에서 파악한 SQL의 의미를 통해, 추출하고자 하는 데이ㅏ터는 조건에 만족하는 "최근 발송된" ROWNUM <= :B3개의 메일 중에서 RNUM >= :B4인 메일이라는 것을 알 수 있습니다. 즉, INDEX_DESC 힌트에 내포된 의미를 풀어보면 다음과 같습니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT x.*
FROM (
  SELECT a.*,
    ROWNUM AS RNUM
  FROM (
    SELECT a.*
    FROM tbs_mbox a
    WHERE userid = :b1
      AND status = :b2
    ORDER BY a.userid DESC,
      a.senddate DESC
        ) a
  WHERE ROWNUM <= :b3
     ) x
WHERE
  x.rnum >= :b4

 

재작성한 SQL을 보면, 원본 SQL에서 INDEX_DESC 힌트의 인덱스 명만을 변경하는 것은 올바른 튜닝 방법이 아니라는 것을 알 수 있습니다. 왜냐하면, INDEX_DESC 힌트에 사용된 IDX_MBOX_SENDDATE 인덱스는 조건에 대한 처리뿐만 아니라 ORDER BY DESC  역할도 수행하기 때문입니다. 따라서 SQL의 의미를 파악한 후에 도출될 수 있는 개선안 중의 하나는 IDX_MBOX_SENDDATE 인덱스에 STATUS 컬럼을 추가하여 재 생성하는 것입니다. 이와 같이 SQL 튜닝을 위해서 가장 필요한 것은 SQL을 정확하게 이해하는 것입니다. SQL의 의미를 파악해야만 올바른 SQL 개선안이 도출될 뿐만 아니라, 때로는 SQL의 의미 파악 자체가 SQL 개선으로 이어지는 경우도 있기 때문입니다.

 

개발자나 DBA 들 중의 일부는 SQL 튜닝의 세계에 뛰어드는 것을 매우 어려워합니다. 왜냐하면, "SQL 튜닝"이란 용어 자체가 친근하지 않을 뿐 아니라 SQL 튜닝의 시작점을 정하기도 쉽지 않기 때문입니다. SQL의 의미를 해석하는 것으로부터, 이미 SQL 튜닝이 시작됩니다. 


저작자표시 비영리 변경금지

'데이터베이스 > 오라클 튜닝' 카테고리의 다른 글

[오라클파] 파티션 테이블  (0) 2018.06.13
[오라클]테이블 스페이스와 데이터파일  (0) 2018.06.13
[Oracle] Tablespace(테이블스페이스)란?  (0) 2018.05.21
[Oracle] Select 쿼리 튜닝 순서  (0) 2018.05.21
블로그 이미지

낭만가을

,

[Oracle] Select 쿼리 튜닝 순서

데이터베이스/오라클 튜닝 2018. 5. 21. 00:29

쿼리 튜닝은 온라인 SQL이냐 대용량 배치 SQL이냐에 따라 튜닝방법이 달라집니다. 하지만 대용량 배치는 프로그램 수가 많지 않은 편입니다. 온라인 SQL 튜닝에서도 관점에 따라 튜닝방법이 다르게 됩니다. 예를 들어 Peak Time에 Insert 문이나 Update 문, Select 문이 집중적으로 몰릴 때의 튜닝 방법이 있고, 단순히 SQL 하나에 집중해서 응답시간을 최소화하는 튜닝방법이 있습니다. 본 포스팅은 일반적으로 가장 많은 튜닝 사례에 해당하는 Select문 튜닝방법론을 기술한 포스팅입니다. 학습 용도로 작성한 포스팅으로 본 포스팅의 원본 출처는 Science of Database 블로그 SQL 튜닝방법론 입니다.




온라인 Select문 튜닝 방법론

온라인 SQL의 튜닝방법은 여러 가지가 있을 수 있습니다. 하지만 그 중에서 가장 기초적이고, 기본적인 방법입니다. 아래의 7가지 항목을 점검하고 약한 곳을 보강하면 됩니다. 이글은 SQL 튜닝책을 두 권정도 본 사람들을 위한 것입니다. 튜닝에 자신있는 사람들은 볼 필요가 없습니다.


1. 적절한 인덱스를 사용하여 Block I/O를 최소화 하라

조인이 없는 경우는 적절한 인덱스를 사용하는 것만으로도 상당한 효과를 볼 수 있습니다. 조인이 있는 경우는 특히 Driving(선행) 집합에 신경을 써야합니다. 왜냐하면 Nested Loop 조인을 사용했고, 선행집합의 건수가 많다면, 후행집합의 조인의 시도횟수가 증가하므로 성능이 느려지게 됩니다. 따라서 적절한 인덱스를 이용하여 선행집합의 건수를 줄인다면, 혹은 가장 적은 집합을 선행으로 놓는다면, 후행집합으로의 조인건수는 줄어들게 됩니다. 물론 이때에도 후행집합의 적절한 인덱스는 필수 조건입니다. Driving 집합의 Block I/O를 줄이기 위하여 최적화된 인덱스가 없다면 생성하고, 있다면 그것을 사용하십시오. 다시 말해 최적의 Access Path를 만드는 것이 좋습니다.  


운영중인 시스템이라면 최적의 Access Path를 위해 인덱스를 변경하거나 생성할 때는 주의해야 합니다. 현재 튜닝하고 있는 SQL에 최적화된 인덱스를 생성하더라도 다른 SQL에 악영향을 줄 수 있기 때문입니다. 인덱스를 생성하거나 변경할 때는 그 테이블을 사용하는 다른 SQL의 실행계획이 변경되지 않는지 각별히 신경써야 합니다. 이런 이유 때문에 개발과정에서 효율적인 인덱스 설계가 중요시 됩니다.


2. 조인방법과 조인순서를 최적화 하라.

온라인에서 사용하는 Select문은 좁은 범위를 검색하는 경우가 많습니다. 이럴때는 대부분 Nested Loop Join이 유리합니다. 그러므로 조인건수가 소량인 SQL에 Hash Join이나 Sort Merge Join이 발견되면 Nested Loop Join으로 변경하는 것이 더 유리한지 검토해야 합니다. 물론 여기서도 Nested Loop 조인에 관해서만 다룹니다.


Nested Loop 조인에서 가장 중요한 것은 조인 순서입니다. From절에 테이블(집합)이 두 개라면 후행집합의 관점에서는 적절한 인덱스만 존재한다면 그것으로 족합니다. 만약 From절에 테이블(집합)이 세 개 이상이라면 조인순서를 변경할 수 있는지에 대한 두 가지 원리를 사용하면 좋습니다. 두 가지 원리는 아래의 단락에서 소개됩니다. 아무리 조인할 집합이 많다고 하더라도 이 두 가지의 원리는 동일하게 적용될 수 있습니다. 두 가지 원리를 이용할 때 필요하다면 Leading 힌트를 사용해야 합니다.


첫번째, 후행집합에 적절한 인덱스가 없는 경우에 조인순서를 바꾸면, 최적의 인덱스를 사용할 수 있는 경우가 많습니다. 예컨대, 튜닝전의 조인순서가 A → B → C라고 하면, 중간 집합인 B에 적절한 인덱스가 없고 오히려 C에 적절한 인덱스가 존재하는 경우가 있습니다. 이럴 때는 B에 인덱스를 무작정 생성하지 말고, 조인순서를 A → C → B로 바꿀 수 있는지, 바꾸는 것이 더 효율적인지 검증하면 좋습니다. 조인 순서만 바꿔주어도 일량이 획기적으로 줄어드는 경우가 많습니다. 만약 조인순서를 바꿀 수 없거나, C를 중간집합으로 하는 것이 비효율적이라면, B를 중간집합으로 유지하고 적절한 인덱스를 사용해야 합니다.


두번째, 조인되는 집합 중 특정 인덱스에서 Block I/O가 증가하는 경우에 조인순서의 변경을 검토하면 됩니다. 이때 10046 Trace나 DBMS_XPLAN.Display_Corsor를 이용하면 조인집합들의 Block I/O량을 관찰할 수 있습니다. 예를 들어, 튜닝전에 조인순서가 A → B → C라고 하고, 집합 B에서 Block I/O량이 증가하면 A → C → B로 바꾸면 일량이 줄어드는 경우가 많습니다. C를 먼저 조인(Filter)하여 선행집합(B의 입장에서는 C가 선행이다)의 건수를 줄이고 B에 조인하면 성능이 향상됩니다.


3. Table Access(Random Access)를 최소화하라.

Random Access란 rowid로 테이블을 액세스하는 것을 말합니다. 1번과 2번을 최적화했다면 Random Access도 자동으로 많이 줄어들었을 것입니다. 하지만 그것이 끝은 아닙니다. 여전히 성능이 만족스럽지 못한다면 Random Access 횟수를 줄이는 것을 간과해서는 안됩니다.


인덱스를 사용하면 rowid가 자동으로 획득됩니다. 만약 인덱스에 없는 칼럼을 Select 해야 한다면 rowid로 테이블을 액세스 해야합니다. 이때 테이블로액세스해야 할 건수가 많고, 인덱스 컬럼순으로 테이블이 sort되어 있지 않다면 성능이 매우 저하됩니다. 왜냐하면 테이블이 인덱스 기준으로 sort되어 있지 않기 때문에 테이블을 방문할 때마다 서로 다른 블럭을 읽어야 하기 때문입니다.


비유적으로 설명해보겠습니다. 우리가 심부름을 할 때 세 군대의 상점(A, B, C)을 들러야 한다고 치겠습니다. 그 상점들이 모두 한 건물 내부에 존재한다면 얼마나 좋겠습니까? 그 심부름은 매우 빠른 시간에 끝날 것입니다. 하지만 반대로 상점 A는 부산에 있고 상점 B는 대구에 있고, 상점 C는 서울에 있다면? 만약 당신의 성격이 매우 좋아서 그 심부름을 한다고 해도 시간이 많이 걸릴 것입니다. Random Access도 마찬가지 입니다. 인덱스의 rowid로 테이블을 방문할 때 테이블이 인덱스기준으로 sort되어 상점처럼 다닥다닥 붙어있다면 성능은 매우 빠르고, 흩어져 있을수록 성능은 느려집니다. (오라클에서는 테이블이 인덱스 기준으로 sort되어 있는 정도를 Clustering Factor라고 합니다) 바로 이런 이유 때문에 index scan 보다는 Table Scan이 느린 것입니다. 따라서 우리는 Random Access의 부하를 최소화해야 합니다.


Random Access의 부하를 줄이는 방법은 네가지 입니다. 첫번째, 테이블의 종류를 변경하는 방법입니다. IOT나 클러스터를 이용하면 Clustering Factor가 극단적으로 좋아집니다. 또한 파티션을 이용하면 같은 범위의 데이터를 밀집시킬 수 있습니다. 두 번째, 효율적인 인덱스를 사용하거나 조인방법과 순서를 조정하여 Table Access를 최소화하는 방법입니다. 이 방법은 1번과 2번에서 이미 설명되었습니다. 세번째, 인덱스에 칼럼을 추가하여 Table Access를 방지하는 방법입니다. 예를 들어 Select절의 특정 칼럼 때문에 테이블이 액세스 된다면, 인덱스의 마지막에 그 컬럼을 추가하면 됩니다. 네 번째, 인덱스만 액세스하고 테이블로의 액세스는 모든 조인을 끝내고 마지막에 시도하여 Random Access의 횟수를 줄이는 방법입니다.



4. Sort나 Hash 작업을 최소화하라.

1,2,3번을 통하여 최적의 Access Path와 Join을 사용했다면, Block I/O의 관점에서는 튜닝이 끝난 것입니다. 하지만 1,2,3번이 모두 해결되었다 해도 Order by나 Group By 때문에 성능이 저하될 수 있습니다. 특히 결과가 많은 경우, sort는 치명적입니다.


인덱스가 sort 되어 있다는 특성을 이용하면 order by 작업을 대신할 수 있습니다. Group By도 sort가 발생하는데 group by 단위와 인덱스의 컬럼이 동일하다면 sort는 발생하지 않습니다. 최적의 인덱스를 사용하면 Access Path를 개선하는 효과뿐만 아니라 Sort의 부하도 없어집니다.


Union All을 제외한 집합연산(Union, Minus, Intersect)를 사용하면 Sort Unique 혹은 Hash Unique가 발생하게 됩니다. Union은 Union All로 바꿀수 없는지 검토해야 하고, Minus는 Not Exists 서브 쿼리를 이용하여 Anti Join으로 바꿀 수 없는지 고려해야 합니다. Intersect는 교집합이므로 조인으로 바꿀 수 있는지 검토해야 합니다. 아주 가끔 Distinct를 사용한 SQL이 눈에 띄는데 이 또한 Sort unique 혹은 Hash Unique를 발생시킵니다. 모델러나 설계자의 문의하여 Distinct를 제거할 방법이 없는지 문의해야 합니다.


Oracle 10g 부터는 Hash Group By가 발생할 수 있는데, 이미 적절한 인덱스를 사용하는 경우라면 Hash Group By를 사용할 필요는 없습니다. 이런 경우 NO_USE_HASH_AGGREGATION 힌트를 사용하면 Sort Group By 로 바꿀수 있습니다. 이렇게 해주면 실행계획에 "SORT GROUP BY NOSORT" Operation이 발생하며, Sort나 Hashing 작업이 전혀 발생하지 않습니다. Group By의 부하를 해결하는 또 하나의 방법은 스칼라 서브쿼리를 사용하는 것입니다. 조인을 사용하면 Sum 값을 구하기 위해 Group By가 필수적입니다. 하지만 스칼라 서브쿼리를 사용하면 Group By를 사용하지 않고도 sum이나 Min/Max 값을 구할 수 있습니다. 또한 분석함수의 Ranking Family(rank, dens_rank, row_number)를 최적화된 인덱스와 같이 사용하면 Group By나 Sort를 하지 않고도 Min/Max 값을 구할수 있습니다. 이때는 실행계획에 "WINDOW NOSORT" Operation이 발생합니다.



5. 한 블록은 한번만 Scan하고 끝내라.

같은 데이터를 반복적으로 Scan하는 SQL이 의외로 많습니다. 대표적인 경우가 Union All로 분리되었지만 실제로는 그럴 필요가 없는 경우입니다. 예를 들어 Where 절에 구분코드가 1일 때, 2일 때, 3일 때 별로 SQL이 나누어져 있는 경우 입니다. Where 절을 구분코드 in (1, 2, 3)으로 처리하고, Select절에서 Decode나 Case 문을 사용하여 구분코드별로 처리해 준다면 Union All은 필요 없습니다. Unon All을 사용하는 또 한가지의 경우는 Sub Total(소계)와 Grand Total(총계)를 구해야 하는 경우입니다. 이 경우도 Rollup/Cube나 Grouping Sets를 Group By절에 사용한다면 소계나 총계를 위한 별도의 Select문을 실행시킬 필요는 없습니다. 1~4번의 과정은 SQL문의 변경이 없거나 최소화됩니다. 하지만 5번의 경우는 SQL을 통합시켜야 하기 때문에 시간이 많이 소모되며, 많은 사고가 요구되는 창조적인 과정입니다. 여기까지 했다면 진행되었다면 원본 SQL 자체의 튜닝은 완료된 셈입니다.



6. 온라인의 조회화면이라면 페이징 처리는 필수다.

부분범위 처리를 해야합니다. 물론 전체 건을 처리해야 하는 경우는 있을 것입니다. 하지만 조회화면이라면 몇 십만건 혹은 몇 만건이나 되는 결과를 모두 볼 수 없습니다. 따라서 볼 수 있는 단우로 끊어서 출력해야 합니다. 예를 들어 결과 건수가 10만건이라고 해도 최초의 50건을 화면에 먼저 뿌린다면 1,2,3,4 번에서 설명했던 모든 부하(Block I/O의 부하, 조인의 부하, Random Access의 부하, Sort의 부하)를 한꺼번에 감소시킬 수 있습니다. 따라서 가능하면 개발자를 설득하거나 책임자를 설득하여 페이징처리를 하는 것이 바랍직합니다.


페이징 처리를 해도 효과를 볼 수 없는 몇가지 예외가 있습니다. 분석함수를 사용하건, Connect By + Start With를 사용한다면 페이징 처리의 효과는 없습니다. 분석함수의 경우 인라인뷰의 외부로 뺄 수 있다면 부분범위 처리가 가능합니다. Connect By + Start With를 사용한 경우는 부분범위처리가 불가능합니다. 인덱스의 구성을 적절히 하여 Sort를 대신해야 합니다.



7. 답이 틀리면 안된다. SQL을 검증하라

7번은 SQL 자체를 튜닝하는 것은 아닙니다. 하지만 7번을 튜닝 방법에 추가한 이유는 있습니다. 튜닝을 하였음에도 답이 틀린다면, 튜닝을 하지 않은 것보다 못합니다. 그러므로 튜닝 후에 답이 옳은지 항상 검증해야 합니다. 1번~7번 중에 가장 중요한 것이 7번입니다.



방법론 정리

1. 적절한 인덱스를 사용하여 Block I/O를 최소화하라.

2. 조인방법과 조인순서를 최적화 하라.

3. Table Access(Random Access)를 최소화하라.

4. Sort나 Gash 작업은 최소화하라.

5. 한 블록은 한번만 Scan하고 끝내라.

6. 온라인의 조회화면이라면 페이징처리는 필수이다.

7. 답이 틀리면 안된다. SQL을 검증하라.



만약 1~7번을 모두 적용할 수 있는 경우임에도 불구하고 하나라도 빠진다면 그것은 최적화된 SQL이 아닙니다. 물론 튜닝을 할때 위의 1~6번을 항상 적용할 수 있는 것은 아닙니다. 경우에 따라서는 하나만 적용될 수도 있고, 두 개만 적용할 수 있는 SQL도 있습니다. 하지만 1~6을 모두 적용할 수 있는지 꼼꼼히 살펴야 합니다.


위 항목들은 튜닝의 기본 중에 기본입니다. 이것들만 알아도 온라인 조회화면에 사용하는 SQL을 튜닝하는데 어려움이 없을 것입니다. 다시 말해 90%는 해결할 수 있습니다. 그렇다면 나머지 10%는 상황별로 다르게 처리돕니다. 또한 그것들은 책이나 매뉴얼에 나와있지 않기 때문에 경험치이거나 실험과 연구의 결과로 알아내는 것들입니다.

저작자표시 비영리 변경금지

'데이터베이스 > 오라클 튜닝' 카테고리의 다른 글

[오라클파] 파티션 테이블  (0) 2018.06.13
[오라클]테이블 스페이스와 데이터파일  (0) 2018.06.13
[Oracle] Tablespace(테이블스페이스)란?  (0) 2018.05.21
[오라클] SQL 튜닝 시작하기  (0) 2018.05.21
블로그 이미지

낭만가을

,

오라클 12 에서 scoot,hr 을 사용할수 있는 방법

데이터베이스/문제해결 2017. 1. 22. 02:35

아래 동영상을 보고 따라서 설치하자 






저작자표시 비영리 변경금지

'데이터베이스 > 문제해결' 카테고리의 다른 글

Listener refused the connection with the following error 해결  (0) 2017.01.21
블로그 이미지

낭만가을

,

Listener refused the connection with the following error 해결

데이터베이스/문제해결 2017. 1. 21. 01:16

Listener refused the connection with the following error : 

ORA-12505, TNS: listener does not currently know of SID given in connect descriptor



뭐 딱봐도 리스너 접근이 거부되어 에러가 뜨는건 알겠습니다.

SID가 서로 안 맞는것도 알거 같고...

처음 경험하는거라 어쩔줄 몰라 헤맸습니다.



그래도 역시 검색을 하면 정답은 나오네요.

일단 아래 방법대로 해도 안된다면 설치에오류가 생겼다고 생각하시고

재설치 하시는게 좋을 것 같아요.




■  CMD에 명령어 " Lsnrctl services "입력



시작 → 실행 → CMD 를 실행시킨 후 Lsnrctl services 를 입력합니다.

리스너를 제대로 잡아주는 역활을 하는것 같습니다.

만약에 초록색 상자처럼 화면이 안 뜬다면 설치하는 과정에서 문제가 생긴거라고 하네요.





그리고  [ " ~~~ " 서비스는 인스턴스를 가집니다. ]

이 부분이 SID에 해당하는 부분입니다. 

orcl 을 확인하고 개발툴로 가보겠습니다.





■  SQL Developer 에서 접속정보 확인


음..  역시 SID가 다르네요.

왜 설치할때는 orcl로 설치하라 해놓고 실행시키니 xe로 접속시도하다니..

아무튼 1시간동안 헤맨 이유는 요것 때문이였습니다.




얼른 바꿔줘야겠죠??

CMD에서 확인했던 SID인 orcl을 입력하고 비밀번호를 입력한뒤 테스트를 해보았더니

기분좋게 성공이라고 떴습니다!!

역시 오류를 해결하는 기분은 최고에요.




접속을 눌렀더니 이제 아무런 오류도 뜨지않고 

정상적으로 동작이 잘됩니다.



출처: http://hunit.tistory.com/211 [Hun IT Blog]

저작자표시 비영리 변경금지

'데이터베이스 > 문제해결' 카테고리의 다른 글

오라클 12 에서 scoot,hr 을 사용할수 있는 방법  (0) 2017.01.22
블로그 이미지

낭만가을

,

공지사항

글 보관함

달력

«   2024/10   »
일 월 화 수 목 금 토
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31

티스토리툴바