DB 데이터 실습(4)

substr, replace

drop table TB_X3 purge;
create table TB_X3( 
a varchar2(10) 
, b varchar2(20) 
);

insert into TB_X3 (a,b) values('1', 'idb');
insert into TB_X3 (a,b) values('2', 'Idb');
insert into TB_X3 (a,b) values('5', 'IDB');

insert into TB_X3 (a,b) values('6', '아이디비');
insert into TB_X3 (a,b) values('7', '%dB');
insert into TB_X3 (a,b) values('8', '8');
insert into TB_X3 (a) values('2');
insert into TB_X3 (a) values('4');

select * from TB_X3 order by b desc;
select substr(b,1,2) as x, b from TB_X3 where b is not null order by b;
select substr(b,2,3) as x, b from TB_X3 where b is not null order by b;
select substr(b,-3,1) as x, b from TB_X3 where b is not null order by b;

select b as "asis", replace(b, 'd', '2002') as "tobe" from TB_X3 where b is not null order by b;

update TB_X3 set b = replace(b, 'd', '2002') where b is not null;
select * from TB_X3;
commit;

update TB_X3 set b = replace(b, '2002', 'd') where b is not null;
select * from TB_X3;
commit;

create table TB_X3_1
as
select a, replace(b, 'd', '2002') as b from TB_X3 where b is not null order by b;

select * from TB_X3_1;
alter table TB_X3 rename to TB_X3_2;
alter table TB_X3_1 rename to TB_X3;
select * from user_tables where table_name like 'TB%';
select * from TB_X3;

alter session, 환경설정 변경

-- 도구 > 환경설정 > 데이터베이스 > NLS
-- 날짜형식 변경 : RR/MM/DD > YYYY-MM-DD HH24:MI:SS

select sysdate from dual; // 날짜까지만 나옴  
select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') from dual; // 시분초까지 나옴
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS'; // 세션 변경
select sysdate from dual; // 시분초까지 나옴

to_number, to_char, to_date

select to_number('1234') from dual; -- 우측정렬
select to_char('1234') from dual; -- 좌측정렬
select to_date('1992-12-31') from dual;
--select to_date('20070229') from dual; // error, no leap year 
select to_date('20080229') from dual;

중요 배경지식 정리

• NULL
  : 아직 정의되지 않아 값을 받을 수 있는 준비상태, 0은 숫자고 공백은 문자라 NULL이랑 다름, 연산에 NULL 포함시 결과도 NULL, NULL이 아닌 다른 결과값을 얻고자 할 때 NVL/ISNULL 함수 사용

• 테이블에 반드시 있어야 되는 제약조건
  : PK, PK는 테이블에 하나만 정의 가능, PK 지정된 컬럼은 NOT NULL, UNIQUE 자동 설정되고 고유 인덱스가 자동으로 생성됨

• FK 또는 JOIN의 대상이 되는 테이블에 반드시 만드는 것
  : 인덱스, NOT NULL, SORTED, 인덱스는 FK 지정된 컬럼이나 JOIN에 자주 사용되는 컬럼은 인덱스를 만듬, 인덱스는 테이블의 검색속도를 높여주는 자료구조로 특정 컬럼에 인덱스 생성 시 해당 컬럼 데이터를 정렬해 별도 메모리공간에 데이터 물리적주소와 함께 저장함, 정렬했기 때문에 WHERE 조건절로 쉽게 검색가능

• 아는 Dictionary 종류와 어떤 정보 담고있는지 적으시오.
  https://jaeho4646.tistory.com/30
  <접두사>
  USER: 해당 사용자가 소유한 객체
  ALL: 해당 사용자가 소유한 객체 + 다른 사용자로부터 부여받은 객체
  DBA: 모든 사용자의 객체
  <접미사>
  OBJECT: 객체 정보 (테이블, 인덱스, 뷰, 시노님)
  TABLES: 테이블 정보
  TAB_COLUMNS: 테이블 컬럼 정보
  INDEXES: 인덱스 정보
  IDX_COLUMNS: 인덱스 구성 컬럼 정보
  CONSTRAINTS: 제약 조건
  TAB_COMMENTS: 테이블 주석 정보
  v$instance: 현재 인스턴스 상태, no mount v$process: 현재 active process 상태, no mount
  v$database: 현재 DB 정보, mount

• CHAR, VARCHAR, VARCHAR2 차이
  : CHAR는 고정된 길이의 문자열 저장하는 데이터 타입, 선언된 길이보다 적게 넣어도 공백으로 채움, VARCHAR2는 가변 길이의 문자열 저장하는 데이터 타입, 메모리 상의 이점이 있음, VARCHAR/VARCHAR2는 차이 없지만 VARCHAR는 다른 용도로 변경될 수 있기 때문에 VARCHAR2 사용 권장, NVARCHAR 사용하면 한글,영어 모두 같은 숫자

• Objects, Table 및 Column 명명 표준화 목적 : 생산성 향상과 관리를 위해서

• DELETE, DROP, TRUNCATE 차이
  DELETE: 조건삭제, flashback 복구
  DROP: 조건불가, purge안쓰면 복구가능, 데이터, 테이블까지 삭제
  TRUNCATE: 조건불가, 복구불가, 데이터만 지우고 테이블은 남김

• % 는 LIKE와 결합하여 사용하고, 사용 시 %는 문자열 뒤에 배치.
  WHERE EMPLOYEE_NAME LIKE '홍%'; -->이렇게 사용
  WHERE EMPLOYEE_NAME LIKE '%홍%'; -->이러면 성능에 문제가 생긴다.(안되는건 아님)
  WHERE EMPLOYEE_NAME NOT LIKE '홍%'; --> 해도 됨
  WHERE EMPLOYEE_NAME NOT LIKE '%홍%'; --> 절대 하면 안됨

• decode 와 case의 차이점
  https://data-make.tistory.com/20
  decode는 조건에 따라 기존 컬럼의 데이터를 다른 컬럼의 값으로 치환, nvl처럼 null값 있는지 체크 가능, 대신 속도가 case보다 느림
  case는 decode와 같은 기능, 구문 차이, string으로 함수 아니고 명령어

• NLS_DATE_FORMAT 변경
  도구 > 환경설정 > 데이터베이스 > NLS
  날짜형식 변경 : RR/MM/DD > YYYY-MM-DD HH24:MI:SS
  select sysdate from dual; // 날짜까지만 나옴
  select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') from dual; // 시분초까지 나옴
  alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS'; // 세션 변경
  select sysdate from dual; // 시분초까지 나옴

추가)

• PK 속성 1. Not Null 2. Unique 3. Index
• SQL 연산자 우선순위
• 로그 관련 : spool, start
• FK 제약조건 index (성능향상)
• selection 행 단위, projection 열 단위
• windows 함수(Hierarchical구조)에서 START WITH와 CONNECT BY에는 양쪽 모두 INDEX가 있어야 한다.
• Oracle Optimizer가 Plan을 만드는 가장 중요한 판단 기준은 Index
• 빈번한 DML 발생시 트리의 빈번한 삽입, 위치변경, 삭제에 따라 구조 불균형 발생
• 한 개의 테이블에 만들 수 있는 인덱스의 갯수(3~6개)
• 한 개의 인덱스에 컬럼의 갯수는 6개까지 권장
• 선분이력의 데이터 조회 시 날짜시간 컬럼을 선행하는 인덱스를 넣는 것이 유리
• 좌변 변형 금지 -> 컬럼을 가공하지말고 치환시켜 상수(변수) 부분을 가공해야 한다.