DBMS 2
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
이장은 DB2 UDB에서 사용되는 SQL에 대한 성능을 고려한 SQL 작성을 어떻게 할 것이지 가이드를 합니다. Select 문에 필요로 하는 column만 기술합니다. * 를 사용하여 모든 column을 기술하면 사용하기는 편리할 수도 있습니다만 원치 않았던 column들에 대한 processing은 응답시간을 늘어나게 할 수 있습니다. Order by, distinct 는 data에 대한 sort를 필요로 하며 시스템의 CPU를 사용하게 됩니다. Sort heap이 부족한 경우는 temporary tablespace에 대한 Disk I/O가 발생하게 됩니다. ㆍ불필요한 order by나 distinct로 인한 성능저하가 발생하지 않도록 해야 합니다. ㆍcurosr문의 select 절에 order by, group by등을 기술하게 되면 open 시점에 data reading이 일어나며 result set이 만들어진 후 fetch를 하게 됩니다. 이에 반해 order by, group by를 사용하지 않은 cursor의 경우 fetch시 한 건이 데이터베이스로부터 data를 읽어 오게 됩니다. Union은 sort를 필요로 합니다. Sort가 필요 없는 경우 union all을 사용하여 불필요한 sort를 하지 않도록 합니다. Table에 해당 data 건수가 하나 이상인 것을 check하기 위해 count column function을 사용하지 말고 select into절을 사용하여 -811 SQL error code를 check하도록 합니다. 필요로 하는 row만 result set으로 하기 위해서는 Predicate을 사용하여 return되는 row 건 수를 줄여야 합니다. predicate에는 4가지 종류가 있으며 각각 독립된 processing 방법 및 해당 cost를 가지고 있습니다. 이 4가지 종류의 predicate은 evaluation process에서 어떻게 언제 사용되는지에 따라 결정됩니다. 다음은 4가지 종류의 predicate을 성능이 좋은 순서로 나열하였습니다. Range delimiting predicate이 가장 좋은 성능을 나타냅니다. 1. Range delimiting predicates 다음은 각 predicate의 예로 사용될 Employee Table의 Index입니다. Range delimiting predicates은 Index search에 대한 start key / stop key를 정의할 때 사용되는 predicates입니다. 다음 Select 문의 예에서 NAME과 DEPT predicate는 delimiting predicates이 됩니다. l Index SARGable predicate은 Index search에 대해 start key /stop key를 정의할 수 없지만 Index column중에 predicate의 column이 있어 Index column으로 predicate에 대한 검증이 가능한 경우 이 predicates를 Index SARGable predicates라고 합니다. 다음의 예에서 NAME과 DEPT는 delimiting predicates이지만 YEARS의 경우 Index SARGable predicates으로 사용됩니다. YEARS > 5는 index search결과 bufferpool로 read된 index column값으로 검증이 됩니다. 만약 YEARS >= 5이면 YEARS도 range delimiting predicate으로 사용됩니다 Range delimiting predicate과 Index SARGable predicate은 access할 Table의 row 개수를 줄일 수는 있지만 Index SARGable predicate의 경우 access 될 index page 수를 줄이지는 못합니다. Data SARGable predicate은 index에 의해 검증되지 않고 table로부터 data를 읽어서 검증을 할 수 있는 predicate을 의미합니다. DB2는 해당 predicate을 검증하는데 필요한 column과 select 절에 있는 column중 index로부터 가져올 수 없는 column을 table로부터 읽어옵니다. 다음의 예에서 salary predicate은 salary column에 index가 없기 때문에 table로 부터 data를 읽어와서 검증을 하는 data SARGable로 처리됩니다. Residual SARGable predicate은 단순히 base table을 access하는 것 이상으로 I/O를 요구하는 predicate입니다. 예를 들면 ANY, ALL, SOME, IN을 사용한 subquery나 Long Varchar 혹은 LOB data 가 이에 해당합니다. Residual predicate은 네 가지 predicate중에서 가장 많은 cost를 필요로 합니다. 가능하면 row의 건수를 줄일 수 있는 range delimiting predicate이나 index SARGable predicate을 사용해야 합니다. 두 table간의 join시 join column없이 join을 하거나 join을 하더라도 M:1 혹은 1:1의 관계가 되도록 둘 Table중 적어도 한 table의 join predicate들을 unique하게 하지 않을 경우 M:M의 관계의 join으로 인해 원치 않는 많은 값이 return될 수 있고 성능도 저하되게 됩니다. 특히 table의 size가 큰 table에 이런 join을 할 경우 상당한 성능저하가 발생합니다. Table에 해당 data 건수가 하나 이상인 것을 check하기 위해 count column function을 사용하지 말고 select into절을 사용하여 -811 SQL error code를 check하도록 합니다. Table에 해당 data 건수가 하나 이상인 것을 check하기 위해 count column function을 사용하지 말고 select into절을 사용하여 -811 SQL error code를 check하도록 합니다. Cursor 내에서 commit을 반복적으로 하고자 하는 경우 with hold절을 사용합니다. With hold option을 사용할 경우 cursor내에서 commit을 하게 되더라도 cursor가 close되지 않습니다. 얼마나 많은 result set이 return될지는 몰라도 N row 이하로 data를 읽어와야만 하는 경우 fetch first n rows only절을 기술합니다. 이 절은 for update절과 함께 사용할 수 없습니다. 아래 예문에서는 첫 번째 5 row만 읽어오는 select 문입니다. fetch first n rows only절은 row block되는 row의 건수를 결정합니다. 위의 예문에서는 block에 5건의 row가 들어가게 되고 communication buffer 단위로 사용됩니다. 한번의 operation으로 5 row가 application으로 넘어오게 됩니다. Data type converion은 피해야 합니다. 특히 numeric type의 data conversion은 성능에 막대한 영향을 미칩니다. 두 Table이 join을 할 때 반드시 동일한 data type으로 join을 해야 합니다. 아 래 예문 1)에서 employee table의 dept_id가 integer 이고 department table의 dept_id가 decimal 이라고 한다면 두 table join시 내부적으로 data conversion이 발생하며 엄청난 성능저하가 발생합니다. 예문 2)는 employee table의 emp_id가 integer이고 project table의 emp_id가 character인 경우 project table의 emp_id가 integer로 casting을 통한 data conversion을 하는 경우입니다.. Max/min column function 대신에 fetch fist 절을 사용합니다. application에서 여러 건의 row에 대한 변경처리를 하는 경우 적절한 row단위로 commit을 함으로써 log full이 발생하지 않도록 합니다. Import시에는 commitcount를 기술하도록 합니다. 불필요한 lock을 걸지 않도록 Quest Central과 같은 GUI tool에 autocommit을 설정합니다. CLP환경에서도 autocommit을 default로 설정합니다.DB2 UDB SQL General 가이드
DB2 UDB SQL General 가이드
DB2 UDB SQL General 가이드
Select 문에 필요한 column만 기술합니다.
Select 문에 필요시 order by, distinct 를 기술합니다.
union보다는 union all을 사용합니다.
Table의 row가 1건 이상 유무 check
From emp
Where empno = '12344'
Return되는 row의 수를 줄입니다.
2. Index SARGable predicates
3. Data SARGable predicates
4. Residual predicates
주) SARGable : Search ARGument
DEPT ASC,
YEARS ASC );
Where NAME = 'John'
And DEPT = 10 ;
Where NAME = 'John'
And DEPT = 10 ;
And YEARS > 5
Where salary > 100000000
Cartesian Product가 생성되지 않도록 합니다.
FOR UPDATE 절을 기술합니다.
Select projno, prstdate, prendate
From project
For update of prstdate, prendate
FOR FETCH ONLY 절을 기술합니다.
Select projno, prstdate, prendate
From project
For fetch only [ for read only와 동일 ]
Cursor 내에서의 commit시 WITH HOLD 절을 기술합니다.
Select projno, prstdate, prendate
From project
FETCH FIRST n ROWS ONLY 절을 기술합니다.
Where deptno = 'D11'
Fetch first 5 rows only
Data Type Conversion을 피합니다.
Where e.dept_id = d.dept_id
2) Select name, job, salary from employee e, project p
Where e.emp_id = cast(p.emp_id as integer)
fetch first로 max/min value를 구합니다.
Where dept = 'DBA' Order by salary desc
Fetch first row only
적절한 commit을 합니다.
Autocommit을 설정합니다.