Jun, Log

ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

위와 같은 에러 발생은 SELECT문의 데이터 추출시 한 행에 2개 이상의 데이터가 나오는 경우입니다. 단일 테이블의 데이터 추출 시에는 이런 에러를 볼 수 없습니다. 하지만 SELECT문의 서브쿼리를 사용하게 되면 한 행에 2개의 데이터가 나올 수 있습니다.

예를 들어 다음 쿼리는 고객과 고객의 등급을 조회하는 쿼리입니다. (정규화 하지 않은 예제입니다.)

SELECT A.CLOSE_MONTH,
       A.USER_NAME,
       (SELECT B.GRADE
          FROM USER_VIP B
         WHERE B.USER_NAME = A.USER_NAME
       ) as grade
FROM USER_SHOP A
;

SELECT 문에 서브쿼리를 사용하게 되면 하나의 유저네임에 여러개의 등급이 서브쿼리로 나오게 되어 2개 이상의 행이 리턴되었다는 에러가 발생합니다. 에러 발생 시 2개 이상 행이 추출되는지 확인을 하시면 되겠습니다.

팁을 하나 드리자면 GROUP BY 를 이용하여 어떤 유저가 여러개 등급이 나오는지 확인 하면 되는데, 서브쿼리 내 복잡하게 조인이 되어 있는 경우도 있습니다. 이때 빠르게 어떤 유저로 인한 에러가 발생하는지 확인을 하는 방법은 서브쿼리에 CURSOR를 씌어주면 한행에 2개, 3개가 추출되는 값들을 에러 없이 확인 하실 수 있습니다.

테이블 생성 시 ,생성한 테이블은 하나지만(논리적 테이블은 1개) 물리적으로 테이블을 분리 할 수 있습니다. 이때 사용 되는 기술이 파티션 테이블입니다. 테이블을 분리한다는 말은 특정 컬럼을 기준으로 물리적으로 데이터를 분리하여 저장한다는 것 입니다. 예를 들어 날짜 컬럼 기준으로(ex)2017년(600만건) / 2018년(700만건) / 2019년(70만건)) 각각 테이블을 물리적으로 나누어 저장 할 수 있습니다. 어떤 이점이 있냐면 데이터 조회 시 2019년도 데이터는 2019년도 테이블만 바라보게끔 하여 전체 데이터를 조회하는 것이 아닌 70만건 만 조회하기 때문에 성능을 향상 시킬 수 있습니다. 또한 파티션 테이블의 강점은 데이터 삭제 할 때 더욱 큰 강점을 보이기도 합니다.

ms-sql 같은 경우는 파티션 테이블을 사용하기가 지인을 통해서 굉장히 까다롭다고 들었습니다. 하지만 오라클에서는 아래 구문을 통해서 쉽게 파티션 테이블을 생성 할 수 있습니다. 다음 예제를 보도록 하겠습니다.

CREATE TABLE SHOP_USER (
    CLOSE_MONTH VARCHAR2(6) NOT NULL,
    CLOSE_DATE VARCHAR2(8) NOT NULL,
    SEQ VARCHAR20(20) NOT NULL,
    USER_NAME VARCHAR2(10),
    POINT_MONEY NUMBER(15)
)
PARTITION BY RANGE(CLOSE_MONTH)
(
    PARTITION SHOP_USER_2012 VALUES LESS THAN ('201301') TABLESPACE DATA_USER,
    PARTITION SHOP_USER_2013 VALUES LESS THAN ('201401') TABLESPACE DATA_USER,
    PARTITION SHOP_USER_2014 VALUES LESS THAN ('201501') TABLESPACE DATA_USER,
    PARTITION SHOP_USER_2015 VALUES LESS THAN ('201601') TABLESPACE DATA_USER,
    PARTITION SHOP_USER_2016 VALUES LESS THAN ('201701') TABLESPACE DATA_USER,
    PARTITION SHOP_USER_2017 VALUES LESS THAN ('201801') TABLESPACE DATA_USER,
    PARTITION SHOP_USER_ETC VALUES LESS THAN (MAXVALUE) TABLESPACE DATA_USER
);

정산월(CLOSE_MONTH) 기준으로 테이블을 나눴습니다. 2012년 데이터는 SHOP_USER_2012 테이블에 들어가고 2013년 데이터는 SHOP_USER_2013 테이블에 값이 들어갑니다. 2018년 데이터 부터는 SHOP_USER_ETC 테이블에 값이 들어 가겠습니다. TABLESPACE를 명시하지 않으면 DB세팅시 디폴트 테이블 스페이스에 생성이 되겠습니다. 하나 명심해야 될 것은 파티션을 나누고 각각의 파티션 네임을 선언하고 데이터가 각각 들어가지만, 실제 사용은 SHOP_USER 테이블 하나만 사용 하는 것입니다. 데이터 조회 시 어떤 파티션 테이블 조회하는지 확인을 하고 싶을 때는 실행계획을 확인하여 알 수 있습니다.

오라클의 제약조건이란 테이블 생성 시 필요의 따라 NOT NULL, 기본키, UNIQUE, 외래키, CHECK 제약조건을 걸어 줄 수 있다. 제약조건은 데이터의 무결성(데이터의 일관성 및 정확성)을 유지하기 위해서 제약조건을 걸어준다. 각각의 제약조건의 대해서 살펴 보자. (테이블 생성이 반복될 수도 있지만 각각의 차이점을 보고 익숙해지기 위해서 계속 눈여겨 보도록 하자.)

NOT NULL

CREATE TABLE SHOP_USER (
    CLOSE_MONTH VARCHAR2(6) NOT NULL,
    CLOSE_DATE VARCHAR2(8) NOT NULL,
    SEQ VARCHAR20(20) NOT NULL,
    USER_NAME VARCHAR2(10),
    POINT_MONEY NUMBER(15)
) TABLESPACE USER_DATA NOLOGGING

CLOSE_MONTH, CLOSE_DATE, SEQ에 NOT NULL 제약조건을 걸게 되면 값이 무조건 입력 되어야 된다. 

제약 조건이 NOT NULL만 있기 때문에 중복된 값이 들어 갈 수 있다.

기본키

CREATE TABLE SHOP_USER (
    CLOSE_MONTH VARCHAR2(6) NOT NULL,
    CLOSE_DATE VARCHAR2(8) NOT NULL,
    SEQ VARCHAR20(20) NOT NULL,
    USER_NAME VARCHAR2(10),
    POINT_MONEY NUMBER(15),
    CONSTRAINT PK_SHOP_USER PRIMARY KEY(CLOSE_MONTH, CLOSE_DATE, SEQ) USING INDEX TABLESPACE USER_IDX NOLOGGING
) TABLESPACE USER_DATA NOLOGGING

CLOSE_MONTH, CLOSE_DATE, SEQ에 기본키 제약조건이 걸려 있다. 기본키 제약조건은 해당 컬럼에 값이 무조건 입력이 되어야 되며 해당 컬럼의 값이 중복이 되면 안된다.

CLOSE_MONTH / CLOSE_DATE / SEQ

201903 / 20190312 / 1

201903 / 20190312 / 2

각각의 해당 컬럼에 들어 가는 값들인데 위의 데이터는 현재 중복이 없다.

201903 / 20190312 / 1

다시 한번 똑같은 값이 들어가게 되면 기본키 위배로 에러가 발생한다.

기본키 = NOT NULL + UNIQUE

UNIQUE

CREATE TABLE SHOP_USER (
    CLOSE_MONTH VARCHAR2(6) NOT NULL,
    CLOSE_DATE VARCHAR2(8) NOT NULL,
    SEQ VARCHAR20(20),
    USER_NAME VARCHAR2(10),
    POINT_MONEY NUMBER(15),
    CONSTRAINT UK_SHOP_USER UNIQUE KEY(CLOSE_DATE, CLOSE_TIME, SEQ)
) TABLESPACE USER_DATA NOLOGGING

CLOSE_MONTH, CLOSE_DATE, SEQ에 유니크 제약조건이 걸려 있다.  SEQ와 같이 NULL이 허용된 컬럼에도 UNIQUE를 걸어 줄 수 있다.

201903 / 20190312 / 1

201903 / 20190312 / ''

201903 / 20190312 / 2

두번째 열처럼 SEQ에 널 값을 입력 할 수는 있다.

201903 / 20190312 / 1

하지만 똑같은 값을 입력하게 되면 에러가 발생한다.

외래키

CREATE TABLE SHOP_USER (
    CLOSE_DATE VARCHAR2(8) NOT NULL,
    CLOSE_TIME VARCHAR2(8) NOT NULL,
    SEQ VARCHAR20(20) NOT NULL,
    USER_NAME VARCHAR2(10),
    POINT_MONEY NUMBER(15),
    CONSTRAINT FK_SHOP_USER FOREIGN KEY(USER_NAME) REFERENCES MANAGE_USER(USER)
) TABLESPACE USER_DATA NOLOGGING

외래키는 다른 테이블의 컬럼을 참조하여 현재 테이블의 컬럼 값을 입력 할 수 있다. 위의 테이블에는 USER_NAME이 외래키로 걸려 있는데 USER_NAME이라는 컬럼은 MANAGE_USER테이블의 USER 테이블을 참조하여 현재 테이블의 값을 입력 할 수 있는 것이다. 외래키에 걸려 있는 테이블에 입력할려는 값이 없다면 에러가 발생한다.

실무에서는 생각보다 외래키를 많이 사용하지 않는다. 스키마에는 외래키 표시가 되어 있지만 실제 테이블에는 외래키 제약조건을 사용하지 않는다. 이유는 외래키를 생성 해놓으면 데이터 무결성을 지키겠지만 외래키로 인한 작업 시 불편한 점이 많다.

CHECK

CREATE TABLE SHOP_USER (
    CLOSE_DATE VARCHAR2(8) NOT NULL,
    CLOSE_TIME VARCHAR2(8) NOT NULL,
    SEQ VARCHAR20(20) NOT NULL,
    USER_NAME VARCHAR2(10),
    POINT_MONEY NUMBER(15)
CONSTRAINTS PM_CHECK CHECK (POINT_MONEY BETWEEN 1 AND 10000)
    REGION VARCHAR2(10),
CONSTRAINTS REGION_CHECK (REGION IN ('서울', '경기', '대구'))
)

마지막으로 CHECK 제약조건이다. POINT_MONEY의 금액을 1 부터 10,000 까지 제약조건을 줬으며 해당 범위에 포함이 안된 값이 들어간다면 에러가 발생한다. 지역 컬럼에는 서울, 경기, 대구 값만 들어 갈 수 있도록 제약조건을 설정하였다.

오라클 테이블 설계 시 데이터 타입을 처음 마주하게 된다. 데이터 추출 시 그리고 PL/SQL을 활용한 함수를 만들 때, 인덱스를 활용할 때 데이터 타입에 대한 이해는 필요하다. 이유를 간략하게 설명을 하자면 테이블 설계를 할 때는 테이블의 컬럼 이름, 데이터 타입이 들어가기 때문에 데이터 타입을 처음 마주하게 될 것이다. PL/SQL을 활용할 때는 인자값을 받고 인자값을 이용하여 SELECT문을 이용하여 데이터를 추출하는 경우가 많다. 이때 SELECT문의 컬럼 데이터 타입과 인자값의 타입이 갖지 않으면 에러가 발생한다. 마지막에 언급한 인덱스를 활용할 때인데 인덱스에 들어가 있는 컬럼이 조건에 들어가 있을 때 컬럼과 찾고자 하는 값의 데이터 타입이 같지 않으면 인덱스를 타지 않는다. ( '1' 과 1은 다른 값이다.)

문자 데이터 타입

문자열 데이터 타입에서 주로 사용하는 타입은 2가지가 있다.

• VARCHAR2 : 문자열 데이터의 사용하며 테이블 컬럼 크기를 10Byte를 할당했을 때 문자열 저장 시 5Byte만 사용하면 5Byte만 할당된다.
• CHAR : VARCHAR2와 같이 문자열에 사용되며 컬럼 사이즈를 10Byte를 할당하면 문자열 저장 시 5Byte를 사용해도 10Byte를 할당한다.

대분의 책, 그리고 실무에서도 VARCHAR2를 사용을 권장한다. CHAR같은 경우는 최초 테이블 설계 시 키값이 적용된 컬럼의 CHAR형을 사용하기도 한다.

숫자/날짜 데이터 타입

• NUMBER : 인자 생략 시 22BYTE 까지 입력이 된다.
• DATE : 연/월/일/시/분/초  범위로 입력이 가능하다.

테이블 설계 시 날짜를 저장하는 경우가 많다. 하지만 DATE형보다는 VARCHAR2에 날짜를 저장한다. 데이터 가공 시 TO_CHAR을 활용하여 VRCHAR2 형을 사용하는 것을 많이 볼 수 있다. (ex)varchar2, 20190310) 이유는 DATE 보다 보기(가독성) 쉽고 사용하기가 쉽다.

NUMBER형 사용시 주의 할 점이 있다. NUMBER(3, 2) 선언 시 두번째 인자 2는 소수점 2번째 자리까지 표시된다. 만약 NUMBER(3, 2) 타입에 10.22을 저장한다면 에러가 발생할 것이다.

NULL

NULL은 값이 없는 것을 의미하며 테이블 설계시 NULL 또는 NOT NULL을 명시함으로써 NULL을 허용하거나 허용하지 않을 수 있다. 데이터 추출 시, 데이터를 가공 할 때 널처리에 주의하자. NULL인 경우 SELECT문 조건을 잘 작성하여도 NULL이 있음을 인지 못하고 데이터를 빼먹기도 하며 데이터 가공 시 실수가 많은 부분이다. NULL 처리는 NVL을 이용한다.

테이블은 오라클에서 가장 기본이 되는 객체로써 테이블에 데이터를 입력하여 데이터를 관리한다. 테이블은 엑셀표와 같이 생각하면 쉽다. 구성은 로우(행, 가로)와 컬럼(열, 세로)으로 구성이 된다. 테이블을 생성하고 이에 따른 필요로 하는 인덱스 생성, 데이터 생성 그리고 테이블 삭제를 진행해보자.

오라클 테이블 생성

기본적인 테이블 생성문은 다음과 같다.

CREATE TABLE 테이블명 (
    컬럼1 데이터타입 [NULL, NOT NULL],
컬럼2 데이터타입 [NULL, NOT NULL],
...
) [TABLESPACE 테이블스페이스명];

테이블 생성 형식은 위와 같이 기본적으로 사용되며 NULL, NOT NULL, TABLESPACE는 필요에 따라 명시해준다.

다음으로는 실제 테이블을 생성하듯이 만들어보자.

CREATE TABLE SHOP_USER (
CLOSE_DATE VARCHAR2(8) NOT NULL,
CLOSE_TIME VARCHAR2(8) NOT NULL,
SEQ VARCHAR20(20) NOT NULL,
USER_NAME VARCHAR2(10),
POINT_MONEY NUMBER(15),
CONSTRAINT PK_SHOP_USER PRIMARY KEY(CLOSE_DATE, CLOSE_TIME, SEQ) USING INDEX TABLESPACE USER_IDX NOLOGGING
) TABLESPACE USER_DATA NOLOGGING

첫번재 테이블 생성 형식 처럼 테이블을 생성해도 생성이 된다. 테이블 생성 시 조금 더 디테일하게 설정 하는 경우가 있다. 테이블 스페이스(데이터, 인덱스 부분) 설정과 기본키 관련도 테이블 생성 시 같이 생성 할 수 있다. 또한 NOLOGGING 옵션을 설정하여 리두로그를 쌓지 않을 수 있다.

설명을 간략하게 하자면 5개의 컬럼을 생성. 

일자/시간/순번은 기본키를 주기 위해서 NOT NULL 옵션을 주었다. 

기본키를 설정하고 오라클은 기본키 설정 시 인덱스로 잡히는데 이때 이 인덱스를 USER_IDX 테이블 생성에 NOLOGGING 옵션을 적용하여 인덱스를 생성한다. 테이블 및 저장되는 데이터는 USER_DATA 테이블스페이스에 생성 한다.

오라클 인덱스 생성

위와 같이 테이블을 생성을 하였으며 이름순으로 데이터를 조회하는 어드민 메뉴가 있다고 생각해보자. 데이터가 많아질 경우 이름 순으로 조회를 하면 속도가 느려 시간이 오래 걸리니 인덱스 생성을 필요로 한다.

CREATE INDEX IDX_USER_NAME ON SHOP_USER (USER_NAME) TABLESPACE USER_IDX;

인덱스 이름은 IDX_USER_NAME 이며 테이블스페이스는 USER_IDX 위치에 인덱스가 생성된다.

인덱스 컬럼은 USER_NAME이다.

오라클 테이블 삭제 / 인덱스 삭제

DROP TABLE SHOP_USER CASCADE CONSTRAINTS;
DROP INDEX USER_IDX;

첫번째 명령어는 테이블을 삭제하는 명령어로 SHOP_USER 테이블을 삭제한다. 이때 CASCADE CONSTRAINTS 옵션을 주면 관련된 제약조건도 같이 삭제가 된다. 두번째 명령어는 인덱스를 삭제하는 명령어이다.

필요에 따라 필요 명령어를 사용하도록 하자.

오라클 데이터 생성

기본적인 데이터 생성은 다음과 같다.

INSERT INTO SHOP_USER (컬럼1, 컬럼2, 컬럼3, ...) VALUES(컬럼1 값, 컬럼2 값, 컬럼3 값, ...);

INSERT INTO SHOP_USER (CLOSE_DATE, CLOSE_TIME, SEQ, USER_NAME, POINT_MONEY) VALUES('20190307', '15071341', '1', 'JUN', 50000);

SHOP_USER 테이블에 값을 입력 할 수 있겠다.