How To Write Efficient SQL Queries with Tips N Tricks

Transcription

1 ORACLE 9 9i 개발자튜닝가이드 v0.92 (with SQLTools for Oracle) ORACLE 9i 개발자튜닝가이드 v0.92 Mail:heiya@nate.com Homepage: Last edited :

2 목차 - 목차 - I. ORACLE 의이해 1. ORACLE Optimizer 2. SQL 튜닝과옵티마이저의관계 3. Tables에대한 Access 4. SQL문의공유 5. FROM절에서의테이블순서 (RBO만해당 ) 6. Driving Table의선택 7. Optimizer의 Index 선택 8. Oracle의내부 Operation II. INDEX 의활용 1. INDEX를통한성능향상 2. INDEX를이용한작업 3. INDEX의우선순위가분명치않은경우 4. 두개이상의인덱스의 MERGE 5. 사용자에의한 INDEX 사용제한 6. ORACLE에의한 INDEX 사용제한 7. 인덱스컬럼에대한 NOT 사용제한 8. INDEX 컬럼에대한가공 I 9. INDEX 컬럼에대한가공 II 10. 결합 INDEX의선행컬럼사용과 SKIP SCAN의활용 11. 인덱스컬럼에대한 IS NULL / IS NOT NULL의사용제한 12. 인덱스가있는경우 UNION의사용

3 목차 III. SQL 활용 I 1. WHERE절내에서의 JOIN의위치 2. EXISTS 대신 JOIN의사용 3. 관계가없는테이블들에대한단순결합 4. Equal 비교와범위비교 5. 비교문사용하기 ( > 와 >= ) 6. IN 대신 EXISTS의사용 7. NOT IN 대신 NOT EXISTS 사용하기 8. DISTINCT 대신 EXISTS의사용 9. 중복되는레코드의삭제방법 10. ROW수 COUNT 하기 11. TABLE ALIAS의사용 12. WHERE와 HAVING의차이 13. SELECT절에서 '*' 사용하지않기 14. UNION-ALL의활용 15. ORDER BY를사용하지않고정렬하기 16. WHERE절에서주의할사항 17. IN의활용 18. DATE 사용시주의점 IV. SQL 활용 II 1. GROUP BY의대상건수줄이기 2. 불필요한작업을유발하는명령어의사용제한 3. DECODE를활용한내부처리절차단축 4. DELETE 대신 TRUNCATE의사용 5. DATABASE에대한 TRANSACTION 회수줄이기 6. COMMIT 명령어의실행 7. Query시테이블에대한참조 8. Explicit CURSOR의사용 9. Stored Function을활용한 SQL의부하감소 10. Analytic Function의활용

4 목차 V. TUNING 1. STATIC SQL의활용 2. 비효율적인 SQL을확인하는방법 3. TKPROF를이용하여성능에대한통계정보보기 4. SQL문분석을위한 EXPLAIN PLAN의활용 5. HINT의사용 6. Export와 Import의성능향상 7. 물리적 I/O의분산 8. CPU Tuning 9. STATSPACK을활용한성능분석

5 I. ORACLE 의이해 1. ORACLE Optimizer ORACLE은 Rule-Based Optimization(RBO) 과 Cost-Based Optimization(CBO) 를모두지원하고있다. CBO의경우,1992년 Oracle 버전 7부터도입되었고 RBO는간단한규칙위주로최적화를수행하는방법으로앞으로는널리사용되지않을것이며 ORACLE도공식적으로 CBO 사용을권장하고있다. ORACLE 에서사용자의질의한 SQL 은다음 4 단계를거쳐서수행된다. > 파싱 (Parser) > 옵티마이저 (Query Optimizer) > 로우소스생성 (Row Source Generator) > SQL 실행 (SQL Execution Engine) 1) 파싱 (Parser) 파싱단계는 SQL은구문 (syntax) 과의미 (semantics) 검사를수행한다. Syntax 체크는 SQL 문장의문법을검증하는단계이고, Semantic 체크는 SQL 문장내의오브젝트가존재하는지, 권한이존재하는지등을검증한다. 이단계가끝나면, SQL 문은파싱트리 (parsed tree) 형태로변형되어 optimizer에게넘겨진다. 2) 옵티마이저 (Query Optimizer) 옵티마이저 (Query Optimizer) 단계는앞에서넘겨받은파싱트리를이용해서최적의실행 계획을고른다. 이때 SQL 의결과를처리하기위한효율적인방법을결정하기위해내부적 인규칙을사용한다. 3) 로우소스생성 (Row Source Generator) 로우소스생성 (Row Source Generator) 단계는 Optimizer에서넘겨받은실행계획을내부적으로처리하는자세한방법을생성하는단계이다. 로우소스 란실행계획을실제로구현하는인터페이스각각을지칭하는말로, 테이블액세스방법, 조인방법, 그리고정렬 (sorting) 등을위한다양한로우소스가제공된다. 따라서, 이단계에서는실행계획에해당하는트리구조의로우소스들이생성된다 Edited by heiya

6 4) SQL 실행 (SQL Execution Engine) SQL 실행 (SQL Execution Engine) 단계는위에서생성된로우소스를 SQL 수행엔진에서 수행해서결과를사용자에게돌려주는과정이다. 여기서한가지주목할점은, 소프트파싱 (soft parsing) 과하드파싱 (hard parsing) 은크게옵티마이저단계의포함여부에따른차이라는것이다. 즉, 소프트파싱은이미최적화를한번수행한 SQL 질의에대해옵티마이저단계와로우소스생성단계를생략하는것이고, 하드파싱은이두단계를새로수행하는것이다. 따라서, 하드파싱은통계정보접근과실행계획탐색때문에시간이많이걸린다. 결국이차이가주로 SQL 튜닝전문가들이가급적이면하드파싱을피하라고권하는이유 이다. 하드파싱을줄이는방법은 V 장 TUNING 편에서다루도록하겠다. 2. SQL 튜닝과옵티마이저의관계 SQL 튜닝은특정 SQL 질의의수행시간을단축하기위해사용자가취하는다양한방법을통칭한다. SQL 튜닝의범위는굉장히포괄적인데, 옵티마이저와관련한방법으로는 SQL 재작성, 힌트사용, 새로운인덱스추가, 통계데이터의추가 / 갱신등을통해서옵티마이저가더욱더효율적인실행계획을생성하도록하는것이다. 1) SQL 의변경 사용자가원하는데이터를질의하는방법은실제로매우다양할수있다. 그러나 SQL의작성방법에따라 optimizer가다른경로로실행계획을수립할수있다. 이주제에대해서는뒤에서다시다루도록하겠으나 SQL의재작성을통한 SQL 튜닝은원래의 SQL 문을, 같은결과를내지만, 옵티마이저가더효과적인실행계획을생성할수있는 SQL 문으로바꾸는방법이다. 2) HINT 의사용과경로의제어 ORACLE의힌트기능은 ORACLE로하여금사용자가원하는경로의실행계획을만들어내도록하는것이다. 이주제또한뒤에다루겠지만 SQL의변경이나재작성없이실행경로를제어할수있는방법이다. 그러나 CBO를사용하는경우에는반드시지정한경로로실행되지는않는다. 통계 Edited by heiya

7 정보의유무등에의해사용자의 Hint가무시되는경우도있으므로반드시실행계획을확인하도록한다. 힌트외에인덱스를사용하지못하도록인덱스컬럼을가공하는등실행경로를제한하는방법도있다. 그러나이렇게힌트를사용하거나인덱스컬럼을가공하여작성된 SQL 은향후에도지속적 으로영향을미치므로주의하여야한다. 3) 새로운인덱스추가 SQL 문의효율적인처리를위해서는특정테이블의특정칼럼값을이용해서해당데이터를빨리찾아야하는데, 인덱스가없기때문에옵티마이저가어떤실행계획을선택하더라도그 SQL 문은느릴수밖에없는경우가있다. 이와같은상황에서는새로운인덱스생성을통해서옵티마이저가해당인덱스를이용하는새로운실행계획을선택하도록할수있다. 인덱스의생성과개수에대한얘기는뒤에서다시다루도록하겠다. 4) 통계정보의추가및갱신 Optimizer의비용산정모듈에서는테이블, 칼럼, 인덱스등에대한통계정보를이용해서선택도 (selectivity), 카디널리티 (cardinality) 등을구하고이를통해서궁극적으로실행계획의비용을계산한다. 그런데, 만일특정테이블 / 칼럼에대한통계정보가없거나, 오래전에만들어진경우는비용계산이부정확하게되고, 따라서옵티마이저가선택하는실행계획이실제로는안좋은실행계획일수가있다. 이를해결하기위해서는특정통계정보를추가하거나새로갱신해주어서옵티마이저가정확한비용산정을통해서더나은실행계획을선택하도록해주는방법이다. 3. Tables 에대한 Access ORACLE 은기본적으로테이블의 Row 에접근하기위해 2 개의방법을사용한다. 1) TABLE ACCESS FULL Full table scan은테이블의각 ROW를순차적으로읽는다. ORACLE은 full table scan의성능을향상시키기위해동시에여러개의 Block을읽는다. 특히 where절이없는 query를수행할때에는반드시 full table scan을하므로조심하여야한다 Edited by heiya

8 2) ROWID 를통한 TABLE ACCESS 테이블에대한접근성능을향상시키기위해서 RowID라고하는임의의컬럼값을사용하여각 Row에접근한다. RowID는 row가저장되어있는물리적인위치에대한정보를담고있다. ORACLE은데이터의물리적위치정보를담고있는 RowID와관련된 index를사용한다. 이인덱스를통해 ORACLE은 RowID에빨리접근할수있으며 index에사용된컬럼을찾는 query 의수행속도를향상시킨다. 인덱스를통한 Table 접근은 query의형태에따라 unique와 range로나눌수있다. 4. SQL 문의공유 ORACLE은 SQL문을 parsing한후동일한 SQL문이재실행될때다시 parsing하는부하를줄이기위해 SQL문과 parsing된정보를메모리에저장하여활용한다. 이정보는 System Global Area(SGA) 의일부분인 shared buffer pool내에 single shared context area에저장되며모든유저가공유한다. 이렇게저장된정보는 DB 내의어느사용자든동일한 SQL 문을실행하게때다시 parsing 하여 최적화된실행경로를생성해내야하는부하를줄일수있어더빠르게결과값을얻을수있다. 이는성능향상과함께메모리를절약하는방법중하나이다. DBA는효율적인 context area 사용을위해서는 cache로할당할메모리의양에대한적절한값을 init.ora 내의 parameter에지정하여야한다. Context area는클수록많은정보를저장하고활용할수있지만시스템의성능과용도에따라반드시그런것은아니므로숙련된엔지니어에게의뢰하는것이좋다. SQL 문이실행될때마다 ORACLE 은먼저 context area 에동일한문장에대한 parsing 정보가있 는지확인한다. 불행히도 ORACLE 은 carriage return, space 그리고대소문자를구분하므로이러 한문자들까지일치하도록하여야다시 parsing 하는부하를줄일수있다.. 이러한비교조건을만족시켜 shared area 내의 parsing 정보를사용하기위해서는아래의세가 지규칙을따라야한다. 1) 저장된 SQL 문과실행되는 SQL 문의모든문자가일치하여야한다. 예 ) SELECT * ; Edited by heiya

9 앞의예 ) 는다음의문장과전혀다른 SQL 로인식된다. ㄱ ) SELECT * from EMP; ㄴ ) Select * From Emp; ㄷ ) SELECT * ; 아래문장또한첫번째문장이두줄로분리되어있어다음동일한문장으로인식되지않 는다. ㄱ ) Select pin from person where last_name = 'LAU'; ㄴ ) Select pin from person where last_name = 'LAU'; 2) 새로실행되는 SQL 문내에서참조하는 object 가기존 SQL 문내의 object 와동일하여야 한다. 예 ) 다음예에서각유저는아래의 object 들을참조한다고하자. USER JACK JILL OBJECT 명 sal_limit work_city plant_detail sal_limit work_city plant_detail 종류 private synonym public synonym public synonym private synonym public synonym table owner 앞의예 ) 를참조하여다음의 SQL 의공유여부를살펴보자. SQL select max(sal_cap) from sal_limit; select count(*) from work_city where sdesc like 'NEW%'; select a.sdesc, b.location from work_city a, plant_detail b where a.city_id = b.city_id; select * from sal_limit where over_time is not null; OBJECT Matching NO YES NO NO 이유 각각의유저가 private synonym 인 sal_limit 을가지고있으므로서로다른 object 이다. 두 USER 모두 work_city 라는 public synonym 을참조하므로동일한 SQL 문이다. JACK 이 public synonym 을통해 JILL 의 plant_detail 을참조하므로서로다른 object 이다. 각각의유저가 private synonym 인 sal_limit 을가지고있으므로서로다른 object 이다 Edited by heiya

10 3) Bind variable 을사용할때 variable 명은모두동일하여야한다. 예 ) 다음두문장은동일한문장이다. select ename, empno from emp where empno = :pempno; select ename, empno from emp where empno = :pempno; 다음의두문장은 run-time 시동일한값이부여된다하더라도다른문장으로인식한다. select ename, empno from emp where empno = :pempno; select ename, empno from emp where empno = :v_emp_no; 5. FROM 절에서의테이블순서 (RBO만해당 ) ORACLE parser는항상오른쪽테이블부터왼쪽테이블로실행경로를생성한다. 만약 from절에여러개의테이블이름이나열된경우적은수의 row를가진테이블을오른쪽에배치하여 driving 테이블로만들어야한다. ORACLE이여러개의테이블에대한처리를할때, 내부적으로 sort나 merge procedure를통해테이블들이 join 된다. 먼저첫번째테이블을 scan하여 sort한후다음테이블을 scan한다. 그후이테이블에서추출된데이터를첫번째테이블에서추출된데이터와함께조합하여결과값을돌려준다. 예 ) Table TAB1 : 16,384 rows. Table TAB2 : 1 row. TAB2 을 driving table 로하여 select 할때. SELECT COUNT(*) FROM TAB1, TAB2; seconds TAB1 을 driving table 로하여 select 할때. (Poor Approach) SELECT COUNT(*) FROM TAB2, TAB1; seconds 3 개의테이블이조인될때, 상위테이블을 driving table 로선택한다. ERD 상의상위테이블 은그에종속되는많은테이블을가지고있다. 예 ) LOCATION 테이블과 CATEGORY 테이블은 EMP 테이블의속성정보를나타낸다. Case 1 Case 2 SELECT FROM LOCATION L, CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN; SELECT E, LOCATION L, CATEGORY C WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN; Edited by heiya

11 일반적으로 Case 1 이 Case 2 보다더효율적이다. 그러나항상그런것은아니며, 각각의경우마다서로수행성능을보일수있으므로 Application 에대한적용은충분한학습후하여야한다. 6. Driving Table 의선택 Query 수행시처음읽게되는테이블을 Driving Table 이라하며, 이는사용하고있는 optimizer 의모드나통계정보의유무등에따라결정된다. Cost-Base Optimizer(CBO) 를사용하는경우에는 analyze 명령어를통해생성된통계정보를이 용하여테이블의사이즈, 인덱스의유용성, 최소 cost 를요하는경로등을선택한다. Rule-Base Optimizer(RBO) 를사용하고 join 조건으로사용되는컬럼에대해 index가생성되어있거나, 힌트등에의해순서를지정하지않는경우 FROM 절의오른쪽테이블을 driving table로결정하게된다. 예 ) SELECT A.NAME, B.MANAGER FROM WORKER A, LODGING B WHERE A.LODGING = B.LODGING; LODGING 컬럼에대한 index 가사용가능하고, WORKER 테이블에비교가능한 index 가없을경 우, 인덱스가없는 WORKER 테이블이 Driving table 이된다. 이는 LODGING 테이블이 Driving table이될경우 WORKER 테이블을 FULL TABLE SCAN 하게됨으로써발생되는과부하를줄이기위 한 ORACLE 의내부메커니즘에의한선택이다. 7. Optimizer 의 Index 선택 Cost-Based Optimizer(CBO) 는 SQL 문을실행할때사용가능한 index 중 cost 가가장적게드 는 index 를선택하여사용한다. Index 의분포도가좋으면그만큼선택도 (SELECTIVITY) 가높게 된다. 예를들어, 100row 가있는테이블에어떤한컬럼이다른값들과구별이되는 row 가 80 개있 을때, 해당컬럼에대한인덱스의선택도는 80/100 = 0.80 으로높게나타난다. 인덱스의분포도가낮으면 INDEX RANGE SCAN 과 TABLE ACCESS BY ROWID 작업이 TABLE ACCESS FULL 에비해많은 I/O 를발생할수있다 Edited by heiya

12 그러나전체적인컬럼의분포도가좋더라도특정값에대한분포도가떨어진다면해당값에대한 query는인덱스를사용하지않는것이낫다. 일반적으로특정값의분포도가 10-15% 이상이면 full table scan이유리하나 row의전체건수, 시스템의성능등을종합적으로판단하여야한다. 8. Oracle 의내부 Operation ORACLE 이 Query 를수행할때사용하는명령어에따라여러가지내부적인작업을하게된다. 아래의표는 query 를수행할때수반되는내부작업들을보여준다. 명령어 ORDER BY UNION MINUS INTERSECT DISTINCT, MINUS, INTERSECT, UNION MIN, MAX, COUNT GROUP BY ROWNUM Join된 SQL문 CONNECT BY ORACLE 내부작업 SORT ORDER BY UNION-ALL MINUS INTERSECTION SORT UNIQUE SORT AGGREGATE SORT GROUP BY COUNT 또는 COUNT STOPKEY SORT JOIN, MERGE JOIN, NESTED LOOPS CONNECT BY 위와같은 ORACLE 의내부 operation 을잘이해해야만원치않는과부하를사전에방지하고원 하는결과를더빠른시간에얻을수있다 Edited by heiya

13 II. INDEX 의활용 1. INDEX 를통한성능향상 Index 는테이블로부터데이터를빠르게조회하기위해사용하는논리적이고또한물리적인실 체를가진 OBJECT 이다. ORACLE 은내부적으로 B-tree(Balanced-tree) 방식의인덱스구조를사용한다. Index 를통한데이터의조회는테이블내의데이터양이많을경우 full table scan 에의한 조회보다빠르다. ORACLE 의 optimizer 는 select, update, delete 시가장효율적인경로를생성 하기위해 table 에명시된 index 를사용한다. 또한여러테이블의 join 에도 index 를사용한다. Index 를사용함으로써얻을수있는또다른이점은 primary key 에의해유일성 (Uniqueness) 을보장받을수있다는것이다. LONG 이나 LONG RAW 타입의컬럼을제외하고는어떤컬럼을이용해서든인덱스를만들수있다. 일반적으로대용량테이블에만들어진인덱스는대단히유용하다. 만약작은테이블일지라도 빈번히 join 에사용된다면 index 를활용한성능향상이가능하다. 그러나 index가일반적으로성능의향상을제공하기는하지만이의사용에따른 cost가발생한다는것을알아야한다. 우선 index는물리적인 object이므로 storage를필요로한다. 또한데이터의 delete, insert, update시에인덱스에도동일한작업이필요하게된다. 이러한작업이빈번히일어나게되면인덱스의효율이나빠지기도하므로지속적인관리가필요하게된다. 위와같이인덱스의개수에따라수회이상의 storage 와 system 에대한 overhead 가발생하여 오히려성능을저하시키기도한다. 일반적으로테이블별로 4 개이상의인덱스를생성하는것은좋지않다. 그러나상황에따라 필요한경우인덱스는생성하는것도고려하여야한다. 1) INDEX 사용기준 ㄱ ) 대상컬럼선정 -. 조건문에자주등장하는컬럼 -. 분포도가좋은컬럼 ( 같은값이적은컬럼 ) -. 자주 JOIN에사용되는컬럼 Edited by heiya

14 ㄴ ) INDEX 사용시손해보는경우 -. 데이터가적어 full table scan이더유리한경우 ( 보통 16 block 이내인경우 ) -. 분포도가나쁜컬럼 ( 같은값이많은컬럼 ) -. SELECT 보다 DML 부담이더큰경우 2) 결합 INDEX 사용기준 ㄱ ) 대상컬럼선정 -. 2개이상의컬럼이자주 JOIN에사용되는경우 -. 인덱스만으로결과값을얻을수있는경우 -. 자주 JOIN에사용되는컬럼 ㄴ ) 결합 INDEX의컬럼순서 ( 나열순서와는상관없음 ) -. 사용빈도가높은컬럼 -. 분포도가좋은컬럼 -. 자주사용되는컬럼 2. INDEX 를이용한작업 ORACLE 은 index 를통해접근하는데다음과같은두방식을이용한다. 1) INDEX UNIQUE SCAN 다. 조회하고자하는테이블에인덱스가존재하는경우 optimizer 는 query 시인덱스를이용한 예 ) EMP 테이블에 EMP_NAME 컬럼에 EMP_PK 라는 unique index 와 MANAGER 컬럼에 non-unique index 인 EMP_IDX01 이라는두개의인덱스가존재한다고하자. SELECT * WHERE EMP_NAME = 'ROSE HILL'; 내부적으로위의 query 를수행하기위해두개의 step 으로나뉘어진행된다. 첫번째는먼저 EMP_PK 인덱스를통한 INDEX UNIQUE SCAN 작업이수행되어 EMP_PK에서 EMP_NAME이 'ROSE HILL' 인데이터의물리적위치인 RowID를찾게된다. 그다음에 RowID를이용한 TABLE ACCESS BY ROWID 작업이수행되어 EMP 테이블에서해당 row의나머지컬럼을찾아결과값을되돌려준다 Edited by heiya

15 만약 query를통해요청되는값이 index 내의컬럼이라면, 첫번째작업인 INDEX UNIQUE SCAN 만으로도결과값을되돌려줄수있어더높은성능을기대할수있다. 다음의 SQL문은 INDEX UNIQUE SCAN 작업만으로수행한다. SELECT EMP_NAME WHERE EMP_NAME = 'ROSE HILL'; 2) INDEX RANGE SCAN Index 컬럼에대한범위또는 non-unique index의컬럼을이용한 query를수행하면 INDEX RANGE SCAN 을통해데이터를조회하고값을되돌려받게된다. 예 ) SELECT EMP_NAME WHERE EMP_NAME LIKE 'R%'; 위예에서 where절내에 EMP_NAME에대한범위로조회를하므로 unique-index인 EMP_PK를통해조회되더라도 INDEX RANGE SCAN 작업을통해데이터가조회된다. INDEX RANGE SCAN 을통해수행되는작업은인덱스로부터여러개의데이터를조회하기때문에, INDEX UNIQUE SCAN 에의한것보다비효율적이다. 조회하는컬럼이 EMP_PK를구성하는 EMP_NAME이므로 INDEX RANGE SCAN 만으로 query가수행되고값을되돌려받게된다. 예 ) SELECT EMP_NAME WHERE MANAGER = 'BILL GATES'; 위예 ) 와같은 SQL은조회하는컬럼이인덱스의구성컬럼이아니므로내부적으로두단계에걸쳐실행된다. 첫번째는 non-unique index인 EMP_IDX01을 INDEX RANGE SCAN 하여 RowID를얻게되고, 이를통해테이블에대한 TABLE ACCESS BY ROWID 를통해수행되어 EMP_NAME 컬럼값을되돌려준다. 여기서 non-unique index인 EMP_IDX01의 MANAGER 값이 unique할지라도 INDEX UNIQUE SCAN 을통해수행되지는않고 INDEX RANGE SCAN 을통해 n+1건에대한 access가발생한다. 그러나아래의경우와같이인덱스컬럼에대해 range로조회를하더라도맨앞글자가 '%' 와같은와일드카드일경우에는 non-unique index인 EMP_IDX01이존재하더라도인덱스를통한 access가일어나지않고 FULL TABLE SCAN 으로조회된다 Edited by heiya

16 예 ) SELECT EMP_NAME WHERE EMP_NAME LIKE '%GATES'; 3. 우선순위가분명치않은 INDEX 의선택 Index 의우선순위가분명치않은경우 ORACLE 은 Where 절에먼저기술된인덱스하나만을이 용하여경로를생성한다. 예 ) EMP 테이블의 DEPTNO 컬럼과 EMP_CAT 컬럼에각각 non-unique index 가있다고하자. SELECT ENAME WHERE DEPTNO > 20 AND EMP_CAT > 'A'; 위예 ) 는 DEPTNO 에대한인덱스만을사용한다. Explain plan 은다음과같다. TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON DEPT_IDX 4. 두개이상의인덱스의 MERGE 서로다른테이블에 EQUAL('=') 로조회되는두개이상의사용가능한인덱스가있는경우 ORACLE 은 run-time 시모든인덱스를병합 (merge) 하여원하는값을되돌려주기도한다. Unique index 가있는경우 non-unique index 보다상위가된다. 위내용은모두상수와비교될때에만해당되며, 만약다른테이블의인덱스와비교될경우에 는 optimizer 에의해하위로분류되기도한다. 만약서로다른테이블에동등한순위의인덱스가있는경우에는 optimizer 가 FROM 절에쓰인 순서등일정한 RULE 에의해순서를결정한다. 만약동등한순위의인덱스가하나의테이블내에존재한다면 Where 절에먼저쓰인컬럼의인 덱스가우선참조되고아래쪽에기술된컬럼의인덱스가나중에참조된다. 예 ) EMP 테이블의 DEPTNO 컬럼과 EMP_CAT 컬럼에각각 non-unique index 가있다고하자 Edited by heiya

17 SELECT ENAME WHERE DEPTNO = 20 AND EMP_CAT = 'A'; 위예 ) 를보면다음과같이 DEPTNO 에대한인덱스를먼저사용하고그다음에 EMP_CAT 에대한 인덱스를사용하여각각의결과치를가지고 equal 비교를하여결과치를돌려준다. TABLE ACCESS BY ROWID ON EMP AND-EQUAL INDEX (RANGE SCAN) ON 'DEPT_IDX' (NON-UNIQUE) INDEX (RANGE SCAN) ON 'CAT_IDX' (NON-UNIQUE) 5. 사용자에의한 INDEX 사용제한 둘이상의인덱스가동일한순위로참조되어 query 가비효율적인경로로실행될때, 어느하 나의인덱스를강제로사용하지못하게함으로써 SQL 문의실행성능을높일수있다. Character 타입의컬럼에는 '' 을붙이고, Number 타입의컬럼에는 +0 을하여좌변을가공하게되면 ORACLE은해당인덱스를사용하지않는실행계획을세우게된다. 예 ) SELECT ENAME WHERE EMPNO = 7935 AND DEPTNO +0 = 10 AND EMP_TYPE '' = 'A'; 위 SQL문처럼인덱스의사용을제한하는것은 hint의사용처럼현재뿐아니라미래에도영향을미치게된다. 즉, 현재는 EMPNO에대한인덱스가다른인덱스보다분포도가좋아더나은결과를보일지몰라도향후데이터의양이나분포도가변할경우 dynamic하게경로를생성하는 CBO의장점을활용하지못하게된다. 그러므로필요한경우에만잠시사용하는것이좋다. 전략적으로인덱스의사용을제한하는경우를살펴보자. 현재 EMP_TYPE 컬럼에대해 nonunique index 가생성되어있고, EMP_CLASS 컬럼에는인덱스가없다. SELECT ENAME WHERE EMP_TYPE = 'A' AND EMP_CALSS = 'X'; Optimizer 는당연히 EMP_TYPE 에대한인덱스를사용한실행계획을수립한다. 그러나향후에 EMP_CLASS 에대한인덱스가생성되면어떻게될것인가? Edited by heiya

18 일반적인경우앞에서설명한바와같이두개의인덱스를모두활용한실행계획을생성하여 sort/merge를통해 query를수행한다. 이때한인덱스는분포도가좋아 10건미만의결과값을찾고, 다른인덱스는분포도가떨어져수천건을결과값으로돌려받았다면, 이를 sort/merge 하는데걸리는 overhead는분명히성능저하의원인이된다. 이와같이어느한쪽이항상우수한분포도를보이며, 이를확신할수있을경우아래와같이 전략적으로인덱스의사용을제한함으로써향후발생가능한 overhead 를미연에방지할수있다. SELECT ENAME WHERE EMP_TYPE = 'A' AND EMP_CALSS '' = 'X'; 6. ORACLE 에의한 INDEX 사용제한 하나의테이블에두개이상의사용가능한인덱스가있고, 하나의인덱스만 Unique 이고나머 지는 non-unique 일때 ORACLE 은 unique index 만을사용한실행계획을생성하고나머지인덱스 들은완전히무시한다. 예 ) SELECT ENAME WHERE EMPNO = 2362 AND DEPTNO = 20; 위 SQL 문의 EMP 테이블에 EMPNO 에대한 unique index 와 DEPTNO 에대한 non-unique index 가있 을때, EMPNO 에대한 unique index 인 EMPNO_IDX 가조회에사용되며두번째조건인 DEPTNO = 20 은확인조건으로만사용된다. 실행계획은다음과같다. TABLE ACCESS BY ROWID ON EMP INDEX UNIQUE SCAN ON EMPNO_IDX 7. 인덱스컬럼에대한 NOT 사용제한 Where 절내에서인덱스컬럼에대해 NOT 비교문을사용할경우해당컬럼을가공하는것과동 일한효과가나타나서 ORACLE 은 NOT 을만나게되면해당컬럼에대한인덱스를사용하지않는 실행계획을수립한다 Edited by heiya

19 인덱스를사용하지못하는경우 SELECT FROM DEPT WHERE DEPT_CODE!= 0; 인덱스의사용이가능한경우 SELECT FROM DEPT WHERE DEPT_CODE > 0; 드문경우에 ORACLE 의 optimizer 가자동으로 NOT 을변환하는경우도있다. NOT > to <= NOT >= to < NOT < to >= NOT <= to > 8. INDEX 컬럼에대한가공 I Where 절에서인덱스컬럼이가공되어사용될경우 optimizer 는해당인덱스를사용하지않게 되고사용가능한다른인덱스가없다면이 SQL 문은 full-table scan 을통해결과값을얻게된 다. 가공된경우 SELECT FROM DEPT WHERE SAL * 12 > 25000; SAL 컬럼에대한가공이이뤄져이컬럼에대한인덱스가있더라도사용되지않고 full-table scan 을하게된다. 가공하지않은경우 SELECT... FROM DEPT WHERE SAL > / 12; SAL 컬럼에대한인덱스가있다면이를사용하여 INDEX RANGE SCAN 을하게된다. 9. INDEX 컬럼에대한가공 II ORACLE 은두개의서로다른타입의컬럼을비교할때내부적으로형변환을하게된다. 다음과같이 Number 형의 EMPNO 컬럼에인덱스가있다고하자. SELECT WHERE EMPNO = '124'; 위에서 Number 와 Character 가비교되므로 ORACLE 은내부적으로형변환을하여아래와같은 SQL 로변형되어수행된다. SELECT WHERE EMPNO = TO_NUMBER('123'); Edited by heiya

20 여기서형변환이일어났다하더라도인덱스컬럼에대한가공이아니므로인덱스를사용하는 데에는문제가없다. 다음의경우를생각해보자. Character 형의 EMP_TYPE 컬럼에인덱스가있다. SELECT WHERE EMP_TYPE = 123; 위문장은내부형변환에의해다음과같은문장으로수행된다. SELECT WHERE TO_NUMBER(EMP_TYPE) = 123; 인덱스컬럼이가공되므로 EMP_TYPE 에대한인덱스는사용되지않는다. 다음은 TYPE 에따른비교및변환관계이다. 형태 컬럼 1 컬럼 2 결과 비고 CHAR:CHAR CHAR(10) '1234 ' CHAR(4) '1234' Equal 내부형변환없음. 컬럼 2 에 space 6 자리를추가하여 10 자리비교함. CHAR:VARCHAR2 VARCHAR2: VARCHAR2 CHAR: 상수 VARCHAR2: 상수 NUMBER:CHAR or VARCHAR2 or 문자열 DATE: CHAR or VARCHAR2 or 문자열 CHAR(10) '1234 ' VARCHAR2(10) '1234' CHAR(10) '1234 ' VARCHAR2(10) '1234' NUMBER 1234 DATE 01-may-03 00:00:00 VARCHAR2(10) '1234' VARCHAR2(4) '1234' 상수 '1234' 상수 '1234' Not Equal 내부형변환없음. 4 자리만비교후결과값 return. COL 1 > COL 2 Equal 내부형변환없음. 4 자리만비교후결과값 return. COL 1 = COL 2 Equal 내부형변환없음. 컬럼 2 에 space 6 자리를추가하여 10 자리비교함. Equal 내부형변환없음. 4 자리만비교후결과값 return. COL 1 = COL 2 '1234' Equal CHAR, VARCHAR2 또는문자열이 NUMBER 형으로내부형변환이일 어난다. '01-may-03' Equal CHAR, VARCHAR2 또는문자열이 00:00:00 붙은 DATE 형으로내부 형변환이일어난다 Edited by heiya

21 10. 결합 INDEX 의선행컬럼사용과 SKIP SCAN 의활용 ORACLE 9i 이전버전에서는여러컬럼으로구성된결합인덱스가있을때, 컬럼구성상맨앞 에있는컬럼이 Where 절에사용되지않으면해당인덱스를이용한실행계획은생성되지않았다. 결합인덱스를사용하기위해서는 where절에서선행컬럼이가공되지않은채사용되어야하며 equal 비교로사용되는것이성능을향상시키는한방법이었다. 처음컬럼이 equal비교가되지않으면그뒤에오는컬럼은사용하지않거나 range scan이되어효율이떨어지게된다. 그러나 9i부터는이러한고민이해결되었다. 불필요하게 full table scan에의한병목현상을 Oracle9i Database의 INDEX SKIP SCAN 기능을사용하면해결할수있다. 다음과같이 EMP 테이블을한번생각해보자. CREATE TABLE SCOTT.EMP ( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) ); 그리고, 이테이블이다음과같은인덱스를갖고있다고하자. CREATE INDEX SCOTT.EMP_IDX01 ON SCOTT.EMP (DEPTNO, JOB, ENAME); 위의인덱스는 DEPT 컬럼에대해 query 를하는경우에참조하게됩니다. 그러나, 다음과같은 query 에서는사용되지않는다. SELECT PATIENT_SSN, GROUP_NUMBER FROM SCOTT.EMP WHERE ENAME = 'SMITH'; Oracle 9i 이전버전에서는사용가능한인덱스 EMP_IDX01 의선행컬럼이사용되지않기때문 에 full table scan 을통해결과값을되돌려준다. Oracle 9i 의 optimizer 가 INDEX SKIP SCAN 을사용하기로결정하면, 결합인덱스의선행컬럼인 DEPTNO 의값들에대해샘플링을하게되고각각의 DEPTNO 에대해 DEPTNO 가추가된 SQL 을내부 Edited by heiya

22 적으로실행하게된다. INDEX SKIP SCAN 은 Cost-Base Optimizer(CBO) 를사용해야만한다. 만약 Rule-Base Optimzer (RBO) 가사용되고있다면 INDEX SKIP SCAN 은사용할수없다. 8i 와 9i 에서확인한결과를다음과같이표로정리하였다. 표 ) INDEX SKIP SCAN 의결과 SQL SELECT * FROM SCOTT.EMP WHERE ENAME = 'SMITH'; SELECT DEPTNO FROM SCOTT.EMP WHERE JOB = 'SALESMAN' AND ENAME = 'ALEN'; SELECT MAX(ENAME) FROM SCOTT.EMP WHERE JOB = 'SALESMAN'; SELECT /*+ RULE */ MAX(ENAME) FROM SCOTT.EMP WHERE JOB = 'SALESMAN'; SELECT * FROM SCOTT.EMP WHERE DEPTNO = 20 SELECT * FROM SCOTT.EMP WHERE DEPTNO > 0 AND ENAME = 'CLARK'; INDEX 의사용 8i 9i 비고 No Yes 결합인덱스 EMP_IDX01의선행컬럼 DEPTNO가아닌세번째컬럼 ENAME 을사용함. No Yes 결합인덱스 EMP_IDX01의선행컬럼 DEPTNO가아닌두번째, 세번째컬 럼 JOB, ENAME을사용함 No Yes 결합인덱스 EMP_IDX01의선행컬럼 DEPTNO가아닌두번째컬럼 JOB을 사용함. No No RULE 힌트를사용하여 CBO가아닌 RBO로작동하므로 INDEX SKIP SCAN 을사용하지못한다. Yes Yes 결합인덱스 EMP_IDX01의선행컬럼 인 DEPTNO를사용함. Yes Yes 결합인덱스 EMP_IDX01의선행컬럼 인 DEPTNO를사용할수있게조건 을줌. 11. 인덱스컬럼에대한 IS NULL / IS NOT NULL 의사용제한 인덱스로사용되는컬럼이 NULL값을가질경우해당 row에대해서는인덱스를구성하지않으므로 IS NULL 또는 IS NOT NULL을통한비교가불가능하다. 그러므로 optimizer는해당인덱스를사용하지않는실행경로를생성하게된다. 그러나결합인덱스의경우하나의컬럼이라도 NULL이아니면인덱스에포함된다. 만약어느테이블의컬럼 COL1과 COL2에 unique index가생성되어있고, 값이 (123, null) 인 row가이미존재할때 (123, null) 값을가진 row는 unique에위배되어입력되지않는다. 그러나값이 (null, null) 인 row는 unique index에저장되지않고제외되므로수천건이라도입력이가능하다 Edited by heiya

23 예 ) DEPT_CODE 에대한인덱스가있는테이블 DEPARTMENT 가있다. Case 1 Case 2 SELECT... FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; SELECT... FROM DEPARTMENT WHERE DEPT_CODE >= 0; Case 1 에서는 DEPT_CODE 에대한인덱스를참조하지않고, Case 2 의경우는 DEPT_CODE 에대한 인덱스를 RANGE SCAN 하게된다. 12. 인덱스가있는경우 UNION 의사용 각각의인덱스가있는컬럼에대한 OR 검색은때때로 optimizer 로하여금 full table scan 을 하게하는경우가있다. 다음의예를살펴보자. Case 1 Case 2 SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = 'MELBOURNE'; SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (FULL) OF 'LOCATION' SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE REGION = 'MELBOURNE'; SELECT STATEMENT Optimizer=CHOOSE SORT (UNIQUE) UNION-ALL TABLE ACCESS (BY INDEX ROWID) OF 'LOCATION' INDEX (RANGE SCAN) OF 'LOCATION_LOCID' TABLE ACCESS (BY INDEX ROWID) OF 'LOCATION' INDEX (RANGE SCAN) OF 'LOCATION_REGION' 여기서반드시확인할사항이있다. ORACLE optimizer 는버전에따라 Case 1 의실행계획이 아래와같이두개의인덱스를모두사용하도록풀릴수도있으므로반드시실행계획을참조하 여예상치못한성능저하를막아야한다. SELECT STATEMENT Optimizer=CHOOSE CONCATENATION TABLE ACCESS (BY INDEX ROWID) OF 'LOCATION' INDEX (RANGE SCAN) OF 'LOCATION_LOCID' TABLE ACCESS (BY INDEX ROWID) OF 'LOCATION' INDEX (RANGE SCAN) OF 'LOCATION_REGION' Edited by heiya

24 III. SQL 활용 I 1. WHERE 절내에서의 JOIN 의위치 테이블조인은 WHERE절의조건보다먼저기술되는것이좋다. 이는 SQL Parser에의해 SQL이해석될때 WHERE절의조건이밑에서부터위로해석이되기때문이다. 그러므로아래부분에서건수를줄여주면위쪽에서처리하는건수가적어지므로더효율적이된다. 그러나이는 ORACLE 버전에따라 OPTIMIZER가서로다른실행경로를생성하고, 데이터의건수나통계의유무등에의해생성되는경로가달라지므로반드시실행계획을본후에실행시키는것이좋다. 예 ) * 비효율적인경우 (Total CPU = Sec) SELECT E WHERE SAL > AND JOB = 'MANAGER' AND 25 < (SELECT COUNT(*) WHERE MGR = E.EMPNO); * 효율적인경우 (Total CPU = 10.6 Sec) SELECT E WHERE 25 < (SELECT COUNT(*) WHERE MGR = E.EMPNO ) AND SAL > AND JOB = 'MANAGER'; 2. EXISTS 대신 JOIN 의사용 일반적으로 sub-query 보다는다음과같이 join 을하는것이더좋다.: SELECT E WHERE EXISTS (SELECT 'X' FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND D.DEPT_CAT = 'A'); 아래와같이하면성능향상에도움이된다.: SELECT ENAME FROM DEPT D, EMP E WHERE E.DEPT_NO = D.DEPT_NO AND D.DEPT_CAT = 'A'; Edited by heiya

25 3. 관계가없는테이블들에대한단순결합 Relation 이없는여러개의테이블에서단순한조회를하는경우단순결합을통해한번에처 리함으로써효율을증대시킬수있다. 예 ) SELECT NAME WHERE EMP_NO = 1234; SELECT NAME FROM DEPT WHERE DEPT_NO = 10; SELECT NAME FROM CAT WHERE CAT_TYPE = 'RD'; 위의 3 가지 SQL 문을 DUAL 이라는 DUMMY 테이블을이용하여아래와같이단순결합을함으로써 효율성을증대시킬수있다. : SELECT E.NAME, D.NAME, C.NAME FROM CAT C, DEPT D, EMP E, DUAL X WHERE NVL('X', X.DUMMY) = NVL('X', E.ROWID (+)) AND NVL('X', X.DUMMY) = NVL('X', D.ROWID (+)) AND NVL('X', X.DUMMY) = NVL('X', C.ROWID (+)) AND E.EMP_NO (+) = 1234 AND D.DEPT_NO (+) = 10 AND C.CAT_TYPE (+) = 'RD'; 4. Equal 비교와범위비교 한테이블에대해 equal 비교와범위비교를동시에할경우 ORACLE 은이인덱스들에대해 merge 를하지않는다. 예 ) EMP 테이블의 DEPTNO 컬럼과 EMP_CAT 컬럼에각각 non-unique index 가있다고하자. SELECT ENAME WHERE DEPTNO > 20 AND EMP_CAT = 'A'; 위 query 는 EMP_CAT 에대한인덱스만을사용한다. Explain plan 은다음과같다. TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON CAT_IDX Edited by heiya

26 5. 비교문사용하기 ( > 와 >= ) 정수형컬럼 DEPTNO 에대해인덱스가있을때아래의문장을비교해보자. Case 1 Case 2 SELECT * WHERE DEPTNO > 3 SELECT * WHERE DEPTNO >= 4 Case 1 에서는조건이 DEPTNO > 3 이므로인덱스에서 DEPTNO 가 3 인 row 부터 scan 을시작하고, Case 2 에서는 DEPTNO 가 4 인 row 부터 scan 을하게된다. 만약 DEPTNO 가 3 인 row 가많다면 scan 시 그만큼의 I/O 가추가로발생하게되므로결과값추출에더오랜시간이걸리게된다. 6. IN 대신 EXISTS 의활용 ERD 상에서 base 가되는테이블에대한 query 는 select 할때여러테이블과 join 을하는경우 가많다. 이러한경우 IN 과 Sub-query 를사용하는것보다 EXISTS 나 NOT EXISTS 를사용하는것 이더나은성능을보여주는경우가많다. 예 ) * 비효율적인경우 SELECT * (Base Table) WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB'); * 효율적인경우 SELECT * E WHERE EMPNO > 0 AND EXISTS (SELECT 'x' FROM DEPT D WHERE D.DEPTNO = E.DEPTNO AND D.LOC = 'MELB'); 7. NOT IN 대신 NOT EXISTS 의활용 아래와같이 Sub-query 문에서 NOT IN 은내부적으로 sort 와 merge 를수반한다. NOT IN 을사용하면대체적으로가장효율이나쁜데, 이는 sub-query select 에대상이되는테 이블을강제로 full table scan 하도록하기때문이다. NOT IN 보다는 Outer Join 이나 NOT Edited by heiya

27 EXISTS 를사용하는것이좋다. SELECT WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT = 'A'); 위문장의성능을향상시키기위해서는아래와같이변경하여야한다.: Case 1 Case 2 SELECT A, DEPT B WHERE A.DEPT_NO = B.DEPT_NO (+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = 'A'; SELECT E WHERE NOT EXISTS (SELECT 'X' FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND D.DEPT_CAT = 'A'); 위에서 Case 1 보다는 Case 2 가조금더나은결과를보인다. 8. DISTINCT 대신 EXISTS 의활용 유일성을확보하기위한 DISTINCT 의사용을방지하기위해서아래와같이 1:M 관계에서의 select 에서는 EXISTS 를사용해야한다. 예 ) * 비효율적인방법 SELECT DISTINCT DEPT_NO, DEPT_NAME FROM DEPT D, EMP E WHERE D.DEPT_NO = E.DEPT_NO; * 효율적인방법 SELECT DEPT_NO, DEPT_NAME FROM DEPT D WHERE EXISTS (SELECT 'X' E WHERE E.DEPT_NO = D.DEPT_NO); EXISTS 가더빠르게결과값을가져올수있는이유는 RBDMS Kernel 이 sub-query 내에서만족 시키는값을하나찾게되면바로그 sub-query 를종료시켜다음 query 가진행되도록하기때 문이다 Edited by heiya

28 9. 중복되는레코드의삭제방법 중복되는레코드를효율적으로삭제하는방법은아래와같이 RowID 를활용한방법이다. 이는개발과정에서발생하는데이터의중복을배제하고 Primary 키등제약조건을걸고자할 때활용할수있다. 예 ) DELETE E WHERE E.ROWID > (SELECT MIN(X.ROWID) X WHERE X.EMP_NO = E.EMP_NO); 위예에서 MIN() 의사용은경우에따라 MAX() 로도사용할수있다. 그러나중복되는데이터 중지워야하는것을지정하고자할때에는그에맞게 SQL 문을수정하여야함을잊지말자. 10. Row 수 COUNT 하기 일반적인믿음과달리 COUNT(*) 가 COUNT(1) 보다빠르다. 만약인덱스를통해 COUNT 한값을추출하고자할때에는인덱스로잡혀있는컬럼을 COUNT(EMP) 와같이추출하는것이가장빠르게결과값을얻을수있다. 11. Table Alias 의사용 여러개의테이블에대한 Query 시항상테이블에대한 alias 를사용하고, 각각의컬럼에 alias 를붙여사용하는것이좋다. ORACLE 이 dictionary 에서해당컬럼이어느테이블에있는지를찾지않아도되므로 parsing 시간을줄일수있고, 컬럼에대한혼동을미연에방지할수있다. 12. WHERE 와 HAVING 의차이 자주사용하지는않지만간혹 HAVING 을 WHERE 대신사용하는경우가있다. 그러나 SELECT 문에 서 HAVING 을 WHERE 대신사용하는것은피하는것이좋다. HAVING 은 fetch 된 row 들에대한 filter 역할을한다. 여기에는 sort 나 sum 등의작업이수반 된다. 만약 select 하고자하는데이터를일정조건에따라추출하고자할경우에는 where 절을 사용하여 HAVING 을사용함으로써발생할수있는 overhead 를줄여주는것이좋다 Edited by heiya

29 예 ) * 비효율적인경우 SELECT REGION, AVG(LOC_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION!= 'SYDNEY' AND REGION!= 'PERTH'; * 효율적인경우 SELECT REGION, AVG(LOC_SIZE) FROM LOCATION WHERE REGION!= 'SYDNEY' AND REGION!= 'PERTH' GROUP BY REGION; 13. SELECT 절에서 Asterisk('*') 사용 Dynamic SQL 컬럼 '*' 는테이블의모든컬럼을참조할수있게해준다. 그러나이러한 '*' 는값을되돌려줄때테이블의모든컬럼을변환하여반환하므로매우비효율적이다. SQL Parser는 Data Dictionary에서해당테이블에대한모든컬럼의이름을읽어서 SQL 명령문내의 '*' 을대체하는작업을한다. 비록 0.01 초밖에더걸리지않는작업일지라도여러번반복하면많은시간이걸릴수도 있으므로되도록 Asterisk(*) 를사용하지않는것이좋다. 14. UNION-ALL 의활용 두개의 query 에대해서 UNION 을사용할때, 각각의 query 에의한결과값이 UNION-ALL 에의해 합쳐지고다시내부작업인 SORT UNIQUE 작업에의해최종결과값을사용자에게되돌려준다. 이때 UNION 대신 UNION-ALL 을사용하게되면 SORT UNIQUE 작업은불필요하게되며, 그만큼의 시간을줄일수있고수행성능을향상시킬수있다. 이는 SORT 가필요하지않은경우에만가능하므로정확히확인하고사용하여야한다. Case 1 Case 2 SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' UNION SELECT ACCT_NUM, BALANCE_AMT FROM CREDIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM CREDIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95'; Edited by heiya

30 Case 1 의경우에서결과값이서로다르다는것을알고있다면 Case 2 의경우처럼 UNION-ALL 을 사용하는것이좋다. 15. ORDER BY 를사용하지않고정렬하기 ORDER BY 을사용할때인덱스를사용하여 sort 를하지않고정렬된결과값을얻고자할때에 는다음의두조건을만족하여야한다. >> ORDER BY 에사용된모든컬럼이동일한순서로하나의인덱스로만들어져있어야한다. >> ORDER BY 에사용된모든컬럼은테이블정의에반드시 NOT NULL 이어야한다. Null 값은인덱스에저장되지않는다는것을기억하기바란다. Where 절의인덱스와 ORDER BY 절의인덱스는동시에사용될수없다. 예 ) 다음의컬럼을갖는 DEPT 테이블이있다. DEPT_CODE PK NOT NULL DEPT_DESC NOT NULL DEPT_TYPE NULL NON UNIQUE INDEX DEPT_IDX ON DEPT (DEPT_TYPE) Case 1 Case 2 SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE; SORT ORDER BY TABLE ACCESS FULL SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0; TABLE ACCESS BY ROWID ON DEPT INDEX RANGE SCAN ON DEPT_IDX Case 2 에서처럼의미없는 Where 에의해 ORDER BY 와같이 sort 과정을거치지않고도동일한 결과를얻을수있다. WHERE DEPT_TYPE > 0 절에의해 optimizer 는인덱스를활용한 INDEX RANGE SCAN 을하게되어결과적으로 DEPT_TYPE 의순으로정렬된결과값을얻게된다 Edited by heiya

31 16. WHERE 절에서주의할사항 아래처럼몇몇경우 Where 절에사용하는비교문에의해인덱스를사용할수없는경우가있다. No. 인덱스를쓰지못하는경우 1 SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT!= 0; 2 SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME ACCOUNT_TYPE = AMEXA ; 3 SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE AMOUNT < 5000; 4 SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = NVL(:ACC_NAME, ACCOUNT_NAME); 인덱스를쓰기위해변경된경우 SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT > 0 UNION ALL SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT < 0; SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = AMEX AND ACCOUNT_TYPE = A; SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE AMOUNT < 2000; SELECT ACCOUNT_NAME, AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME, % ); 마지막예처럼인덱스가걸린동일한컬럼간의비교에서는인덱스를사용할수없어 fulltable scan 을유발한다. 17. IN 의활용 IN 을이용하여값을나열하게되면 optimizer 는비교치각각에대해 scan 을하고, 결과치를 concatenation 하여최종결과를사용자에게돌려준다. SELECT * WHERE MANAGER IN ( BILL GATES, KEN MULLER ); Optimizer 는위 SQL 문을아래와같이해석하여실행한다. SELECT * WHERE MANAGER = BILL GATES OR MANAGER = KEN MULLER ; 위 query 를해석할때 optimizer 는각각의비교치에대해 MANAGER 컬럼에대한인덱스를 Edited by heiya

32 INDEX RANGE SCAN 하게된다. Index scan 을통해추출된 RowID 를가지고테이블을 access 하여각 각의결과값을추출하고, 이값들을다시 CONCATENATION 하여사용자에게돌려주게된다. Explain Plan 을살펴보면아래와같다. SELECT STATEMENT Optimizer=CHOOSE CONCATENATION TABLE ACCESS (BY INDEX ROWID) OF EMP INDEX (RANGE SCAN) OF EMP_IDX01 (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF EMP INDEX (RANGE SCAN) OF EMP_IDX01 (NON-UNIQUE) 18. DATE 사용시주의점 DATE 형의컬럼에대한작업시소수점 5 자리이하의숫자에대해서는작업을하지않도록한다. 소수점 6 자리숫자를더하게되면다음날짜에대한값을결과로받게된다. SQL 문 SELECT TO_DATE('01-MAY-93') FROM DUAL; SELECT TO_DATE('01-MAY-93') FROM DUAL; 결과값 '01-MAY-03 23:59:59' '02-MAY-03 00:00:00' Edited by heiya

33 IV. SQL 활용 II 1. GROUP BY 의대상건수줄이기 GROUP BY 를사용하는 query 는대상건수를줄여줌으로써성능을향상시킬수있다. 아래와같은경우를생각해보자. Case 1 Case 2 SELECT JOB, AVG(SAL) GROUP BY JOB HAVING JOB = PRESIDENT OR JOB = MANAGER ; SELECT STATEMENT Optimizer=CHOOSE FILTER SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EMP' SELECT JOB, AVG(SAL) WHERE JOB = PRESIDENT OR JOB = MANAGER GROUP BY JOB; SELECT STATEMENT Optimizer=CHOOSE SORT (GROUP BY) CONCATENATION INDEX (RANGE SCAN) OF 'EMP_IDX' (NON-UNIQUE) INDEX (RANGE SCAN) OF 'EMP_IDX' (NON-UNIQUE) Case 1에서는테이블의모든데이터에대해 sort한뒤그결과값중 JOB이 PRESIDENT와 MANAGER인사람을골라평균급여를구했다. 그러나 Case 2에서는우선 JOB이 PRESIDENT와 MANAGER인사람을먼저가져온다음그들의평균급여를구했다. 결국데이터가많아지면많아질수록성능차이는많아지게된다. 위에서와같이극단적인경우가없다고는할수없다. 항상실행계획을확인하는습관을들 이는것이좋다. 2. 불필요한작업을유발하는명령어의사용제한 DISTINCT, UNION, MINUS, INTERESECT, ORDER BY, GROUP BY 등의명령어를사용한 SQL문은 ORACLE 엔진에부하를주고다량의 resource를사용하는 sort 작업을유발한다. 일반적으로 DISTINCT는한번의 sort 작업을거치고, 나머지연산자를처리하기위해서는최소한 2회이상의작업을수행한다. 앞에서살펴본 ORACLE의내부 Operation에서와같이두개의 query를 UNION으로처리할때각각의 query에대해결과값을추출한다음 UNION-ALL을통한병합과정을거치고, 이결과를다시 SORT UNIQUE를통해최종결과값을추출하여사용자에게돌려준다. Sort의회수가많을수록 query의수행으로인한시스템부하는커지게된다. 집합연산을하는대부분의 query는다른방법으로처리가가능하므로되도록 UNION, MINUS, INTERSECT와같은집합연산자의사용을자제하여야한다 Edited by heiya

34 3. DECODE 함수를활용한내부처리단축 DECODE 함수를활용하여동일한 row 에대해다시읽는것을방지하거나동일한테이블에대한 join 을피할수있다. 예 ) SELECT COUNT(*), SUM(SAL) WHERE DEPT_NO = 0020 AND ENAME LIKE 'SMITH%'; SELECT COUNT(*), SUM(SAL) WHERE DEPT_NO = 0030 AND ENAME LIKE 'SMITH%'; DECODE 함수를활용하여위의결과와동일한결과를한번에가져올수있다.: SELECT COUNT(DECODE DECODE(DEPT_NO, 0020, 1, NULL)) D20_CNT, SUM (DECODE DECODE(DEPT_NO, 0020, SAL, NULL)) D20_SAL, COUNT(DEC DECODE ODE(DEPT_NO, 0030, 1, NULL)) D30_CNT, SUM (DECODE DECODE(DEPT_NO, 0030, SAL, NULL)) D30_SAL WHERE ENAME LIKE 'SMITH%'; 이와같이 DECODE 는 GROUP BY 나 ORDER BY 절에서도사용할수있다. 4. DELETE 대신 TRUNCATE 의사용 일반적인경우테이블내의데이터를삭제하고자할때 ORACLE 은 Rollback 세그먼트에기존 데이터의정보 (Before image) 가저장한다. Transaction 을 Commit 명령어를실행하여종료하지 않으면 ORACLE 은이정보를가지고원래의상태로데이터를 Restore 하게된다. TRUNCATE 명령어를사용하면 undo 정보를생성하지않는다. DELETE 보다빠르고 Resource 를덜 사용하는장점이있지만한번 truncate 되면데이터를복구할방법이없으므로주의하는것도잊 지말아야한다. 5. Database 에대한 transaction 회수줄이기 SQL 문이실행될때마다 ORACLE 은 parsing, 인덱스확인, 변수값할당, 데이터읽기등매우 많은내부 process 를수행한다. 그러므로 Database 에대한접근을적게할수록부하는감소하고 효율성은올라간다 Edited by heiya

35 예 ) 아래에사원번호가 0342, 0291 인두사원에대한정보를가져오는 3 가지서로다른방법 이있다. 1) 두번에걸쳐데이터를조회하는방법 SELECT EMP_NAME, SALARY, GRADE WHERE EMP_NO = 0342; SELECT EMP_NAME, SALARY, GRADE WHERE EMP_NO = 0291; 2) Cursor 를공유하는방법 (Next Most Efficient) DECLARE CURSOR C1( E_NO NUMBER) IS SELECT EMP_NAME, SALARY, GRADE WHERE EMP_NO = E_NO; BEGIN OPEN C1(342); FETCH C1 INTO,, ; OPEN C1(291); FETCH C1 INTO,, ; CLOSE C1; END; 3) 하나의 SQL 로두가지정보를조회하는방법 (Most Efficient) SELECT A.EMP_NAME, A.SALARY, A.GRADE, B.EMP_NAME, B.SALARY, B.GRADE A, EMP B WHERE A.EMP_NO = 0342 AND B.EMP_NO = 0291; 참고 - 한번에가져올수있는 row 의수를증가시켜물리적인호출회수를감소시키려면 SQL*Plus, SQL*Forms 그리고 Pro*C 에서 ARRAYSIZE 파라미터의값을증가시키면된다. 권장값은 Edited by heiya

36 6. COMMIT 명령어의실행 가능하면 COMMIT 명령어를자주실행해주는것이좋다. COMMIT 명령어를자주실행하는것은 프로그램의성능을향상시키고아래의자원들을 ORACLE 에반환함으로써 Resource 의필요량이최 소화되기때문이다.: > Transaction 을 UNDO 하기위해 Rollback segment 에기록된정보 > 명령어가수행되는도중에걸린 LOCK > Redo log buffer cache > 위세가지 Resource 를관리하기위한 ORACLE 메커니즘에따른부하 그러나여기서한가지주의할점이있다. 만일사용중인테이블의데이터에대해 UPDATE, DELETE 등을수행하며 COMMIT 을자주수행할경우, 사용자에게아래와같은에러메시지가보일 수있다. ORA snapshot too old: rollback segment number string with name "string" too small 이오류는 Rollback segment와는무관하게작업자가수행한 update, delete 등의명령어에의해변경된데이터의 before image를참조하던사용자가 commit에의해없어진정보를참조하고자할때나타난다. 그러므로운영중인테이블에대한작업은주의를요한다. 7. Query 시테이블에대한참조 Query 시테이블에대한 access 회수를최소화함으로써성능을향상시킬수있다. 특히 Subquery 를포함하거나여러컬럼에대한 update 를수행할때작업시간을단축할수있다. 1) Sub-query ㄱ ) 비효율적인경우 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = (SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER = (SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604); Edited by heiya

37 ㄴ ) 효율적인경우 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VER FROM TAB_COLUMNS WHERE VERSION = 604); 2) Multi-column UPDATE ㄱ ) 비효율적인경우 UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) _CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) _CATEGORIES ) WHERE EMP_DEPT = 0020; ㄴ ) 효율적인경우 UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY), MAX(SAL_RANGE) _CATEGORIES) WHERE EMP_DEPT = 0020; 8. Explicit Cursor 의사용 SELECT 문에사용하는 implicit cursor 는두번의 call 을데이터베이스에하게된다. 첫번째는 데이터를 fetch 하기위한 call 이고, 그다음에 TOO MANY ROWS 오류를 check 하기위해 call 을한 다. Explicit cursor 는이두번째 call 을하지않는다. Implicit 과 Explicit cursor 에대한차이점과사용법은오라클의 PL/SQL 교육교재또는 Developers Guide 를참조하기바란다. 9. Stored Function 을활용한 SQL 의부하감소 다음의경우를살펴보자. SELECT H.EMPNO, E.ENAME, H.HIST_TYPE, T.TYPE_DESC, COUNT(*) FROM HISTORY_TYPE T, EMP E, EMP_HISTORY H WHERE H.EMPNO = E.EMPNO AND H.HIST_TYPE = T.HIST_TYPE GROUP BY H.EMPNO, E.ENAME, H.HIST_TYPE, T.TYPE_DESC; Edited by heiya

38 위 SQL 문은다음과같은 FUNCTION 을만들어사용할경우 GROUP BY 에대한부하를감소시킬수 있어더빠른시간에결과를얻을수있다. 함수 1) CREATE OR REPLACE FUNCTION Lookup_Hist_Type (typ IN NUMBER) RETURN VARCHAR2 AS tdesc VARCHAR2(30); CURSOR C1 IS SELECT TYPE_DESC FROM HISTORY_TYPE WHERE HIST_TYPE = typ; BEGIN OPEN C1; FETCH C1 INTO tdesc; CLOSE C1; RETURN (NVL(tdesc, '?'); END; 함수 2) CREATE OR REPLACE FUNCTION Lookup_Emp (emp IN NUMBER) RETURN VARCHAR2 AS ename VARCHAR2(30); CURSOR C1 IS SELECT ENAME WHERE EMPNO = emp; BEGIN OPEN C1; FETCH C1 INTO ename; CLOSE C1; RETURN (NVL(ename, '?'); END; 적용예 ) SELECT H.EMPNO, Lookup_Emp(H.EMPNO), H.HIST_TYPE, Lookup_Hist_Type(H.HIST_TYPE), COUNT(*) _HISTORY H GROUP BY H.EMPNO, H.HIST_TYPE; 10. Analytic Function 의활용 Analytic function 이란 running summary, moving average, ranking, lead/lag comparisons 등 business 분야에서자주행하여지는여러가지형태의분석에유용하게활용될수있는 SQL function 들을총칭한개념이다. 이함수들을이용하면 ANSI SQL 에서여러단계로나누어하던작업들을한번에끝낼수있기 Edited by heiya

39 때문에시스템에부하를적게주면서도개발속도를향상시킬수있다. Analytic function 은 ORACLE 이후부터사용가능하고, 버전별로사용가능한함수의수 가다르므로사용하는시스템의버전을반드시확인한후사용하여야한다. 1) Analytic 함수의특징및장점 ㄱ ) 특징 -. Analytic function은 SELECT 절과 ORDER BY 절에만올수있다. -. 실행계획상에는 WINDOW SORT로서표시된다. -. analytic function을적용한후의 query의결과집합레벨은 analytic function을적용하기전의결과집합레벨과동일하다. 즉, analytic function을적용하기전의모든로우 (current row) 에대하여 analytic function result value가존재한다. -. Analytic function은 ORDER BY를제외하고는 query에서수행되는가장마지막 operation이다. 즉, 모든 join과 WHERE조건의적용, GROUP BY & HAVING의적용은 analytic function의적용전에모두완료된다. 그러므로, analytic function은 SELECT절과 ORDER BY절에만사용될수있다. ㄴ ) 장점 -. Query speed의향상 -. Self-join 또는클라이언트프로그램의절차형 LOGIC으로표현한것을 native SQL에서바로적용할수있으므로조인이나클라이언트프로그램의 overhead를줄일수있음. -. 향상된개발생산력 (Enhanced Developer Productivity) -. 개발자가명백하고간결한 SQL로복잡한분석작업을수행할수있다. -. 유지보수가편하고생산력을높일수있다. -. 배우기쉬움 (Minimized Learning Effort) -. 기존 SQL syntax를그대로따르기때문에이해가쉽다. -. 표준화 (Standardized Syntax) -. ANSI SQL로채택되면다양한소프트웨어에적용이가능할것이다. 2) 사용방법 Analytic 함수 ([arg1],[arg2],[arg3]) OVER ([PARTITION BY value_expr] [ORDER BY [WINDOWING ]]); >> Analytic함수 : analytic function >> arg1,2,3 : 각각의 analytic function은 0에서 3개의 arguments를갖는다. >> OVER절 : analytic함수의대상이되는결과집합에대한범위, 배열순서등에대 Edited by heiya

40 해기술한다. 3) 사용예 EMP 테이블에서부서별급여순위와급여누적합계, 그리고회사전체순위와급여누적 을구해야한다고할때, 기존의방법과 analytic function 을이용한두가지방법을살펴보 면다음과같다. 예 1) Analytic function 을사용하지않을때 SELECT DEPTNO " 부서명 ", ENAME " 성명 ", SAL " 급여 ", (SELECT COUNT(*) FROM SCOTT.EMP E3 WHERE E3.DEPTNO = E0.DEPTNO AND (E3.SAL > E0.SAL OR (E3.SAL = E0.SAL AND E3.ENAME <= E0.ENAME))) " 부서내급여순서 ", (SELECT SUM(SAL) FROM SCOTT.EMP E2 WHERE E2.DEPTNO = E0.DEPTNO AND (E2.SAL > E0.SAL OR (E2.SAL = E0.SAL AND E2.ENAME <= E0.ENAME))) " 부서내급여누적합계 ", (SELECT COUNT(*) FROM SCOTT.EMP E1 WHERE E1.DEPTNO < E0.DEPTNO OR (E1.DEPTNO = E0.DEPTNO AND (E1.SAL > E0.SAL OR (E1.SAL = E0.SAL AND E1.ENAME <= E0.ENAME)))) " 전체순서 ", (SELECT SUM(SAL) FROM SCOTT.EMP E1 WHERE E1.DEPTNO < E0.DEPTNO OR (E1.DEPTNO = E0.DEPTNO AND (E1.SAL > E0.SAL OR (E1.SAL = E0.SAL AND E1.ENAME <= E0.ENAME)))) " 전체급여누적합계 " FROM SCOTT.EMP E0 ORDER BY DEPTNO, SAL DESC, ENAME; 예 2) Analytic function 을사용할때 SELECT DEPTNO " 부서명 ", ENAME " 성명 ", SAL " 급여 ", RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME) " 부서내급여순서 ", SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME) " 부서내급여누적합계 ", Edited by heiya

41 RANK() OVER (ORDER BY DEPTNO, SAL DESC, ENAME) " 전체순서 ", SUM(SAL) OVER (ORDER BY DEPTNO, SAL DESC, ENAME) " 전체급여누적합계 " FROM SCOTT.EMP ORDER BY DEPTNO, SAL DESC; ORACLE 에서사용가능한 analytic function 은다음과같다. AVG * CORR * COVAR_POP * COVAR_SAMP * COUNT * CUME_DIST DENSE_RANK FIRST FIRST_VALUE * LAG LAST LAST_VALUE * LEAD MAX * MIN * NTILE PERCENT_RANK PERCENTILE_CONT PERCENTILE_DISC RANK RATIO_TO_REPORT REGR_ (Linear Regression) Functions * ROW_NUMBER STDDEV * STDDEV_POP * STDDEV_SAMP * SUM * VAR_POP * VAR_SAMP * VARIANCE * Asterisk(*) 가표시된함수는 windowing_ 절을포함한모든 syntax 를사용할수있다 Edited by heiya

42 V. TUNING 1. STATIC SQL 의활용 ORACLE에서의 SQL 수행과정을간략하게살펴보면, 크게파싱 (Parser), 옵티마이저 (Query Optimizer), 로우소스생성 (Row Source Generator), SQL 실행 (SQL Execution Engine) 의 4단계로분류할수있다. Parsing은수행하고자하는 SQL을오라클 SGA(System Global Area) 내의공유 SQL 영역 (Shared Pool) 에넣고, 문법적오류 (syntax error) 를찾음과동시에 Data Dictionary를검색하여 SQL의유효성을확인한후, 대상테이블이나인덱스구조에따른실행계획 (Execution Plan) 을작성하는과정이다. 앞에서말한바와같이 ORACLE 은 SQL 을 SGA 에공유함으로써 parsing 부하를줄이는방법을사 용하지만, Dynamic SQL 을사용하면공유되지않으므로매번새로 parsing 하게되어시스템에부 하를주게된다. 이러한부하를줄이기위해서는 Static SQL 을활용하여야한다. Dynamic SQL 은클라이언트프로그램에서조건에따라 dynamic 하게 SQL statement 를만들어나 가는방식이며, Static SQL 은조건에따라유동적인부분을변수로정의하여 SQL 이수행될때 변수를 Binding 함으로써 SQL 을공유하고 parsing 에따른부하를줄일수있다. 다음은각 Tool 별로 Static SQL 을사용하는예이다. 일부분만을추출한것으로이것만으로는 실행되지않는것도있으므로참고자료로만활용하고, 자세한사항은각각의매뉴얼을참조하도 록한다. 1) Pro*C/C++ > Dynamic SQL void getdata() { char *dynstmt; } printf("\n Enter Employee Number to Query:"); scanf("%s", emp_number); strcat(dynstmt, "SELECT ename INTO :emp_name FROM emp WHERE emp_no = "); strcat(dynstmt, emp_number); EXEC SQL dynstmt; printf("\n Emplyoee %s :", emp_number); printf("\n Name : %s", emp_name); Edited by heiya

43 > Static SQL void getdata() { printf("\n Enter Employee Number to Query:"); scanf("%d\n", emp_number); } EXEC SQL SELECT ename INTO :emp_name FROM emp WHERE emp_no = :emp_number; printf("\n Emplyoee %d :", emp_number); printf("\n Name : %s", emp_name); 2) JAVA > Dynamic SQL try { String url = "jdbc:myprotocol:mydatabase"; Connection db = DriverManager.getConnection(url, "myid", "mypassword"); Statement stmt = db.createstatement(); ResultSet rs = stmt.executequery("select * WHERE DEPTNO=" + pdeptno); } catch (SQLException e) { System.err.println("SQL Error : " + e.getmessage()); } > Static SQL try { String url = "jdbc:myprotocol:mydatabase"; Connection db = DriverManager.getConnection(url, "myid", "mypassword"); PreparedStatement stmt = db.preparestatement( "SELECT * WHERE DEPTNO =?"); stmt.setstring(1, "10"); ResultSet rs = stmt.executequery(); } catch (SQLException e) { System.err.println("SQL Error : " + e.getmessage()); } 3) DELPHI > Dynamic SQL With Qeury1 do begin Close; Sql1.CLEAR; Edited by heiya

44 Sql1.ADD('SELECT * WHERE DEPTNO = ' + '10'); Open; End; > Static SQL With Qeury1 do begin Close; Sql1.CLEAR; Sql1.ADD('SELECT * WHERE DEPTNO = :pdeptno'); ParamByName('pDeptno').AsNumber := edeptno.text; Open; End; 4) VB > Dynamic SQL Set RS = New ADODB.Recordset RS.ActiveConnection = ADOConn RS.CursorLocation = aduseclientbatch ssql = "SELECT * " & _ " WHERE DEPTNO = '" & pdeptno & "'" RS.Open ssql,, adopenkeyset, adlockbatchoptimistic, adcmdunspecified > Static SQL OraDatabase.Parameters.Add "pdeptno", 10, ORAPARAM_INPUT OraDatabase.Parameters("pDeptNo").ServerType = ORATYPE_NUMBER Dim OraDynaset As Object Set OraDynaset = OraDatabase.CreateDynaset( _ "SELECT * WHERE DEPTNO = :pdeptno", ORADYN_ORAMODE) 5) PHP > Dynamic SQL $sql = "select * from emp where deptno = ".$dno; $stmt = OCIParse($conn, $sql); OCIExecute($stmt); $nrows = OCIFetchStatement($stmt, $results); > Static SQL $sql = "select * from emp where deptno = :pdeptno"; $stmt = OCIParse($conn, $sql); OCIBindByName($stmt,":pDeptNo",&$dno,32); OCIExecute($stmt); $nrows = OCIFetchStatement($stmt, $results); Edited by heiya

45 2. 비효율적인 SQL 을확인하는방법 아래의 SQL 문을이용하여비효율적인 SQL 문을확인해볼수있다. SELECT EXECUTIONS, DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_Ratio, ROUND(DISK_READS/EXECUTIONS,2) Reads_Per_Run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS > 0 AND BUFFER_GETS > 0 AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.80 ORDER BY 4 DESC; 위의문장은 Buffer Cache 에대한 Hit ratio 가 80% 미만인비효율적인 SQL 문을찾아내는 SQL 이다. DBA 또는 system view 를볼수있는권한을가져야만실행할수있으므로주의하도록한다. 참고 - Buffer Cache의기본적인기능은여러프로세스에의해공통으로자주액세스되는데이터베이스블록을메모리에 cache하여물리적인디스크 IO를최소화함으로써더빠른액세스속도를제공하기위한것이다. 버퍼캐쉬의적중률 (Hit ratio) 이란어플리케이션이액세스한메모리 block 가운데이미 cache가되어있어물리적 I/O 없이액세스할수있었던 block의비율을나타낸다. 만약데이터베이스버퍼의적중률 (Hit ratio) 이권장치 (90%) 미만일경우에는할당된버퍼캐쉬의크기가너무적거나, 또는지나치게많은 I/O를유발하는어플리케이션이존재한다는것을의미한다. 3. TKPROF 를이용하여성능통계정보보기 SQL TRACE FACILITY 는 SQL 문사용에대한성능을분석하기위해서사용된다. 이러한 SQL TRACE FACILITY 를이용하면각각의 SQL 문에대해서다음과같은정보를얻을수있다. > parse, execute, fetch count > CPU 와 elapsed 시간 > physical reads 와 logical reads > 처리된 row 의수 SQL TRACE FACILITY 는 SESSION 혹은 INSTANCE 단위로할수있고 TRACE 결과파일은 tkprof UTILITY 에의해사용자가읽을수있는형태로변환시킨다 Edited by heiya

46 SQL Trace 사용법 1) SQL TRACE enable 및 TRACE 파일디렉토리지정 ㄱ ) INSTANCE 단위 init.ora 파일에다음두개의 PARAMETER 를추가하고 DATABASE 를다시 STARTUP 시킨다. sql_trace = true timed_statistics = true timed_statistics 는시스템에많은 LOAD 가걸리므로전체 INSTANCE 단위에사용하는것은 바람직하지않다. ㄴ ) SESSION 단위 >> SQL*PLUS $ sqlplus scott/tiger SQL> ALTER SESSION SET SQL_TRACE = TRUE; SQL> sql 문장실행 SQL> exit >> PRO*C EXEC SQL CONNECT :username; EXEC SQL ALTER SESSION SET SQL_TRACE = TRUE; 이렇게하면 user_dump_dest directory 에 trace file 이생성된다. user_dump_dest 가어디 로지정되어있는지는다음과같이확인한다. SQL> select value from v$parameter where name = 'user_dump_dest'; 2) TRACE 파일변환 SQL문을실행하면 user_dump_dest에지정된디렉토리에 TRACE 파일이생기고 tkprof를이용하여파일을변환시킨다. TRACE 파일은쉽게찾을수있는형태가아니므로 SQL 문을실행하기전에 dump 디렉토리에있는 ora_xxxx.trc 파일을모두삭제하거나가장최근에생긴파일중에서찾아야한다. 이때해당 user에 plan_table 이라는 table이없으면 utlxplan.sql을수행하여 table을만든다 Edited by heiya

47 예 ) $ cd $ORACLE_HOME/rdbms/log $ tkprof ora_xxx.trc result.out sort=fchqry,fchcu explain=scott/tiger print=20 ora_xxx.trc : TRACE 파일명 result.out : 결과파일명 sort : 지정된 OPION(fchqry, fchcu) 에 ASCENDING 순으로 SQL 문을 SORTING 한다. explain : SQL 문의 EXECUTION PLAN 을발생시킨다. print : 지정된개수의 SQL 문에대해서만 TRACE 결과를 PRINT 한다. 3) SQL TRACE 결과분석 ********************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ********************************************************************** SELECT COUNT(*) E, DEPT D WHERE E.DEPTNO=D.DEPTNO call count cpu elapsed disk query current rows Parse Execute Fetch Misses in library cache during parse: 1 Parsing user id: 8 (SCOTT) Rows Execution Plan SELECT STATEMENT 0 SORT (AGGREGATE) 16 NESTED LOOPS 16 TABLE ACCESS (FULL) OF 'EMP' 16 INDEX (UNIQUE SCAN) OF 'DEPT_PRIMARY_KEY' (UNIQUE) ********************************************************************* 분석방법 a. CPU, elapsed 정보가없는경우는 init.ora에 timed_statistics 설정을확인한다. b. Execute count와 Fetch count가동일하게크다고하면 ARRAY FETCH 사용을고려함. c. fetch된 rows 수 : query + current = 1 : 4 이하이면 SQL 문은적절히사용된경우이고, row 수에비하여 query + count가상당히크면부적절하게사용된 SQL 문이므로 (count, Edited by heiya

48 sum, distinct 등 GROUP function을사용하는경우는예외 ) 다음내용들에대해서재검토가필요하다. >> INDEX 사용, 구성여부 >> ROWID 사용 >> COST_BASED OPTIMIZER 사용 (ORACLE 7) >> ARRAY FETCH 사용 >> SORTING을피할수있는 SQL 문구사 d. Parse count, Execute count가비슷한경우 RELEASE_CURSOR, HOLE_CURSOR OPTION 사용하여 Parse count를줄임. 4. SQL 문분석을위한 EXPLAIN PLAN 의활용 Explain Plan은 SQL문을실행하지않고실행되어질경로를파악함으로써성능을가늠해볼수있도록 ORACLE에서제공하는기능이다. Explain Plan의결과에서는 SQL문을수행하기위해 ORACLE이하게될테이블의조회또는 join 방법, index search 또는 full table scan과같은 access의순서및형태, 그리고사용하게될 index의이름등에대한내용등을알수있다. 해석하는방법은맨안쪽부터바깥쪽으로, 그리고위에서부터아래쪽으로읽어나가야한다. 그러므로만약두개의 operation 이나열될경우일반적으로더들쭉날쭉한문장이먼저실행되 고, 둘다동일한레벨이라면먼저기술된문장이먼저실행된다. 가장대표적인 Access Type은 NESTED LOOPS, HASH join, MERGE join이다. NESTED LOOPS은 ' 맨안쪽부터바깥쪽으로, 그리고위에서부터아래쪽으로 ' 의룰에따라건별로순차적으로데이터를읽어나간다. MERGE join 방식은두테이블을읽어 sort한후서로값을비교해나가는방식이며, HASH join 방식은데이터를읽되내부적으로 hash function을이용하여정렬한후이를통해 join해나가는방식이다. 어느한가지방식이항상유리한것은아니고, 데이터의건수나인덱스의구성, 통계정보의 유무에따라성능이결정된다. 이에대해깊게배우고자하는분은 ORACLE 기술문서를참조하기바란다. 다음은자주볼수있는 OPERATION 과 OPTION 을정리한표이다 Edited by heiya

49 표 ) EXPLAIN PLAN 에의해자주생성되는 OPERATION 과 OPTION Operation AND-EQUAL Option Description 단일컬럼인덱스에대한 access 시사용하며, 인덱스 merge 를통해 rowid 별로중복을제거한다. CONNECT BY CONNECT BY 절에의해순차적인결과값을보여준다. CONCATENATION COUNT 여러결과집합을 union-all 하여하나의집합으로돌려준다. 조건에맞는집합내의 row 건수를돌려준다. STOPKEY ROWNUM 에의해 where 절에서건수를세서중지시킨다. FILTER 여러조건에의해결과집합을걸러서돌려준다. FIRST ROW Query 에의해선택된 row 중처음 row 만돌려준다. FOR UPDATE HASH JOIN + INDEX * ANTI SEMI FOR UPDATE 절에의해선택되고 update 를위해 lock 이걸려있을때 DSS 나 Batch 에서다량의데이터에대해사용할때유용하다. CBO 는메모리상에서 join key 를이용해 hash 테이블을생성하고이를통해각테이블에 access 한다. Hash anti-join. Hash semi-join. UNIQUE SCAN Unique-index 를통해테이블의 row 에 access 한다. RANGE SCAN Non-unique index 를통하거나 unique index 의특정범위에대한테이블의 row 에 access 할때 FULL SCAN 인덱스에대해서 full scan 하여결과값을보여준다. FAST FULL SCAN 인덱스를 multiblock 을읽어결과값을보여준다. CBO 에서만사용가능하다. SKIP SCAN 결합인덱스에서선행컬럼을건너뛰고 scan 하여결과값을보여준다. CBO 에서만사용가능하다. INTERSECTION 교집합추출. 중복값없음. MERGE JOIN + 먼저자신의조건만으로액세스한후각각을소트하여 merge 해가는조인. MINUS NESTED LOOPS + REMOTE SORT OUTER ANTI SEMI CARTESIAN Merge join 에 outer join 이지정된경우 Merge anti-join. Merge semi-join. 각결과집합을이용해 Cartesian product 생성 Operation accepting two sets of rows and returning rows appearing in the first set but not in the second, eliminating duplicates. 먼저어떤 DRIVING 테이블의 ROW 를 ACCESS 한후그결과를이용해다른테이블을연결하는조인 OUTER 위와동일한방법으로 OUTER JOIN 을한다. AGGREGATE UNIQUE GROUP BY JOIN ORDER BY 분산 DATABASE 에있는객체를추출하기위해 DATABASE LINK 를사용하는경우. 그룹함수 (SUM, COUNT 등 ) 를사용하여하나의 ROW 를추출 같은 ROW 를제거하기위한소트 액세스결과를 GROUP BY 하기위한소트 MERGE JOIN 을하기위한소트 ORDER BY 를위한소트 Edited by heiya

50 TABLE ACCESS * UNION FULL 테이블의전체 row 에대해 access 한다. CLUSTER Indexed cluster key 를통해테이블의 row 에 access 한다. HASH Hash cluster key 를통해테이블의 row 에 access 한다. BY ROWID RANGE Rowid 의범위에의해테이블의 row 에 access 한다. BY USER ROWID 사용자가지정한 rowid 를통해테이블의 row 에 access 한다. BY INDEX ROWID Partition 되지않은 index 를통해테이블의 row 에 access 한다. BY GLOBAL INDEX ROWID BY LOCAL INDEX ROWID Partition 된테이블에대해 global index 를통해테이블의 row 에 access 한다. Partition 된테이블에대해 local index 를통해테이블의 row 에 access 한다. 중복없는합집합을구한다. 항상전체범위처리 VIEW 어떤처리에의해생성되는뷰에서추출한다. 주로 sub-query 에의해서수행된결과 * access methods + join operations 위표 ) 는자주사용하는것들을정리한것이므로, 그외에더자세한내용을알고자하면 Oracle9i Database Performance Tuning Guide and Reference 를참조하기바란다. 5. HINT 의사용 HINT 란 ORACLE Optimizer 가실행경로를생성할때그경로를제어하기위해사용자가제시하 는가이드라인이다. 가이드라인이라한이유는 HINT 를제시한다고해서경로생성이반드시원 한대로되는것이아니라는것이다. 아래는주로사용하는아래의힌트들에대해설명하고자한다. ALL_ROWS, FIRST_ROWS, CHOOSE, RULE, FULL, ROWID, USE_NL, USE_MERGE, USE_HASH, INDEX, INDEX_ASC, INDEX_DESC 예 ) OPTIMIZER_MODE=CHOOSE (init.ora) TABLE : SCOTT.EMP, SCOTT.DEPT INDEX : EMP - EMPNO에 PK emp_pk, DEPTNO에인덱스 emp_idx01 DEPT DEPTNO에 PK dept_pk 아래의 analyze 문을수행한다. ANALYZE TABLE SCOTT.EMP COMPUTE STATISTICS; ANALYZE TABLE SCOTT.DEPT COMPUTE STATISTICS; Edited by heiya

51 1) ALL_ROWS ALL_ROWS는대상결과를모두표시하는데가장좋은경로를생성하고자할때사용한다. ALL_ROWS를할경우 Full table scan을선호하며 CBO는 default로 ALL_ROWS를선택한다. 그러나아래와같이유용한인덱스가있거나다른결과의통계정보가있는경우아래와같이 INDEX SCAN을한다. SQL> SELECT /*+ ALL_ROWS */ EMPNO, ENAME WHERE EMPNO = 7566; Execution Plan SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=4 Bytes=348) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (Cost=1 Card=4 Bytes=348) 2 1 INDEX (RANGE SCAN) OF 'SCOTT.EMP_PK' (UNIQUE) (Cost=1 Card=2) 2) FIRST_ROWS FIRST_ROWS는대상결과중첫 row를표시하는데가장좋은경로를생성하고자할때사용한다. Full table scan보다는 index scan을선호하며 interactive application인경우 best response time을제공한다. 또한 sort merge join보다는 nested loop join을선호한다. SQL> SELECT /*+ FIRST_ROWS */ E.EMPNO, E.ENAME, D.DNAME E, DEPT D WHERE E.DEPTNO = D.DEPTNO Execution Plan SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=411 Card=409 Bytes=47K) 1 0 NESTED LOOPS (Cost=411 Card=409 Bytes=47K) 2 1 TABLE ACCESS (FULL) OF 'SCOTT.EMP' (Cost=2 Card=409 Bytes=35K) 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.DEPT' (Cost=1 Card=1 Bytes=30) 4 3 INDEX (UNIQUE SCAN) OF 'SCOTT.DEPT_PK' (UNIQUE) (Card=1) 또, select list 에따라 index scan 을하는 table 이바뀔수도있다. SQL> SELECT /*+ FIRST_ROWS */ D.DEPTNO, D.DNAME E, DEPT D WHERE E.DEPTNO = D.DEPTNO Edited by heiya

52 Execution Plan SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=411 Card=409 Bytes=14K) 1 0 NESTED LOOPS (Cost=411 Card=409 Bytes=14K) 2 1 TABLE ACCESS (FULL) OF 'SCOTT.DEPT' (Cost=2 Card=409 Bytes=9K) 3 1 INDEX (RANGE SCAN) OF 'SCOTT.EMP_IDX01' (NON-UNIQUE) (Cost=1 Card=1 Bytes=13) Group by 가있는 SQL 문은 FIRST_RIWS 가있다하더라도 index scan 을하지않는다. SQL> SELECT /*+ FIRST_ROWS */ COUNT(*) E GROUP BY E.DEPTNO; Execution Plan SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=18 Card=409 Bytes=5K) 1 0 SORT (GROUP BY) (Cost=18 Card=409 Bytes=5K) 2 1 TABLE ACCESS (FULL FULL) OF 'SCOTT.EMP' (Cost=2 Card=409 Bytes=5K) 3) CHOOSE Hint level 의 CHOOSE 는 RBO 인지 CBO 인지를선택한다. 만약주어진 table 의통계정보가 없다면 RBO 를사용한다. 4) RULE RBO 를사용하도록지정한다. SQL> SELECT /*+ RULE */ COUNT(*) E GROUP BY E.DEPTNO; Execution Plan SELECT STATEMENT Optimizer=HINT: RULE 1 0 SORT (GROUP BY) 2 1 TABLE ACCESS (FULL) OF 'SCOTT.EMP' 5) FULL FULL 힌트는참조테이블에대해 full table scan 을하도록 ORACLE optimizer 를유도한다. 이는 Index 가있지만선택도 (selectivity) 가좋지않은경우 full table scan 을선택하도록 한다 Edited by heiya