1 / 30

select decode(vsize(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')')))) ,

전화번호 또는 FAX 번호의 값을 12 byte 로 일정하게 나타내고 , 지역번호(4) , 국번(4) , 전화번호뒷자리(4) 자리의 공백은 ‘0’으로 설정되게 하는 SQL 문장입니다. 예) 02-551-1010 --> 000205511010 , 551-1010 --> 000205511010 , (031)432-0101 --> 003104320101 etc. select decode(vsize(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')')))) ,

binta
Télécharger la présentation

select decode(vsize(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')')))) ,

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 전화번호 또는 FAX번호의 값을 12byte로 일정하게 나타내고 , 지역번호(4) , 국번(4) , 전화번호뒷자리(4) 자리의 공백은 ‘0’으로 설정되게 하는 SQL 문장입니다. 예) 02-551-1010 --> 000205511010 , 551-1010 --> 000205511010 , (031)432-0101 --> 003104320101 etc.. select decode(vsize(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')')))) , 7 , '00020'||ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))) , 8 , '0002'||ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))) , 9 , '0002'||'0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),3,7) , 10 , decode(substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,3) , '032' , '0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,3)||'0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),4,7) , '042' , '0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,3)||'0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),4,7) , '051' , '0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,3)||'0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),4,7) , '052' , '0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,3)||'0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),4,7) , '053' , '0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,3)||'0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),4,7) , '062' , '0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,3)||'0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),4,7) , '064' , '0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,3)||'0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),4,7) , '00'||ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))) ) , 11 , decode(substrb(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,4) , '0522' , substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,4)||'0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),5,7) , '0523' , substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,4)||'0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),5,7) , '0527' , substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,4)||'0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),5,7) , decode( substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,3) , '032' , '0'||ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))) , '042' , '0'||ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))) , '051' , '0'||ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))) , '052' , '0'||ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))) , '053' , '0'||ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))) , '062' , '0'||ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))) , '064' , '0'||ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))) , substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),1,4)||'0'||substr(ltrim(rtrim(replace(replace(replace(p_aaa,'-'),'('),')'))),5 , 7))) ) from dual;

  2. 전화번호 또는 FAX번호의 값을 12byte로 일정하게 나타내고 , 지역번호(4) , 국번(4) , 전화번호뒷자리(4) 자리의 공백은 ‘0’으로 설정되게 하는 SQL 문장입니다. 예) 02-551-1010 --> 000205511010 , 551-1010 --> 000205511010 , (031)432-0101 --> 003104320101 etc.. create or replace function fn_phone ( pp_aaa varchar2) return varchar2 is v_return varchar2(20); p_aaa varchar2(12); begin p_aaa := ltrim(rtrim(replace(replace(replace(pp_aaa,'-'),'('),')'))) ; if pp_aaa is null then v_return := ''; else select decode(vsize(p_aaa) , 7 , '00020'||p_aaa , 8 , '0002'||p_aaa , 9 , '0002'||'0'||substr(p_aaa,3,7) , 10 , decode(substr(p_aaa,1,3) , '032' , '0'||substr(p_aaa,1,3)||'0'||substr(p_aaa,4,7) , '042' , '0'||substr(p_aaa,1,3)||'0'||substr(p_aaa,4,7) , '051' , '0'||substr(p_aaa,1,3)||'0'||substr(p_aaa,4,7) , '052' , '0'||substr(p_aaa,1,3)||'0'||substr(p_aaa,4,7) , '053' , '0'||substr(p_aaa,1,3)||'0'||substr(p_aaa,4,7) , '062' , '0'||substr(p_aaa,1,3)||'0'||substr(p_aaa,4,7) , '064' , '0'||substr(p_aaa,1,3)||'0'||substr(p_aaa,4,7) , '00'||p_aaa ) , 11 , decode(substrb(p_aaa,1,4) , '0522' , substr(p_aaa,1,4)||'0'||substr(p_aaa,5,7) , '0523' , substr(p_aaa,1,4)||'0'||substr(p_aaa,5,7) , '0527' , substr(p_aaa,1,4)||'0'||substr(p_aaa,5,7) , decode( substr(p_aaa,1,3) , '032' , '0'||p_aaa , '042' , '0'||p_aaa , '051' , '0'||p_aaa , '052' , '0'||p_aaa , '053' , '0'||p_aaa , '062' , '0'||p_aaa , '064' , '0'||p_aaa , substr(p_aaa,1,4)||'0'||substr(p_aaa,5 , 7))) ) into v_return from dual; end if; exception when others then return ''; end;

  3. Oracle SQL*PLUS의 사용이나 Server Program을 개발시에 도움이 되는 것들.. 1. Dbms_output.put_line : Server Program 개발시 원하는 data가 출력되는지를 확인할수 있는 debug 용 package . 주의 : dbms_output.put_line의 package를 사용할때에는 다음과 같은 환경을 설정하여야만 한다. Set serveroutput on 2. Raise_application_error : client / server 상에서 Server Program을 개발하여 Client에서 Server Program을 호출시 error 발생에 대해서 Client에게 자료를 넘겨주게 하는 package . Raise_application_error( 코드값 , 메시지 ) 로 구성되어있다. 코드값의 범위는 -20001 ~ -20999 의 범위중 임의로 설정이 가능하다 메시지는 해당하는 메시지를 기술하면 된다 . 사용예) exception when no_data_found then raise_application_error(-20001 , ‘데이터가 존재하지 않음’); end; 3. Set autotrace on : Oracle version 7.3이상에서 부터 제공되는 기능으로 일반적으로 explain plan을 하지 않고도 실행하고자 하는 query문장이 parsing되는 경위를 보여준다. . 사용법 : set autotrace on . 주의 : 해당 사용법을 SQL*PLUS 상에서 설정하였을때.. 에러가 발생할수가 있슴. 그때는 다음의 파일을 실행하여 explain_plan table을 생성시켜주어야 한다. oracle_home/rdbms/admin/utlxplan.sql -- Unix orawin95\rdbms80(또는rdbms73)\admin\utlxplan.sql -- Nt 위의 파일을 이용하여 table을 생성시켜 준 다음 다시 set autotrace on의 명령은 수행한다. 수행시 plustrace role에 관한 문제가 발생시에는 다음과 같이 처리한다. Oracle_home/plus80/plustrace.sql 문장을 user가 sys 또는 system에서 실행시킨다. 실행후 다음의 문장을 수행한다. Grant plustrace to user명; 다음의 환경이 설정이 되는 정확히 사용할수가 있다. . 참조 : 위의 autotrace 기능은 해당 query문장에 대한 정확한 parsing을 해주지 못하는 경우도 있다. 그런 경우에는 개발적인 query문장의 tuning이 필요하지만, 일반적으로 query문장이 index를 제대로 참조하고 있는지의 여부를 확인하는 차원에서는 좋은 기능중의 하나임. 4. Program 개발시에 문제가 많이 되는 lock 의 문제를 해결하는 방법 . 일단 lock이 걸렸는지를 확인하는 작업이 필요하다. Lock이 걸렸는지를 확인하는 view는 다음과 같다 select * from v$lock; -- 이문장은 어떤 sid가 lock를 잡고있는지를 확인할수가 있다.

  4. Oracle SQL*PLUS의 사용이나 Server Program을 개발시에 도움이 되는 것들.. Select * from v$locked_object -- 이문장은 어떤 session이 어떤 object에 대해서 lock를 잡고있는지를 확인할수가 있다. 이제 해당 sid 나 session이 잡고있는 lock을 해제하는 방법은 자동으로 lock이 풀리기를 기다리던지 , 아니면 해당 session을 죽여서 lock을 해제할수가 있다. 해당 session을 죽이는 방법은 다음과 같다. Alter system kill session ‘sid,serial#’; 사용예) alter system kill session ‘11,22’; --해당 session이 죽었는지는 status 컬럼값으로 확인한다. -- 어떤 client에서 작업이 이루어지고있는지는 osuser로 확인할수가 있다. . Table에 생성되어 있는 자료를 삭제시에… 일반적으로 몇건만을 삭제시에는 delete의 명령을 사용하지만.. 전체 data가 필요하지 않을때는 다음의 명령문을 사용한다. Truncate table table명 drop storage; ( drop storage의 옵션은 해당 table이 사용한 block공간을 table에 되돌려주는것임 ) . 어떤 table에 대해서 설정되어 있는 index의 종류와 column을 보기위한 script select a.table_name , a.index_type , b.column_name from user_indexes a , user_ind_columns b where a.table_name = b.table_name and a.table_name = ‘table명’ order by b.column_position . Batch 작업을 할 경우에 해당 server program(procedure)이 작업하는 상태를 확인하기 위해서는 v$transaction을 참조한다. USED_UBLK , USED_UREC의 두 컬럼값을 기준으로 얼마나 처리가 이루어 지고있는지를 확인할수가 있다. . 무엇인가 찾고자 하는 dictionary 정보를 보기 위해서는 다음의 query를 이용하여 필요한 view를 참조한다. Select * from v$dictionary; . Oralce 에서 사용할수 있는 index의 종류 . Bitmap index : 중복된 컬럼의 값이 많은 column을 기준으로 index를 생성할때 사용( 구분가능한 값이 5%이내일때 ) . Bitmap index는 bit 연산으로 인덱스를 설정하기 때문에 상당한 속도를 부여한다 . B-tree index : 일반적으로 사용하는 index . Partition global index ( global prefixed index ) : oracle8 version에서 부터 추가로 설정된 부분 . Partition local index ( local prefixed index ) : oracle8 version에서 부터 추가로 설정된 부분 . Partiton index ( local nonprefixed index ) : oracle8 version에서 부터 추가로 설정된 부분

  5. Oracle SQL*PLUS의 사용이나 Server Program을 개발시에 도움이 되는 것들.. . Set pause on ( off ) : SQL*PLUS에서 Query문장을 실행시에 나타나는 자료의 건수가 많은 경우에 일정한 간격단위로 화면상에 나타내기 위해서 사용하는 명령문.. . Optimizer hint의 사용 : DML문장을 사용시에 oracle optimizer에게 적절한 사용 방법을 알려주는것을 말한다. Optimizer에서 사용할수 있는 mode는 두가지가 있다. 첫번째는 rule base 형태 , 두번째는 cost base 형태 일반적으로 oracle을 설치시에는 default로 설정되어 있는 optimizer_mode는 choose로 되어있다. 이것은 oracle에게 알아서 rule base로 처리하던지 , cost base로 처리하라는 형태이다. Rule base에는 1단계에서 15단계까지가 설정이 되어있다. 가장 좋은 performance를 나타내는 1단계는 rowid를 가지고 처리하는 방법이다. 예로) select * from emp where rowid = ‘rowid값’; 반면에 cost base로 사용되는 경우에는 해당 table이나 index에 관한 정보를 optimizer가 참조할수 있도록 하기위해서 analyze를 해주어야 한다. 그래서 프로그램을 개발하는 경우에 일정한 간격으로 analyze를 실행하기 위해서 dbms_job이라는 package를 이용하여 사용자들이 사용하지 않는 시간에 존재하는 table에 관해서 분석을 해주도록 설계들을 한다. Cost base로 최적의 작동을 하게하기 위해서는 일반적으로 optimizer_mode를 first_rows로 변경하여 사용한다. Oracle이 startup 된후에 설정하는 방법 : Alter session set optimizer_mode = ‘first_rows’; 초기에 oracle의 init.ora 파일에서 설정하는 방법도 있다. Optimizer_mode = first_rows optimizer_hint를 이용하여 dml문장을 사용시에 하는 방법은 다음과 같다. 예) select /*+ first_rows */ empid from emp ;

  6. Oracle SQL*PLUS의 사용이나 Server Program을 개발시에 도움이 되는 것들.. . Trigger의 사용 표준 syntax create or replace trigger tri_이름 before( after ) insert or update or delete on 테이블명 for each row when ( 조건을 기술할때 ) declare 변수를 선언 및 exception 선언 insert_error exception; update_error exception; delete_error exception; begin if inserting then insert into …. If sql%notfound then raise insert_error; end if; elsif updating then update ... If sql%notfound then raise update_error; end if; elsif deleting then delete … if sql%notfound then raise delete_error; end if; end if; exception when insert_error then raise_application_error(-20001 , ‘insert_error’); when update_error then raise_application_error(-20002 , ‘update_error’); when delete_error then raise_application_error(-20003 , ‘delete_error’); end; ( 주의 ) When 절의 사용시에 주의 사항 : when절에 값을 기술할때는 ‘:’의 표현을 사용할수가 없다. 사용 예) when( old.aaa = ‘111’ or new.aaa = ‘111’ )

  7. Oracle SQL*PLUS에서 SERVER PROCEDURE를 호출하여 SAMFILE을 만드는 방법 . Oracle Database에서 samfile을 만들게 하는 방법 << 환경설정 >> 1. parameter file에 다음의 내용을 첨부합니다. utl_file_dir = path 2. user sys로 connect 하여 다음을 실행합니다. oracle7 --> oracle_home\rdbms73\admin\utlfile.sql을 실행 oracle8 --> oracle_home\rdbms80\admin\utlfile.sql을 실행 이것은 utl_file package의 header부 입니다. 3. body를 생성하여야 합니다. oracle7 --> oracle_home\rdbms73\admin\prvtfile.plb을 실행 oracle8 --> oracle_home\rdbms80\admin\prvtfile.plb을 실행 4. 이제 사용하고자 하는 user로 connect를 하여 procedure를 작성하여 실행하면 됩니다. create or replace procedure pro_smlocareatrd(file_name in varchar2 ) as IN_FILE UTL_FILE.FILE_TYPE; LINEBUF VARCHAR2(1000); cursor cur_c1 is select * from smlocareatrd order by rowid; begin IN_FILE := UTL_FILE.FOPEN('/db/utl_file',file_name,'w'); for a in cur_c1 loop LINEBUF := rpad(nvl(a.dong_cd,' '),6)||rpad(nvl(a.snd_area_nm,' '),20)|| rpad(nvl(a.dong_name,' '),20)||rpad(nvl(a.dong_addr,' '),100); UTL_FILE.PUT_LINE(IN_FILE,LINEBUF); end loop; UTL_FILE.FCLOSE(IN_FILE); -- 뒷장 이어서...

  8. Oracle SQL*PLUS에서 SERVER PROCEDURE를 호출하여 SAMFILE을 만드는 방법 exception when no_data_found then dbms_output.put_line('no_data_found'); utl_file.fclose_all; when utl_file.invalid_operation then dbms_output.put_line('aaa'); utl_file.fclose_all; when utl_file.invalid_path then dbms_output.put_line('bbb'); utl_file.fclose_all; when utl_file.invalid_mode then dbms_output.put_line('ccc'); utl_file.fclose_all; when others then dbms_output.put_line('others error'); utl_file.fclose_all; end; -- samfile을 oracle database에 생성하는 방법의 사용 예 create or replace procedure pro_conv(tname in varchar2 , file_name in varchar2 ) as ojs utl_file.file_type ; emp_buffer varchar2(2300); imsi_buffer varchar2(2300); imsi_sql varchar2(2000); imsi_txt varchar2(1000); pri_num number := 0; open_cur integer; last_cnt number ; exec_cnt integer; error_code varchar2(30); error_msg varchar2(200); col_desc varchar2(2300); cnt number := 0; -- 다음장에 이어서

  9. Oracle SQL*PLUS에서 SERVER PROCEDURE를 호출하여 SAMFILE을 만드는 방법 cursor cur_c1 is select a.new_tab_name , a.old_tab_name , b.seq , b.col_name , b.new_fr_leng , b.new_to_leng , b.old_fr_leng , b.old_to_leng from utl_detail b , utl_master a where a.new_tab_name = b.new_tab_name and a.old_tab_name = b.old_tab_name and a.new_tab_name = tname and b.old_fr_leng is not null order by b.seq; cursor cur_a1 is select distinct a.new_tab_name from utl_master a where a.new_tab_name = tname; begin select max(seq) into last_cnt from utl_detail where new_tab_name = tname and old_fr_leng is not null; for c in cur_c1 loop if c.seq = last_cnt then col_desc := col_desc||c.col_name; else col_desc := col_desc||c.col_name||','; end if; end loop; imsi_sql := 'insert into '||tname||'('||col_desc||')'||' values( '; ojs := utl_file.fopen('/db/utl_file' , file_name , 'r');

  10. Oracle SQL*PLUS에서 SERVER PROCEDURE를 호출하여 SAMFILE을 만드는 방법 for a in cur_c1 loop if last_cnt = a.seq then imsi_sql := imsi_sql||'rtrim(substrb(:emp_buffer ,'||a.old_fr_leng||','||a.old_to_leng||')))'; else imsi_sql := imsi_sql||'rtrim(substrb(:emp_buffer ,'||a.old_fr_leng||','||a.old_to_leng||')),'; end if; end loop; open_cur := dbms_sql.open_cursor; loop cnt := cnt + 1; begin utl_file.get_line(ojs,imsi_buffer); exception when no_data_found then exit; end; begin dbms_sql.parse(open_cur , imsi_sql , dbms_sql.v7); exception when others then error_code := sqlcode; error_msg := sqlerrm; insert into utl_error values( tname , cnt , error_msg , error_code); end; dbms_sql.bind_variable(open_cur , 'emp_buffer' , imsi_buffer); begin exec_cnt := dbms_sql.execute(open_cur); exception when others then error_code := sqlcode; error_msg := sqlerrm; insert into utl_error values( tname , cnt , error_msg , error_code ); end; end loop;

  11. Oracle SQL*PLUS에서 SERVER PROCEDURE를 호출하여 SAMFILE을 만드는 방법 dbms_sql.close_cursor(open_cur); utl_file.fclose_all; update utl_master set ins_upd_date = to_char(sysdate,'YYYYMMDD hh24:mi:ss') , confirm_yn = 'Y' where new_tab_name = tname; commit; exception when no_data_found then utl_file.fclose_all; when utl_file.invalid_operation then utl_file.fclose_all; when utl_file.invalid_path then utl_file.fclose_all; when utl_file.invalid_mode then utl_file.fclose_all; when others then utl_file.fclose_all; end;

  12. SCHEMA 에 대한 VERSION 관리를 할수있는 PROCEDURE << VERSION 관리 PROCEDURE >> -- 환경 설정 -- create table no_map_tab ( OWNER VARCHAR2(30) , TABLE_NAME VARCHAR2(30) , COLUMN_NAME VARCHAR2(30) , DATA_TYPE VARCHAR2(30) , DATA_LENGTH NUMBER , MAP_DATE VARCHAR2(20) , STATUS VARCHAR2(100) ); * 위의 TABLE은 Procedure를 돌리고 난뒤에 변경된 내용들을 저장하는 table 임. /*********************************************************************************************/ /* TABLE LAYOUT의 VERSION 관리 PROCEDURE */ /* OLD VERSION USER : SDMS1 , NEW VERSION USER : SDMS2 */ /* 변경된 TABLE 구조를 저장하는 TABLE : NO_MAP_TAB( USER : SDMS1 ) */ /* 사용 방법 : sid가 sdms의 것을 export */ /* sid가 helpdesk이고 user가 sdms2에 import */ /* connect sdms1/sdms1@helpdesk 로 연결 */ /* 기존에 존재하는 no_map_tab의 내용을 삭제 */ /* ( truncate table no_map_tab drop storage ) */ /* exec pro_mapping; 의 문구를 실행 */ /* no_map_tab의 내용을 확인 */ /*********************************************************************************************/ * 위의 comment는 환경설정에 필요한 것들임 create or replace procedure pro_mapping as cursor cur_a1 is select a.owner , a.table_name , a.column_name , -- USER : SDMS1 a.data_type , a.data_length from all_tab_columns a where OWNER = 'SDMS2' ; cursor cur_c1 is select a.table_name , a.column_name , -- USER : SDMS a.data_type , a.data_length from user_tab_columns a; v_table_name varchar2(30);

  13. SCHEMA 에 대한 VERSION 관리를 할수있는 PROCEDURE begin for a in cur_a1 loop begin select table_name into v_table_name from user_tab_columns where table_name = a.table_name and column_name = a.column_name and data_type = a.data_type and data_length = a.data_length; exception when no_data_found then begin select table_name into v_table_name from user_tab_columns where table_name = a.table_name and column_name = a.column_name; begin select table_name into v_table_name from user_tab_columns where table_name = a.table_name and column_name = a.column_name and data_type = a.data_type; exception when no_data_found then insert into no_map_tab values( a.owner , a.table_name , a.column_name , a.data_type , a.data_length , to_char(sysdate , 'YYYY/MM/DD hh24:mi:ss') , 'NEW VERSIN 에서 TYPE 변경'); end;

  14. SCHEMA 에 대한 VERSION 관리를 할수있는 PROCEDURE begin select table_name into v_table_name from user_tab_columns where table_name = a.table_name and column_name = a.column_name and data_length = a.data_length; exception when no_data_found then insert into no_map_tab values( a.owner , a.table_name , a.column_name , a.data_type , a.data_length , to_char(sysdate , 'YYYY/MM/DD hh24:mi:ss') , 'NEW VERSION 에서 LENGTH 변경'); end; exception when no_data_found then insert into no_map_tab values( a.owner , a.table_name , a.column_name , a.data_type , a.data_length , to_char(sysdate , 'YYYY/MM/DD hh24:mi:ss') , 'NEW VERSION 신규로 생성'); end; end; end loop; for c in cur_c1 loop begin select table_name into v_table_name from all_tab_columns where owner = 'SDMS2' and table_name = c.table_name and column_name = c.column_name; exception when no_data_found then insert into no_map_tab values( 'SDMS' , c.table_name , c.column_name , c.data_type , c.data_length , to_char(sysdate , 'YYYY/MM/DD hh24:mi:ss') , 'NEW VERSION에서는 항목이 삭제'); end; end loop; exception when others then dbms_output.put_line(sqlcode||sqlerrm); end;

  15. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 1. 부분범위 처리( sort를 대신하는 index ) 경우 1 ) -- 설정되어있는 index column : ymd select * from product where ymd = ‘951023’ and item like ‘AB%’ order by ymd , item 경우 2) -- 설정되어있는 index column : ymd , item select * from product where ymd = ‘951023’ and item like ‘AB%’; 설명 : 경우2에 대해서 설정되어있는 index로 column에 대한 sort의 과정을 피할수 있다는 의미 경우 1) select orddate , custno -- 설정되어 있는 index column : orddate from order1t where orddate between ‘940101’ and ‘941130’ order by orddate desc parsing path 단계 21200 sort order by 21200 table access by rowid order1t 21201 index range scan ord_ordate 경우 2) select /*+ index_desc(a orddate) */ orddate , custno -- 설정되어 있는 index column : orddate from order1t a where orddate between ‘940101’ and ‘941130’; parsing path 단계 20 index range scan descending orddate 설명 : 경우2에서는 hint를 이용하였다. 이것은 optimizer에게 data dictionary에 관한 정보중에 설정되어 있는 index를 기준으로 자료를 참조하게 하는 방법을 제시하였다.

  16. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 2. 부분범위 처리( index만 처리 ) 경우1 ) select dept , sum(qty) -- 설정되어 있는 index column : dept from product where dept like ‘12%’ group by dept; 경우2 ) select dept , sum(qty) -- 설정되어 있는 index column : dept , qty from product where dept like ‘12%’ group by dept; 설명 ) index에 설정되어 있는 column의 활용으로 해당 select문장에서 추구하는 값을 table을 access하지 않고도 자료를 처리할수 있게 하는 방법으로 필요이상의 속도 향상을 올릴수가 있다. 이 방법은 적절한 index의 사용이 필요하다는것을 알려주는 예제이다. 3. 부분범위 처리( max 처리 ) 경우1) select max(seq) + 1 -- 설정되어 있는 index column : dept from product where dept = ‘12300’; parsing 단계 --------------------------------------- sort table access by rowid product index range scan index명 경우2) select /*+ index_desc( a index1 ) */ seq + 1 -- 설정되어 있는 index column : dept , seq from product a where dept = ‘12300’ and rownum = 1; parsing 단계 ------------------------------ index range scan descending index명

  17. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 설명) 경우2는 table의 access를 하지 않고 index만으로 필요로 하는 max값을 찾아오는데 hint로 index_desc를 사용하였고 rownum의 사용으로 더 이상의 진행을 필요로 하지 않게 설정을 하였다 예제1) select max(orddate) -- index 설정되어 있는 column : orddept from orddat1t where orddept = ‘430’ and status = ‘30’; parsing 단계 --------------------- 1 sort aggregate 2892 table access by rowid order1t 15230 index range scan dept_date 예제2) select /*+ index_desc( a dept_date ) */ orddate -- index 설정되어 있는 column : orddept from order1t a where orddept = ‘430’ and status = ‘30’ and rownum = 1; parsing 단계 ----------------------- 1 count stopkey 1 table access by rowid order1t 2 index range scan descending dept_date

  18. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 예제1) select type , count(*) -- 설정되어 있는 index column : item from order2t where item like ‘HJ%’ group by type; parsing 단계 ---------------------------- 20 sort group by 36631 table access by rowid order2t 36631 index range scan item_status 예제2) select status , count(*) -- 설정되어 있는 index column : item , status from order2t where item like ‘HJ%’ group by status parsing 단계 ----------------------- 20 sort group by 36631 index range scan item_status 4. 부분범위처리( exists ) 예제1) select count(*) into :cnt from item_tab where dept = ‘101’ and seq > 100; ….. If cnt > 0 then …..

  19. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 예제2) select 1 into :cnt from dual -- 설정되어 있는 index column : dept where exists ( select ‘x’ from item_tab where dept = ‘101’ and seq > 100 ) …… if cnt > 0 then …. 설명) exists의 사용은 필요한 자료가 존재하는지의 여부만을 확인하기에 전체를 검색할 필요성이 없다 예제2의 parsing 단계는 다음과 같다. Filler table access (full) of dual table access by rowid order2t index range scan index명 . 부분범위처리( rownum ) 예제1) select count(*) into :cnt from item_tab where dept = ‘101’ and seq > 100; …….. If cnt > 0 then …. 예제2) select 1 into :cnt from item_tab where dept = ‘101’ and seq > 100 and rownum = 1; …… if cnt > 0 then …..

  20. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 . 1:M join의 부분범위 유도 -- 전체범위 예제1) select x.cust_no , x.addr , x.name , …… from cust x , reqt y where x.cust_no = y.cust_no and x.cust_stat in ( ‘A’ , ‘C’ , ‘F’ ) and y.un_pay > 0 group by x.cust_no having sum(y.un_pay) between :val1 and :val2; -- 부분범위 예제2) select x.cust_no , x.addr , x.name , …………… from cust x where cust_stat in ( ‘A’ , ‘C’ , ‘F’ ) and exists( select ‘x’ from reqt y where y.cust_no = x.cust_no and un_pay > 0 group by x.cust_no having sum(y.un_pay) between :val1 and :val2 ); 예제3) select cust_no , addr , up_pay , ………. From ( select cust_no , addr , unpay_sum(cust_no) as un_pay , ….. From cust where cust_stat in ( ‘A’ , ‘C’ , ‘F’)) where un_pay between :val1 and :val2; 설명 ) 예제2의 경우에서 1:M의 관계이기에 master의 자료만을 필요로 하는 경우에 최적이라고 할수가 있다. 즉 detail의 정보는 참조용으로 활용을 할뿐인경우에 최적이다. 주의할것은 exists 문구에서 subquery에서 참조한 column을 master 쪽 query에서 참조를 할수가 없다는 것이다. 예제3의 경우에는 detail에 해당하는 부분에 대한 값의 참조를 function을 이용하여 처리하였다. Query의 속도를 올리는 방법중에 function을 이용하는것도 최적의 방법중의 하나이다.

  21. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 . Index column의 변형( external ) 예제1) select * from emp where substr(dname,1,3) = ‘ABC’ --> select * from emp where dname like ‘ABC’; 예제2) select * from emp where sal * 12 = 12000000 --> select * from emp whree sal = 12000000/12; 예제3) select * from emp where to_char(hiredate,’YYYYMMDD’) = ‘940101’; --> select * from emp where hiredate = to_date(‘940101’,’YYYYMMDD’); 예제4) select * from emp where nvl(comm,0) < 100; --> ? 위의 예제의 경우에 where절에 설정되어 있는 column에 대해서 각각의 index가 설정되어 있다는 가정하에 문장을 실행해보면 인덱스로 설정되어 있는 column의 변형으로 해당 index를 참조하지 않고 full scan하는 현상이 발생한다 인덱스를 사용하여 속도를 향상시킬수 있는 문장이 있는 반면에 인덱스를 사용하지 않는것이 더 나은 속도를 낼수 있는 문장이 있으니 적절한 사용이 필요하다( 즉 인덱스를 참조하지 않는것이 좋은경우에는 index의 컬럼을 변형하는것이 좋다 )

  22. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 . Index Column의 변형( internal ) create table samplet ( chr char(10) , num number(12,3) , var varchar2(20) , dat date ); 예제1) select * from samplet where cha = 10 --> select * from samplet where to_number(cha) = 10; 예제2) select * from samplet where num like ‘9410%’; --> select * from samplet where to_char(num) like ‘9410’; 예제3) select * from samplet where dat = ‘01-JAN-94’; --> select * from samplet where dat = to_date(‘01-JAN-94’); 예제4) select * from samplet where var = 10 --> select * from samplet where to_number(var) = 10 설명 : variable로 들어오는 값에 따라서 기존의 column의 유형이 변화를 일으킨다. 즉 예제1 , 예제2에서 보듯이 variable value로 들어온 값이 비교 column의 data값과 다른경우에는 비교 column의 유형이 내부적으로 변화를 일으킨다. 예외적인 경우는 date형에 대해서는 비교 column의 유형은 그대로 이고 , variable value로 들어온값의 유형이 to_date로 변한다. 예제3의 경우를 참조.

  23. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 . Index Column의 변형( internal ) 예제5) select * from samplet where num = cha; --> select * from samplet where num = to_number(cha); 예제6) select * from samplet where dat = cha; --> select * from samplet where dat = to_date(cha); 설명) 예제1 ~ 예제4까지의 경우와는 다르게 variable value와 column을 비교하는것이 아니고 , column 과 column을 비교하는 경우에는 우측편에 사용되는 column의 유형이 변한다. . Null 값의 적용 기준 . 어떤 값보다 크지도 않고 작지도 않다. . 그러므로 어떤 값과 비교될수 없다. . 즉 null과의 연산결과는 null이 된다. . Cost_based vs Rule_based의 차이 -- Rule based -- 현재 unique로 설정되어 있는 index가 존재하지 않고 있다는 가정에서 ... . Select * from emp -- index merge( and_equal) where job = ‘SALESMAN’ and empno = ‘7890’; . Select * from emp -- 항상 empno index만 사용 where ename like ‘AB%’ and empno = ‘7890’; . Select * from emp -- 항상 나중에 생성된 index만 사용 where ename between ‘1101’ and ‘1210’ and job like ‘SA%’; --> 우선순위 => = : like : between

  24. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 -- cost based -- 현재 unique로 설정되어 있는 index가 존재하지 않고 있다는 가정에서 ... . Select * from emp -- index merge( and_equal ) , 특정 index where job = ‘SALESMAN’ and empno = ‘7890’; . Select * from emp -- 분포도에 따라 ename index도 사용 where ename like ‘AB%’ and empno = ‘7890’; . Select * from emp -- 분포도에 따라 index 사용 , 혹은 full scan where ename between ‘1101’ and ‘1210’ and job like ‘SA%’; . Index merge explainplan의 설정이 다음의 경우와 비슷할 경우에는 해당 column에 대해서 결합 인덱스의 사용을 생각해볼 필요가 있다. 예제1) select col1 , col2 -- empno , ename 각각 index가 설정되어 있을때. from emp where empno = ‘111’ and ename = ‘ttt’; Explain plan ------------------------------------------------------ table access( by index rowid ) of ‘emp’ and_equal index ( range scan ) of ‘inx_emp1’ index ( range scan ) of ‘inx_emp2’

  25. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 참고 ) 결합 index를 생성시에는 결합되는 column의 순서에 각별히 신경을 써야 한다. 각각의 컬럼에 대한 분포도를 확인하고 , 인덱스를 이용하여 data를 검색을 하더라도 찾고자 하는 범위를 축소시키면서 사용할수 있도록 인덱스 컬럼을 구성해야 한다. 예제1) inx_imsi : col1 + col2 + col3 예제2) inx_imsi : col2 + col1 + col3 구분된 값들 == col1 : 10 , col2 : 100 , col3 : 50일 경우… 예제문장) select * from tab1 where col1 = ‘A’ and col2 = ‘112’ ; select * from tab1 where col1 = ‘A’ and col2 between ‘111’ and ‘113’; . IN을 이용한 access 효율 향상 예제1) select * from tab1 -- 설정되어있는 index 컬럼 : col2 where col1 = ‘A’ and col2 between ‘111’ and ‘112’; explain plan ----------------------- table a access by rowid tab1 index range scam index1 예제2) select * from tab1 where col1 = ‘A’ and col2 in(‘111’,’112’); explain plan --------------------------- concatenation table access by rowid tab1 index range scan index1 table access by rowid tab1 index range scam tab1 설명) 위의 예제에서는 예제1에 해당하는 것은 111과 112사이의 값을 찾아내는것이기에.. 둘 사이에 다른 어떤 정수의 값도 포함되어 있지 않기에 예제2의 형태로 사용하라는 의미이다. -- 뒷장에 이어서..

  26. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 반면에 이곳에서 생각을 할것은 in 연산자의 사용이다 예제2는 oracle의 내부에서 다음과 같이 갈라지게 되어있다. 즉 select * from tab1 where col1 = ‘A’ and col2 = ‘111’; select * from tab1 where col1 = ‘A’ and col2 = ‘112’; 이상과 같이 두가지로 나누어 지기때문에 between ~ and 사이에서 참조하고자 하는값의 사이에 어떤 정수의 값도 존재하지 않을때는 예제2처럼 사용하라는 의미이다. . 추가된 인덱스컬럼의 역할 select * from tab1 where a = ‘2’ and c = 51; 방법1 ) 현재 설정되어 있는 index column이 a , b로 되어있을경우 -- 예로 인덱스명은 inx_tab1 위의 문장에서 inx_tab1에서 a 컬럼을 참조하여 range scan을 하고 , 그때 참조된 rowid를 가지고 table을 access한다. Table access에서는 참조하는 범위가 너무 넓기 때문에.. 문제가 된다. 방법2 ) 현재 설정되어 있는 index column이 a , b , c로 되어있을 경우 -- 예로 인덱스명은 indx_tab1 위의 문장에서 inx_tab1에서 a,c를 기준으로 range scan을 하고 , 그때 참조된 rowid를 가지고 table을 access한다. Table access에서 참조하는 범위가 적기 때문에.. 속도가 방법1에서보다는 항상된다. . 결합 index column순서 결정 1. 항상 사용되는가? 2. 항상 ‘=‘로 사용되는가? 3. 분포도가 좋은 컬럼 우선 4. Sort 순서는? 5. 어떤 컬럼을 추가?( 적용 후보 )

  27. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 . JOIN의 순서에 따른 속도차이 -- 예제에서 설정되어 있는 index : 1. custno( pk ) , 2. Nation , 3. Chuldate 예제1) select chulno , chuldate , custname from customer y , chulgot x -- drive table에 관한 부분 where x.custno = y.custno and x.chuldate = ‘941003’ and y.nation = ‘kor’; explain plan ------------------------------ nested loop table access by rowid customer index range scan cu_nation table access by rowid chulgot and-equal index range scan cu_custno index range scan cu_chuldate; 예제2) select chulno , chuldate , custname from chulgot x , customer y where x.custno = y.custno and x.chuldate = ‘941003’ and y.nation = ‘kor’; explain plan ------------------------------ nested loops table access by rowid chulgot index range scan ch_chuldate table access by rowid customer index unique scan pk_custno

  28. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 설명) 이곳에서 주의해야 할것이 있다 . From 절에 기술되는 table의 순서이다. 특별한 경우가 아닌 이상 Oracle은 from 절에 나열되어있는 table의 목록중에 제일 나중에 기술된것을 drive table로 선정을 한다. 그리고 예제1 , 예제2에서 사용되는 index의 적용순서는 우선 unique index를 우선으로 참조하고 , 다음에 변수값을 가지는 index를 사용하는 규칙을 사용한다. From절에 기술되어 있는 table에 대해서 두 table을 비교하는 column이 unique로 설정이 되어있기에 동일한 조건하에서 drive table을 선택하게 되는것이다. . Join시 index의 영향 예제1) select custno , chuldate , custname from customer y , chulgot x where x.custno = y.custno and x.chuldate = ‘941130’ and y.nation = ‘kor’; explain plan ------------------------ nested loops table access by rowid customer index range scan cu_nation table access by rowid chulgot and-equal index range scan ch_custno index range scan ch_chuldate 예제2) select custno , chuldate , custname from customer y , chulgot x where rtrim(x.custno) = y.custno and x.chuldate = ‘941130’ and y.nation = ‘kor’; 뒷장에 이어서 ………..

  29. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 explain plan ---------------------- nested loops table access by rowid chulgot index range scan ch_chuldate table access by rowid customer index unique scan pk_custno 설명) 이곳에서는 이전의 예제와는 약간 다르다.. 이곳에서 명심해야 할 부분은 where 절에 있는 다음의 문구이다 즉 where rtrim(x.custno) = y.custno 절이다 이전의 문구에서는 where x.custno = y.custno 이기에 , 즉 두 table에서 사용하는 컬럼인 custno가 key로 설정이 되어있거나 , unique index로 설정이 되어있는 상황이기에 from절에 기술된 순서를 적용하여 뒷 부분에 있는 table를 drive table로 선정이 된것이다. 반면에 이곳에서는 rtrim(x.custno)라는 customer table에 대한 unique 컬럼에 대한 변형이 발생하여 , from절에 기술된 순서를 무시하고 , customer 테이블을 drive table로 선정을 한것이다. 이런 경우에 대해서 명심할 필요가 있다. . Nested loop JOIN 예) select a.fld1 , ….. , b.fld1 , ,,,,, from tab1 a , tab2 b where a.key1 = b.key2 and a.fld1 = ‘AB’ and b.fld2 = ‘10’; explain plan -------------------------------- nested table table access by rowid tab2 index range scan …. table access by rowid tab1 index range scan … 뒷장에 이어서..

  30. SQL Tunging 및 사용 방법에 관해서 설명한다.( 참조책자 : 이화식 dbsolution ) SQL Tuning에 관한 solution 및 설명 설명) 이곳에서는 explain plan에서 drive로 설정되어 있는 table이 생각하기에 tab2로 되어야만 한다고 생각을 할수가 있다. 그렇지만 driving table로 설정이 되어있는 table은 tab1이다. 이것은 optimizer가 해당 컬럼에 대한 analyze statistics 를 보고 해당 table에 대해서 driving table를 선택한 경우이다. 만약 이 경우에 원하는 table을 driving table로 선택하고자 할 경우에는 hint를 사용하여 처리할수가 있다. . Sort merge join 예제) select /*+ use_merge(a b) */ a.fld1, …. , b.fld2 , … from tab1 a , tab2 b where a.key1 = b.key2 and a.fld1 = ‘AB’ and b.fld2 = ‘10’; -- 위의 예제에서는 다음과 같은 index가 있다 , tab1에는 fld1에 대해서 , tab2에는 fld2에 대해서 index 설정되어있슴 explain plan ---------------------------------- merge join table access by rowid tab1 index by range scan xxx -- tab1에 설정되어 있는 index table access by rowid tab2 index by range scsn yyy -- tab2에 설정되어 있는 index 설명) 이곳에서는 optimizer hint로써 use_merge를 사용하였다 이 hint의 사용은 다음과 같은 용도로 사용된다 두 table(tab1 , tab2)에 대해서 각각 index를 활용하여 각각 table을 access 한다 그런뒤 각각 table에서 참조된 자료를 가지고 sort merge join이 발생을 한다 merge join은 큰 두 table에 대해서 작업을 수행할때가 좋다.

More Related