13 posts tagged “mysql”

[참고 :  MySql Bug “Calling MySQL5 stored procedures multiple times from PHP5″ ]
PHP 5와 MySQL 5를 연동하여 사용하는 조건에서 procedures를 호출(call)/query하는 도중 db접속을 까먹는 현상.

 

• 오랫동안 db에 접근이 없다  Query를 하면 “Lost connection to MySQL server during query”같은 에러가 뜬다?
  한번 Lost Connection이 뜨고 난뒤에 다시한번 Query하면 다시 정상 작동?
• Mysqli  Bug?
  일반 Select 구문은 여러번 실행해도 별 문제 없지만, procedure 호출을 위한 call 한번만해도 connection이 그냥 끊겨 버립니다.

 

[해결방안 또는 필요 사항]

1. PDO 사용.
2. 하나의 procedures 종료 후 다시 DB를 connect.
   (이때 procedures는 insert, update, select등의 연속 작업을 피하고 하나의 작업만 해야 한다)
3. 하나의 procedures에서 모든 값을 처리 후 MYSQL_MULTI_QUERY로 해결.

 

PDO 란 무엇인가?
…

MYSQL_MULTI_QUERY란 무엇인가?
…

killall mysqld -> 죽이기
mysqladmin -p reload -> 권한 수정 재 적용하기
mysql>flush privileges; -> mysql 안에서 권한 수정 재 적용하기

• mysql.com…
• LOCK TABLES and UNLOCK TABLES  Syntax : dev.mysql.com
• database.sarang.net…
• phpschool.com…

 

DB type이 MyISAM Type이면 insert, update등을 할때마다 LOCK이 발생.
myisam의 경우 insert, update시 자동 lock이 되므로 select되는 부분만 read lock을 걸어줘도 충분.
(데이터 무결성이 정확히 보장되어야 한다면 “스토리지 엔진을 InnorDB를 쓰고 트랜잭션을 사용”도 참고)

 

mysql의 기본 테이블 타입인 myisam의 table 단위의 lock과,
oracle, ms-sql의 row 단위 lock의 장단점.

• table단위 lock은 쓰기작업시 table 전체를 잠그기 때문에
  다른사람이 그 table에 접근하기 위해 대기를 해야하는것.
• row단위 lock은 해당 row를 제외한 다른 row 들도 쓰기, 읽기가 가능.
• row단위의 lock은 table단위 lock보다 어떤 row가 어떤 락 상태인지 파악하므로
  리소스를 더 쓸 수 있으며, 그에따른 오버헤드도 발생할 수 있다.

 

기본적인 Lock의 개념

• Lock설정은 특정 Table을 사용하고 있는 상황에서 다른 접속자의 접근 차단하는 기능을 한다.
• Read Lock을 걸면 다른 사용자는 해당 Table에 읽기만 할 수 있다.
• Write Lock을 걸면 다른 사용자는 Table에 전혀 접근 할 수 없다.

 

LOCK TABLES tab_name READ
현재 client 이외의 client들은 tab_name이란 테이블에 READ를 못한다는 즉, SELECT를 못한다는 것.

LOCK TABLES tab_name WRITE
현재 client 만이 WRITE 즉, INSERT, DELETE 등을 할 수 있다는 것.
WRITE Lock을 걸면, 자동으로 READ lock 까지 걸린다.

 

Example
lock tables AAA write;
insert …
unlock tables;

User가 INSERT, DELETE, UDATE, SELECT .. FROM .. FOR UPDATE OF 문장을 실행하면,
변경되는 ROW에 대한 ROW LOCK과 TABLE에 대한 TABLE LOCK이 발생.

ROW LOCK (TX)
INSERT INTO … VALUE.. ;,
DELETE FROM …WHERE …;,
UPDATE ..SET ..WHERE ..;,
SELECT .. FROM .. WHERE .. FOR UPDATE OF.. ; 등의 SQL 문장에서,
WHERE 조건에 해당되는 ROW에 대하여 다른 유저들이 변경할 수 없도록 EXCLUSIVE LOCK 이 생긴다.
TX LOCK이 걸린 ROW는 DML 문장을 실행한 유저가 COMMIT이나 ROLLBACK을 할때 까지 걸리므로
다른 유저들이 변경할 수 없다.

TABLE LOCK (TM)
TX LOCK이 걸린 ROW가 저장된 TABLE에 대한 LOCK 이다.
DML SQL 문장을 수행하는 중에,
해당 테이블이 ALTER 나 DROP 되는 것을 방지하기 위해서 TM LOCK을 사용한다.
같은 테이블에서 실행할 수 있는 SQL 문장과 실행할 수 없는 SQL 문장을 구분하기 위해서다.
TM LOCK에는 RS(ROW SHARE), RX(ROW EXCLUSIVE), S(SHARE), SRX(SHARE ROW EXCLUSIVE), X(EXCLUSIVE) 가 있다.

 

RS : ROW SHARE LOCK
table에 lock을 걸려는 transaction이 table안에 lock된 row가 있고
그 row를 변경시키고자 하는 것을 가리킨다.

SELECT .. FROM .. WHERE .. FOR UPDATE OF .. ; 이나
LOCK TABLE .. IN ROW SHARE MODE; 명령에 의해 해당 테이블에는 RS LOCK 이 생긴다.
RS LOCK 이 걸린 테이블에는 RS, RX, S, SRX LOCK 을 걸 수 있고, X LOCK 은 걸 수 없다.

단, SELECT .. FROM FOR UPDATE OF 명령에 의해 WHERE 조건에 걸린 ROW 에 대해서는
TX LOCK 이 생기므로 이 ROW 에 대해서 UPDATE, DELETE 를 실행할때는 테이블에 대해서는
RX LOCK이 생기므로 에러는 안 나지만, COMMIT 이나 ROLLBACK 할때까지 WAITING 을 한다.

SELECT .. FOR UPDATE OF; 문장은 테이블에는 RS LOCK 이므로 에러는 안나지만,
ROW 에 대해서는 TX LOCK 이 걸리므로 WAITING 한다.

 

RX : ROW EXCLUSIVE LOCK
lock이 걸린transaction이 그 table에 있는 row들에 대해
하나 이상의 update를 수행하고자 하는 것을 가리킨다.

UPDATE ..;, INSERT INTO ..;, DELETE FROM ..; 이나
LOCK .. IN ROW EXCLUSIVE MODE ; 명령에 의해 테이블에 걸리는 LOCK 이다.
RX LOCK 도 RS LOCK 과 비슷한 내용이고, 단지 S, SRX, X LOCK 을 걸 수 없다.

 

S : SHARE LOCK
Transaction에 의해서 걸리는 share table lock은 다른 transaction들이
단지, table에 대한 query, SELECT … FOR UPDATE를 이용한 특정 row에 대한
lock, LOCK TABLE … IN SHARE MODE문들을 성공적으로 수행하기 위해서 허용한다.

LOCK .. IN SHARE MODE; 에 의해 테이블에 생긴 LOCK 이다.
S LOCK 은 같은 테이블에 대해서 RS, S LOCK 만 가능하고, RX, SRX, X LOCK 을 걸 수는 없다.
SQL> LOCK TABLE EMP IN SHARE MODE;

 

SRX : SHARE ROW EXCLUSIVE
한 시점에 주어진 table에 대해 하나의 share row exclusive table lock만이 걸릴 수 있다.
transaction에 의해 걸린 share row exclusive table lock은
다른 transaction이 query을 하거나 SELECT … FOR UPDATE로
특정 row를 lock하는 것을 허용하나 table의 갱신은 허용하지 않는다.

LOCK TABLE .. IN SHARE ROW EXCLUSIVE MODE; 에 의해 테이블에 생긴 LOCK 이다.
SRX LOCK 은 같은 테이블에 대해서 RS LOCK 만 가능하고 RX, SRX, S, X, LOCK 을 걸 수 없다.
SQL> LOCK TABLE 사원 IN SHARE ROW EXCLUSIVE MODE;

 

X : EXCLUSIVE
lock을 건 transaction이 table에 대한 access를 exclusive write로 허용하는
table lock의 가장 제한적인모드

LOCK TABLE .. IN EXCLUSIVE MODE; 에 의해 테이블에 생긴 LOCK 이다.
X LOCK 은 같은 테이블에서는 어떠한 LOCK 도 걸 수 없다.
즉, DROP TABLE ..;, ALTER TABLE ..; 등의 DDL 문장에 의해 테이블에 생기는 LOCK 이다.

1.동일 database내의 동일 Table구조

{ Database - Table }

• 열(row)단위의 insert와 select 작업이 동시에 일어나는 경우 table lock으로 인한 “처리 속도저하”등의 부하가 일어날 수 있는다.
• table lock이란?

2.서로 다른 database내의 서로 다른 databse 구조

{ Database A - Table a } insert용도

{ Database B - Table b } select용도

• 위의 경우도 [Database B - Table b]를 사용할때 [Database A - Table a]에서 해당 데이터가 넘어오기때문에
  insert와 select의 중복현상은 마찬가지로 일어난다.
• 열(row)단위의로 작업이 이루어지는 구조를 바꾼다.
• 지속적인 [Database B - Table b]의 select 사용이 있을때,
  [Database B - Table b]에 채워질 데이터(select용 데이터)를 cron등을 사용하여
  일정 시간에 한번씩 [Database A - Table a]에서 가져와 바꾼다.
• 실시간 처리는?

조건이 걸린 SQL에서 인덱스를 탈려면
단독컬럼으로만 만들어도 되지만 결합인덱스의 앞에 위치하는 경우도 인덱스를 타게 된다.

Field-A+Filed-B+Filed-C 이러한 인덱스가 있다면

where조건에

• Filed-A만 있는 경우 –> 인덱스 사용
• Filed-B만 있는 경우 –> 인덱스 미사용
• Filed-A, Filed-B 있는 경우 –> 인덱스 사용
• Filed-A, Filed-B, Filed-C 있는 경우 –> 인덱스 사용

*from : http://spaceufo.wordpress.com/2007/07/22/mysql-411-date-and-time-functions/

 

*Reference : http://www.mysql.com/search/?q=Date_and_time

4.11 Date and Time Functions
See section 7.3.3 Date and Time Types for a description of the range of values each type has and the valid formats in which date and time values may be specified.

 
 

Here is an example that uses date functions. The query below selects all records with a date_col value from within the last 30 days:

mysql> SELECT something FROM table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

DAYOFWEEK(date)
Returns the weekday index for date (1 = Sunday, 2 = Monday, … 7 = Saturday). These index values correspond to the ODBC standard:
mysql> select DAYOFWEEK(’1998-02-03′);
-> 3

WEEKDAY(date)
Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday):
mysql> select WEEKDAY(’1997-10-04 22:23:00′);
-> 5
mysql> select WEEKDAY(’1997-11-05′);
-> 2

DAYOFMONTH(date)
Returns the day of the month for date, in the range 1 to 31:
mysql> select DAYOFMONTH(’1998-02-03′);
-> 3

DAYOFYEAR(date)
Returns the day of the year for date, in the range 1 to 366:
mysql> select DAYOFYEAR(’1998-02-03′);
-> 34

MONTH(date)
Returns the month for date, in the range 1 to 12:
mysql> select MONTH(’1998-02-03′);
-> 2

DAYNAME(date)
Returns the name of the weekday for date:
mysql> select DAYNAME(”1998-02-05″);
-> ‘Thursday’

MONTHNAME(date)
Returns the name of the month for date:
mysql> select MONTHNAME(”1998-02-05″);
-> ‘February’

QUARTER(date)
Returns the quarter of the year for date, in the range 1 to 4:
mysql> select QUARTER(’98-04-01′);
-> 2

WEEK(date)
WEEK(date,first)
With a single argument, returns the week for date, in the range 0 to 53 (yes, there may be the beginnings of a week 53), for locations where Sunday is the first day of the week.
The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday.
The week starts on Sunday if the second argument is 0, on Monday if the second argument is 1:

mysql> select WEEK(’1998-02-20′);
-> 7
mysql> select WEEK(’1998-02-20′,0);
-> 7
mysql> select WEEK(’1998-02-20′,1);
-> 8
mysql> select WEEK(’1998-12-31′,1);
-> 53

YEAR(date)
Returns the year for date, in the range 1000 to 9999:
mysql> select YEAR(’98-02-03′);
-> 1998

YEARWEEK(date)
YEARWEEK(date,first)
Returns year and week for a date. The second arguments works exactly like the second argument to WEEK().
Note that the year may be different from the year in the date argument for the first and the last week of the year:

mysql> select YEARWEEK(’1987-01-01′);
-> 198653

HOUR(time)
Returns the hour for time, in the range 0 to 23:
mysql> select HOUR(’10:05:03′);
-> 10

MINUTE(time)
Returns the minute for time, in the range 0 to 59:
mysql> select MINUTE(’98-02-03 10:05:03′);
-> 5

SECOND(time)
Returns the second for time, in the range 0 to 59:
mysql> select SECOND(’10:05:03′);
-> 3

PERIOD_ADD(P,N)
Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value:
mysql> select PERIOD_ADD(9801,2);
-> 199803

PERIOD_DIFF(P1,P2)
Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values:
mysql> select PERIOD_DIFF(9802,199703);
-> 11

DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)
These functions perform date arithmetic. They are new for MySQL Version 3.22.
ADDDATE() and SUBDATE() are synonyms for DATE_ADD() and DATE_SUB().

In MySQL Version 3.23, you can use + and - instead of DATE_ADD() and DATE_SUB() if the expression on the right side is a date or datetime column.
(See example) date is a DATETIME or DATE value specifying the starting date.

expr is an expression specifying the interval value to be added or substracted from the starting date.
expr is a string; it may start with a `-’ for negative intervals.
type is a keyword indicating how the expression should be interpreted.

The EXTRACT(type FROM date) function returns the ‘type’ interval from the date.
The following table shows how the type and expr arguments are related: type value Expected expr format

SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
MONTH MONTHS
YEAR YEARS
MINUTE_SECOND “MINUTES:SECONDS”
HOUR_MINUTE “HOURS:MINUTES”
DAY_HOUR “DAYS HOURS”
YEAR_MONTH “YEARS-MONTHS”
HOUR_SECOND “HOURS:MINUTES:SECONDS”
DAY_MINUTE “DAYS HOURS:MINUTES”
DAY_SECOND “DAYS HOURS:MINUTES:SECONDS”

MySQL allows any punctuation delimiter in the expr format.
Those shown in the table are the suggested delimiters.
If the date argument is a DATE value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts),

the result is a DATE value. Otherwise the result is a DATETIME value:

mysql> SELECT “1997-12-31 23:59:59″ + INTERVAL 1 SECOND;
-> 1998-01-01 00:00:00
mysql> SELECT INTERVAL 1 DAY + “1997-12-31″
-> 1998-01-01
mysql> SELECT “1998-01-01″ - INTERVAL 1 SECOND;
-> 1997-12-31 23:59:59
mysql> SELECT DATE_ADD(”1997-12-31 23:59:59″, INTERVAL 1 SECOND);
-> 1998-01-01 00:00:00
mysql> SELECT DATE_ADD(”1997-12-31 23:59:59″, INTERVAL 1 DAY);
-> 1998-01-01 23:59:59
mysql> SELECT DATE_ADD(”1997-12-31 23:59:59″, INTERVAL “1:1″ MINUTE_SECOND);
-> 1998-01-01 00:01:00
mysql> SELECT DATE_SUB(”1998-01-01 00:00:00″, INTERVAL “1 1:1:1″ DAY_SECOND);
-> 1997-12-30 22:58:59
mysql> SELECT DATE_ADD(”1998-01-01 00:00:00″, INTERVAL “-1 10″ DAY_HOUR);
-> 1997-12-30 14:00:00
mysql> SELECT DATE_SUB(”1998-01-02″, INTERVAL 31 DAY);
-> 1997-12-02
mysql> SELECT EXTRACT(YEAR FROM “1999-07-02″);
-> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM “1999-07-02 01:02:03″);
-> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM “1999-07-02 01:02:03″);
-> 20102

 

If you specify an interval value that is too short (does not include all the interval parts that would be expected from the type keyword),
MySQL assumes you have left out the leftmost parts of the interval value. For example,
if you specify a type of DAY_SECOND, the value of expr is expected to have days, hours, minutes, and seconds parts.

If you specify a value like “1:10″, MySQL assumes that the days and hours parts are missing and the value represents minutes and seconds.
In other words, “1:10″ DAY_SECOND is interpreted in such a way that it is equivalent to “1:10″ MINUTE_SECOND.

This is analogous to the way that MySQL interprets TIME values as representing elapsed time rather than as time of day.
Note that if you add or subtract a date value against something that contains a time part, the date value will be automatically converted to a datetime value:

mysql> select date_add(”1999-01-01″, interval 1 day);
-> 1999-01-02
mysql> select date_add(”1999-01-01″, interval 1 hour);
-> 1999-01-01 01:00:00

If you use really incorrect dates, the result is NULL.
If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month:

mysql> select DATE_ADD(’1998-01-30′, Interval 1 month);
-> 1998-02-28

 

Note from the preceding example that the word INTERVAL and the type keyword are not case sensitive.
TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it doesn’t take into account the days that were lost when the calender was changed.
TO_DAYS(date)
Given a date date, returns a daynumber (the number of days since year 0):

mysql> select TO_DAYS(950501);
-> 728779
mysql> select TO_DAYS(’1997-10-07′);
-> 729669

 

FROM_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582),
because it doesn’t take into account the days that were lost when the calender was changed.
FROM_DAYS(N)
Given a daynumber N, returns a DATE value:
mysql> select FROM_DAYS(729669);
-> ‘1997-10-07′

DATE_FORMAT(date,format)
Formats the date value according to the format string.
The following specifiers may be used in the format string:

%M Month name (January..December)
%W Weekday name (Sunday..Saturday)
%D Day of the month with English suffix (1st, 2nd, 3rd, etc.)
%Y Year, numeric, 4 digits
%y Year, numeric, 2 digits
%X Year for the week where Sunday is the first day of the week, numeric, 4 digits, used with ‘%V’
%x Year for the week, where Monday is the first day of the week, numeric, 4 digits, used with ‘%v’
%a Abbreviated weekday name (Sun..Sat)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%m Month, numeric (01..12)
%c Month, numeric (1..12)
%b Abbreviated month name (Jan..Dec)
%j Day of year (001..366)
%H Hour (00..23)
%k Hour (0..23)
%h Hour (01..12)
%I Hour (01..12)
%l Hour (1..12)
%i Minutes, numeric (00..59)
%r Time, 12-hour (hh:mm:ss [AP]M)
%T Time, 24-hour (hh:mm:ss)
%S Seconds (00..59)
%s Seconds (00..59)
%p AM or PM
%w Day of the week (0=Sunday..6=Saturday)
%U Week (0..53), where Sunday is the first day of the week
%u Week (0..53), where Monday is the first day of the week
%V Week (1..53), where Sunday is the first day of the week. Used with ‘%X’
%v Week (1..53), where Monday is the first day of the week. Used with ‘%x’
%% A literal `%’.

All other characters are just copied to the result without interpretation:
mysql> select DATE_FORMAT(’1997-10-04 22:23:00′, ‘%W %M %Y’);
-> ‘Saturday October 1997′
mysql> select DATE_FORMAT(’1997-10-04 22:23:00′, ‘%H:%i:%s’);
-> ‘22:23:00′
mysql> select DATE_FORMAT(’1997-10-04 22:23:00′, ‘%D %y %a %d %m %b %j’);
-> ‘4th 97 Sat 04 10 Oct 277′
mysql> select DATE_FORMAT(’1997-10-04 22:23:00′,
‘%H %k %I %r %T %S %w’);
-> ‘22 22 10 10:23:00 PM 22:23:00 00 6′
mysql> select DATE_FORMAT(’1999-01-01′, ‘%X %V’);
-> ‘1998 52′

As of MySQL Version 3.23, the `%’ character is required before format specifier characters.
In earlier versions of MySQL, `%’ was optional.

TIME_FORMAT(time,format)
This is used like the DATE_FORMAT() function above,
but the format string may contain only those format specifiers that handle hours, minutes, and seconds.
Other specifiers produce a NULL value or 0.

CURDATE()
CURRENT_DATE
Returns today’s date as a value in ‘YYYY-MM-DD’ or YYYYMMDD format, depending on whether the function is used in a string or numeric context:

mysql> select CURDATE();
-> ‘1997-12-15′
mysql> select CURDATE() + 0;
-> 19971215

CURTIME()
CURRENT_TIME
Returns the current time as a value in ‘HH:MM:SS’ or HHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> select CURTIME();
-> ‘23:50:26′
mysql> select CURTIME() + 0;
-> 235026

NOW()
SYSDATE()
CURRENT_TIMESTAMP
Returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> select NOW();
-> ‘1997-12-15 23:50:26′
mysql> select NOW() + 0;
-> 19971215235026

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds since ‘1970-01-01 00:00:00′ GMT).
If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since ‘1970-01-01 00:00:00′ GMT. date may be a DATE string,
a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD in local time:

mysql> select UNIX_TIMESTAMP();
-> 882226357
mysql> select UNIX_TIMESTAMP(’1997-10-04 22:23:00′);
-> 875996580

When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function will receive the value directly, with no implicit “string-to-unix-timestamp” conversion. If you give UNIX_TIMESTAMP() a wrong or out-of-range date, it will return 0.

FROM_UNIXTIME(unix_timestamp)
Returns a representation of the unix_timestamp argument as a value in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context:

mysql> select FROM_UNIXTIME(875996580);
-> ‘1997-10-04 22:23:00′
mysql> select FROM_UNIXTIME(875996580) + 0;
-> 19971004222300

FROM_UNIXTIME(unix_timestamp,format)
Returns a string representation of the Unix timestamp, formatted according to the format string.
format may contain the same specifiers as those listed in the entry for the DATE_FORMAT() function:

mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(), ‘%Y %D %M %h:%i:%s %x’);
-> ‘1997 23rd December 03:43:30 x’

SEC_TO_TIME(seconds)
Returns the seconds argument, converted to hours, minutes, and seconds, as a value in ‘HH:MM:SS’ or HHMMSS format,
depending on whether the function is used in a string or numeric context:

mysql> select SEC_TO_TIME(2378);
-> ‘00:39:38′
mysql> select SEC_TO_TIME(2378) + 0;
-> 3938

TIME_TO_SEC(time)
Returns the time argument, converted to seconds:

mysql> select TIME_TO_SEC(’22:23:00′);
-> 80580
mysql> select TIME_TO_SEC(’00:39:38′);
-> 2378

 

*where : http://www.phpschool.com/gnuboard4/bbs/board.php?b…

 

출처 : http://www.coolzzin.com
이 글은 SURESH AIYER라는 사람이 쓴 글로 오라클에서 SQL을 효과적으로 작성하기 위한 원칙과 기법을 25가지로 정리해서 DATABASE PROGRAMMING & DESIGN 2002년도 1월호에 쓴 글입니다.

 

1.데이터와 비즈니스 어플리케이션을 잘 알아야 한다.
동일한 정보는 다른 비즈니스 데이터 원천으로부터 검색될 수 있다.
이러한 원천에 익숙해야 한다.
당신은 당신의 데이터베이스 안의 데이터의 크기와 분포를
반드시 알아야 한다.
또한 SQL을 작성하기 전에 비즈니스 개체 안의 관계와 같은
데이터 모델을 전체적으로 이해해야 한다.
이러한 이해는 당신이 여러 테이블에서 정보를 검색하는데 있어서
보다 좋은 쿼리를 작성할 수 있다.
DESIGNER/2000과 같은 CASE TOOLS은 다른 비즈니스와 데이터베이스
객체사이의 관계를 문서화하는데 좋은 역할을 한다.

 

2.실제 데이터를 가지고 당신의 쿼리를 검사하라.
대부분의 조직은 개발, 검사, 제품의 3가지 데이터베이스 환경을 가진다.
프로그래머는 어플리케이션을 만들고 검사하는데 개발 데이터베이스 환경을
사용하는데, 이 어플리케이션이 제품 환경으로 전환되기 전에 프로그래머와
사용자에 의해 검사 환경하에서 보다 엄격하게 검토되어야 한다.
SQL이 검사 환경하에서 테스트될 때, 검사 데이터베이스가 가지고 있는 데이터는 제품 데이터베이스 를 반영해야 한다.
비실제적인 데이터를 가지고 테스트된 SQL문은 제품 안에서는
다르게 작동할 수 있다.
엄격한 테스트를 보장하기 위해서는, 검사 환경하에서의 데이터 분포는
반드시 제품 환경에서의 분포와 밀접하게 닮아야 한다.

 

3.동일한 SQL을 사용하라.
가능한한 BIND VARIABLE, STORED PROCEDURE, PACKAGE의 이점을 활용하라. IDENTICAL SQL문의 이점은 PARSING이 불필요하기에 데이터베이스 서버안에서 메모리 사용의 축소와 빠른 수행을 포함한다.

예로서 아래의 SQL 문은 IDENTICAL하지 않다.

SELECT * FROM EMPLOYEE WHERE EMPID = 10;
SELECT * FROM EMPLOYEE WHERE EMPID = 10;
SELECT * FROM EMPLOYEE WHERE EMPID = 20;

그러나 I_EMPID라고 이름 주어진 BIND VARIABLE을 사용하면
SQL 문은 이렇게 된다.
SELECT * FROM EMPLOYEE WHERE EMPID = :I_EMPID;

 

4.주의 깊게 인덱스를 사용하라.
테이블상에 모든 필요한 인덱스는 생성되어야 한다.
하지만 너무 많은 인덱스는 성능을 떨어뜨릴 수 있다.
그러면 어떻게 인덱스를 만들 칼럼을 선택해야 하는가?

*최종 사용자에 의해 사용되는
어플리케이션 SQL과 쿼리의 WHERE 절에서 빈번하게 사용되는
칼럼에 인덱스를 만들어야 한다.

*SQL 문에서 자주 테이블을 JOIN하는데 사용되는 칼럼은 인덱스되어야 한다.

*같은 값을 가지는 ROW가 적은 비율을 가지는 칼럼에 인덱스를 사용하라.

*쿼리의 WHERE 절에서 오직 함수와 OPERATOR로 사용되는
칼럼에는 인덱스를 만들면 안된다.

*자주 변경되거나 인덱스를 만들때 얻는 효율성보다 삽입, 갱신, 삭제로
인해 잃는 효율성이 더 큰 칼럼에는 인덱스를 만들면 안된다.
이러한 OPERATION은 인덱스를 유지하기 위한 필요 때문에 느려진다.

*UNIQUE 인덱스는 더 나은 선택성 때문에 NONUNIQUE 인덱스보다 좋다. PRIMARY KEY 칼럼에 UNIQUE 인덱스를 사용한다.
그리고 FOREIGN KEY 칼럼과 WHERE 절에서 자주 사용되는 칼럼에는
NONUNIQUE 인덱스를 사용한다.

 

5.가용한 인덱스 PATH를 만들어라
인덱스를 사용하기 위해서는 기술한 SQL문을 이용할 수 있는 식으로
SQL을 작성하라.
OPTIMIZER는 인덱스가 존재하기 때문에 인덱스를 사용하는
ACESS PATH를 사용할 수 없다.
따라서 ACCESS PATH는 반드시 SQL이 사용할 수 있게 만들어 져야 한다.
SQL HINT를 사용하는 것은 인덱스 사용을 보증해주는 방법중 하나이다.
특정 ACCESS PATH를 선택하기 위한 다음의 힌트를 참고 하라

 

6.가능하면 EXPLAIN과 TKPROF를 사용하라
만약 SQL문이 잘 다듬어지지 않았다면 비록 오라클 데이터베이스가
잘 짜여져 있어도 효율성이 떨어질 것이다.
이럴 경우 EXPLAIN TKPROF에 능숙해져야 한다.
EXPALIN PLAN은 SQL이 사용하는 ACCESS PATH를 발견할 수 있게 해주고 TKPROF는 실제 PERFORMANEC의 통계치를 보여준다.
이 TOOL은 오라클 서버 소프트웨어에 포함되어 있고 SQL의 성능을 향상시켜 준다.

 

7.OPTIMIZER를 이해하라.
SQL은 RULE-BASED나 COST-BASED중 하나를 이용해서 기동된다.
기존의 소프트웨어는 RULE BASED 방식을 채택하고 있다.
그리고 많은 오라클 소프트웨어가 이러한 방식을 오랫동안 사용해 왔다.

그러나 새로 출시된 소프트웨어에 대해서는 COST BASED 방식의 OPTIMIZER를 고려해야 한다.

오라클은 새로 출시되는 프로그램을 COST BASED방식으로 업그레이드 시켜왔으며 이러한 방식은 시스템을 훨씬 더 안정적으로 만들었다.

만약 COST BASED방식의 OPTIMIZER를 사용한다면 반드시 ANALYZE 스키마를 정기적으로 사용해야 한다. ANALYZE스키마는 데이터베이스 통계를 데이터 사전 테이블에 기록하는 역할을 수행하며 그렇게 되면 COST BASED OPTIMIZER가 그것을 사용하게 된다.

SQL은 COST BASED OPTIMIZER를 사용할 때만 잘 조정될 수 있다.
만약 RULE BASED에서 COST BASED로 바꾸고 싶다면 데이터베이스를 사용하는 모든 소프트웨어의 모든 SQL문의 성능을 평가해 보아야 한다.

 

8.지엽적으로 동작하더라도 전역적으로 생각하라
항상 주의할 것은 하나의 SQL문을 조정하기 위해 생긴 데이터베이스안의 변화는 다른 응용프로그램이나 다른 사용자가 이용하는 다른 명령문에 영향을 미친다는 사실이다.

 

9.WHERE절은 매우 중요하다.
비록 인덱스가 가용하다고 해도 다음의 WHERE 절은
그 인덱스 ACCESS PATH 를 사용하지 않는다.
(즉 COL1 과 COL2는 같은 테이블에 있으며 인덱스는 COL1에 만들어진다.)

COL1 > COL2
COL1 < COL2
COL1 > = COL2
COL1 <= COL2
COL1 IS NULL
COL1 IS NOT NULL.

인덱스는 NULL값을 갖는 칼럼에는 ROWID를 저장하지 않는다. 따라서 NULL값을 갖는 ROW를 검색할 때는 인덱스를 사용하지 못한다.

COL1 NOT IN (VALUE1, VALUE2 )
COL1 != EXPRESSION
COL1 LIKE ‘%PATTERN’.

이럴 경우 THE LEADING EDGE OF THE INDEX(?) 는 작동되지 않고 인덱스가 사용되지 못하게 한 다.

한편 COL1 LIKE ‘PATTERN %’이나 COL1 LIKE ‘PATTERN % PATTERN%’ 는 한정된 인덱스 스캔을 수행하기 때문에 인덱스를 사용할 수 있다.

NOT EXISTS SUBQUERY
EXPRESSION1 = EXPRESSION2.

인덱스된 컬럼을 포함하는 표현(EXPRESSION), 함수, 계산(CALCULATIONS)은 인덱스를 사용하지 못한다.

다음의 예에서 보면 UPPER SQL 함수를 사용하면 인덱스 스캔을 사용할 수 없고
FULL TABLE SCAN으로 끝나고 만다.

SELECT DEPT_NAME
FROM DEPARTMENT
WHERE UPPER(DEPT_NAME) LIKE ‘SALES%’;

 

10.레코드 필터링을 위해서는 HAVING보다는 WHERE를 사용하라
인덱스가 걸려있는 칼럼에는 GROUP BY와 같이 HAVING절을 사용하지 마라. 이 경우 인덱스는 사용 되지 않는다. 또한 WHERE절로 된 ROW를 사용하지 마라.

만약 EMP테이블이 DEPTID컬럼에 인덱스를 가지고 있다면
다음 질의는 HAVING 절을 이용하지 못한다.

SELECT DEPTID,
SUM(SALARY)
FROM EMP
GROUP BY DEPTID
HAVING DEPTID = 100;

그러나 같은 질의가 인덱스를 사용하기 위해 다시 씌여질 수 있다.

SELECT DEPTID,
SUM(SALARY)
FROM EMP
WHERE DEPTID = 100
GROUP BY DEPTID;

 

11. WHERE 절에 선행 INDEX 칼럼을 명시하라.
복합 인덱스의 경우, 선행 인덱스가 WHERE절에 명시되어 있다면 쿼리는
그 인덱스 를 사용할 것이다.
다음의 질의는 PART_NUM과 PRODUCT_ID 칼럼에 있는 PRIMARY KEY CONSTRAINT에 기초한 복합 인덱스를 이용할 것이다.

SELECT *
FROM PARTS
WHERE PART_NUM = 100;

반면, 다음의 쿼리는 복합인덱스를 사용하지 않는다.

SELECT *
FROM PARTS
WHERE PRODUCT_ID = 5555;

같은 요청(REQUEST)이 인덱스를 이용하기 위해 다시 씌어 질 수 있다.
다음 질의의 경우,  PART_NUM컬럼은 항상 0 보다 큰 값을 가질것이다.

SELECT *
FROM PARTS
WHERE PART_NUM > 0
AND PRODUCT_ID = 5555;

 

12.인덱스 SCAN과 FULL TABLE SCAN을 평가하라.
한 행(ROW)의 15% 이상을 검색하는 경우에는 FULL TABLE SCAN이 INDEX ACESS PATH보다 빠르다.
이런 경우, SQL이 FULL TABLE SCAN을 이용할 수 있도록 여러분 스스로 SQL을 작성하라.

다음의 명령문은 비록 인덱스가 SALARY COLUMN에 만들어져 있어도 인덱스 SCAN을 사용하지 않을 것이다.

첫 번째 SQL에서, FULL HINT를 사용한다면 오라클은 FULL TABLE SCAN을 수행할 것이다.

인덱스의 사용이 나쁜 점이 더 많다면 아래의 기술을 이용해서
인덱스 수행을 막을수 있다.

SELECT * –+FULL
FROM EMP
WHERE SALARY = 50000;

SELECT *
FROM EMP
WHERE SALARY+0 = 50000;

다음의 명령문은 비록 인덱스가 SS# COLUMN에 있어도
인덱스 SCAN을 사용하지 않을 것이다.

SELECT *
FROM EMP
WHERE SS# || ‘ ‘ = ‘111-22-333′;

오라클이 불분명한 데이터 변환을 수행해야 하는 경우 인덱스가 항상 사용되지않는 것은 아니다.

다음의 예를 보면, EMP 칼럼에 있는 SALARY는 숫자형 칼럼이고 문자형이 숫자값으로 변환된다.

SELECT *
FROM EMP
WHERE SALARY = ‘50000′;

테이블의 행이 15%이거나 그보다 작을 경우 인덱스 스캔은 보다 잘 수행 될 것이다. 왜냐 하면 인덱스 스캔은 검색된 행(ROW)하나 하나 마다 다중의 논리적인 읽기 검색(READ)을 할 것이기 때문이다.

그러나 FULL TABLE SCAN은 하나의 논리적 인 읽기 검색 영역 안의 BLOCK에 있는 모든 행들을 읽을 수 있다. 그래서 테이블의 많은 행들에 접근해야 하는 경우에는 FULL TABLE SCAN이 낫다.

예로 다음의 경우를 보자. 만약 EMP TABLE과 그 테이블의 모든 인덱스에 대해 ANALYZE라는 명령어 가 수행된다면, 오라클은 데이터 사전인 USER_TABLES와 USER_INDEXES에 다음과 같은 통계치를 산출해 낸다.

TABLE STATISTICS:
NUM_ROWS = 1000
BLOCKS = 100

INDEX STATISTICS:

BLEVEL = 2
AVG_LEAF_BLOCKS_PER_KEY = 1
AVG_DATA_BLOCKS_PER_KEY = 1

이러한 통계치에 근거해서, 아래에 보이는 것이 각각의 다른 SCAN에 대한 논리적인 읽기(READ)-즉 ACESS된 BLOCK이 될 것이다.

USE OF INDEX TO RETURN ONE ROW = 3

(BLEVEL+(AVG_LEAF_BLOCKS_PER_KEY - 1) +
AVG_DATA_PER_KEY

FULL TABLE SCAN = 100
(BLOCKS)

USE OF INDEX TO RETURN ALL ROWS = 3000
(NUM_ROWS * BLOCKS ACCESSED TO RETURN ONE ROW USING INDEX)

 

13. 인덱스 스캔에 ORDER BY를 사용하라
오라클의 OPTIMIZER는 , 만약 ORDER BY라는 절이 인덱스된 칼럼에 있다면 인덱스 스캔을 사용할 것이다. 아래의 질의는 이러한 점을 보여 주는 것인데 이 질의는 비록 그 칼럼이 WHERE 절에 명시 되어 있지 않다고 해도 EMPID컬럼에 있는 가용한 인덱스를 사용할 것이다. 이 질의는 인덱스로부 터 각각의 ROWID를 검색하고 그 ROWID를 사용하는 테이블에 접근한다.

SELECT SALARY
FROM EMP
ORDER BY EMPID;

만약 이 질의가 제대로 작동하지 않는다면, 당신은 위에서 명시되었던 FULL HINT를 사용하는 같은 질의를 다시 작성함으로써 다른 대안들을 이용해 볼 수 있다.

 

14. 자신의 데이터를 알아라
내가 이미 설명한 것처럼, 당신은 당신의 데이터를 상세하게 알고 있어야 한다.예를 들어 당신이 BOXER라는 테이블을 가지고 있고 그 테이블이 유일하지 않은 인덱스를 가진 SEX라는 컬럼과 BOXER_NAME이라는 두 개의 테이블을 가지고 있다고 가정해 보자. 만약 그 테이블에 같은 수의 남자, 여자 복서가 있다면 오라클이 FULL TABLE SCAN을 수행하는 경우 다음의 질의가 훨씬 빠를 것이다.

SELECT BOXER_NAME
FROM BOXER
WHERE SEX = ‘F’;

당신은 다음과 같이 기술함으로써 질의가 FULL TABLE SCAN을 수행하는지를 확실하게 해둘수 있다.

SELECT BOXER_NAME –+ FULL
FROM BOXER
WHERE SEX = ‘F’;

만약 테이블에 980 명의 남성 복서 데이터가 있다면, 질의는 인덱스 SCAN으로 끝나기 때문에 아래형식의 질의가 더 빠를 것이다.

SELECT BOXER_NAME –+ INDEX (BOXER BOXER_SEX)
FROM BOXER
WHERE SEX = ‘F’;

이 예는 데이터의 분포에 대해 잘 알고 있는 것이 얼마나 중요한 가를 예시해 준다. 데이터가 많아지고(GROW) 데이터 분포가 변화하는 것처럼 SQL 도 매우 다양할 것이다. 오라클은 OPTIMIZER 가 테이블에 있는 데이터의 분포를 잘 인식하고 적절한 실행 계획을 선택하도록 하기 위해 오라클 7.3 에 HISTOGRAMS라는 기능을 추가했다.

 

15. KNOW WHEN TO USE LARGE-TABLE SCANS.
작거나 큰 테이블에서 행들을 추출할 때, 전체 테이블의 검색은 인텍스를 사용한 검색보다 성능이 더 좋을 수도 있다.

매우 큰 테이블의 인덱스 검색은 수많은 인덱스와 테이블 블록의 검색이 필요할 수도 있다. 이러한 블록들이 데이터베이 스 버퍼 캐쉬에 이동되면 가능한한 오래도록 그곳에 머무른다.

그래서 이러한 블록들이 다른 질의등에 필요하지 않을 수도 있기 때문에, 데이터베이스 버퍼 히트 비율이 감소하며 다중 사용자 시스템의 성능도 저하되기도 한다.

그러나 전체 테이블 검색에 의해서 읽혀진 블록들은 데이터베이스 버퍼 캐쉬에서 일찍 제거가 되므로 데이터베이스 버퍼 캐쉬 히트 비율은 영향을 받지 않게 된다.

 

16. MINIMIZE TABLE PASSES.
보통, SQL질의시 참조하는 테이블의 숫자를 줄임으로 성능을 향상시킨다. 참조되는 테이블의 숫자가 적을수록 질의는 빨라진다.

예를 들면 NAME, STATUS, PARENT_INCOME, SELF_INCOME의 네개의 컬럼으로 이루어진 학생 테이블 에서 부모님에 의존하는 학생과 독립한 학생의 이름과 수입에 대해서 질의시, 이 학생 테이블을 두번 참조하여 질의하게 된다..

SELECT NAME, PARENT_INCOME
FROM STUDENT
WHERE STATUS = 1
UNION
SELECT NAME, SELF_INCOME
FROM STUDENT
WHERE STATUS = 0;
( NAME이 프라이머리 키이며, STATUS는 독립한 학생의 경우는 1, 부모님에 의존적인 학생은 0으로 표시한다)
위의 같은 결과를 테이블을 두번 참조하지 않고도 질의 할 수 있다.

SELECT NAME,PARENT_INCOME*STATUS + SELF_INCOME(1-STATUS)
FROM STUDENT;

 

17. JOIN TABLES IN THE PROPER ORDER.
다수의 테이블 조인시 테이블들의 조인되는 순서는 매우 중요하다. 전반적으로, 올바른 순서로 테이블이 조인되었다면 적은 수의 행들이 질의시 참조된다.

언제나 다수의 조인된 테이블들을 질의시 우선 엄격하게 조사하여 행들의 숫자를 최대한으로 줄인다. 이러한 방법으로 옵티마이저는 조인의 차후 단계에서 적은 행들을 조사하게 된다.

뿐만 아니라, 여러 조인을 포함하는 LOOP JOIN에서는 가장 먼저 참조되는 테이블(DRIVING TABLE)이 행들을 최소한으로 리턴하도록 해야한다.

그리고, 마스터와 상세 테이블 조인시에는(예를 들면 ORDER & ORDER LINE ITEM TABLES) 마스터 테이블을 먼저 연결 시켜야 한다.

규칙에 근거한 옵티마이저의 경우에는 FROM CLAUSE의 마지막 테이블
이 NESTED LOOP JOIN의 DRIVING TABLE이 된다. NESTED LOOP JOIN이 필요한 경우에는 LOOP의 안쪽의 테이블에는 인텍스를 이용하는 것을 고려할 만하다.

EXPLAIN PLAN과 TKPROF는 조인 타입, 조인 테이블 순서, 조인의 단계별 처리된 행들의 숫자들을 나타낸다.

비용에 근거한 옵티마이저의 경우에는 WHERE CLAUSE에 보여지는 테이블의 순서는 옵티마이저가 가장 최적의 실행 계획을 찾으려고 하는 것과 상관 없다.

조인되는 테이블의 순서를 통제하기 위해서 ORDERED HINT를 사용하는 것이 낫다.

SELECT ORDERS.CUSTID, ORDERS.ORDERNO,
ORDER_LINE_ITEMS.PRODUCTNO –+ORDERED
FROM ORDERS, ORDER_LINE_ITEMS
WHERE ORDERS.ORDERNO = ORDER_LINE_ITEMS.ORDERNO;

 

18. USE INDEX-ONLY SEARCHES WHEN POSSIBLE.
가능하다면, 인덱스만을 이용하여 질의를 사용하라.
옵티마이저는 오직 인덱스만을 찾을 것이다.
옵티마이저는 SQL을 만족시키는 모든 정보를 인덱스에서 찾을수 있을때, 인덱스만을 이용할 것이다.

예를들면, EMP테이블이 LANME과 FNAME의 열에 복합 인덱스를 가지고 있다면 다음의 질의는 인덱스만은 이용할 것이다.

SELECT FNAME
FROM EMP
WHERE LNAME = ‘SMITH’;

반면에 다음의 질의는 인덱스와 테이블을 모두 참조한다.

SELECT FNAME , SALARY
FROM EMP
WHERE LNAME = ‘SMITH’;

 

19. REDUNDANCY IS GOOD.
WHERE CLAUSE에 가능한한 많은 정보를 제공하라.
예를 들면 WHERE COL1 = COL2 AND COL1 = 10 이라면 옵티마이저는 COL2=10이라고 추론하지만, WHERE COL1 = COL2 AND COL2 = COL3이면 COL1=COL3이라고 초론하지는 않는다.

 

20. KEEP IT SIMPLE, STUPID.
가능하면 SQL문을 간단하게 만들라.
매우 복잡한 SQL문은 옵티마이저를 무력화시킬 수도 있다.
때로는 다수의 간단한 SQL문이 단일의 복잡한 SQL문보다 성능이 좋을 수도 있다.

오라클의 비용에 근거한 옵티마이저는 아직은 완벽하지않다.
그래서 EXPLAIN PLAN에 주의를 기울여야 한다.
여기서 비용이란 상대적인 개념이기에 정확히 그것이 무엇을 의미하는지 알지
목한다.

하지만 분명한 것은 적은 비용이 보다 좋은 성능을 의미한다는 것이다.
종종 임시 테이블을 사용하여 많은 테이블들을 포함하는 복잡한 SQL 조인을 쪼개는 것이 효율적일 수도 있다.

예를 들면, 조인이 대량의 데이터가 있는 8개의 테이블을 포함할 때, 복잡한 SQL을 두세개의 SQL로 쪼개는 것이 낫을 수 있다.

각각의 질의는 많아야 네개정도의 테이블들을 포함하며 중간 값을 저장 하는 것이 낫을 수 있다.

 

21. YOU CAN REACH THE SAME DESTINATION IN DIFFERENT WAYS.
많은 경우에, 하나 이상의 SQL문은 의도한 같은 결과를 줄 수 있다. 각각의 SQL은 다른 접근 경로 를 사용하며 다르게 수행한다.

예를들면,
MINUS(-) 산술자는 WHERE NOT IN (SELECT ) OR
ERE NOT EXISTS 보다 더 빠르다.

예를들면,
STATE와 AREA_CODE에 각각 다른 인덱스가 걸려 있다.
인덱스에도 불구하고 다음의 질의는 NOT IN의 사용으로 인해 테이블 전체를 조사하게된다.

SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE STATE IN (’VA’, ‘DC’, ‘MD’)
AND AREA_CODE NOT IN (804, 410);

그러나 같은 질의가 다음 처럼 쓰여진다면 인덱스를 사용하게 된다

SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE STATE IN (’VA’, ‘DC’, ‘MD’)
MINUS
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE AREA_CODE IN (804, 410);

WHERE절에 OR을 포함한다면 OR대신에 UNION을 사용할 수 있다.
그래서, SQL 질의를 수행하기전에 먼저 실행계획을 조심스럽게 평가해야 한다.
이러한 평가는 EXPLAIN PLAN AND TKPROF를 이용하여 할 수 있다.

 

22. USE THE SPECIAL COLUMNS.
ROWID AND ROWNUM 열을 이용하라. ROWID를 이용하는 것이 가장 빠르다.
예를들면, ROWID를 이용한 UPDATE는 다음과 같다.

SELECT ROWID, SALARY
INTO TEMP_ROWID, TEMP_SALARY
FROM EMPLOYEE;

UPDATE EMPLOYEE
SET SALARY = TEMP_SALARY * 1.5
WHERE ROWID = TEMP_ROWID;

ROWID값은 데이터베이스에서 언제나 같지는 않다. 그래서, SQL이나 응용 프로그램이용시 ROWID값을 절대화 시키지 말라.

리턴되는 행들의 숫자를 제한 시키기위해 ROWNUM을 이용하라.

만약에 리턴되는 행들을 정확히 모른다면 리턴되는 행들의 숫자를 제한하기위해 ROWNUM을 사용하라.

다음의 질의는 100개 이상의 행들을 리턴하지는 않는다.

SELECT EMPLOYE.SS#, DEPARTMENT.DEPT_NAME
FROM EMPLOYEE, DEPENDENT
WHERE EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID
AND ROWNUM < 100;

 

23.함축적인 커서대신 명시적인 커서를 사용하라.
함축적 커서는 여분의 FETCH를 발생시킨다. 명시적 커서는 DECLARE, OPEN, FETCH와 CLOSE CURSOR문을 사용하여 개발자에 의해서 생성된다.
함축 커서는 DELETE, UPDATE, INSERT와 SELECT문을 사용하면 오라클에 의해서 생성된다.

 

24.오라클 병렬 쿼리 옵션을 찾아서 이용하라.
병렬 쿼리 옵션을 사용하면, 보다 빠른 성능으로 SQL을 병렬로 실행할 수 있다.
오라클 7에서는, 오직 FULL TABLE SCAN에 기반한 쿼리만이 병렬로 수행될 수 있다.

오라클 8에서는, 인덱스가 분할되어있다면 INDEXED RANGE SCANS에 기반한 쿼리도 병렬로 처리될 수 있다.

병렬 쿼리 옵션은 다수의 디스크 드라이버를 포함하는 SMP와 MPP SYSTEM에서만 사용될 수 있다.

오라클 서버는 많은 우수한 특성을 가지고 있지만, 이러한 특성의 존재만으로는 빠른 성능을 보장하지 않는다.

이러한 특성을 위해서 데이터베이스를 조정해야하며 특성을 이용하기 위해 특별하게 SQL 을 작성해야 한다.

예를 들면, 다음의 SQL은 병렬로 수행될 수 있다.

SELECT * –+PARALLEL(ORDERS,6)
FROM ORDERS;

 

25.네트웍 소통량을 줄이고 한번에 처리되는 작업량을 늘려라.
ARRAY PROCESSING과 PL/SQL BLOCK을 사용하면
보다 나은 성능을 얻을 수 있고 네트웍 소통량을 줄인다.

ARRAY PROCESSING은 하나의 SQL문으로 많은 ROW를 처리할 수 있게 한다.

예를 들면,
INSERT문에서 배열을 사용하면 테이블내의 1,000 ROW를 삽입할 수 있다.
이러한 기술을 사용하면 주요한 성능 향상을 클라이언트/서버와 배치시스템에서 얻어질 수 있다.

복합 SQL문은 과도한 네트웍 소통을 유발할 수 있다.

그러나 만일 SQL문이 단일 PL/SQL 블록안에 있다면,
전체 블록은 오라클 서버에 보내져서 그곳에서 수행되고, 결과는 클라이언트의
APPLICATION에게 돌아온다.

개발자와 사용자는 종종 SQL을 데이터베이스에서 데이터를 검색하고 전송하는 간단한 방법으로 사용한다.

때때로 직접적으로 SQL을 작성하지 않고 코드 발생기를 사용하여 작성한 APPLICATION은 심각한 성능 문제를 일으킨다.

이러한 성능감퇴는 데이터베이스가 커지면서 증가한다.

SQL은 유연하기 때문에, 다양한 SQL문으로 같은 결과를 얻을 수 있다.

그러나 어떤 문은 다른 것보다 더 효율적이다.

여기에 기술된 팁과 기법을 사용하면 빠르게 사용자에게 정보를 제공할 수 있는APPLICATION과 리포트를 얻을 수 있다.

 

• MySQL AB :: Stored Procedures in MySQL
• 데이터베이스 사랑넷 검색

 

“프로시져는 함수보다 작은 기능을 가지고 있습니다.
함수와 거의 같지만 리턴 값만 없다고 보시면 됩니다….

 

“procedure 란 절차란 뜻입니다. 일종의 함수라고 봐도 무방합니다.
db 에서 일괄(batch)작업하는데 아주 훌륭하게 사용됩니다….

 

“function 은 리턴값을 하나 받을 수있고, procedure 는 리턴값을 안받을 수도 여러개 받을 수도 있음…

*where : http://www.phpschool.com/gnuboard4/bbs/board.php…

1. 테이블 생성
CREATE TABLE `TranTest` (
`num` int(11) NOT NULL auto_increment,
`col01` varchar(32) default NULL,
PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `TranTest2` (
`num` int(11) NOT NULL auto_increment,
`col01` varchar(32) default NULL,
PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 입력 용 프로시저 생성
CREATE PROCEDURE `Prc_TranTest_Input`
(
in in_col01 varchar(32)
)
BEGIN
INSERT INTO TranTest SET col01 = in_col01;
END;

3. SELECT 용 프로시저 생성
CREATE PROCEDURE `Prc_TranTest_Select`
(
in in_col01 varchar(32)
)
BEGIN
SELECT num, col01 FROM TranTest WHERE col01 like concat(in_col01, ‘%’) ;
END;

4. Transaction 용 프로시저 생성
CREATE PROCEDURE `Prc_TranTest_InError`
(
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ”
BEGIN
DECLARE dbErr int default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET dbErr = -1;
START TRANSACTION;
INSERT INTO TranTest SET col01 = ‘1234′;
INSERT INTO TranTest2 SET col012 = ‘1234′; //일부러 에러값을 내기위해 칼럼명을 다르게 적습니다.
IF dbErr < 0 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END;

5. PHP 를 이용하여 SELECT 및 입력하기
$mysqli = new mysqli(”localhost”, “USERID”, “USERPW”, “USERDB”);
if (mysqli_connect_error()) {
printf(”Connect Failed : %s\n”, mysqli_connect_error());
exit;
}
$mysqli->query(”set names utf8″);

//SELECT
if($qry = $mysqli->query(”Call Prc_TranTest_Select(’123′)”)) {
$rs = $qry->fetch_object();
echo $rs->col01;
}

//INSERT TranTest;
$mysqli->query(”Call Prc_TranTest_InError()”);