농부지기

[ Nexacro. Dataset에서 컬럼에 대한 distinct한 레코드 갯수/목록 얻기 ]

1. distinct 한 값 얻기

2. colId1에 대한 distinct한 레코드 갯수/목록 얻기

   예제 1)

   var arrArgs = "colId1"; sFindRowExpr += arrArgs + "=='\" + " + arrArgs + " + \"'"; sFilterExpr = "rowidx==dataset.findRowExpr(\"" + sFindRowExpr + "\")"; this.ds_name1.getCaseCount(sFilterExpr);

   예제 2)

출처 : http://theone79.tistory.com/entry/오라클-hint-사용법 

출처: http://luckys.tistory.com/342 [Lucky's...]

[ Oracle-실행계획 ]

1. INLIST ITERATOR 

   - IN 절이나 동일한 열에 OR로 연결된 여러 등식(=)이 있는 경우

   - 열거된 값 리스트를 반복하고 모든 값은 별도로 실행

   - 동일한 인덱스를 사용하는 여러 개의 OR 절이 있는 경우 옵티마이저는 

      효율상CONCATENATION 또는 UNION ALL이 아닌 이 연산을 선택

   - 

3. INDEX FULL SCAN (MIN/MAX)

   - SELECT MAX(sal) FROM EMP;   --sal에 index가 존재 시

   - SELECT /*+ INDEX_DESC (EMP, IDX_EMP_SAL) */ SAL

        FROM EMP

      WHERE SAL > 0

          AND ROWNUM = 1;

2. SORT GROUP BY

   - 10.2이전 버전인 경우 실행계획

2. HASH GROUP BY 

   - 10.2버전부터 존재

   - 10g부터 group by 시 느린성능의 Sort group by가 사라지고 빠른성능의 Hash Group by가 등장

   - SELECT JOB, DEPTNO, SUM(SAL) AS SAL_TOT, COUNT(*) AS CNT

        FROM EMP

      WHERE DEPT_NO BETWEEN 10 AND 20 

      GROUP BY JOB, DEPTNO;

    - 

오라클 10.2를 시작할때, Group by와 Order by를 동일한 칼럼에 결합하여 사용할때, 성능과 관련하여 괄목할 만한 성능 절감을 발견하게 될 것이다. 

오라클 10.2에서는 Hash 기반의 Group By에 대해서 소개했다. 이전 Group by를 수행하면 연관된 칼럼의 소팅을 포함해서 처리했었고, 이러한 수행결과를 모아서 결과로 반환했다. Hash Group By 메소드는 소팅 없이 이러한 결과를 생성한다. 즉, 이 처리방식은 Sort-Based Group by보다 항상 낳은 성능을 발휘한다. 불행하게도 Order By 절을 동일 칼럼에 Group By절과 함께 사용한다면 Sort-Based Group by로 변경하며, 응답속도는 떨어질 것이다. 그러나 요청한 순서대로 데이터를 획득하고 싶다면 Hash-Based Group by를 이용하면 조금더 이점은 있다.

 http://neokido.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-102-%EB%B2%84%EC%A0%BC%EB%B6%80%ED%84%B0-%EB%8B%AC%EB%9D%BC%EC%A7%84-Group-by%EC%99%80-Order-by%EC%9D%98-%EC%98%B5%ED%8B%B0%EB%A7%88%EC%9D%B4%EC%A7%95

치명적인 Hash Group By 버그

10g 부터 group by 시에 느린성능의 Sort Group by 가 사라지고 빠른성능의 Hash Group By 가 등장 했다.

하지만 현재시점(10.2.0.3)에서 항상 Hash Group By 가 동작되는 것은 아니다.

성능면에서 배치 SQL 혹은 Migration 작업시에 몇억건의 데이터를 sort 하게되면 견딜수 없다.

주로 Sort Group by 는 insert - select - group by 상황에서 발생한다.

opt_param 힌트로도 해결되지 않았으며 Only select 문또는

CTAS (Create table as Select)문에서는 발생하지 않는걸로 확인됬다.

아래는 간단한 테스트를 진행 하고 현상황에서 Sort Group by 를 피할수 있는 해법을 제공한다.

오라클은 group by와 order by절에 같은 컬럼이 지정될 경우, group by를 hash기법이 아닌 sort기법을 사용하게 된다. 이를 피하고, hash기법을 적용하여 group by로 처리하도록 하려면 

해당 sql문장중 order by를 제외한 모든 문장을 서브쿼리로 작성하고 힌트로 /*+ NO_MERGE */를 지정한다.

그런다음 그 겱과를 ORDER BY로처리. 데이터량이 많을 경우 이 방법이 가장 빠를 듯.

참고로, GROUP BY를 강제로 SORT기법을 쓰도록 하려면

/*+ OPT_PARAM('_GBY_HASH_AGGREGATION_ENABLED', 'false') */하면 됨

원본 출처는 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%는 상황별로 다르게 처리돕니다. 또한 그것들은 책이나 매뉴얼에 나와있지 않기 때문에 경험치이거나 실험과 연구의 결과로 알아내는 것들입니다.

[ Oracle-튜닝-수행내역 이력을 파일로 저장 ]

1. save

   - 정의 : 방금 수행한 sql 명령어를 c:\ind.sql 파일로 저장

2. @ind

   - 정의 : c:\ind.sql 파일을 실행하기

3. spool

   - 정의 : 다음부터 수행되는 sql문장들을 파일로 저장

   - 파일에 저장된 내용  

99. 파일로 관리함 좋은 명령어들

    - index 목록 조회, 파일 생성, drop index 실행 하기

remark   현재 상태의 화경을 저장(아래쪽에서 다시 on을 할 필요가 없음)

store set saved_settings replace

prompt tbl '테이블명입력:'

remark '구','신' 이라는 라인 조회 되지 않음

set verify off

remark  모든 메세지가 조회되지 않음

remark set termout off

remark  첫줄에 컬럼 heading안보이게 처리

set heading off  

remark 처리 결과 레코드수 안보이게 처리

set feedback off  

set autotrace off 

spool C:\drop_index.sql

SELECT 'drop index ' || index_name || ';'

  FROM user_indexes

 WHERE TABLE_NAME = '&tbl' AND UNIQUENESS = 'NONUNIQUE';

spool off;

remark set heading on

remark set feedback on

remark set autotrace on

remark 위쪽에서 저장된 환경설정을 다시 on 처리

@C:\saved_settings

remark drop_index.sql 파일을 실행

@C:\drop_index.sql

[ Array.Object.HashMap ]

1. 테스트 : nexacro에서 해 봤음.

   1. array 에 추가, 삭제 하기 var aTemp = []; for (var i=0; i<10; i++){ aTemp.push(i); } aTemp.pop(3); --되는지 테스트 안해 봤음. 2. array에서 sort하기 aTemp.sort(); 3. Array 초기값 설정 var objArray = new Array(1,2,3); 3. Array에 dataset넘기기 this.fn_name([this.ds_name1, this.ds_name2,...]); ... this.fn_name = function(objDsList){ for(var i=0; i<objDsList.length; i++){ var str = objDsList[i].getColumn(nRow, "columId"); <-- 넘겨받은 dataset을 바로 사용가능 } } 4. argument로 array값 넘기고 받기 >> 넘기기 this.fn_setAppendModule([{url:'www.naver.com', height:42, width:100}, {url:'www.empas.com', height:42, width:100}]); >> 받아서 for문 돌기 this.fn_setAppendModule = function(aCondition){ var strUrl, nHeight; var objAppendDiv = ''; for (var idx in aCondition) { strUrl = aCondition[idx].url; nHeight = aCondition[idx].height; nWidth = aCondition[idx].width; .... } 5. 객체(Object) 다루기(Array Style관련 객체 다루기) 1. 선언 : var objType = {"code":"a01", "name":"apple"}; 사용 : objType["code"] objType["name"] 2. 선언 : var objTye = [ {"code":"a01", "name":"apple"} , {"code":"b01", "name":"banana"} , {"code":"c01", "name":"tomato"} ]; 사용 : objType[0].code objType[0].name objType[1].code objType[1].name 추가 : objType.push({"code":"d01", "name":"mellon"}) 갯수 : bjType.length 3. 선언 : var objType={}; objType["a01"] = "apple"; objType["b01"] = "banana"; objType["c01"] = "tomato"; 사용 : var name = objType["a01"]; 갯수 : 별도함수 없음. 함수를 추가해서 갯수 파악 this.objSize(objType); <--이와 같이 해서 oobject의 size를 구할 수 있음. this.objSize = function(obj) { var size = 0, key; for (key in obj) { if (obj.hasOwnProperty(key)) size++; } return size; } 또는 var size = Object.keys(myObj).length;

[ javascript - Array ]

1. 배열선언 및 함수들 사용방법

2. array function 목록

   - push, pop, join, toString, indexOf, reversh, shift, sort

3. 아래 소스는 nexaplatform에서 테스트 해봤음

    <SPAN style="COLOR: #000">//1. 배열 선언 var aAlpha1 = []; var aAlpah2 = new Array(); var aAlpah3 = new Array('A', 'B', 'C'); var aHangul = ['가', '나', '다']; var aNum = new Array(2,1,6,3,4,5,7); var aNum1 = new Array(1,2,3,4,5,6,7); var aNum2 = new Array(1,2,3,4,5,6,7); var aNum3 = new Array(2,1,6,3,4,5,7); //2. 배열에 값 set aAlpha1.push('A', 'B'); aAlpha1.push('C'); aAlpha1[3]='D'; aAlpha1[4]='E'; //3. 배열에서 값 얻기1 // : 배열 index방식 trace(aAlpha1.length + " :: " + aAlpha1[0]); //4. 배열에서 값 얻기2 // : pop() : 배열에서 맨 뒤쪽부터 하나씩 꺼낸 후 삭제 함 trace(aAlpha1.length + " :: " + aAlpha1.pop()); trace(aAlpha1.length + " :: " + aAlpha1.pop()); //5. 배열에서 값 얻기3, 복사 // - slice() : 배열중 일부 갯수만 얻어서 배열에 넣기 var aNumPart1 = aNum.slice(3); var aNumPart2 = aNum.slice(3,5); trace('aNumPart1->' + aNumPart1); //결과 : 4,5,6,7 trace('aNumPart2->' + aNumPart2); //결과 : 4,5 trace('aNumPart2->' + aNumPart2.length); //결과 : 2 //var aNumCopy1 = aNum1; //function arrayCopy(objArr) { objArr[3] = 'a'; } //arrayCopy[aNum1]; //10. 배열을 이용한 문자열 결합한 문자열 만들기 // - join(seperator) : 배열간에 seperator를 구분자로 결합 // - toString() : 배열간에 comma(,) 를 구분자로 결합 var sNumSum1 = aNum.join('+'); var sNumSum2 = aNum.join('*'); var sHanGulSum1 = aHangul.join('^^'); var sHanGulSum2 = aHangul.toString(); trace('sNumSum1 ->' + sNumSum1); //결과 : 1+2+3+4+5+6+7 trace('sNumSum2 ->' + sNumSum2); //결과 : 1*2*3*4*5*6*7 trace('sHanGulSum1->' + sHanGulSum1);//결과 : 가^^나^^다 trace('sHanGulSum2->' + sHanGulSum2);//결과 : 가,나,다 //11. 배열값의 index얻기 var idx = aHangul.indexOf("나"); trace('나 idx ->' + idx); //결과 : 나 idx ->1 //12. 2개의 배열을 결합(연결) var aHanNum = aHangul.concat(aNum); trace('aHanNum->' + aHanNum); //결과 : 가,나,다,1,2,3,4,5,6,7 //13. 배열 멤버들간에 정렬(오름, 내림)하여 배열에 set // - reverse() : 배열순서를 반대로 정렬 var aNumAsc = aNum.sort(); var aNumDesc = aNum.reverse(); trace('aNumAsc ->' + aNumAsc); trace('aNumDesc->' + aNumDesc); //14. 배열의 첫번째 멤버를 삭제 후 나머지 배열만 얻기 // - shift() : //20. sort 추가 trace(this.queue(aHangul)); trace('aNum2->' + aNum2); //결과 : 1,2,3,4,5,6,7 trace('this.sort(aNum, 0)->' + this.sort(aNum, 0)); //결과 : 7,6,5,4,3,2,1 trace('this.sort(aNum, 1)->' + this.sort(aNum, 1)); //결과 : 6,5,4,3,2,1,7 trace('this.sort(aNum, 2)->' + this.sort(aNum, 2)); //결과 : 5,4,3,2,1,7,6 trace('this.sort2(aNum3) ->' + this.sort2(aNum3)) ; //결과 : 1,2,3,4,5,6,7 } //정렬 (이게 제일 잘됨) this.sort2 = function(objArr) { objArr.sort(function(a,b){ if(a<b) {return -1;} //오름차순 : 자리 바꿀 필요 없음 else if(a>b) {return 1;} //오름차순 : 자리 바꿔야 함 else return 0; }); return objArr; } //queue this.queue = function(obj) { //단순 배열복사는 obj = obj.slice(0);// slice 객체를 써주는 것과 안써주는 것의 차이 이다. var cnt = obj.length; if (cnt <= 1) { return obj; } var last = obj[0]; var first = obj[1]; for (var i = 0; i < cnt; i++) { if (i == 0) { obj[0] = first; } else if (i == (cnt - 1)) { obj[cnt - 1] = last; } else { obj[i] = obj[i+1]; } } return obj; } // 선택한 인덱스를 기준으로 큐정렬된다. this.sort = function(obj,index) { index = parseInt(index); var obj = obj.slice(0); var cnt = obj.length; if (cnt <= 1) { return obj; } var first = obj[index]; var ret = []; ret[0] = first; var num = index; if (cnt <= 1) { return obj; } for (var i = 1; i < cnt; i++) { num++; if (num >= cnt) { num = 0; } ret[i] = obj[num]; } return ret; } </SPAN>

1. index 구조

  . B*tree 구조로 되어 있음. ( B : blance )

  . 균형이 잡힌 Blance Tree 구조

  . index block들이 모여 존재하는데  이 앞쪽에 B*tree구조가 존재 한다.

    그래서 검색 시 B*tree를 먼저 검색 하고 index block을 찾아 간다.

  . 

2. index를 생성하기 좋은 컬럼들

  . where절에 조건 컬럼

  . join절에 연결 컬럼

  . 분포가 고른 컬름

  . null을 많이 포함하고 있는 컬럼

3. index 컬럼선택  -  예)

  . 군인 테이블에서.성별 컬럼을 index잡아야 하는 경우

    - 군인중 남자는 60만명, 여자는2만명인데 분포도가 너무 않좋다.

    - 그래서 '여자'를 검색할 때는 빠르지만 '남자'를 검색하게 되면 index full scan후

      table검색하기 때문에 속도가 너무 느리다.

    - 그래서 오히려 남자를 검색할 때 full table scan이 빠른경우가 이런 경우이다.

    - 최종 : 성별컬럼에 '남자는 : null', '여자는 : 1' 로 저장 하면

              자연스럽게 남자검색시 full table scan을 하게 된다.

    - 추가 : 남자레코드가 많기 때문이 null을 포함한 컬럼을 index를 잡으면 index size도 작다.

3. index는 null컬럼을 포함하지 않음.

  . SELECT * FROM EMP WHERE ID is not null

    - ID에 index 존재 시

    - INDEX FULL SCAN ; 실행계획 처리.

    - is not null : 값이 존재 하는 id는 index에 모든 자료이므로 index full scan으로 처리 됨

3. index 목록 보기

   > SELECT INDEX_NAME , INDEX_TYPE, TABLE_NAME, UNIQUENESS FROM USER_INDEXES;

3. index order by

   . index가 존재 하는 자료를 조회 시 자료는 index기준으로 조회하기 때문에 정렬이 되어 조회됨

   . 만약 index key를 order by 해도 실행계획은 sort order by를 하지 않는다.

   . create index idx_emp_sal on emp(sal);

   . select * from emp where sal >= 3000 order by ename; 

      - empnm은 index가 없기 때문에 order by를 수행할 때 SORT ORDER BY 가 수행 됨

   . select * from emp where sal >= 3000 order by sal; 

     - sal에 index가 존재 하기 order by를 했지만 SORT ORDER BY 가 수행 되지 않음

   . select * from emp where sal >= 3000 order by sal desc; 

     - sal에 index가  asc 로 존재 하기 order by를할 경우 DESCENDING 으로 scan index range scan은 하고, SORT ORDER BY 가 수행 되지 않음

3. Block

   .  size

    > show parameter db_block_size;  --결과 : 8192 (8k)

      - db block size는 운영체제가 i/o를 읽을 수 있는 배수로 설정되어야 한다.

3. 테이블 Block

   . 저장구조

      [RH : 컬럼갯수 : 자리수 : 값 : 자리수 : 값 ....]  (RH : Row Header)

   . 실재 생성 구조 예1)

      -> 컬럼 : ID, NAME

      -> 값 : 1234, 'ABC'

          값 : NULL, 'DEF'

          값 : 4567, NULL   -> 일 경우 아래 Record가 저장되는 구조임.

      -> [RH : 2 : 4 : 1234 : 3 : ABC]

          [RH : 2 : 0 : 3 : DEF] -> 마지막 이전 컬럼값이 null이면 자릿수만 존재 '0'

                                      값에 대한 영역은 존재하지 않음

          [RH : 2 : 4 : 4567] -> 마지막 컬럼값이 null이면 자릿수와 값영역 둘다 존재하지 않음

      -> NULL이 들어가는 컬럼들은 마지막에 두면 더 좋다.     

   . 실재 생성 구조 예2)

     -> 컬럼 : ID, NAME

     -> 값 : 1234, A

     -> 값 : 1235, B

     -> 값 : 1236, C

     -> [RH : 2 : 1234 : 1 : A]

     -> [RH : 2 : 1235 : 1 : B]

     -> [RH : 2 : 1236 : 1 : C]

     -> 약 100만건 존재

     . 위 레코드에서 name 값을 아래 값으로 update한 경우 Row Migration 이 발생한다.

       처음 Insert시 block에 꽉 차게 된다. 그런데 update하므로써 block에 넘치므로

       다른 block을 하나 만들어서 현재 레코드를 빈 block으로 이주시킨다.

       이주 시킨후에는 기존 block에 이동한 block주소를 가지고 있어야 찾아갈 수 있다.

       만약, 계속 같은 레코드에 더 많은 문자를 UPDATE하게 되면 이주는 여러번 발생하고

       각 BLOCK에 체인처럼 이주 주소를 가지고 있어야 하는 경우가 발생할 수 있다.

        * 1차 해결방법 : PCT Free영역을 잘 관리하기

        * 2차 해결방법 : dba는 주시적으로 이 체인정보등을 관리해준다.

                           아래 BLOCK관리방법에 존재

     -> 값 : 1234, 'AAAA......약 3,000 개 문자'

     -> 값 : 1235, 'BBBB......약 3,000 개 문자'

     -> 값 : 1236, 'CCCC......약 3,000 개 문자' 

     -> 약 100만건 모두 update

     -> 기존 block에서 자료가 너무 많으므로 다른 block

     -> [RH : 2 : 1234 : 3000 : 'AAAA....']

    . PCT FREE

    . PCT USED

3. Block 관리방법

   SELECT TABLE_NAME, BLOCKS, EMPTY_BLOCKS, CHAIN_CNT, AVG_ROW_LEN

      FROM USER_TABLES

    WHERE TABLE_NAME= 'T2';

   - BLOCKS          : 테이블에서 사용된 block수

   - EMPTY_BLOCKS : 테이블에서 비어있는 block수

   - CHAIN_CNT      : 체인 수

   - AVG_ROW_LEN  : row별 평균 자리수

   - 위 SQL문장을 수행하면 Table_name은 나오지만 나머지 컬럼들은 나오지 않는다.

     아래 문장을 수행해줘야  위 sql문장을 수행했어야 자료가 조회 된다.

   - ANALYZE TABLE T2 COMPUTE STATISTICS;  --테이블별 전체 분석

     ANALYZE TABLE T2 ESTIMATE STATISTICS;   --테이블별 샘플로 일부 분석

   - 

   - ANALYZE 후 T2테이블결과 보기

   - CHAIN_CNT : 이 갯수가 많으면 SELECT속도가 너무 느려진다.

     이 CHAIN_CNT 갯수 없애주기

     * 방법1 : 저장장소(tablespace)이동하기

                . 저장장소를 이동하게 되면 이동한 tablespace에서 테이블 신규생성

                 , 하나의 레코드씩 insert 시키므로 chain이 없는 깨끗한 block으로 만들어 진다.

                . SELECT * FROM V$TABLESPACE;  --사용할 수 있는 저장장소 목록 조회

                . ALTER TABLE T2 MOVE TABLESPACE SAMPLE;

                . ALTER TABLE T2 MOVE TABLESPACE USERS;

       이동 후 분석결과 - chain-cnt가 0 으로 됐다.

      추가 : . 테이블이 이동 된 경우 테이블 레코드의 ROW_ID가 변경된다.

               이로인해 index 에서 가지고 있는 row_id와 table의 row_id가 서로 상이하게 된다.

             . 이 상태에서 where조건에 index column을 연결해도 db는 index의 상태를 확인하고 

               UNUSABLE 상태를 확인 후 table full scan을 수행하게 된다.

             . index 상태 확인 방법

               - SELECT index_name, status FROM user_indexes WHERE table_name = 'emp';

             . 그래서 index도 다시 생성 되어야 한다. 

               - drop index ..;

               - create index ...;

               - alter index .... rebuild;

                   --rebuild중 DML 문장(crud) 사용불가.

               - alter index index_name rebuild online;

                  --내부적으로 drop & create를 하게 됨.

                  --rebuild중 DML 문장(crud).

     * 방법2 : 신규테이블을 생성 후 INSERT하기

              . 이런방법도 가능하지만 내부적으로 이유는 모르지만 위 MOVE 사용방법을 더 권장

--관리 파일

remark '구','신' 이라는 라인 조회 되지 않음

set verify off

remark  모든 메세지가 조회되지 않음

remark set termout off

remark  첫줄에 컬럼 heading안보이게 처리

set heading off  

remark 처리 결과 레코드수 안보이게 처리

set feedback off  

set autotrace off 

SPOOL MOVET.SQL

SELECT 'ALTER TALE ' || TABLE_NAME || ' MOVE TABLESPACE EXAMPLE;'

   FROM USER_TABLES

UNION ALL

SELECT 'ALTER TABLE ' || TABLE_NAME || 'MOVE TABLESPACE USERS;'

  FROM USER_TABLES;

SPOOL OFF

@MOVET.sql

remark set heading on

remark set feedback on

remark set autotrace on

3. 테이블과 index 간 insert,update 구조

   ------------------

   테이블    : index

   ------------------

   INSERT    INSERT

   DELETE    DELETE

   UPDATE   DELETE & INSERT

   ------------------

   즉, 테이블에 update문장이 수행되면 index는 delete & insert가 수행 됨

3. Index Block

   . 특징

     - NULL값을 포함하지 않음 

     - B*Tree구조 (Blance)

     - 저장구조 : [RH : index컬럼갯수 : 자리수 : 값 : 자리수 : 값...] (RH : Row Header)

   . 저장구조 예

     -> 컬럼 : ID, NAME

     -> 컬럼 ID에 index 존재

     -> 값 : 1234, 'ABC'

         값 : NULL, 'DEF'

         값 : 4567, NULL   -> 일 경우 아래 index 형태로 저장됨

     -> [RH : 1 : 4 : 1234 : Rowid]

         [RH : 1 : 0 : Rowid]           <--하지만 이 자료는 만들어지지 않음.

                                                NULL은 index Block에 저장되지 않음

         [RH : 1 : 4 : 4567 : Rowid]

   . ROWID : OFBR : Object(6자리) + file(3자리) + block(6자리) + row(3자리)

   . index block생성 시 null은 생성하지 않음.

   . SELECT ....

     WHERE id is null ;  과 같이 검색할 때 id가 index를 존재 해도 'TABLE ACCESS FULL' scan하게 됨

                           null 값은 index block에 생성되지 않기 때문

   . index 재구성 시점

     - table을 다른 tablespace로 move했다 다시 원래 tablespace로 move했을 경우

     - 잦은 delete와 insert로 밸런스가 깨졌을 경우

   . 밸런스가 깨졌는지 확인 방법. index 구조를 분석.

     - ANALYZE INDEX idx_name VALIDATE STRUCTURE;

     - SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS Balancing FROM INDEX_STATS;

        . DEL_LF_ROWS_LEN : DELETE LEAF ROWS LENGTH : 삭제된 leaf row의 길이

        . LF_ROWS_LEN : 현재 총 leaf rows 의 길이

        . 이 숫치가 0 이면 최상이고, 클 수 록 조금씩 이슈가 발생한다.

        . oracle에서 20 이상이면 rebuild 하길 권장하고 있음.

   . 인덱스의 효율

     - 선택도가 낮을 수록 인덱스 효율이 좋다.

     - 선택도 범위:  0 <= selectivity <= 1

     - 선택도 값 : selectivity = 1 / distinct value;

       . 예) select 1/(distinct deptno) from emp;

     - SELECT column_name, num_distinct, density, num_nulls

         FROM USER_TAB_COL_STATISTICS

        WHERE table_name = 'EMP'

        ORDER BY 1;

        . NUM_DISTINCT : 각 컬럼별 distinct 한 수

        . DENSITY : 선택도 값

        . num_nulls : 숫자컬럼중이 null값 수

      - 인덱스 효율을 보는 script 파일 만들기

col owner for a10

col column_name for a25

store set saved_settings replace

set termout off

set verify off autotrace off

set feedback on termout on

prompt

prompt columns of table &1:

prompt

select column_name, num_distinct, density, num_nulls

  from user_tab_col_statistics

 where table_name = upper('&1')

 order by 1;

@saved_settings

set termout on

4. Reverse Index

   - 생성이유 :하나의 Block에 I/O가 너무 많이 집중 될 경우 index lock등이 발생한다.

      Index Block Header정보에 I/O가 발생하게 되면 기록을 하는데 무한대로 접근이 하닌

      Limit이 있기 때문에 index lock이 발생한다.

   - 예) 항공회사.항공권 테이블

         . 컬럼 : 항공권번호, 출발일, 도착일, 기종, 게이트번호, 도착지...

         . INDEX : 항공권번호  (규칙:201802031130G30S25 : 출발일 + 게이트번호 + 좌석)

         . 이때 출발일을 기준으로 모든검색이 이루어 지기에 몇개의 block에 I/O가 집중된다.

           만약, 거의 모든 BLOCK으로 I/O를 분산시킬 수 있다면 가장 좋을텐데..

           이 해법이 Reverse Index가 된다.

   - 예) CREATE INDEX IDX_EMP_ENAME_REV ON EMP(ENAME) REVERSE;

        . Reverse index : 52S03G031130208102 

   - Reverse Index를 db가 검색시 내부적으로는 정상index처럼 검색이 이루워진다.

     그래서 최종결과는 정상 index처럼 정렬되어 나온다.

4. Bitmap index

   - 예) 결혼정보 회사

        . 회원정보 : 이름, 나이, 성별, 연봉,직업, 거주지, 가족관계, 재산,자녀수,키,국적,취미,종교,...

        . index : 거의 모든컬럼이 index가 구성되어야 한다. 자주 검색조건에 포함 되므로

        . 이럴 경우 Bitmap index를 활용

   - 정의 : 컬럼값을 Bitmap (숫자 0 과1 의 조합)으로 mapping시킨다.

   - 속도 : 특별한 상황에서 일반 index보다 약 30배 속도가 더 빠르다.

   - 생성 ) CREATE BITMAP INDEX IDX_EMP_JOB_SAL ON EMP(JOB, SAL);

   - SQL : 개발자는 특별히 bitmap index라고 해서 다르게 sql문장을 작성할 필요는 없다.

   - 단점) 

      . 예) 성별컬럼 : 남,여  (1,0) 으로 Bitmap index가 존재 하고 있었는데

           법이 변경되어 성별에 종류가 추가 될 수 있다. (남,여,게이,트렌스젠더..  등)

           만약, 게이라는 성별을 가진 신입회원이 가입하게 되면

           전체 회원의 성별컬럼에 대한 Bitmap index가 전체 재 생성된다.

           (지금까지 1 자리 bitmap을 사용하다가 2자리 bitmap이 필요하기 때문)

           이런 회원이 첫 신규 가입하게 되면 속도가 어마어마하게 느려진다.

4. Index + Table 이 결합된 IOT

   - 정의 : Index와 Table이 결합된 구조

   - IOT 테이블 생성)

     CREATE TABLE IOTTEST

        ( NO NUMBER CONSTRAINT IOTEST_PK_NO PRIMARY KEY,

          TITLE VARCHAR2(50),

          CONTENTS VARCHAR2(500)

        )

       ORGANIZATION INDEX TABLESPACE USERS PCTTHRESHOLD 40

       INCLUDING TITLE

       OVERFLOW TABLESPACE USERS ;

      . ORGANIZATION INDEX TABLESPACE USERS  : 이 문장으로 IOT 가 된다

      . PCTTHRESHOLD 40

         : 전체 DATA를 저장하는 block에 40%를 찾이 할 경우 별도 block에 이동시킴

         : 

     INSERT INTO IOTTEST VALUES(1111, 'AAA', 'BBB');

     INSERT INTO IOTTEST VALUES(3333, 'AAA', 'BBB');

     INSERT INTO IOTTEST VALUES(9999, 'AAA', 'BBB');

     INSERT INTO IOTTEST VALUES(2222, 'AAA', 'BBB');

     SELECT * FROM IOTTEST;     

      . 조회결과 : 1111, 2222, 3333, 9999 순으로 조회 됨

     . INDEX FAST FULL SCAN : Index block을 한번에 여러게 읽어 옴

   - 보통 자료를 Insert 후 자료를 자료를 SELECT하면 insert한 순서대로 조회가 된다.

     하지만 IOTTEST 테이블은 PK를 기준으로 B*Tree를 만들고 최종 Leaf Node에 레코드가 존재 한다.

     그래서 자료를 조회 하면 정렬이 된 결과를 볼 수 있다.

  - IOT 단점

    . Index는 항상 정렬된 순서대로 구조를 만들게 된다.

     그래서 값들이 계속 추가 되면 속도가 느리다.

    . 값이 큰 자료가 들어 올 경우 자즌 split등이 발생하여 속도가 느려린다.

      그래서 PCTTHRESHOLD 속성을 이용해서 조정 할 수 는 있다.

4. Clustered Index

   - 

4. index 통계정보 만들기

  예제 : 부서에 index가 존재하고 있는데, 한 부서(A10)에 약 90%인력이 포함되어 있고,

  나머지 인력은 10%로 분산 되어있다고 가정할때..

  CBO가 정상적이지 않으면 A10 조건이 올 경우 INDEX SCAN을 해서 TABLE FULL SCAN보다 

  속도가 더 오래 수행 될 수 있다.

  해결방법 : ANALYZE를 해주면 CBO가 A10번 부서가 조건이 걸릴때 INDEX SCAN하지 안고 

  TABLE FULL SCAN하게 된다.

   - 통계정보 분석하기

     . ANALYZE TABLE emp ESTIMATE STATISTICS FOR TABLE 

        FOR COLUMNS DEPTNO SIZE 20  SAMPLE 10 PERCENT;

      . SIZE 20 : bucket 크기. 이 bucket을 이용해서 db는 자료를 담아 가면서 분석

      . SAMPLE 10 : 전체 크기에서 SAMPLE 10%를 가지고 analyze 하기.

    - 분석정보 만들기

      . DESC DBMS_STATS;  --package정보 보기

      . EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'EMP',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1');

      . 필수 argument값 만 기술해도 됨

      . cascade=true : 해당 테이블에 연관되어 있는 정보도 같이 조회.

      . ALL COLUMN SIZE 1 : 모든 컬럼들 다 분석 및 조회

    - 히스토 그램은 index가 있고 value의 분포가 불규칙한 컬럼에만 생성하자.

4. index 선택기준

   - 정의 : index가 여러개 존재할 때 어떤 index를 사용하지 선택 판단기준.

   - 선택기준 

     . 밸런싱 

     . 선택도

     . 조건절의 비교연산자 ( => 보단 =로 되어 있는 index)

   - CBO정보를 항상 최신정보로 유지시켜줘야 DB성능이 좋아진다.

   - 두개의 index를 동시에 찾아서 index scan을 할 수 도 있다.(hint 이용)

      . JOB에 index존재.  DEPTNO에 index존재 시 아래 sql문장 수행

      . 2개의 index를 동시에 사용할 수 있다.

      . SELECT /*+ AND_EQUAL(EMP, IDX_EMP_JOB, IDX_EMP_DEPTNO) */

                 EMPNO, ENAME, JOB, DEPTNO

          FROM EMP

         WHERE JOB = 'MANAGER' AND DEPTNO= 10;

      .  

              : 8i 도입 후 속도에 이슈 발생으로. 10g폐기. 11g 사용안됨  

      . SELECT /*+ INDEX_COMBINE(EMP, IDX_EMP_JOB, IDX_EMP_DEPTNO) */

                 EMPNO, ENAME, JOB, DEPTNO

          FROM EMP

         WHERE JOB = 'MANAGER'  AND DEPTNO= 10;

5. ORDER BY (sort, 정렬)

   - User가 접속하면 SESSION하나가 생성됨

     하나의 Session에 PGA 영역 존재

     PGA 영역에  Sort Area 존재

     Sort Area는 메모리 생성되는데 이 메모리가 부족하면 disk와 swapping한다.

     이러므로 속도가 느림

     추가로, 이 Sort Area는 각 session별로 관리 되기에 다른 session과 공유가 안된다.

     그래서 같은 정렬을 다른사용자가 해도 각각 sorting이 일어 난다.

   - 정렬 이유

     . Order by

     . Group by

     . distinct

     . minus

     . union

   - 예)

    SELECT * FROM EMP A

    MINUS

    SELECT * FROM EMP_HIS B;

     . 수학: A-B = A- (A교집합B)  (교집합:Intersection)

       이때 A교집합B는 항상  A정렬, B 정렬 후 서로 비교해 가면서 값을 추출한다.

       정렬 후 비교하므로 최종결과는 ORDER BY 된 순서로 조회 된다.

     SELECT * FROM EMP a 

      WHERE NOT EXISTS (SELECT 1 FROM EMP_HIS b WHERE a.empno = b.empno);

     SELECT * FROM EMP

      WHERE empno not in (SELECT empno FROM EMP_HIS); 

    SELECT A.*

      FROM EMP A LEFT OUTER JOIN EMP_HIS B

              ON (A.empno = B.empno)

     WHERE B.empno is null;

     SELECT * FROM EMP

     UNION ALL

     SELECT * FROM EMP_HIS;

        . UNION ALL 은 정렬이 발생하지 않아 빠르다.

4. Extent

   - 정의 : 여러개 block을 묶어서 관리하는 단위 

   - Blocks : 값이 존재하는 block

   - Empty block : 값이 비어있는 block

   - HWM(high water mark) : block과 empty block과의 경계선

     . 

5. 자료 조회 순서

   1. 먼저 index scan

   2. index의 row id를 기준으로 data block 읽기

   3. block을 읽어서 DBC(Data buffer cache)로 저장함.

6. index scan을 안하는 경우

   - index column을 변경시켰을 경우

   - null값 질의

   - 형변환 : 서로 다른 자료형을 일치 시킴

   - 부정연산자 : 대상의 범위가 넓어지므로 full table scan 을 함.

   - 예  (TABLE ACCESS FULL)

     where sal * 12 > 36000;                ->where sal > 36000/12;

     where sal + 0 = 3000;                  

     where year||month||day='20180127' ->where year = '2018' and month='01' and day='27'

     where name || '' = 'KIM'               ->일부러 name에 index scan안되게 하는 방법

     where upper(name) = 'HONG'

     where name is null;

     where deptno like '1%'           -> deptno가 number이므로 deptno를 형변환하기에 index scan안함

     where to_char(deptno) like '1%' -> deptno가 number이므로 deptno를 형변환하기에 index scan안함

     where deptno != 10;    -> != 부정연산자를 사용시 index scan을 안함

7. function-based normal index

   - 정의 : 컬럼값에 변형을 가해서 만들어진 index

   - CREATE INDEX IDX_EMP_SAL12 ON EMP(SAL*12);

   - SELECT * FROM EMP WHERE SALE*12 >= 36000;

8. Table Recycle

   - DROP TABLE table_name;

   - 수행했던 이력 보고 : C:\app\user\oradata\orcl\*.LOG 파일에 정보가 존재.

     . *.LOG파일은 Text파일이 아니고 binary파일 이여서 바로 볼 수 없다.

     . 이 파일들을 지우면 DB가 아에 수행되지 않는다.

   - 이전에 수행했던 명령어 삭제방법

     . alter system switch logfine;

     . 위 문장을 3번 쓰면 됨.

     . 3번 쓰는 이유 : *.LOG파일이 3개 존재 하는데 위 문장을 한번 수행할 때마다

       첫 번째 LOG파일이 두 번째 LOG파일로 이동된다. 그래서 3번 수행하면

       사라지게 되는 이유이다.

   - SHOW RECYCLEBIN;

      . DROP된 테이블 목록 보기

      . 특정시점에 레코드 정보도 볼 수 있다.(??)

   - FLASHBACK TABLE table_name TO BEFORE DROP;

      . drop이전으로 테이블을 복구 시킬 수 있다.

      . 단점, FLASHBACK 복구 후에 인덱스의 상태 확인하기

       >COL INDEX_NAME FORMAT A40

       >COL TABLE_NAME FORMAT A20

       >COL COLUMN_NAME FORMAT A20

       >SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS;

       index_name이 깨진 현상을 볼 수 있다.         

      . 해결방법 : index rename

       - ALTER INDEX "BIN$2+6uQ8eVSFaZjrGsYdM3XQ==$0" RENAME TO IDX_EMP_SAL;

   - 복구 가능한 정보는 그리 오래 가지고 있지는 않다.

   - PURGE RECYCLEBIN ;   --휴지통 비우기

[ Nexacro.Grid - cell 좌표 ]

1. 정의 : grid에서 특정cell의 expandimage등을 클릭하면 필요에 따라 popupdiv를 띄우게 된다.

             이때 클릭한 cell의 좌표를 찾아서 popupdiv를 띄워 줘야 된다.

2. 예)

var nHeadSize = obj.getFormatRowSize(-1);   //head height 구하기

var arrCellRect = obj.getCellRect(e.col, e.cell);   //grid col, cell 위치 구하기
var nDivX = system.clientToScreenX(obj, arrCellRect.left) - system.clientToScreenX(application.mainframe, 0);
var nDivY = system.clientToScreenY(obj, arrCellRect.bottom) - system.clientToScreenY(application.mainframe, 0);

 this.popup_Filter.trackPopup(nDivX, nDivY);   //grid 클릭한 col, cell 에 popup창 띄우기

   - 실행계획테이블에 저장된 실행계획을 조회

[ Oracle-실행계획 ]

1. Optimizer

    . RBO (Rule Based Optimizer)

       - 규칙기반

       - Oracle 7까지 기본 Optimzer방식이였음. 현재는 RBO방식을 사용하지 않음

    . CBO(Cost Based Optimzer)

       - 비용기반

       - CPU사용시간, Memory사용량등을 기반으로 수행

       - DB, Schema등 통계정보 관리

2. 실행계획 초기설정 방법

    . sql수행 후 실행계획을 보려면 아래 파일을 수행하면 됨

    . plan table생성 및 plustrace권한 부여

    . 파일위치 : C:\app\user\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql

    sys>@     --@를 key in 후 위 파일을 drag & drop 해서 sqlplus창에 이동

                   --@는 파일내부의 내용을 수행하는 예약어

                   --sys계정만 plustrce.sql 수행 가능 (즉, 실행계획 수행되도록 설정가능)

                   --이 설정을 해야 만 set autotrace on;등을 할 수 있음.

    sys>grant plustrace to scott;   --scott에 권한부여

                                              --이 권한이 부여 되어야 scott사용자는 set autotrace on;을 할 수 있음.

    scott>set autotrace on;   --sys에서 권한이 부여되었기에 정상수행

                                      --이후 수행되는 sql들은 실행계획도 같이 조회 됨

3. 실행계획 이해방법 1

    . 들여쓰기 존재

    . 맨 마지막 level 부터 수행

    . 같은 level에서는 위쪽라인부터 수행

3. 실행계획 이해방법 2

    . INDEX RANGE SCAN

      - index 범위 scan

    . TABLE ACCESS BY INDEX ROWID

      - index의 rowid를 이용해서 table access

      - 조건절에 rowid를 이용하게 되면 index scan없이 바로 table access가능

    . RBO 수행 성능 Rank

       1. Single row by rowid

       2. Single row by Cluster Join

       3. Single row by Hash Cluster key with Unique or Primary key

       4. Single row by Unique or Primary Key

       5. Clustered Join

       6. Hash Cluster Key

       7. Indexed Cluster Key

       8. Composite Index

       9. Single-Column Indexes

          - index range scan

       10. Bounded Range Search on Indexed Columns

       11. Unbounded Range Search on Indexed Columns

       12. Sort Merge Join

       13. MAX or MIN of Indexed column

       14. ORDER BY on Indexed Column

       15. Full Table Scan

1. INDEX RANGE SCAN : index는 존재 하지만 nonunique index인 경우 equal 검색할 때 index range scan으로 수행

2. INDEX UNIQUE SCAN : unique index인 경우 equal 검색할 때 index unique scan으로 수행

3. Full Table Scan : 상당히 느리지만 DB는 읽을때 한번에 128개 block(default size)만큼 읽어서 검색한다.

                       생각보다 큰 size를 읽어 메모리에서 검색하기 때문에 때론 빠를 수 있다.

                       (show parameter db_file_multi)

                       HWM(high water mark)까지 읽어 들인다.

===========================================================

**  위 15개 이외의 실행계획

2. HASH GROUP BY 

   - 

===========================================================

1. 실행계획 - Statistics

    . recursive calls : - db입장에서 사용자가 수행한 sql을 call이라고 인식함)

                        - 요청된 sql에 대한 권한등을 db입장에서 내부적(table)으로 처리하는 sql수 횟수

    . db block gets

       - CURRENT mode에 있는 block의 데이터를 읽은 숫자

       - TKPROF 레포트에서 current에 해당하는 값이구요.

       - current mode에서는 곧 수정될 Segment header나 block을 얻을 때 일어납니다. 

       - INSERT, UPDATE, DELETE에서 데이터 쿼리부분이 아니라 

          수정될 값을 읽을 때 나타나는 숫자입니다.

       - select문일 경우에는 Full table scan일 경우 Segment header를 읽을 때 나타납니다. 

    . consistent gets

       - consistent mode에서 db block read를 수행한 숫자

       - TKPROF 레포트에서 query에 해당하는 값입니다. 

       - consistent라는 말은 read consistency와 관련이 있는데 즉 읽기 일관성이 보장되는 상황에서 읽는 숫자라는 것입니다. 

       - 데이터를 수정하지 않고 단지 읽기만 하기때문에 lock이 발생하지 않습니다. 

    . physical reads : disk에서 읽은 block수

    . redo size : 

    . bytes sent via SQL*Net to client : 

    . bytes received via SQL*Net from client : 

    . SQL*Net roundtrips to/from client : 

    . sorts (memory)  : memory에서 정렬 수

    . sorts (disk)       : disk에서 정렬 수

    . rows processed : 조회한 record수

=========================================================== 

1.EXPLANIN PLAN

  - PLAN_TABLE

    . oracle 10g 이전 버전

    . 관리자가 생성해야 됨

    . 생성파일 : C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\??.sql

  - SYS.PLAN_TABLE$ : oracle 10g이후 버전 부터는 자동생성되어 있음.

  - DBMS_XPLAN.DISPLAY('테이블명', '문장ID', '형식')

    . package를 통해서 실행계획을 볼 수 있음

    . 실행계획테이블에 저장된 실행계획을 조회

2. EXPLANIN PLAN 수행

  - SCOTT>explain plan

             set statement_id = 'FULL_EMP'

             for

             SELECT * FROM EMP;

  - PLAN 테이블에 'FULL_EMP' id 로 실행계획을 저장

3. EXPLAN 내역 보기

   - 아래 sql문장을 파일로 저장 후 수행하면 됨

     수행시 statement_id를 요구함 'FULL_EMP' 를 입력하면 됨

   - 

   COL PLAN FORMAT A30

   --SET VERIFY OFF

   ACCEPT statement_id PROMPT "문장 id 입력:"

   SELECT cardinality "ROWS",

             lpad('', level-1) || operation || '' || options || '' || object_name "plan"

      FROM PLAN_TABLE

   CONNECT BY prior id = parent_id

       AND prior statement_id = statement_id

    START WITH id=0

       AND statement_id = '&statement_id'

   ORDER BY id;

  - COL PLAN FORMAT A30 : PLAN 컬럼의 자리수를 30자리까지 조회 시켜줌.

  - SET VERIFY OFF  : 수행 후 '구', '신'이라는 목록이 안 보임

4. DBMS_XPLAN.DISPLAY('테이블명', '문장ID', '형식')

   - 실행계획테이블에 저장된 실행계획을 조회

   - SELECT *

       FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table', 'FULL_DEPT', 'TYPICAL'));

5. SQL*TRACE

   - 사용자의 특정한 작업을 볼 수 있음

   > show parameter trace

   - 결과 : sql_trace : FALSE  로 되어 있음.

            TRUE : 현 db에서 수행되는 모든 sql문장 수행에 대한 실행계획을 모두 생성함

  - 현 session만 sql trace 수행

  - 권한 필요 ( SYS>grant alter session to scott; )

  SCOTT>ALTER SESSION SET SQL_TRACE=TRUE;     --현 Session을 기준으로 지금부터 수행된 sql에 대한 Trace 정보를 생성 시작

  SCOTT>SELECT EMPNO, ENAME, SAL FROM EMP;   --sql수행 하면 실행계획테이블에 실행계획이 저장됨

  SCOTT>SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO > 50; 

  - 수행 후 SQL TRACE 정보는

    : C:\app\user\diag\rdbms\orcl\orcl\trace\ 폴더 하위에 생성 됨

  - trace 파일 내용보기

   . 단순 notepad등으로 보면 너무 지저 분함

   . cmd 창에서 아래 처럼 수행하면 trace파일을 일정한 format(이쁘게 ^^)으로 test.txt 파일로 만들어줌.

   . sys=no : 실행계획 결과에서 Statistics 목록에서 recursive calls에 대한 정보는 생성안함.

   C:\tkprof orcl_ora_4336.trc test.txt  sys=no  

   C:\notepad test.txt

6. V$ dynamic performace view

   - 배우지 않았음.

(참고 : Berryz WebShare  (파일공유))

--sql trace

SELECT d.value || '/' || lower(c.instance_name) || '_ora_' || b.spid || '.trc'  "User Trace File"

FROM v$session a, v$process b, v$instance c, v$parameter d,

          ( select * from v$mystat where rownum = 1 ) e

WHERE a.paddr = b.addr

AND a.sid = e.sid

AND d.name = 'user_dump_dest' 

/

[ sql*plus 명령어 모음 ]

0. sqlplus접속

    c:\sqlplus scott/tiger

    c:\sqlplus sys/oracle  as sysdba    --이때 sys 사용자는 반드시 normal, sysdba, sysoper 중 하나로 접속해야 됨

1. 화면 설정

    > set linesize 200;   --조회결과를 200 자까지 넓혀줌. (cmd창 width도 조절필요)

2. SQL 수행 Log 파일 저장

    > save idx(파일명)  --최근에 수행된 sql문장이 idx.sql 파일에 저장됨

                                --저장파일 위치 : cmd창에서 sqlplus수행했던 경로에 저장 됨

    > save idx replace   --기존에 파일이 존재 시 replace함.

    > ed idx        --저장된 sql문장들을  메모장으로 보여줌

    > @idx         --저장된 파일을 읽어서 실행시켜줌.

    > set sqlprompt "_USER>"  --현재 접속 user id로 prompt를 설정

    > spool on   --spool 설정

    > spool T1(파일명)  --수행된 sql문장, 결과를 T1파일에 저장

                       --저장파일 위치 : cmd창에서 sqlplus수행했던 경로에 저장 됨

                      --spool off를 해야만 파일에 sql수행내역이 저장 됨

    > spool T1 append   --T1파일이 기 존재시 append함

    > spool off   --spool 설정 해제

                      --이때 sql수행내역이 저장 됨

3. Prompt

    > SET SQLPROMPT "_USER>"     --prompt를 접속user로 변경

    > set time on/off                          --프롬프트에 현재 시간 조회 및 제외

4. 실행계획

    . 실행계획 초기 설정 : 별도 문서 존재

    > set autotrace on                          --sql수행시 실행계획 조회 on

    > set timing on                                --sql 수행결과 시간 조회

    > show parameter optimizer           --optimizer_mode 보기

       - CHOOSE            : CBO, RBO 자동 선택 옵션

       - ALL_ROWS         : 전체 data가 빨리 나오는 기반으로 실행계획 생성 옵션.

       - FIRST_ROWS     : 첫번째 data가 빨리 나오는 기반으로 실행계획 생성 옵션.

       - FIRST_ROWS_n : n개  data가 빨리 나오는 기반으로 실행계획 생성 옵션.

70. 사용자추가 및 사용자내용 변경

    > create user dev

       identified by dev

       password expire;                      --사용자 추가

                                                         --expire : 첫 login시 바로 pw변경

    > alter user dev identified by "tiger";  --dev 사용자에 대한 password 변경

    . system> alter user scott     --약 v11부터는 scott 사용자는 존재 하지만 lock했음.

                                                 --그래서 scott사용자를 사용하려면 unlock을 해야 됨

        identified by tiger     --식별자를 tiger 로 설정(pw)

        account unlock;      --scott 계정 unlock 해제 (마지막 라인에는 ; [세미콜론 필요]

80. 권한 부여

    > grant create session to dev;

    > grant connect, resource to dev;   --접속권한 부여

    > grant plustrace to dev;              --dev에 plustrace권한 부여 (실행계획 권한부여)

81. 권한부여(테이블)

    > grant select on scott.emp to dev;  --dev에 scott.emp 테이블 select 권한부여

    > grant create table to dev;              --테이블 생성권한 부여

    > grant all      on scott.emp to dev;  --scott.emp 테이블의 모든 권한을 dev에 부여

    > alter user 유저명 default tablespace users quota unlimited on users;  --tablepsace권한부여

90. 설치환경 확인

    > show user

    > show parameter

    > show parameter background       --background가 포함된 parameter조회

    > show parameter user_dump_dest --dump 경로

91. 진단도구 

    - background process가 남기는 log

    - 시스템에 대한 이상징후 및 처리 결과등을 남김

    > show parameter user_dump_dest --dump 경로

      --경로path : C:\app\user\diag\rdbms\orcl\orcl\trace

    > show parameter core_dump

    > show parameter 

91. 진단도구2

    - 정의 : 보통 DB Server의 부하는 하루 중 특정시간대가 존재한다.

             그래서 부하가 시작하기 전과 부하가 종료한 후의 상태를 서로 비교해서 DB를 관리할 수 있다.

    - 진단방법 : 성능에 이슈가 있는 시점(시간)에 이유를 찾기 위해서 사용하는 도구

    - 진단정의 : 특정시간대의 DB Server에대한 상태를 관리(테이블에 저장)

    - 성능이슈 : 회사에서 보통 성능이슈가 있는 시간대가 동일(비슷)하다.

                  그래서 성능이슈가 발생하기 이전시점 ~ 종료시점 까지의 DB상태를 점검할 수 있다.

   - 성능이슈 이전시점 : 현상태 저장(시작)(테이블을 생성하여 사용되는 SQL문장등의 상태를 저장)

      >@C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlbstat.sql

   - 성능이슈 발생종료시점 : 현상태 저장(종료)(임시테이블의 내용을 Report로 생성. 임시테이블 삭제)

      >@C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlestat.sql

   - 최종결과는 sqlplus를 수행했던 폴더에  'report.txt' 파일로 생성됨

     . 시작시점의 상태와 종료시점의 상태를 저장해서 두 자료를 비교해서 DB의 차이점을 비교 분석

   - 만약, 파일이 만들어지지 않는다면  cmd창을 수행할때 '관리자권한으로 실행'하기로 하면 됨

   - 단점 : . utlbstat.sql 파일을 수행하면 임시테이블인 $ 테이블들이 생성 됨

             . sys 계정으로 만들기 때문에 항상 일반사용자보다 사용/성능에 우선순위가 부여되어

               일반사용자는 성능이 늦어 진다.

             . sys tablespace쪽에 만들어짐

   - 위 단점을 보완 : statspack package 도구 존재

   - 참고 : 진단시작(utlbstat.sql 수행)을 시작했다고 해서 그때 부터 계속 진단테이블에 저장하는것은 아니다.

            이때 당시의 상태를 snapshop형태로 저장해 놓고, 

            이후 특정시점에 이전 snapshop정보와 서로 비교해서 분석자료를 만들어 낸다.

91. 진단도구2 - report.txt 파일 보기

    . 테이블 : stats$lib; 에 대한 결과

    . GETS : 요청

    . GET HIT RATI : 요청에 대한 memory에서 찾은 비율

92. 진단도구2 - statspack package

    - 정의 : 중요한 포인트(특정시점)마다 DB Server의 상태를 저장 함.

             각 시점간의 DB정보를 기준으로 서로 비교해서 차이점을 분석,비교해준다.

    - 장점 : utlbstat.sql 진단도구의 단점을 보안해서 나옴.

    - 파일 : spcreate.sql

    -수행. 진단시작

     . 사용자생성 : PERFSTAT

     . 성능테스트용 : sp package 생성  (desc statspack)

    >@C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\spcreate.sql

    - db상태 저장

      . snap 과 snap간의 상태를 볼 수 있음.

    PERFSTAT>desc statpack            --생성된 sp package보기

    PERFSTAT>exec statspack.snap;    --현재의 db상태를 snapshop형태로 찍어서 저장

    PERFSTAT>exec statspack.snap;    --매일 하루에 한번식 수행

   - snapshop 정보 보기

     . 위쪽에 snapshop 목록이 보여줌.

     . 각 snapshop간에 db 상태정보를 볼 수 있다.

    PERFSTAT>@C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\spreport.sql

    - statspace 도구 삭제

    SYS>@C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\spdrop.sql

    - statspack package : schedular에 등록하기

      . 등록sql :   select instance_number into :instno from v$instance;

  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24/60,'MI'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

        . 앞쪽에 있는 날짜는 : 맨 첫 schedular가 시작하는 시간

        . 뒤쪽에 있는 날짜는 : 맨 첫 schedular가 수행된 후 계속 반복되는 수행간격 시간

                                 (예, SYSDATE+1/24/60  이렇게 한 경우 1분 간격으로 계속 수행 됨)

        . 만약, spauto.sql를 수행했는데 ' ..파일을 열 수 없습니다.' 라는 메세지가 나오면

         cmd창을 새로 열어서 'perfstat' user로 재 login하면 됨

         - 참고, autotrace 가 on 상태인 경우에 안될 수 있음. (set autorace off 수행 하면 됨)

    > @C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\spauto.sql

   - statspace 테이블 목록 보기

     . 단점 : 너무 많이 나옴

   > SELECT * FROM TAB;

   - schedular 삭제

   >select job, what from user_jobs;   --job 목록보기

   >DESC DBMS_JOB

   >EXEC DBMS_JOB.REMOVE(23)    --job 제거

99. 기타

    > HOST       --sqlplus를 빠져 나와서 CMD창으로 이동.

                       --cmd창에서   EXIT를 하면 다시 sqlplus창으로 이동 함

    > connect sys/oracle as sysdba   --dba권한으로  sys계정 login

    > startup force                       --db restart. sys만 가능

    > ALTER SESSION SET NLS_LANGUAGE='AMERICAN';  --sql수행 후 결과등을 영문으로 조회