오라클에서 데이터 억세스 모듈을만들때, 바인딩 변수를 사용하면, DB에 부하를 덜수있는 잇점이 있죠. 머 제가 DBA도 아니므로 내부적인 매커니즘은 잘 모르더라도, 가끔 오라클사에서 와서, 시스템 튜닝을 할때 개발자 교육을 하는데 거기서 항상 강조하는게, 리터럴 변수를 쓰지 말고 바인딩 변수를 쓰라고 그럽니다... 리터럴과 바인딩에대해 간단히 설명하자면, 리터럴은 select * from table where id='설사댄스' 처럼 값을 직접 쿼리에 적용시켜 대입시키는 방법입니다. 바인딩은 리터럴과는 좀 틀리게 작동합니다. 바인딩은 select * from table where id=:input_id 처럼 작성되어지며, 프롬프트가 :input_id 를 입력을 기다립니다. 일반적으로 쿼리가 실행되면, 파싱 및 컴파일이 이루어진 후 실행이되어집니다. dbms는 이전에 사용되어진 쿼리에대한 정보를 가지고 있는데, 이 정보에 기록된 쿼리와 동일한 쿼리를 사용하게되면, 컴파일단계를 생략합니다. 바인딩을 이용하면, 바인딩변수에 해당하는 부분에 대입되는 값만 틀리지, 한번 컴파일되면, 언제든지 재사용되어집니다. 하지만 리터럴의 경우는 쿼리문 자체를 틀리게 인식하므로, 리터럴변수 위치에 있는 값이 틀리면 매번 컴파일이 이루어지므로, 발생 빈도수가높은(사용율이 높은 쿼리) 는 바인딩처리를 해주면 DB의 부하를 상당히 덜어줄수 있습니다.. 예를들어 회원이 1000명인 사이트에 1000명의 유저에대해 로그인을 해주는 쿼리를 생각해볼때, select count(1) from user_tb where user_id = '설사댄스' and passwd = '변비' 처럼 리터럴형 쿼리를 만들어준다면, 1000명이 각각 접속을할때 각각에대해 컴파일이 일어납니다. 서로 다른 쿼리로 인식되어지는것이죠. 하지만, 바인딩을 이용하면 select count(1) from user_tb where user_id = :user_id and passwd = :passwd 처럼 작성되어진다면, 한번만 컴파일이 일어나고, 1000명의 유저가 각각의 로그인에대해서 같은 쿼리를 이용하게되므로 컴파일에 걸리는 부하를 1/1000 로 줄일수 있는 기대효과를 볼수 있습니다.. 바인딩은 어떻게 이용할까요.. 간단합니다. 요새 언어들은 대부분 prepareStatement 라는 api 를 제공합니다. select count(1) from user_tb where user_id = ? and passwd = ? 처럼 ? 기호로 바인딩변수가 위치하는곳을 알려주고, 멤버함수로 해당위치에 값을 셋팅해주면, 알아서 값을 대입해주고 결과값을 돌려줍니다.... 이정도면, 왜 바인딩을 이용해야하는지 공감하시리라 생각되어지구요. 오늘 이 글을 쓰게된 이유는, 동적인쿼리에서 바인딩을 이용하는 방법을 연구해보고저 쓰게되었습니다. select * from table where id = ? ... 우리는 이런 쿼리를 정적쿼리라고 합니다... 파라미터의 갯수가 정해진 것이죠. select * from table where id in (select .... from table_b) 이처럼 가변적인 형태의 파라미터를 받아들이는 형태를 동적쿼리라고 합니다. 동적쿼리의 장점은 한번의 커넥션으로 다중적인 결과를 도출할수 있습니다. 예를들어 회원테이블에서 유저의 정보를 가져올려구할때 우리는 select * from member_tb where user_id = ? 처럼 쿼리를 던질수 있습니다. 대부분의 경우라면 위의 쿼리는 unique 속성을 갖는 user_id 에대해 정보를 얻을수 있습니다. 그런데, 여러명의 데이터도 불러올수 있죠...이럴때 쓰는 게 in 키워드입니다. select * from member_tb where user_id in ('날나리개발자', '설사댄스') 이처럼 쿼리를 던지면, '날나리개발자' 와 '설사댄스' 라는 아이디를 갖는 유저의 정보를 가져올것입니다... 그런데 문제가 있습니다.....저런경우는 프로그램적으로 어떻게 바인딩할것인지 ......... 리터럴을 쓰면 오히려 편합니다. select * from member_tb where user_id in ($user_list) 처럼 쿼리를 만든다음에 $user_list = "'날나리개발자', '설사댄스'"; 처럼 처리해주면 간단하죠. ..................................................................... 그런데 이 글의 주제가 바인딩이므로 저렇게 쓰면 매우 슬퍼지죠 ㅠ,.-;;; === 바인딩기법1(저렙용) === 어떤 언어든 for , while 등의 루프를 제공합니다. parameter 로 $user_list[] = {"날나리개발자", "설사댄스"}; 이렇게 값을 가져온다면 $qry = "select * from user_tb "; $qry.= "where user_id in ("; for($i=0; $i<$user_list.length; $i++) { //$user_list.length ??? 맞는문법인지 원..php 한지 오래되서... // 어쨌든 주제는 그게 아니니 걸러들으시길 -o-;;;; if($i>0) $qry.=" , "; $qry .= "?"; } $qry.=" ) "; for($i=0; $i<$user_list.length; $i++) { // 여기에 바인딩변수를 setting 하는 메서드 호출; } 대충 이런 로직을 생각해낼수 있겠죠......머 좀 복잡하긴하지만, 일단 바인딩이라는 주제아래 충분히 가치있는 로직을 만들어냈습니다. 그런데 문제가 있습니다... 배열의 갯수가 틀리면, 바인딩을 쓴 효과가 줄어든다는 것이죠... 그렇다면,,이를 해결할 해결책은 무엇일까요? 그건 바로 배열형태로 파라미터를 가져오지말고, 구분자를 갖는 문자열로 가져오는 겁니다... $user_list = "설사댄스,날나리개발자"; 처럼요.. 그럼 이걸 바인딩하는 기술을 알아보도록 하겠습니다..실은 오늘 주제가 이겁니다. ^^;;; 그 전에 먼저 준비운동을 하겠습니다... 문자열을 구분자 (예:콤마",")를 기준으로 row 로 나누는 방법을 연구해봅시다. 먼저 pivot table을 만들어봅시다. CREATE TABLE PIVOT ( NO NUMBER NOT NULL, NAME VARCHAR2(256 BYTE) ); CREATE UNIQUE INDEX PKX_M_PIVOT ON PIVOT (NO); insert into pivot select rownum, to_char(rownum) from dual connect by level <= 1000; commit; 이렇게해서 no 컬럼의 값이 1 부터 1000 까지의 값을 갖는 pivot 테이블을 만들었습니다. 이렇게 pivot 을 만들면 상당히 유용하게 쓸수있습니다. select "설사댄스,날나리개발자" as col1 from dual; result>> ----- col1 ----- 설사댄스,날나리개발자 ----- , 를 기준으로 row 로 나눈다면 2개의 로우가 필요하겠죠. 몇개의 row가 필요한지 알아내는 방법은 select length(a.col1)-length(replace(a.col1,',',''))+1 as len from (select "설사댄스,날나리개발자" as col1 from dual) a; 처럼하면 2 가 나옵니다. 그럼 다음단계로 가서 피봇과 크로스조인을 해봅시다. select a.len, a.col1, b.no from ( select length(a.col1)-length(replace(a.col1,',',''))+1 as len, a.col1 from (select '설사댄스,날나리개발자' as col1 from dual) a ) a cross join pivot b where b.no <>> --------+----------------------+----------+ len col1 no --------+----------------------+----------+ 2 설사댄스,날나리개발자 1 2 설사댄스,날나리개발자 2 ------------------------------------------- 데이터 복제에 성공햇습니다.... 만약 "설사댄스,날나리개발자,행복한고니" 를 입력하면, result>> --------+----------------------+----------+ len col1 no --------+----------------------+----------+ 3 설사댄스,날나리개발자,행복한고니 1 3 설사댄스,날나리개발자,행복한고니 2 3 설사댄스,날나리개발자,행복한고니 3 ------------------------------------------- 처럼 결과가 나오겠죠. 자 이제 저걸 --------- 1 설사댄스 2 날나리개발자 3 행복한고니 ---------- 의 형태로 만들려면 어떻게 해야할까요... 네.. instr() 과 substr() 을 이용하면됩니다... select trim( SUBSTR ( ','col1',' , INSTR(','col1',',',',1, NO)+1 , (INSTR(','col1',',',', 1,NO+1)-INSTR(','col1',',',', 1,NO))-1 ) ) as col2 from ( select a.len, a.col1, b.no from ( select length(a.col1)-length(replace(a.col1,',',''))+1 as len , a.col1 from (select '설사댄스,날나리개발자,행복한고니' as col1 from dual) a ) a cross join pivot b where b.no <= a.len ) 결과>> ------+--------------+ no col2 ------+--------------+ 1 설사댄스 2 날나리개발자 3 행복한고니 ------+--------------+ 이쯤되면 눈치채셨죠...이제 이사람의 정보를 어떻게 가져와야할것인지... === 바인딩기법(고렙용) : 구분자를 가진 문자열을 바인딩으로 받아서 파싱하여 동적쿼리 만들기 === select * from member_tb where user_id in ( select trim( SUBSTR ( ','col1',' , INSTR(','col1',',',',1, NO)+1 , (INSTR(','col1',',',', 1,NO+1)-INSTR(','col1',',',', 1,NO))-1 ) ) as col2 from ( select a.len, a.col1, b.no from ( select length(a.col1)-length(replace(a.col1,',',''))+1 as len , a.col1 from (select ? as col1 from dual) a ) a cross join pivot b where b.no <= a.len ) ) 단 하나의 바인딩변수를 이용함으로서 루프를 이용한 바인딩을 셋팅하는 부담을 줄였습니다.... 이걸 이용하면 어떤 장점이 있을까요... update, insert, select 모든 쿼리에 다양하게 이용되어질수 있습니다. 파라미터를 구분자로 연결된 형태로 넘기는건 그리 어려운 작업이 아니니깐요. |
출처 : http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=tipntech&wr_id=46546&sca=&sfl=mb_id%7C%7Csubject&stx=VALENNY&sop=and
