교육특징
- 실무실습 위주의 교육
- 실습을 통해 이론을 취득할 수 있도록 합니다.
과정상세 페이지 : http://topcredu.co.kr/sub/crcl/view/db/2128
교육센터 홈페이지 : http://www.topcredu.co.kr
2017년 6월 26일 월요일
2017년 6월 22일 목요일
(Oracle Tip) ORACLE DATE TYPE _탑크리에듀
ORACLE의 DATE TYPE의 크기는 7BYTE라고 한다.
그런데 SELECT LENGTH(HIRE_DATE), LENGTH(SYSDATE) FROM OE.EMPLOYEES WHERE ROWNUM <= 2; 하면 결과는? 8로 나온다.
SELECT DUMP(hire_date, 10), hire_date, DUMP(SYSDATE, 10), SYSDATE
FROM OE.EMPLOYEES
WHERE ROWNUM <= 2;
Typ=12 Len=7: 120,107,6,21,1,1,1 2007-06-21 Typ=13 Len=8: 223,7,4,3,10,52,5,0 2015-04-03 10:52:05
Typ=12 Len=7: 120,107,6,21,1,1,1 2007-06-21 Typ=13 Len=8: 223,7,4,3,10,52,5,0 2015-04-03 10:52:05
위 같이 나온다. SYSDATE와 TABLE의 COLUMN 크기가 다르다. 8과 7로 나온다.
SYSDATE 맨 마지막 BYTE 사용하지 않는다고 나온다.
DUMP를 보면 hire_date(TABLE COLUMN)의 경우 120,107이라 나오는데 앞 1을 뺀 20+07해서
2007이고 다음 월,일 시각,분,초 일건데 DEFAULT 값이 1이다 실제는 0으로 표시 된다.
SYSDATE 경우 223, 7 인데 256*7=1792 1792+223=2015가 된다. 즉 2 BYTE는 256의 배수이고 1번째 BYTE 수를 더하면 해당 년도가 나온다. 그 다음은 월,일,시각,분,초 순이다. 제일 마지막의 8BYTE는 사용하지 않는다고 한다.
참고 바랍니다.
(Oracle Tip) SCN_TO_TIMESTAMP 함수를 이용하여 FLASHBACK으로 데이터 복구 _탑크리에듀
-- 테이블을 조회하고 UPDATE하려 합니다.
SELECT SALARY -- 3800
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = 188;
--이렇게 UPDATE하려 했습니다.
UPDATE HR.EMPLOYEES SET SALARY = SALARY*10
WHERE EMPLOYEE_ID = 188;
-- 그런데 이렇게 UPDATE해 버렸습니다.
UPDATE HR.EMPLOYEES SET SALARY = SALARY*10;
COMMIT;
-- 테이블 전체를 UPDATE하고 COMMIT까지 해 버렸습니다.
SELECT SALARY -- 38000
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = 188;
-- 모든 직원의 월급이 10배로 뛰었습니다. 복구해야 지요.(그냥 나둘까요?)
-- ORA_ROWSCN 는 Pseudocolumns 입니다. ORACLE이 만들어 주는 의사 컬럼 입니다.
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) -- 2015-04-21 12:28:26.000000000
FROM HR.EMPLOYEES;
FLASHBACK TABLE HR.EMPLOYEES TO TIMESTAMP
TO_TIMESTAMP('2015-04-21 12:28:26.000000000', 'YYYY-MM-DD HH24:MI:SS.FF');
-- ORA-08189: 행 이동이 사용으로 설정되지 않았으므로 테이블을 플래시백할 수 없음
-- 위를 실행하면 메세지 같은 오류가 납니다.
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
-- 위와 같이 테이블을 수정합니다.
FLASHBACK TABLE HR.EMPLOYEES TO TIMESTAMP
TO_TIMESTAMP('2015-04-21 12:28:26.000000000', 'YYYY-MM-DD HH24:MI:SS.FF');
-- 다시 실행하면 복구가 됩니다.
-- 확인하면 UPDATE 이전으로 복구 되었습니다.
SELECT *
FROM HR.EMPLOYEES;
-- 테이블도 원래 상태로 되돌립니다.
ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
감사합니다.
(Oracle Tip) 계층구조 테이블의 Query _탑크리에듀
계층 구조의 테이블을 SELECT하는 방법은 일반 테이블의 SELECT 방법과 조금 다르게
이루어 집니다. 계층 구조의 모델링을 Recusive Referential(회귀적 참조)라고도 합니다.
그럼 Table을 만들고 데이터를 넣은 다음에 SELECT 해 보겠습니다. 데이터는 11번가의
분류를 참조 했습니다. (절대 PR하기 위한 건 아닙니다.ㅋㅋㅋ)
-- 테이블 생성
CREATE TABLE CATEGORY
(
CATEGORY_ID NUMBER(6) NOT NULL,
CATEGORY_NM VARCHAR2(60 BYTE),
PAR_CATEGORY_ID NUMBER(6)
);
-- PK
ALTER TABLE CATEGORY ADD (
CONSTRAINT PK_CATEGORY
PRIMARY KEY
(CATEGORY_ID)
USING INDEX);
-- FK
ALTER TABLE CATEGORY
ADD CONSTRAINTS FK_CATEGORY FOREIGN KEY (PAR_CATEGORY_ID)
REFERENCES CATEGORY(CATEGORY_ID);
-- DATA INSERT
INSERT INTO CATEGORY VALUES ('100001','카테고리최상위','');
INSERT INTO CATEGORY VALUES ('100002','브랜드패션','100001');
INSERT INTO CATEGORY VALUES ('100003','의류','100001');
INSERT INTO CATEGORY VALUES ('100004','잡화/뷰티','100001');
INSERT INTO CATEGORY VALUES ('100005','식품/유아동','100001');
INSERT INTO CATEGORY VALUES ('100006','리빙/건강','100001');
INSERT INTO CATEGORY VALUES ('100007','레저/자동차','100001');
INSERT INTO CATEGORY VALUES ('100008','디지털/가전','100001');
INSERT INTO CATEGORY VALUES ('100009','도서/잡화','100001');
INSERT INTO CATEGORY VALUES ('100010','브랜드 여성의류/언더웨어','100002');
INSERT INTO CATEGORY VALUES ('100011','브랜드 남성의류/언더웨어','100002');
INSERT INTO CATEGORY VALUES ('100012','브랜드 캐주얼의류','100002');
INSERT INTO CATEGORY VALUES ('100013','TV/냉장고/세탁기','100008');
INSERT INTO CATEGORY VALUES ('100014','주방/이미용/생활가전','100008');
INSERT INTO CATEGORY VALUES ('100015','전기매트/가습기/난방가전','100008');
INSERT INTO CATEGORY VALUES ('100016','DSLR/디카/액세서리','100008');
INSERT INTO CATEGORY VALUES ('100017','MP3/AV/음향가전','100008');
INSERT INTO CATEGORY VALUES ('100018','노트북/데스크탑','100008');
INSERT INTO CATEGORY VALUES ('100019','모니터/프린터/잉크','100008');
INSERT INTO CATEGORY VALUES ('100020','PC부품/주변기기','100008');
INSERT INTO CATEGORY VALUES ('100021','저장장치/스마트패드/게임','100008');
INSERT INTO CATEGORY VALUES ('100022','휴대폰/액세서리','100008');
INSERT INTO CATEGORY VALUES ('100023','LED TV','100013');
INSERT INTO CATEGORY VALUES ('100024','3D TV','100013');
INSERT INTO CATEGORY VALUES ('100025','LCD TV','100013');
INSERT INTO CATEGORY VALUES ('100026','PDP TV','100013');
INSERT INTO CATEGORY VALUES ('100027','브라운관 TV','100013');
INSERT INTO CATEGORY VALUES ('100028','TV 주변기기','100013');
INSERT INTO CATEGORY VALUES ('100029','양문형냉장고','100013');
INSERT INTO CATEGORY VALUES ('100030','일반형냉장고','100013');
INSERT INTO CATEGORY VALUES ('100031','김치냉장고','100013');
INSERT INTO CATEGORY VALUES ('100032','기능성냉장고','100013');
INSERT INTO CATEGORY VALUES ('100033','드럼세탁기','100013');
INSERT INTO CATEGORY VALUES ('100035','삼성전자 매장','100013');
INSERT INTO CATEGORY VALUES ('100036','리퍼/반품/전시','100013');
INSERT INTO CATEGORY VALUES ('100037','렌탈서비스','100013');
INSERT INTO CATEGORY VALUES ('100038','삼성전자','100023');
INSERT INTO CATEGORY VALUES ('100039','LG전자','100023');
INSERT INTO CATEGORY VALUES ('100040','브랜드기타','100023');
INSERT INTO CATEGORY VALUES ('100041','삼성전자','100024');
INSERT INTO CATEGORY VALUES ('100042','LG전자','100024');
INSERT INTO CATEGORY VALUES ('100043','소니','100024');
INSERT INTO CATEGORY VALUES ('100044','노트북','100018');
INSERT INTO CATEGORY VALUES ('100045','브랜드PC','100018');
INSERT INTO CATEGORY VALUES ('100046','조립PC본체','100018');
INSERT INTO CATEGORY VALUES ('100047','조립PC세트','100018');
INSERT INTO CATEGORY VALUES ('100048','태블릿PC','100018');
INSERT INTO CATEGORY VALUES ('100049','중고/리퍼/반품/전시','100018');
INSERT INTO CATEGORY VALUES ('100050','렌탈서비스','100018');
INSERT INTO CATEGORY VALUES ('100051','노트북액세서리','100018');
INSERT INTO CATEGORY VALUES ('100052','삼성전자','100044');
INSERT INTO CATEGORY VALUES ('100053','애플','100044');
INSERT INTO CATEGORY VALUES ('100054','한성','100044');
INSERT INTO CATEGORY VALUES ('100055','코어i7','100052');
INSERT INTO CATEGORY VALUES ('100056','코어i5','100052');
INSERT INTO CATEGORY VALUES ('100057','코어i3','100052');
INSERT INTO CATEGORY VALUES ('100058','코어2듀오','100052');
INSERT INTO CATEGORY VALUES ('100059','AMD','100052');
INSERT INTO CATEGORY VALUES ('100060','i7','100054');
INSERT INTO CATEGORY VALUES ('100061','i5','100054');
INSERT INTO CATEGORY VALUES ('100062','펜티엄 이하','100054');
INSERT INTO CATEGORY VALUES ('100063','CPU','100020');
INSERT INTO CATEGORY VALUES ('100064','메인보드','100020');
INSERT INTO CATEGORY VALUES ('100065','데스크탑용 메모리(RAM)','100020');
INSERT INTO CATEGORY VALUES ('100066','노트북용 메모리(RAM)','100020');
INSERT INTO CATEGORY VALUES ('100067','기타PC부품','100020');
INSERT INTO CATEGORY VALUES ('100068','코어i7','100063');
INSERT INTO CATEGORY VALUES ('100069','코어i5','100063');
INSERT INTO CATEGORY VALUES ('100070','코어i3','100063');
INSERT INTO CATEGORY VALUES ('100071','코어i3/i5/i7','100064');
INSERT INTO CATEGORY VALUES ('100072','코어2듀오이상','100064');
INSERT INTO CATEGORY VALUES ('100073','스포츠의류/운동화/용품','100007');
INSERT INTO CATEGORY VALUES ('100074','등산/아웃도어/캠핑/낚시','100007');
INSERT INTO CATEGORY VALUES ('100075','바다낚시장비','100074');
INSERT INTO CATEGORY VALUES ('100076','낚시대/뜰채','100075');
INSERT INTO CATEGORY VALUES ('100077','바다 낚시 세트','100075');
INSERT INTO CATEGORY VALUES ('100078','바늘류','100075');
INSERT INTO CATEGORY VALUES ('100079','갯바위릴대','100076');
INSERT INTO CATEGORY VALUES ('100080','바다루어대','100076');
INSERT INTO CATEGORY VALUES ('100081','우럭/지깅 선상대','100076');
SELECT *
FROM CATEGORY;
-- 이제 계층 구조 SELECT 입니다.
SELECT LEVEL,LPAD(' ', 2*LEVEL-1)||CATEGORY_ID||' '||CATEGORY_NM
FROM CATEGORY A
START WITH PAR_CATEGORY_ID IS NULL
CONNECT BY PRIOR CATEGORY_ID = PAR_CATEGORY_ID
ORDER BY LEVEL, CATEGORY_ID;
위 는 방향성이 위에서 아래 쪽입니다.
반대로 아래에서 위 쪽으로 찾아 갑니다.
SELECT LEVEL,LPAD(' ', 2*LEVEL-1)||CATEGORY_ID||' '||CATEGORY_NM
FROM CATEGORY A
START WITH CATEGORY_ID = 100055
CONNECT BY PRIOR PAR_CATEGORY_ID = CATEGORY_ID;
START WITH 문과 COONECT BY 문을 첫번째 QUERY문과 비교해 보십시요.
데이터의 갯수가 많으면 자신의 부모를 찾기 힘듦니다.
그럴때 자신의 부모가 보이면 좋겠지요. 그런 함수가 있습니다.
SELECT LPAD(' ', 2*LEVEL-1)||SYS_CONNECT_BY_PATH(CATEGORY_NM,'-') PATH
FROM CATEGORY A
START WITH PAR_CATEGORY_ID IS NULL
CONNECT BY PRIOR CATEGORY_ID = PAR_CATEGORY_ID
ORDER BY LEVEL, CATEGORY_ID;
결과를 보시면 아실겁니다.
그럼 참고하시고...
감사합니다.
(Oracle Tip) [오라클TRIGGER예제]SCOTT 계정으로 로그인을 막는 트리거 _탑크리에듀
[오라클TRIGGER예제]SCOTT 계정으로 로그인을 막는 트리거
SQL> conn system/onj@onj
연결되었습니다.
SQL> CREATE OR REPLACE TRIGGER deny_scott
2 AFTER LOGON ON DATABASE
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(sys_context('USERENV','SESSION_USER') || ' logged o
n...');
5
6 --IF sys_context('USERENV','SESSION_USER') = 'SCOTT' THEN
7 IF USER = 'SCOTT' THEN
8 RAISE_APPLICATION_ERROR(-20001,'SCOTT NO ACCESS');
9 END IF;
10 END;
11 /
트리거가 생성되었습니다.
-- SCOTT계정이 DBA권한이 있다면 로그온을 막을 수가 없으므로 DBA권한이 있는 경우라면 회수
SQL> revoke dba from scott;
revoke을(를) 성공했습니다.
SQL> conn scott/tiger@onj
ERROR:
ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다
ORA-20001: SCOTT NO ACCESS
ORA-06512: 2행
경고: 이제는 ORACLE에 연결되어 있지 않습니다.
(Oracle Tip) 언두 테이블스페이스 변경작업 _탑크리에듀
[undo tablespace 변경 작업]언두 테이블스페이스 변경작업
-- 먼저 현재 시스템의 undo tablespace를 확인
SQL> show parameter undo tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
- 새로 undo tablespace를 하나 만들자/
SQL> create undo tablespace undotbs datafile 'C:\app\ooo\oradata\onj\UNDOTBS.DBF' size 1000m
테이블스페이스가 생성되었습니다.
경 과: 00:00:19.33
-- undo에 할당 되어 있는 rollback segment를 확인해보자, 금방 만든 undo tablespace와 기존의 것을 확인할 수 있다. 방금 만든 undo tablespace는 offline으로 됨을 확인하자.
SQL> select segment_name, tablespace_name, status from dba_rollback_segs order by 2;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU17_222748869$ UNDOTBS OFFLINE
_SYSSMU18_3634303265$ UNDOTBS OFFLINE
_SYSSMU19_3181162363$ UNDOTBS OFFLINE
_SYSSMU20_247679012$ UNDOTBS OFFLINE
_SYSSMU21_3214651666$ UNDOTBS OFFLINE
_SYSSMU15_2917605919$ UNDOTBS OFFLINE
_SYSSMU23_2401741525$ UNDOTBS OFFLINE
_SYSSMU24_887281181$ UNDOTBS OFFLINE
_SYSSMU25_3640496044$ UNDOTBS OFFLINE
_SYSSMU26_1561920818$ UNDOTBS OFFLINE
_SYSSMU16_4192955508$ UNDOTBS OFFLINE
_SYSSMU22_3865558656$ UNDOTBS OFFLINE
_SYSSMU1_3086899707$ UNDOTBS1 ONLINE
_SYSSMU2_1531987058$ UNDOTBS1 ONLINE
_SYSSMU3_478608968$ UNDOTBS1 ONLINE
_SYSSMU4_1451910634$ UNDOTBS1 ONLINE
_SYSSMU5_2520346804$ UNDOTBS1 ONLINE
_SYSSMU6_1439239625$ UNDOTBS1 ONLINE
_SYSSMU7_1101470402$ UNDOTBS1 ONLINE
_SYSSMU8_1682283174$ UNDOTBS1 ONLINE
_SYSSMU9_3186340089$ UNDOTBS1 ONLINE
_SYSSMU10_378818850$ UNDOTBS1 ONLINE
_SYSSMU11_3378023884$ UNDOTBS1 ONLINE
_SYSSMU12_2631554543$ UNDOTBS1 ONLINE
_SYSSMU13_3875143044$ UNDOTBS1 OFFLINE
_SYSSMU14_1585475619$ UNDOTBS1 OFFLINE
27 개의 행이 선택되었습니다.
경 과: 00:00:00.09
-- 현재 DB의 undo tablespace를 방금 만든것으로 변경하자.
SQL> alter system set undo_tablespace= undotbs;
시스템이 변경되었습니다.
경 과: 00:00:00.10
SQL> select segment_name, tablespace_name, status from dba_rollback_segs order by 2;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU17_222748869$ UNDOTBS ONLINE
_SYSSMU18_3634303265$ UNDOTBS ONLINE
_SYSSMU19_3181162363$ UNDOTBS ONLINE
_SYSSMU20_247679012$ UNDOTBS ONLINE
_SYSSMU21_3214651666$ UNDOTBS ONLINE
_SYSSMU15_2917605919$ UNDOTBS ONLINE
_SYSSMU23_2401741525$ UNDOTBS ONLINE
_SYSSMU24_887281181$ UNDOTBS ONLINE
_SYSSMU25_3640496044$ UNDOTBS ONLINE
_SYSSMU26_1561920818$ UNDOTBS ONLINE
_SYSSMU16_4192955508$ UNDOTBS ONLINE
_SYSSMU22_3865558656$ UNDOTBS ONLINE
_SYSSMU1_3086899707$ UNDOTBS1 OFFLINE
_SYSSMU2_1531987058$ UNDOTBS1 OFFLINE
_SYSSMU3_478608968$ UNDOTBS1 OFFLINE
_SYSSMU4_1451910634$ UNDOTBS1 OFFLINE
_SYSSMU5_2520346804$ UNDOTBS1 OFFLINE
_SYSSMU6_1439239625$ UNDOTBS1 OFFLINE
_SYSSMU7_1101470402$ UNDOTBS1 OFFLINE
_SYSSMU8_1682283174$ UNDOTBS1 OFFLINE
_SYSSMU9_3186340089$ UNDOTBS1 OFFLINE
_SYSSMU10_378818850$ UNDOTBS1 OFFLINE
_SYSSMU11_3378023884$ UNDOTBS1 OFFLINE
_SYSSMU12_2631554543$ UNDOTBS1 OFFLINE
_SYSSMU13_3875143044$ UNDOTBS1 OFFLINE
_SYSSMU14_1585475619$ UNDOTBS1 OFFLINE
27 개의 행이 선택되었습니다.
경 과: 00:00:00.03
-- 기존 undo tablespace를 삭제
SQL> drop tablespace undotbs1;
-- 물리적으로 오라클 데이터파일이 있는 폴더에서(저의 경우 "C:\app\ooo\oradata\onj\") 아래 undotbs01.dbf를 지워주면 끝
(Oracle Tip) Oracle EXTERNAL TABLE _탑크리에듀
ORACLE에서 TEXT의 데이터를 테이블에 넣는 방법은
SQL LOADER를 이용하여 FILE의 데이터를 Table에 넣을 수 있고 다른 방법으로는 우리가 흔히 쓰는 TOAD, Orange 기타 TOOL을 이용하여 Text Data를 Table로 넣을 수 있는데 다른 또 하나의 방법은 EXTERNAL Table이다.
우선 DIRECTORY를 만드는데 DBA 권한에서 작업해야 된다.(WINDOW를 기준으로 작업했습니다.) C 밑에 tmp라는 Folder를 Directory로 선언
CREATE DIRECTORY EXT_TAB_DIR AS 'C:\tmp';
GRANT READ, WRITE ON DIRECTORY EXT_TAB_DIR TO SCOTT;
CREATE TABLE TEST
(
EMPLOYEE_NUMBER VARCHAR2(5 BYTE),
EMPLOYEE_NAME VARCHAR2(10 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_TAB_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
NOBADFILE
NODISCARDFILE
NOLOGFILE
SKIP 0
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' AND '"'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
EMPLOYEE_NUMBER Char,
EMPLOYEE_NAME Char ENCLOSED BY '"' AND '"'
) )
LOCATION (EXT_TAB_DIR:'EMP.dat')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
External Table 만든 후에
C:\tmp\EMP.dat File을 만든다.
내용은
11001,한덕수
11002,김경환
11003,차일권
을 EMP.dat File에 넣어 주고
SELECT *
FROM TEST;
바로 DATA가 나온다.
이번에는
11004,백일섭
File에 추가해서 다시 table select 하면 바로 추가 된다.
File의 데이터를 바로 끌어 오는것이다.
한 번 사용해 보시길...
감사합니다.
(Oracle Tip) 오라클 임시테이블스페이스(temp tablespace) 변경 _탑크리에듀
오라클 임시테이블스페이스(temp tablespace) 변경
임시 테이블 스페이스(Temporary Tablespace)는 어느 순간이 되면 사이즈가 커져 있으며 사이즈를 줄이는 것은 불가능 하다. 새로운 임시 테이블스페이스를 만들고 기존것을 삭제하고, 새로만든 테이블스페이스를 임시 테이블스페이스로 등록하면 된다.
기본 사용자들은 임시 테이블스페이스를 변경하면 된다.
SQL> create temporary tablespace temp2 tempfile 'C:\app\ooo\oradata\onj\temp02.dbf' size 100m autoextend on;
테이블스페이스가 생성되었습니다.
SQL> alter database default temporary tablespace temp2;
데이타베이스가 변경되었습니다.
SQL> drop tablespace temp;
테이블스페이스가 삭제되었습니다.
-- 기존 사용자의 임시테이블 스페이스를 변경
SQL> alter user scott temporary tablespace temp2;
사용자가 변경되었습니다.
(Oracle Tip) 테이블의 PK, FK 등의 확인 _탑크리에듀
TABLE 작업시 TABLE의 유일성을 보장하는 PK,
만들고 있는 TABLE의 COLUMN이 다른 TABLE의 데이터를 참조하는 FK,
PK는 아니지만 유일한 값을 보장해야 한다면 UK를 선언 하고,
어떤 COLUMN이 NOT NULL 이거나 어떠어떠한 값 외에는 들어가지 못하게 CHECK를
만듭니다. 이러한 내용을 저장하는 DATA DICTIONARY가 있습니다.
이것에 대해서 알아 봅시다.
우선 PK, FK, UK, CHECK(혹은 NOT NULL)을 선언하면
USER_CONSTRAINTS에 저장이 됩니다.
우리가 조회 하여 볼려면
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'table명';
이라고 조회 하시면 됩니다. 내용을 확인하면 여기에 CONSTRAINT_TYPE이 나옵니다.
P-PK, R-FK, U-UK, C-NOT NULL(혹은 CHECK) 입니다.
그럼, CONSTRAINT를 구성하는 COLUMN은 어떻게 확인 할까요?
USER_CONS_COLUMNS에서 보시면 됩니다.
이것은 각자 확인하시고 어떤 정보가 있는지 보기 바랍니다.
감사합니다.
(Oracle Tip) order by와 인덱스 , 그리고 힌트 _탑크리에듀
[order by와 인덱스 , 그리고 힌트]
--현재 MYEMP1 테이블의 ename 칼럼에는 idx_myemp1_ename 이라는 인덱스가 생성됨
select a.index_name, a.column_name, b.visibility
from user_ind_columns a, user_indexes b
where a.table_name = 'MYEMP1'
and a.index_name = b.index_name
IDX_MYEMP1_ENAME ENAME VISIBLE
-- first_rows 모드는 한건의 데이터를 가지고 오는데 있어 가장 비용이
-- 적게드는 실행계획을 수립하므로 정렬된 곳 즉 인덱스에 가서 데이터를 추출한다.
alter session set optimizer_mode = first_rows
select ename, sal from myemp1 order by ename
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 190M| 2399K (1)| 07:59:54 |
| 1 | TABLE ACCESS BY INDEX ROWID| MYEMP1 | 10M| 190M| 2399K (1)| 07:59:54 |
| 2 | INDEX FULL SCAN | IDX_MYEMP1_ENAME | 10M| | 34883 (1)| 00:06:59 |
------------------------------------------------------------------------------------------------
-- all_rows 모드는 모든 데이터를 가지고 오는데 있어 가장 비용이 적게드는
-- 살행계획을 생산하므로 FULL TABLE SCAN한다.
alter session set optimizer_mode = all_rows
select ename, sal from myemp1 order by ename
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 190M| | 76864 (2)| 00:15:23 |
| 1 | SORT ORDER BY | | 10M| 190M| 268M| 76864 (2)| 00:15:23 |
| 2 | TABLE ACCESS FULL| MYEMP1 | 10M| 190M| | 15658 (2)| 00:03:08 |
-------------------------------------------------------------------------------------
-- 그래서 where절에 ename 칼럼을 출현시켜 보자.
select ename, sal from myemp1 where ename >= 'ㄱ' order by ename
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 190M| | 76899 (2)| 00:15:23 |
| 1 | SORT ORDER BY | | 10M| 190M| 268M| 76899 (2)| 00:15:23 |
|* 2 | TABLE ACCESS FULL| MYEMP1 | 10M| 190M| | 15693 (2)| 00:03:09 |
-------------------------------------------------------------------------------------
-- 위 경우도 FULL TABLE 스캔한다. 어떻게 개선할 까?
-- 옵티마이저 모드를 first_rows로 두면 되겠지만 이경우 조인등의 경우
-- 해시조인 보다는 중첩루프 조인등을 선호하여 전체적인 쿼리 성능이 저하될 수있다.
1. 인덱스 자체를 ename, sal 칼럼 두개로 구성하든지
sal 칼럼을 select 안하면 ename 인덱스로 가서 데이터 추출한다.
select ename from myemp1 where ename >= 'ㄱ' order by ename
2. 오라클 힌트를 사용하면 된다.
select /*+ index(myemp1 idx_myemp1_ename)*/
ename, sal
from myemp1
where ename >= 'ㄱ' order by ename
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 190M| 2399K (1)| 07:59:54 |
| 1 | TABLE ACCESS BY INDEX ROWID| MYEMP1 | 10M| 190M| 2399K (1)| 07:59:54 |
|* 2 | INDEX RANGE SCAN | IDX_MYEMP1_ENAME | 10M| | 34883 (1)| 00:06:59 |
------------------------------------------------------------------------------------------------
(Oracle Tip) 함수기반인덱스의 칼럼정보 확인(user_ind_expression) _탑크리에듀
함수기반인덱스의 칼럼정보 확인(user_ind_expression)
create index fidx_emp_ename on emp( lower(ename) );
//칼럼이름이 'SYS_NC00009$' 형태로 출력된다.
select * from user_ind_columns WHERE table_name = 'EMP';
------------------------------------------------------------------------------
IDX_EMP_ENAME EMP ENAME 1 10 10 ASC
PK_EMP EMP EMPNO 1 22 0 ASC
IDX_EMP_SAL EMP SAL 1 22 0 ASC
FIDX_EMP_ENAME EMP SYS_NC00009$ 1 10 10 ASC
//칼럼이름을 확인할 수 있다.
select * from user_ind_expressions WHERE table_name = 'EMP';
-----------------------------------------------------------------
FIDX_EMP_ENAME EMP LOWER("ENAME") 1
********************************************************************
인덱스 생성시 desc 옵션을 줘서 역순으로만들면 함수기반 인덱스로 생성되어
user_ind_columns 에서는 칼럼명 확인 불가하다.
create index fidx_emp_sal on emp(sal desc);
//칼럼이름이 'SYS_NC00010$' 형태로 출력된다.
select * from user_ind_columns WHERE table_name = 'EMP';
------------------------------------------------------------------------------------
IDX_EMP_ENAME EMP ENAME 1 10 10 ASC
PK_EMP EMP EMPNO 1 22 0 ASC
FIDX_EMP_SAL EMP SYS_NC00010$ 1 34 0 DESC
//칼럼이름을 확인할 수 있다.
select * from user_ind_expressions WHERE table_name = 'EMP';
--------------------------------------------
FIDX_EMP_SAL EMP "SAL" 1
2017년 6월 13일 화요일
2017년 6월 12일 월요일
2017년 6월 9일 금요일
2017년 6월 5일 월요일
2017년 6월 1일 목요일
피드 구독하기:
글 (Atom)