쿼리를 날리다 보면, 필요에 따라 그룹별로 순위를 매겨야 할 때가 있다.
이에 대해 오라클에서는 그러한 기능을 제공하는데,
아래가 바로 그 예이다.

[Oracle]
SELECT empno, ename, job, sal,
ROW_NUMBER() OVER(PARTITION BY job ORDER BY sal) AS rnum
FROM scott.emp;

<<결과>>

EMPNO ENAME JOB SAL RNUM
---------- -------------------- ------------------ ---------- ----------
7902 FORD ANALYST 3000 1
7788 SCOTT ANALYST 3000 2
7369 SMITH CLERK 800 1
7900 JAMES CLERK 950 2
7876 ADAMS CLERK 1100 3
7934 MILLER CLERK 1300 4
7782 CLARK MANAGER 2450 1
7698 BLAKE MANAGER 2850 2
7566 JONES MANAGER 2975 3
7839 KING PRESIDENT 5000 1
7654 MARTIN SALESMAN 1250 1
7521 WARD SALESMAN 1250 2
7844 TURNER SALESMAN 1500 3
7499 ALLEN SALESMAN 1600 4

상기 쿼리는,
emp 테이블의 JOB을 기준으로 하여 그룹을 정하고 (PARTITION BY job), -- 1
sal을 기준으로 하여 순위를 매겨(ORDER BY sal),
각각의 행에 ROW_NUMBER를 부여하겠다는 의미이다. -- 2

여기서 'PARTITION BY job'은 job별 정렬을 발생시킨다.
즉, 최종 결과물의 넘버링은 ORDER BY job, sal의 순으로 결과가 나오는 것이다.




[MySQL]
그런데, 불행하게도..... MySQL에는 저 기능이 없다.
그렇기 때문에 우리의 친구 꼼수(?)를 이용하여 저것을 구현해 내야 하는데.....

SELECT empno, ename, job, sal, rnum
FROM (
SELECT a.*,
(CASE @vjob WHEN a.job THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,
(@vjob:=a.job) vjob
FROM emp a, (SELECT @vjob:='', @rownum:=0 FROM DUAL) b
ORDER BY a.job, a.sal
) c;

<<결과>>

+-------+--------+-----------+------+------+
| empno | ename | job | sal | rnum |
+-------+--------+-----------+------+------+
| 7902 | FORD | ANALYST | 3000 | 1 |
| 7788 | SCOTT | ANALYST | 3000 | 2 |
| 7369 | SMITH | CLERK | 800 | 1 |
| 7900 | JAMES | CLERK | 950 | 2 |
| 7876 | ADAMS | CLERK | 1100 | 3 |
| 7934 | MILLER | CLERK | 1300 | 4 |
| 7782 | CLARK | MANAGER | 2450 | 1 |
| 7698 | BLAKE | MANAGER | 2850 | 2 |
| 7566 | JONES | MANAGER | 2975 | 3 |
| 7839 | KING | PRESIDENT | 5000 | 1 |
| 7654 | MARTIN | SALESMAN | 1250 | 1 |
| 7521 | WARD | SALESMAN | 1250 | 2 |
| 7844 | TURNER | SALESMAN | 1500 | 3 |
| 7499 | ALLEN | SALESMAN | 1600 | 4 |
+-------+--------+-----------+------+------+
14 rows in set (0.00 sec)

어때... 결과가 같아 보이는가?

자, 그럼 쿼리를 뜯어보자.
여기서 궁금하게 생각되는 부분은 아래 3개의 쿼리라고 예상 된다.

1. (CASE @vjob WHEN a.job THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,
--> 이전 job 필드와 동일한 그룹인가를 판별하고, 그룹에 따라 순번을 부여하기 위함이며,
테이블에서 각각의 행을 읽을 때마다,
변수 @vjob 값이 지금 새로 읽은 job 과 같다면 변수 @rownum을 1증가 시키고,
그렇지 않은 경우(@vjob이 현재 읽은 job값과 같지 않다면) @rownum을 1로 초기화 시킨다.

2. (@vjob:=a.job) as vjob
--> 테이블에서 각각의 행을 읽을 때마다,
그룹 판별을 위해 현재 읽고 있는 행의 job값을 변수 @vjob에 입력

3. (SELECT @vjob:='', @rownum:=0 FROM DUAL) b
--> 원래는 쿼리를 수행하기 이전에,
SET @vjob:=0, @rownum:=0; 을 수행하여 변수를 초기화 해야 한다.
만약 해주지 않으면, NULL 값이 들어가게 된다.

하지만 그럴 경우 쿼리가 2번 수행되어야 하기 때문에,
하나의 쿼리로 만들기 위해서 이런 식의 서브 쿼리를 이용한 것이다.
이 서브쿼리는 초기 테이블 확인시 1회만 수행되고,
이후부터는 열람되지 않는다.

!! 주의 !!
서브쿼리 안에서의 결과값만 가지고 현재의 결과값을 얻고자 할 때,
변수가 되는 항목의 값을 동일한 자료형으로 맞춰주지 않으면,
정상적인 결과값이 나오지 않는다.
가령 위의 예를 이용하자면, @vjob의 초기값을 @vjob:=0 으로 수행 하고
서브쿼리만을 수행하면 정상적인 결과값이 나오지 않게 된다.
한 번 해보자~

이 3가지를 이해한다면 아마 이해할 수 있을 것이라 생각되지만,
한 가지 짚고 넘어가야 할 것이 있다.

Q. 우리가 흔히 쓰는 SELECT 문장의 수행순서는 어떻게 될까?
무슨의미냐 하면..
위에서 사용한 것처럼 변수를 이용한 SELECT 내 연속적인 값의 할당은,
수행결과에 영향을 미치게 되지 않을까?
라는 질문이다.

흠.. 내가 말을 써놓고 난해하군..
예제를 보도록 하자.

<<예제>>
SET @val1=0, @val2=0; #아까도 말했듯이 변수 초기화는 먼저 선행되어야 한다.
SELECT @val1:=@val1+1, @val2:=@val1+1, @val2:=0, @val1=@val2+1
FROM DUAL;

자.... 당신이 예상하는 결과는?.....



<<쿼리 수행 결과>>

+----------------+----------------+----------+---------------+
| @val1:=@val1+1 | @val2:=@val1+1 | @val2:=0 | @val1=@val2+1 |
+----------------+----------------+----------+---------------+
| 1 | 2 | 0 | 1 |
+----------------+----------------+----------+---------------+
1 row in set (0.00 sec)

상기와 같이 SELECT 내 수행 결과는,
왼쪽에서 오른쪽으로 순차적인 수행이 이루어짐을 알 수 있다.

즉, @val1:=@val1+1 → @val2:=@val1+1 → @val2:=0 → @val1=@val2+1
로 수행 순서가 정해진다는 의미.

그러므로,
변수를 이용한 SELECT를 이용할 때는 반드시 수행순서를 염두해 두고 쿼리를 작성하도록 하자.



PS : 오라클에는 예제 테이블이 있지만 MySQL 에는 없으니
혹시 테스트 해보고 싶은 사람은 아래 쿼리를 수행해서 테스트 해보도록...

CREATE TABLE emp (
empno INT,
ename VARCHAR(30),
job VARCHAR(30),
sal INT
)ENGINE=INNODB DEFAULT CHAR SET=UTF8;

INSERT INTO emp
VALUES
(7902,'FORD','ANALYST',3000),
(7788,'SCOTT','ANALYST',3000),
(7369,'SMITH','CLERK',800),
(7900,'JAMES','CLERK',950),
(7876,'ADAMS','CLERK',1100),
(7934,'MILLER','CLERK',1300),
(7782,'CLARK','MANAGER',2450),
(7698,'BLAKE','MANAGER',2850),
(7566,'JONES','MANAGER',2975),
(7839,'KING','PRESIDENT',5000),
(7654,'MARTIN','SALESMAN',1250),
(7521,'WARD','SALESMAN',1250),
(7844,'TURNER','SALESMAN',1500),
(7499,'ALLEN','SALESMAN',1600);

출처 : http://blackbull.tistory.com/43

1. 시작 - 실행 - services.msc를 실행(시작 - 관리도구 - 서비스)
오라클 관련 서비스 9가지를 모두 중지시킨다.


2, 위의 그림처럼
시작 - 모든프로그램 - Oracle OraDb11g_home1 - Oracle 설치제품 - Universal Installer 를 이용하여 오라클 관련 제품을 삭제.

2-1), Universal Installer 클릭 - 설치제품 해제 - 항목선택 -
시작 - 실행 창에 C:\오라클설치디렉토리\app\Administrator\product\11.2.0\dbhome_2\deinstall\deinstall.bat 를 실행.

2-2),
Please wait...
[LISTENER]에서 구성을 해제할 단일 인스턴스 리스너를 모두 지정?? 뭔 말이냐?
시작-실행-listener 입력
status 해서
ip 나오면 그아이피를 http://해당 IP 입력
.
.
2-3),
이 Oracle 홈에 구성된 데이터베이스 이름 목록을 지정하십시오
[ORCL] orcl 엔터.
??
된거냐?
5분 기다림.
.
.
OCR check is finished .
y엔터
.
.

데이터베이스 정리구성 시작.
.
.
Windows 및 .NET 제품 구성 시작을 제거하는중
.
.
.
어라?... 이 bat 파일이 끝나니까
메뉴에서도 오라클 항목이 사라진다.


3. regedit를 실행해서 오라클이 포함된 경로들을 모두 삭제합니다. (경우에 따라 경로가 다를 수 있음)
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 삭제

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services 삭제 (Oracle 포함 삭제)
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services 삭제 (Oracle 포함 삭제)
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services 삭제 (Oracle 포함 삭제)

HKEY_CLASSES_ROOT -> Oracle과 Ora로 시작되는 것 삭제
여긴 좀 많군.


4. 재부팅합니다.

5. 오라클이 설치되어있는 디렉토리들을 삭제한다

출처 : http://iooic2.blog.me/24330297

deinstall 명령어를 더 자세히 알고 싶을 경우 사이트

http://download.oracle.com/docs/cd/E11882_01/install.112/e10843/deinstall.htm#CIHDGGJJ

ins-32025 로 인해 홈 디렉토리 겹치는 문제가 발생시

program\oracle 폴더를 지우고 하면 된다.

유닉스는 깔기는 어려우나 지우기는 쉽고 윈도우는 깔기는 쉬우나 지우기가 힘드네여..

출처 : http://fullmetalhead.tistory.com/131

다음과 같은 결과가 있다고 가정하자

select id, value

from test;

그런데 id 별로 아래와 같이 단순하게 다중로우를 단일로우값으로 붙여서 표현하고 싶다면??

그렇다면 아래와 같은 쿼리를 구현하면 된다.

select id, wmsys.wm_concat(value) as concat_value

from test

group by id;

==> 단, wm_concat 함수는 오라클 10g 이상의 버전에서만 사용이 가능하고,

아래와 같이 선언되어 있기 때문에 어떤 스키마에서도 실행이 가능하다.

GRANT EXECUTE ON WMSYS.WM_CONCAT TO PUBLIC;

CREATE PUBLIC SYNONYM WM_CONCAT FOR WMSYS.WM_CONCAT;

--undo tablespace
select name, value
from v$parameter
where name in ('undo_management', 'undo_tablespace');

--undo segments
select owner, segment_name, tablespace_name
from dba_rollback_segs
order by 3;

-- undo관련 parameter
SELECT NAME, VALUE, DESCRIPTION
FROM V$PARAMETER
WHERE NAME LIKE '%undo%';

--undo guarantee 확인
select tablespace_name, retention
from dba_tablespaces
where tablespace_name = 'UNDOTBS1';
--undo guarantee mode 변경
--alter tablespace undotbs1 retention guarantee;
--alter tablespace undotbs1 retention guarantee;

--undo 관련 상태 정보
select *
from V$UNDOSTAT;

-- 아래는 초당 생성되는 undo block수를 알 수 있는 공식입니다.
-- 생성되는 전체 block의 수를 계산하여 모니터되는 시간(초)으로 나눕니다.
SELECT (SUM(undoblks)/SUM (((end_time-begin_time)*86400)))
FROM V$UNDOSTAT;
 

-- END_TIME 과 BEGIN_TIME 은 DATE형이기 때문에 뺄셈을 수행하면
-- 결과가 날짜로 표시됩니다. 그러므로 날짜를 초로 변환하려면
-- 하루를 초로 계산한 86400을 곱합니다.

-- 완성된 SQL문장
SELECT ROUND(((UR * (UPS * DBS)) + (DBS * 24)) / 1000000000, 2) AS "GB"
FROM (
    SELECT value AS UR
    FROM V$PARAMETER
    WHERE name = 'undo_retention'),
  (
    SELECT (SUM(undoblks)/SUM(((end_time-begin_time)*86400))) AS UPS
    FROM V$UNDOSTAT),
  (
    SELECT value AS DBS
    FROM V$PARAMETER
    WHERE name = 'db_block_size');
 

설치 파일은 오라클 홈페이지에서 배포중인 10204_vista_w2k8_x64_production_db.zip 를 사용했습니다. 총 3개의 파일을 수정해야 하며, 관리자 권한으로 설치하세요. 윈도우즈7에만 TEST 해 보았으나 윈도우즈 서버 2008 R2 에서도 같은방법으로 설치 가능할겁니다.(아마도...^^)32/64bit 모두 동일하게 적용이 가능합니다. OS가 64bit 라도 32bit Oracle 설치가 가능합니다.


<기본 설치 환경>
OS : Microsoft Windows7 32bit / 64bit
DB : Oracle 10g 32bit / 64bit

<설치전에 수정해야 할 파일 및 내용>
- 빨간 글씨 부분이 추가/수정 해야 할 내용 입니다. 메모장 등의 문서편집기로 수정하면 됩니다.(총 3부분)
기본폴더이름은 사용자별로 다를수 있습니다.

① \10204_vista_w2k8_x64_production_db\database\install\oraparam.ini

[Certified Versions]

#You can customise error message shown for failure, provide value for CERTIFIED_VERSION_FAILURE_MESSAGE
Windows=5.0,5.1,5.2,6.0,6.1

② \10204_vista_w2k8_x64_production_db\database\stage\prereq\db\refhost.xml

<CERTIFIED_SYSTEMS>

<OPERATING_SYSTEM>
<VERSION VALUE="5.0"/>
<SERVICE_PACK VALUE="1"/>
</OPERATING_SYSTEM>
<OPERATING_SYSTEM>
<VERSION VALUE="5.1"/>
<SERVICE_PACK VALUE="1"/>
</OPERATING_SYSTEM>
<OPERATING_SYSTEM>
<VERSION VALUE="5.2"/>
</OPERATING_SYSTEM>
<!--Microsoft Windows Vista-->
<OPERATING_SYSTEM>
<VERSION VALUE="6.0"/>
</OPERATING_SYSTEM>
<!--Microsoft Windows 7-->
<OPERATING_SYSTEM>
<VERSION VALUE="6.1"/>
</OPERATING_SYSTEM>
</CERTIFIED_SYSTEMS>

③ \10204_vista_w2k8_x64_production_db\database\stage\prereq\db_prereqs\db\refhost.xml

<CERTIFIED_SYSTEMS>

<OPERATING_SYSTEM>
<VERSION VALUE="5.0"/>
<SERVICE_PACK VALUE="1"/>
</OPERATING_SYSTEM>
<OPERATING_SYSTEM>
<VERSION VALUE="5.1"/>
<SERVICE_PACK VALUE="1"/>
</OPERATING_SYSTEM>
<OPERATING_SYSTEM>
<VERSION VALUE="5.2"/>
</OPERATING_SYSTEM>
<!--Microsoft Windows Vista-->
<OPERATING_SYSTEM>
<VERSION VALUE="6.0"/>
</OPERATING_SYSTEM>
<!--Microsoft Windows 7-->
<OPERATING_SYSTEM>
<VERSION VALUE="6.1"/>
</OPERATING_SYSTEM>
</CERTIFIED_SYSTEMS>

기본 설치로 진행 할 경우 오류가 발생합니다. 꼭 고급 설치 로 진행해야 합니다.

기본 설치를 할 경우 다음과 같은 오류가 발생할수 있습니다. [오류] Oracle 홈 이름은 적합하지 않습니다. Oracle 홈 이름은 128자 이하여야 하며 영문자와 밑줄만 포함되야 합니다.

고급 설치로 진행 합니다. 이후 부터는 별다른 주의 사항이 없습니다. 자신에게 맞는 적당한 옵션을 선택하여 설치하세요.

 

출처 : http://guisin.net/69

create tablespace : 오라클 데이터베이스내에서 생성되고 처리될 테이블들의 레코들들이 실제로 존재할 영역을 디스크 상에 물리적으로 생성시키는 명령어이다.

 tablespace_name : 생성될 테이블 스페이스의 이름이다.

 datafile : 데이터베이스내에서 사용되는 레코드들이 실제로 디스크상에 파일로 존재하게 되는데, 이때의 파일의 위치와 이름을 지정하는 곳이다.

 data_file : 데이터베이스내에서 사용되는 레코드들이 실제로 디스크상에 파일로 존재하게 되는데, 이때의 파일의 위치와 이름을 지정하는 곳이다.

 data_full_file_name : 레코드들이 실제로 존재할 디렉토리(절대패스사용)와 파일의 이름이다.

 size : 테이블 스페이스내의 레코드들을 저장할 디스크상의 파일의 최대 코기를 지정해 줄 수 있다.

 datafilesize : 레코드들을 저장할 파일의 크기를 k(킬로바이트), M(메가바이트)의 단위를 사용하여 나타낼 수 있다.

 initial : 테이블 생성시 해당 테이블에 할당되어 있는 영역의 크기를 지정해 줄 수 있다.

 datafilesize_min : 테이블생성시 사용할 수 있는 공간의 크기로, 예를 들어 10m로 지정되면 생성된 임의의 테이블에 입력되는 데이터들을 10m의 영역에 저장한다는 의미이다.

 next : 처음에 저장될 데이터의 영역인 initial만큼을 다 쓰고 더 이상의 공간이 없을 때, 사용할 수 있는 영역을 할당 시켜 준다.

 datafilesize_max : 추가로 테이블에 데이터가 입력될 때, 사용할 수 있는 여역의 크기이다. 예를 들어 5M를 할당하여 두면, 임의의 테이블이 사용한 영역이 10M (위의 initial영역의 크기이다)를 넘을 경우, 주가로 5M만큼의 영역을 더 사용할 수 있게 된다. 따라서 총 사용공간은 15M가 된다.

 minextents minuum : next 영역으로 할당할 수 있는 최소의 갯수를지정해 줄 수 있다.

 maxextents maxnum : next 영역으로 할당할 수 있는 최대의 갯수를 지정해 줄 수 있다.

 picincrease num : next를 지정하여 추가로 사용할 영역을 확장하고자 할 때, 늘어날 영역의 크기를 '%'로 나타낸 값이다. pct는 '%'를 의미한다. 예를 들어 picincrease 5라고 지정해 두면, next로 추가로 작업할 영역을 늘여 줄때, 처음에는 next롤 설정된 영역만을 확장시켜 주나, 두 번째부터는 next영역의 크기에서 5%만큼 더 크게 확장시켜 주게 되는 것이다.

 online/offline : 테이블 스페이스 생성시 online이나 offline 중 택일하여 쓸 수 있으며, 생략하면 online을 의미한다.
online으로 설정하여 테이블 스페이스를 생성하면, 테이블스페이스를 생성함과 동시에 데이터베이스 사용자들이 사용가능하다는 것을 의미하며, 일반적으로 online으로 설정하여 사용한다.

 -- 테이블스페이스 정보 조회
select * from dba_data_files; 
select * from dba_tablespaces;

-- 테이블스페이스생성
  create tablespace info_data
  datafile '/oracle/infodata/infodata.dbf'
  size 200m
  default storage(
     initial     80k
     next        80k
     minextents  1
     maxextents  121
     pctincrease 80
     )online;

-- 테이블스페이스 online / offline
 > alter tablespace tablespace_name offline;
 > alter tablespace tablespace_name online;

 -- 생성된 테이블 스페이스의 추가하기 공간 늘여주기
  alter tablespace info_data
  add datafile '/oracle/infodata/infodata/dbf'
  size 100m;

 -- 생성된 테이블 스페이스 크기 변경하기
  alter database datafile '/oracle/infodata/infodata.dbf'
  RESIZE 200M;

 -- 테이블스페이스 변경하기
  alter tablespace tax2110
  default storage(
     initial        1024k
     next           2048k
     minextents     1
     maxextents     5
    )online ;
  pctincrease 기본이 50%이다

 -- 테이블스페이스 자동확장 추가 (Automatic Extension)
  alter tablespace tax2110
  add datafile 'd:\tablespace\tax2110_03.dbf'
  size 50m
  autoextend on next 10m
  maxsize 100m;
  -> maxsize 를 지정할때 데이터 화일보다 크거나 같아야함.

 -- 기존테이블스페이스에 자동확장 변경하기
  alter database datafile 'd:\tablespace\tax2110_03.dbf'
  autoextend on next 10m
  maxsize 100m; 

 -- 테이블스페이스 삭제
  drop tablespace tablespace_name
  including contents    --> 테이블스페이스의 모든 세그먼트를 삭제( 데이터가 있는 테이블스페이스는 삭제할수 없다)
  cascade constraints;  --> 삭제된 테이블스페이스 내의 테이블의 기본키와 유일키를 참조하는
         다른 테이블스페이스의 테이블로부터 참조무결성 제약 조건을 삭제합니다.
$ rm kit.dbf    -- Drop한 tablespace명의 Datafile이 kit.dbf일때.

 -- 테이블 스페이스 의 물리적파일까지 삭제하기
drop tablespace test_tbs including contents and datafiles; 
 

-- 오프라인 테이블스페이스
  alter tablespace tax2110 offline;
 
-- 데이터베이스 사용자 아이디 생성 및 수정
  create user 사용자아이디
  identified by 비밀번호(새비밀번호)


-- 유저생성
create user panda
identified by panda123

default tablespace yswater_ts;

-- 생성한 유저에 권한주고 연결하기
grant resource,connect to panda;
grant dba to panda;

05 바인드 변수의 중요성

• 테스트 준비

  SQL> CREATE TABLE t AS SELECT * FROM all_objects; 테이블이 생성되었습니다. SQL> UPDATE t SET object_id = ROWNUM; 6989 행이 갱신되었습니다. SQL> CREATE UNIQUE INDEX t_idx ON t(object_id); 인덱스가 생성되었습니다. SQL> ANALYZE TABLE t COMPUTE STATISTICS; 테이블이 분석되었습니다. SQL> SET AUTOT TRACEONLY EXPLAIN SQL> SELECT object_name FROM t WHERE object_id = 1000; Execution Plan ---------------------------------------------------------- Plan hash value: 2929955852 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=1000) SQL> SET AUTOT OFF SQL> ALTER SYSTEM FLUSH SHARED_POOL;

• 바인드변수 테스트

  SQL> SET TIMING ON SQL> DECLARE 2 TYPE rc IS REF CURSOR; 3 l_rc rc; 4 l_object_name t.object_name%TYPE; 5 BEGIN 6 FOR i IN 1 .. 20000 7 LOOP 8 OPEN l_rc FOR 9 'SELECT /* test1 */ object_name 10 FROM t 11 WHERE object_id = :x' USING i; 12 FETCH l_rc INTO l_object_name; 13 CLOSE l_rc; 14 END LOOP; 15 END; 16 / PL/SQL 처리가 정상적으로 완료되었습니다. 경 과: 00:00:00.96 SQL> SELECT sql_text 2 , loads 3 , parse_calls 4 , executions 5 , fetches 6 FROM v$sql 7 WHERE sql_text LIKE '%test1%' 8 AND sql_text NOT LIKE '%v$sql%' 9 AND sql_text NOT LIKE '%DECLARE%' 10 ; SQL_TEXT LOADS PARSE_CALLS EXECUTIONS FETCHES ------------------------------- ----- ----------- ---------- ------- SELECT ... WHERE object_id = :x 1 20000 20000 20000 경 과: 00:00:00.04

  1. 하드파싱 1회에 20000회 실행 : 커서 공유 확인
  2. PARSE_CALLS이 20000회 발생된 이유는 Dynamic Sql 을 사용했기 때문
  3. Static Sql 을 사용했다면 PARSE_CALLS 1회만 발생.

• 리터럴 상수 테스트

  SQL> DECLARE 2 TYPE rc IS REF CURSOR; 3 l_rc rc; 4 l_object_name t.object_name%TYPE; 5 BEGIN 6 FOR i IN 1 .. 20000 7 LOOP 8 OPEN l_rc FOR 9 'SELECT /* test2 */ object_name 10 FROM t 11 WHERE object_id = ' || i; 12 FETCH l_rc INTO l_object_name; 13 CLOSE l_rc; 14 END LOOP; 15 END; 16 / PL/SQL 처리가 정상적으로 완료되었습니다. 경 과: 00:00:08.62 SQL> SELECT SUBSTR(sql_text, 56, 25) sql_text 2 , loads 3 , parse_calls 4 , executions 5 , fetches 6 FROM v$sql 7 WHERE sql_text LIKE '%test2%' 8 AND sql_text NOT LIKE '%v$sql%' 9 AND sql_text NOT LIKE '%DECLARE%' 10 ; SQL_TEXT LOADS PARSE_CALLS EXECUTIONS FETCHES ----------------------- ----- ----------- ---------- ------- WHERE object_id = 15977 1 1 1 1 WHERE object_id = 15978 1 1 1 1 WHERE object_id = 15979 1 1 1 1 WHERE object_id = 15980 1 1 1 1 WHERE object_id = 15981 1 1 1 1 ... WHERE object_id = 19996 1 1 1 1 WHERE object_id = 19997 1 1 1 1 WHERE object_id = 19998 1 1 1 1 WHERE object_id = 19999 1 1 1 1 WHERE object_id = 20000 1 1 1 1 4228 개의 행이 선택되었습니다. 경 과: 00:00:01.62

  1. 수행시간 증가 : 0.96 초 ==> 8.62 초 (약 9배)
  2. 수행횟수만큼 커서 생성 되어 공유 영역에서 15772건이 밀려나고 4228건만 남아 있음
  3. 엄청남 성능 저하 : 커서 공유 안됨, 하드 파싱 2만회

• 바인드 변수 사용의 효과
  1. 커서를 많이 생성하지 않고 하나를 반복 재사용하므로 메모리 사용량과 파싱 소요시간을 줄여준다.
  2. 시스템 전반의 메모리와 cpu 사용률을 낮춰 데이터베이스 성능과 확장성을 높이는대 기여
  3. 동시사용자 접속이 많을 경우 영향력이 크다.

문서정보

• 이 문서는 오라클클럽에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://www.gurubee.net/pages/viewpage.action?pageId=4948362&
• 오라클클럽 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

The granularity of data refers to the fineness with which data fields are sub-divided. For example, a postal address can be recorded, with low granularity, as a single field:

1. address = 200 2nd Ave. South #358, St. Petersburg, FL 33701-4313 USA

or with high granularity, as multiple fields:

1. street address = 200 2nd Ave. South #358
2. city = St. Petersburg
3. postal code = FL 33701-4313
4. country = USA

Cardinality 예측 실습 [O3. 325페이지]

http://sites.google.com/site/ukja/books-cbo-in-depth/chapter5/01--cardinalities-sql 

조동욱님의 O3 325페이지를 보면서 위 조동욱님의 실습을 함께 하였다. 한번 한 이것을 다 나열하는 것보다는
TEXT 파일을 펼쳐서 직접 하나 하나 해보면서 결과를 보면 큰 도움이 된다.

cardinality.txt

Cardinality 예측 실습  

 카페 : http://cafe.naver.com/gseducation/469                                                       간편보기 "클릭" >>>>>>>>

생성하기

drop table t1 purge;  create table t1(c1 int, c2 int);  create index t1_n1 on t1(c1+1);  insert into t1  select 1, mod(level, 2)+1  from dual  connect by level <= 10000  union all  select 2, mod(level, 300)+1  from dual  connect by level <= 1000  union all  select 3, mod(level, 50)+1  from dual  connect by level <= 100  union all  select 4, mod(level, 5)+1  from dual  connect by level <= 10; commit;

 select c1, count(*) from t1 group by c1 order by 1;
 select c2, count(*) from t1 group by c2 order by 1;

 1. Histogram 없는 경우

SQL> exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size 1');

 SQL> @tab_stat t1

-----------------  TABLE_NAME                   : T1  NUM_ROWS                      : 11110  -----------------  TABLE_NAME                    : T1  COLUMN_NAME                : C1  NUM_DISTINCT                  : 4  DENSITY                           : .25  -----------------  TABLE_NAME                    : T1  COLUMN_NAME                 : C2  NUM_DISTINCT                  : 300  DENSITY                           : .00333333333333333  -----------------

          set autot traceonly explain
          variable b1 number
  

1) select * from t1 where c1 = 1;      select 1/4  * 11110 from dual;

2) select * from t1 where c1 = :b1;   select 1/4  * 11110 from dual;

3) select * from t1 where c1 > :b1;   select 0.05 * 11110 from dual;

4) select * from t1 where c1 = 0;           select (1/4 * 11110) - (1/4 * 11110 / (4-1)) from dual;           select * from t1 where c1 = 5;     select (1/4 * 11110) - (1/4 * 11110 / (4-1)) from dual;           select * from t1 where c1 = -1;    select (1/4 * 11110) - (1/4 * 11110 / (4-1)) - (1/4 * 11110 / (4-1)) from dual;           select * from t1 where c1 = 6;     select (1/4 * 11110) - (1/4 * 11110 / (4-1)) - (1/4 * 11110 / (4-1)) from dual;

  2. Histogram 있는 경우

SQL> exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size skewonly');

 SQL> @tab_stat t1

-----------------  TABLE_NAME                    : T1  COLUMN_NAME                : C1  NUM_DISTINCT                 : 4  NUM_NULLS                     : 0  DENSITY                          : .000045004500450045  LOW_VALUE                     : C102  HIGH_VALUE                    : C105  HISTOGRAM                     : FREQUENCY  -----------------  TABLE_NAME                   : T1  COLUMN_NAME                : C2  NUM_DISTINCT                 : 300  NUM_NULLS                     : 0  DENSITY                       : .406332971731016  LOW_VALUE                     : C102  HIGH_VALUE                    : C204  HISTOGRAM                     : HEIGHT BALANCED  -----------------  TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE  -------------------- -------------------- --------------- --------------------  T1                   C1                             10000 1()  T1                   C1                             11000 2()  T1                   C1                             11100 3()  T1                   C1                             11110 4()  T1                   C2                               113 1()  T1                   C2                               228 2()  T1                   C2                               229 4()  ... 생략 ...  T1                   C2                               253 286()  T1                   C2                               254 300()

Frequency : -> Density = (1/전체 Row의 개수) / 2  1) select * from t1 where c1 = 1;     10000  2) select * from t1 where c1 = :b1;   select 1/4 * 11110 from dual;    3) select * from t1 where c1 > :b1;   select 0.05 * 11110 from dual;  4) select * from t1 where c1 = 5;     1      select * from t1 where c1 = 6;     1    Height-balanced : -> Density = sum of the square of the frequency of the nonpopular values /                                     (number of nonnull rows * number of nonpopular nonull rows) (10.2.0.2 이후 버전 기준)  1) select * from t1 where c2 = 1;     select 113/254 * 11110 from dual;       select * from t1 where c2 = 4;     select .406332971731016 * 11110 from dual;  2) select * from t1 where c2 = :b1;   select .406332971731016 * 11110 from dual;     3) select * from t1 where c2 > :b1;   select 0.05 * 11110 from dual;   4) select * from t1 where c2 = 301;   select (.406332971731016 * 11110) - (.406332971731016 * 11110 / (300 - 1))                                                      from dual;          select * from t1 where c2 = 302;       select (.406332971731016 * 11110) - (.406332971731016 * 11110 / (300 - 1)) -(.406332971731016 * 11110 / (300 - 1))     from dual;