index\ub780?<\/p>\n
\uac80\uc0c9\uc744 \ube60\ub978 \uc18d\ub3c4\ub85c \ud558\uae30 \uc704\ud574\uc11c \uc0ac\uc6a9\ub429\ub2c8\ub2e4.
\nSQL \uba85\ub839\ubb38\uc758 \ucc98\ub9ac \uc18d\ub3c4\ub97c \ud5a5\uc0c1\uc2dc\ud0a4\uae30 \uc704\ud574\uc11c \uceec\ub7fc\uc5d0 \ub300\ud574\uc11c \uc0dd\uc131\ud558\ub294 \uc624\ub77c\ud074 \uac1d\uccb4\uc784.<\/p>\n
–index \uc0ac\uc6a9\ud574\uc57c\ud560 \uacbd\uc6b0
\n\ud14c\uc774\ube14\uc5d0 \ud589\uc758 \uc218\uac00 \ub9ce\uc744\ub54c
\n\uc870\uac74\uc808\uc778 where\ubb38\uc5d0 \ud574\ub2f9 \uceec\ub7fc\uc774 \ub9ce\uc774 \uc0ac\uc6a9\ub420\ub54c
\n\uac80\uc0c9 \uacb0\uacfc\uac00 \ub370\uc774\ud130\uc758 2% ~ 4% \uc815\ub3c4\uc77c\ub54c(\uc801\uc740 \uc591\uc758 \uceec\ub7fc\uc744 \uac00\uc838\uc62c\ub54c)
\njoin\uc5d0 \uc790\uc8fc \uc0ac\uc6a9\ub418\ub294 \uceec\ub7fc
\nNULL\uc744 \ud3ec\ud568\ud558\ub294 \uceec\ub7fc\uc774 \ub9ce\uc740 \uacbd\uc6b0<\/p>\n
–index \uc0ac\uc6a9\ud558\uc9c0 \ub9d0\uc544\uc57c \ud558\ub294 \uacbd\uc6b0
\n\ud14c\uc774\ube14\uc5d0 \ud589\uc758 \uc218\uac00 \uc801\uc744\ub54c
\nwhere \ubb38\uc5d0 \ud574\ub2f9 \uceec\ub7fc\uc774 \uc790\uc8fc \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uc744 \ub54c
\n\uac80\uc0c9 \uacb0\uacfc\uac00 \uc804\uccb4 \ub370\uc774\ud130\uc758 10% ~ 15% \uc774\uc0c1 \ub192\uc744 \ub54c(\ub9ce\uc740 \uc591\uc758 \uceec\ub7fc\uc744 \uac00\uc838\uc62c\ub300)
\n\ud14c\uc774\ube14\uc5d0 DML \uc791\uc5c5\uc774 \ub9ce\uc740 \uacbd\uc6b0(\uc785\ub825,\uc218\uc815,\uc0ad\uc81c \ub4f1\uc774 \uc790\uc8fc \ubc1c\uc0dd\ud560\ub54c)<\/p>\n
–index \ub515\uc158\ub108\ub9ac
\nUSER_INDEXES :\uc778\ub371\uc2a4 \uc774\ub984 \ubc0f \uace0\uc720\uc131\uc744 \ud3ec\ud568
\nUSER_IND_COLUMNS : \uc778\ub371\uc2a4 \uc774\ub984, \ud14c\uc774\ube14 \uc774\ub984, \uc5f4 \uc774\ub984 \ud3ec\ud568<\/p>\n
–index \uc0dd\uc131\ubc95
\ncreate index index\uba85 on table\uba85(index\uac78\uc5b4\uc904 table\uc5d0 colum\uba85);
\nex)create index idx_emp_ename on emp(ename);<\/p>\n
–index \uc81c\uac70
\ndrop index index\uba85
\nex)drop index idx_emp_ename;<\/p>\n
–index \uc7ac\uc0dd\uc131
\nalter index index\uba85 rebuild;
\nex)alter index idx_emp_ename rebuild;<\/p>\n
–index \uc870\ud68c(index\ub97c \ubaa8\ub450 \ubcf4\uc5ec\uc900\ub2e4.)
\nselect object_name, object_type
\nfrom user_objects
\nwhere object_type =’INDEX’;<\/p>\n
\ud6a8\uc728\uc131\uc774 \ub5a8\uc5b4\uc9c0\uba74 \uc778\ub371\uc2a4\ub97c \uc7ac \uc0dd\uc131\ud574 \uc8fc\uc5b4\uc57c \ud55c\ub2e4.
\n\uceec\ub7fc\uc758 \ub370\uc774\ud130\uac00 \uc785\ub825,\uc218\uc815,\uc0ad\uc81c\ub420 \uacbd\uc6b0 \ud574\ub2f9 \uceec\ub7fc\uc5d0 \uc758\ud574 \uc0dd\uc131\ub41c \uc778\ub371\uc2a4\uc5d0 \ub300\ud574\uc11c \uc7ac\uad6c\uc131 \ud574\uc57c \ub41c\ub2e4.<\/p>\n
–index \uc885\ub958
\nUnique Index(\uace0\uc720 \uc778\ub371\uc2a4) – \uc720\uc77c\ud55c \uac12\uc744 \uac16\ub294 \uceec\ub7fc\uc5d0 \ub300\ud574\uc11c \uc0dd\uc131\ud558\ub294 \uc778\ub371\uc2a4
\nNonUnique Index(\ube44\uace0\uc720 \uc778\ub371\uc2a4) – \uc911\ubcf5\ub41c \ub370\uc774\ud130\ub97c \uac16\ub294 \uceec\ub7fc\uc5d0 \ub300\ud574\uc11c \uc0dd\uc131\ud558\ub294 \uc778\ub371\uc2a4
\nSingle Index(\ub2e8\uc77c \uc778\ub371\uc2a4)
\nComposite Index(\uacb0\ud569 \uc778\ub371\uc2a4)
\n\uacb0\ud569 \uc778\ub371\uc2a4 \uc6b0\uc120\uc21c\uc704
\n1\uc21c\uc704 – \uceec\ub7fc\uc774 \uc0ac\uc6a9\ud55c \uc5f0\uc0b0\uc790\uc5d0 \uc758\ud55c \uc778\ub371\uc2a4 \uceec\ub7fc \uc120\uc815
\n2\uc21c\uc704 – \ub79c\ub364 \uc561\uc138\uc2a4\ub97c \uace0\ub824\ud55c \uc778\ub371\uc2a4 \uceec\ub7fc \uc120\uc815
\n3\uc21c\uc704 – \uc815\ub82c \uc81c\uac70\ub97c \uc704\ud55c \uc778\ub371\uc2a4 \uceec\ub7fc \uc120\uc815
\n4\uc21c\uc704 – \ub2e8\uc77c \uceec\ub7fc\uc758 \ubd84\ud3ec\ub3c4\ub97c \uace0\ub824\ud55c \uc778\ub371\uc2a4 \uceec\ub7fc \uc120\uc815<\/p>\n
\uc704\uc758 \uc6b0\uc120\uc21c\uc704 \uad6c\uc131 \uaddc\uce59\uc758 \uc774\uc720\ub294 \ub514\uc2a4\ud06c I\/O\ub97c \uac00\uc7a5 \uc801\uac8c \ubc1c\uc0dd\uc2dc\ud0a4\uae30 \uc704\ud574\uc11c\ub2e4.<\/p>\n
Function Based Index(\ud568\uc218 \uae30\ubc18 \uc778\ub371\uc2a4)
\n\ud45c\ud604\uc2dd\uc744 \uae30\ubc18\uc73c\ub85c \ud558\ub294 \uc778\ub371\uc2a4
\n\uc778\ub371\uc2a4 \ud45c\ud604\uc2dd\uc740 \ud14c\uc774\ube14 \uc5f4, \uc0c1\uc218, SQL \ud568\uc218 \ubc0f \uc0ac\uc6a9\uc790\uac00 \uc815\uc758\ud55c \ud568\uc218\ub85c\ubd80\ud130 \uc0dd\uc131<\/p>\n
1.\uceec\ub7fc\uc758 \uc911\uac04 \ubd80\ubd84\uc758 \uac80\uc0c9
\ncreate index from_loc_idx on orders(sbstr(ship_id,5,3));
\ncreate index repair_loc_idx on orders(substr(ship_id,3,2,),ord_date);<\/p>\n
2.\uc870\uc778 \uc5f0\uacb0\uace0\ub9ac \uceec\ub7fc\uc774 \ub300\uc751\ud558\uc9c0 \uc54a\ub294 \uacbd\uc6b0\uc758 \ud574\uacb0
\n…
\nfrom item_group x,items y
\nwhere x.class1||x.class2||x.class3 = y.group_cd….
\n\u25bd
\n…
\nfrom item_group x, items y
\nwhere x.class1 = substr(y.group_cd,1,2)
\nand x.class2 = substr(y.group_cd,3,2)
\nand x.class3 = substr(y.group_cd,5,3)
\n…
\n\u25bd
\ncreate index group_cd_idx_ on item_group(class1||class2||class3);<\/p>\n
3.\uc77c\uc790 \uceec\ub7fc\uc774 \ubd84\ud65c\ub41c \uacbd\uc6b0\uc758 \ud574\uacb0
\nwhere sal_yyyy >=’2005′ and sal_mm >=’12’ and sal_dd>=’10’ (x)
\n\u25bd
\nwhere sal_yyyy||sal_mm||sal_dd >=’20051210′ (o)
\n\u25bd
\ncreate index sal_date_idx on sales(sal_yyyy||sal_mm||sal_dd);<\/p>\n
4.\ub370\uc774\ud0c0 \ud0c0\uc785\uc774 \uc0c1\uc774\ud55c \uc870\uc778 \uceec\ub7fc
\ncreate index deptno_idx on emp(to_number(deptno));<\/p>\n
5.\uc870\uc778 \uceec\ub7fc\uc774 \uacbd\uc6b0\uc5d0 \ub530\ub77c \ub2ec\ub77c\uc9c0\ub294 \uacbd\uc6b0
\n…
\nfrom sales s, department d
\nwhere d.deptno = (case when sal_type = 1 then sal_dept else agent_no end)
\nand d.location = ‘BUSAN’
\n”’
\n\u25bd
\ncreate index deptno_idx on sales(case when sal_type = 1 then sal_dept else agent_no end);<\/p>\n
6.\ub300\/\uc18c\ubb38\uc790\ub098 \uacf5\ubc31\uc774 \ud63c\uc7ac\ub41c \uceec\ub7fc\uc758 \uac80\uc0c9
\ncreate index ename_upper_ix on employees(upper(ename));(\ub300\ubb38\uc790)<\/p>\n
create index ename_upper_ix on employees(upper(replace(ename,”));(\ub300\ubb38\uc790\/\uacf5\ubc31)<\/p>\n
7.NULL \uac12\uc744 \uce58\ud658\ud558\uc5ec \uac80\uc0c9
\n…
\nwhere :input_date between start_date and nvl(end_date,’99991231′);
\n…
\n\u25bd
\ncreate index end_date_idx on account_history(nvl(end_date,’99991231′),start_date);<\/p>\n
8.\ubcf5\uc7a1\ud55c \uacc4\uc0b0 \uacb0\uacfc\uc758 \uac80\uc0c9
\ncreate index order_amount_idx on order_items(item_cd,(order_price – nvl(order_discount,0)) * order_count));<\/p>\n
select\/*+index_desc(x order_amount_idx)*\/ *
\nfrom ordero_items x
\nwhere item_cd =:b1
\nand rownum <= 100;<\/p>\n
9.\uae30\uac04, \uceec\ub7fc \uae38\uc774 \uac80\uc0c9
\ncreate index item_idx on activities (expire_date – start_date); (\uae30\uac04)
\ncreate index source_length_idx on print_media(text_length(source_text));(\uceec\ub7fc \uae38\uc774)<\/p>\n
10.\ubc30\ud0c0\uc801 \uad00\uacc4\uc758 \uc720\uc77c\uc131 \ubcf4\uc7a5
\ncreate unique index official_id_idx on customers
\n(case when cust_type=1 then resident_id else business_id end);<\/p>\n
select * from customers
\nwhere(case when cust_type=1 then resident_id else business_id end) =:b1;
\n\ud568\uc218\uae30\ubc18 \uc778\ub371\uc2a4\ub294 \ubc84\uc804\uc5d0 \ub530\ub77c \uc81c\uc57d\uc0ac\ud56d\uc5d0 \ucc28\uc774\uac00 \uc788\uc73c\ubbc0\ub85c \uad00\ub828 \uba54\ub274\uc5bc\uc744 \ucc38\uc870\ud574\uc57c\ud568.<\/p>\n
ex)DATE TYPE \uacfc \ud568\uc218\uae30\ubc18\uc778\ub371\uc2a4 \ud65c\uc6a9 \uc608
\nDATE TYPE\uc73c\ub85c \uce7c\ub7fc\uc744 \uc124\uacc4\ud588\uc744\uacbd\uc6b0 \uc77c\uc790\ub9cc \ube44\uad50\ud558\uace0 \uc2f6\uc73c\uba74 TRUNC(DATE_COL)\uc774\ub807\uac8c \ud558\uba74\ub428
\n\uadf8\ub7f0\ub370 \uc774\ub7f0 \ud568\uc218\ub97c \uc4f0\uba74 INDEX\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc5c6\ub2e4.(\ud574\uacb0\ubc29\ubc95 Function-Based index)<\/p>\n
create index ix_t on t(trunc(date_col)); (\uc5ec\uae30\uc11c date_col\uc740 date_type\uc758 \uceec\ub7fc\uba85)
\n\ucffc\ub9ac\ubb38
\nselect * from t
\nwhere trunc(date_col) = ‘20091001’;
\n\uc774\ub807\uac8c \ud558\uba74 \ub41c\ub2e4.<\/p>\n
trunc(date_col)\uc774 \ubd80\ubd84\uc744 \uac04\ud3b8\ud558\uac8c \uc774\uc6a9\ud558\ub824\uba74 \ubdf0\ub97c \uc774\uc6a9<\/p>\n
create view v_t
\nas
\nselect trunc(date_col) as date_col from t;
\n\uc774\ub7ec\uba74 , \ucffc\ub9ac\ubb38\uc774
\nselect * from t
\nwhere date_col = ‘20091001’;<\/p>\n
———————————————————
\n[\uace0\uc720\uc778\ub371\uc2a4]-\ub2e8\uc77c
\ncreate unique index idx_dept_deptno on dept(deptno);
\n[\ube44\uace0\uc720\uc778\ub371\uc2a4]-\ub2e8\uc77c
\ncreate index idx_dept_loc on dept(loc);
\n[\uacb0\ud568 \uc778\ub371\uc2a4]
\ncreate index ind_dept_com on dept(deptno,loc);
\n[\ud568\uc218\uae30\ubc18 \uc778\ub371\uc2a4]<\/p>\n
create table emp as select * from emp;
\n\uac80\uc0c9 \uc870\uac74\uc73c\ub85c sal*12 = 3600 \uc744 \uc9c0\uc815\ud558\ub294 \uacbd\uc6b0, \uc774\ub7f4\uacbd\uc6b0 sal*12\ub97c \uc778\ub371\uc2a4\ub97c \ud0c8 \uc218 \uc5c6\uc744\uc218 \uc788\ub2e4.
\n\uc774\ub7f4\ub54c \uad6c\uc131\ud558\ub294 \uc778\ub371\uc2a4\uac00 \ud568\uc218\uae30\ubc18 \uc778\ub371\uc2a4 \uc785\ub2c8\ub2e4.
\ncreate index idx_emp_annsal on emp(sal*12);(\ud568\uc218\uae30\ubc18 \uc778\ub371\uc2a4 \uc0dd\uc131)<\/p>\n
select index_name, column_name
\nfrom user_ind_columns
\nwhere table_name=’emp’; (\uc778\ub371\uc2a4 \uc0dd\uc131 \ud655\uc778)<\/p>\n
–where \uc808\uc758 \uc0ac\uc6a9\uc5d0 \ub530\ub978 \uc778\ub371\uc2a4 \ud65c\uc6a9
\n\uc810 \uc870\uac74 :IN,= –>\ud574\ub2f9 \uc5f0\uc0b0\uc790\ub294 \ud558\ub098\uc758 \uc810\ub9cc\uc744 \uc758\ubbf8
\n\uc120\ubd84 \uc870\uac74:LIKE,BETWEEN,<,> –>\ud574\ub2f9 \uc870\uac74\uc744 \ub9cc\uc871\ud558\ub294 \ubaa8\ub4e0 \uc2e4\uc218\ub97c \uc758\ubbf8<\/p>\n
\uc870\uac74\uc5d0 \uc0ac\uc6a9\ub41c \uc5f0\uc0b0\uc790\uc5d0 \uc758\ud574 \uc561\uc138\uc2a4\ud574\uc57c \ud558\ub294 \ucc98\ub9ac \ubc94\uc704 \ucc28\uc774<\/p>\n
\uc810 \uc870\uac74 + \uc810 \uc870\uac74 : \ub450 \uc870\uac74\uc5d0 \uc758\ud574 \ucc98\ub9ac \ubc94\uc704 \uac10\uc18c
\n\uc810 \uc870\uac74 + \uc120\ubd84 \uc870\uac74 : \ub450 \uc870\uac74\uc5d0 \uc758\ud574 \ucc98\ub9ac \ubc94\uc704 \uac10\uc18c
\n\uc120\ubd84 \uc870\uac74 + \uc120\ubd84 \uc870\uac74 : \uc55e\uc758 \uc120\ubd84 \uc870\uac74\uc5d0 \uc758\ud574\uc11c\ub9cc \ucc98\ub9ac \ubc94\uc704 \uac10\uc18c
\n\uc120\ubd84 \uc870\uac74 + \uc810 \uc870\uac74 : \uc55e\uc758 \uc120\ubd84 \uc870\uac74\uc5d0 \uc758\ud574\uc11c\ub9cc \ucc98\ub9ac \ubc94\uc704 \uac10\uc18c<\/p>\n
\ub2e8\uc77c \uc778\ub371\uc2a4\ubcf4\ub2e4\ub294 \uacb0\ud569 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\uace0 \uc810 \uc870\uac74\uc744 \uc6b0\uc120\uc801\uc73c\ub85c \uc778\ub371\uc2a4 \uc870\uac74\uc73c\ub85c \ud574\uc57c \uc131\ud5a5\uc0c1\uc744 \uae30\ub300\ud560 \uc218 \uc788\ub2e4.<\/p>\n
–index \uc0ac\uc6a9\/\ubd88\uac00 \uc608<\/p>\n
* \uc778\ub371\uc2a4\ub97c \ud0c0\uc9c0 \uc54a\ub294 SQL
\n1. \uc778\ub371\uc2a4 \uceec\ub7fc \uc808\uc758 \ubcc0\ud615
\n2. \ub0b4\ubd80\uc801\uc778 \ub370\uc774\ud130 \ubcc0\ud658
\n3. NULL \uc870\uac74\uc758 \uc0ac\uc6a9
\n4. \ubd80\uc815\ud615 \uc870\uac74\uc758 \uc0ac\uc6a9
\n5. LIKE \uc5f0\uc0b0\uc790 \uc0ac\uc6a9
\n6. \ucd5c\uc801\uae30\uac00 \ud310\ub2e8<\/p>\n
1 – \uc778\ub371\uc2a4 \uceec\ub7fc \uc808\uc758 \ubcc0\ud615
\nSQL> select ename from emp where sal * 2.1 > 950 –\uc778\ub371\uc2a4 \uc0ac\uc6a9\ubd88\uac00
\nSQL> select ename from emp where sal > 950 \/2.1 –\uc778\ub371\uc2a4 \uc0ac\uc6a9\uac00\ub2a5
\nSQL> select ename from emp where to_char(hiredate,’DDMMYY’) = ‘250884’ –\uc778\ub371\uc2a4 \uc0ac\uc6a9\ubd88\uac00
\nSQL> select ename from emp where hiredate = to_date(‘250884′,’DDMMYY’) –\uc778\ub371\uc2a4 \uc0ac\uc6a9\uac00\ub2a5<\/p>\n
-> \uc778\ub371\uc2a4 \uceec\ub7fc\uc5d0 \ubcc0\ud615\uc744 \uac00\ud558\uba74\uc740 \uc0ac\uc6a9\ud560\uc218 \uc5c6\uc2b5\ub2c8\ub2e4. \ub2e8 \ubcc0\ud615 \uac00\ub2a5\ud558\ub354\ub77c\ub3c4 \uc4f0\uace0 \uc2f6\ub2e4\uba74\uc740
\n\ub9d0\ub9ac\uc9c0\ub294 \uc54a\uaca0\uc9c0\ub9cc create index …. on emp to_char(hiredate,’DDMMYY’) \uc774\ub807\uac8c \ud558\uc2dc\uba74 \ub429\ub2c8\ub2e4.<\/p>\n
2 – \ub0b4\ubd80\uc801\uc778 \ub370\uc774\ud130 \ubcc0\ud658
\nSQL> select * from emp where hiredate =’14-JAN-85′ –\uc778\ub371\uc2a4 \uc0ac\uc6a9\ubd88\uac00
\nSQL> select * from emp hiredate = to_date(’71-10-22′,’YY\/DD\/DD’) –\uc778\ub371\uc2a4 \uc0ac\uc6a9\uac00\ub2a5
\nSQL> select * from emp where empno = ‘7936’ –\uc778\ub371\uc2a4 \uc0ac\uc6a9\ubd88\uac00
\nSQL> select * from emp where empno = to_number(‘7936’) –\uc778\ub371\uc2a4 \uc0ac\uc6a9\uac00\ub2a5<\/p>\n
-> \ub0b4\ubd80\uc801\uc778 \ub370\uc774\ud130\ubcc0\ud658\uc5d0\uc11c \uac00\uc7a5 \ub9ce\uc774 \uc2e4\uc218\ud558\ub294 \ubd80\ubd84\uc740 \ubb38\uc790\uac12 \ub370\uc774\ud130\ud0c0\uc785\uc744 \uac16\ub294 \uceec\ub7fc\uc5d0
\n‘\uac12’ -> \uac12 \uc774\ub807\uac8c \ud558\uc2dc\ub294\ubd84\uc774 \ub9ce\uc2b5\ub2c8\ub2e4. \ub531\ub9de\ub294 \ub370\uc774\ud130\ud0c0\uc785\uc744 \uc8fc\uc138\uc694 ^_^<\/p>\n
3 – NULL \uc870\uac74\uc758 \uc0ac\uc6a9
\nSQL> select ename from emp where comm is null –\uc778\ub371\uc2a4 \uc0ac\uc6a9\ubd88\uac00
\nSQL> select ename from emp where comm is not null –\uc778\ub371\uc2a4 \uc0ac\uc6a9\ubd88\uac00
\nSQL> select ename from emp where ename > ” –\uc778\ub371\uc2a4 \uc0ac\uc6a9\uac00\ub2a5
\nSQL> select ename from emp where comm >= 0 –\uc778\ub371\uc2a4 \uc0ac\uc6a9\uac00\ub2a5<\/p>\n
-> NULL\uc870\uac74\uc73c\ub85c \uac80\uc0c9\ud55c\ub2e4\ub294 \uac00\uc815\ud558\uc5d0\ub294 \uac70\uc758 \uc778\ub371\uc2a4 \ud480 \uc2a4\uce94\uc774 \uc77c\uc5b4\ub098\uaca0\uc8e0. \uc801\uc808\ud788 \uc0ac\uc6a9\ud569\uc528\ub2e4.<\/p>\n
4 – \ubd80\uc815\ud615 \uc870\uac74\uc758 \uc0ac\uc6a9
\nSQL> select ename from emp where deptno != 30 –\uc778\ub371\uc2a4 \uc0ac\uc6a9\ubd88\uac00
\nSQL> select ename from emp where deptno < 30 and deptno > 30 –\uc778\ub371\uc2a4 \uc0ac\uc6a9\uac00\ub2a5<\/p>\n
-> \ub17c\ub9ac\uc801\uc73c\ub85c \ubd80\uc815\ud615\uc744 \uc774\uc6a9\ud558\uc5ec \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\uaca0\ub2e4\ub294\uac83\uc740 \ub9d0\uc774 \uc548\ub418\uc8e0…
\n\uc774 \ucffc\ub9ac\ubb38\ub3c4 \uc801\uc808\ud788 \uc0ac\uc6a9\ud569\uc528\ub2e4.<\/p>\n
5 – Like \uc5f0\uc0b0\uc790 \uc0ac\uc6a9
\nSQL> select * from emp where ename like ‘S%’ –\uc778\ub371\uc2a4 \uc0ac\uc6a9\uac00\ub2a5
\nSQL> select * from emp where ename like ‘%S%’ –\uc778\ub371\uc2a4 \uc0ac\uc6a9\ubd88\uac00<\/p>\n
-> %S% \ubd80\ubd84\uc744 \uaf2d \uc4f0\uace0\uc2f6\ub2e4\uba74\uc740 \uc774\ub807\uac8c \ud558\uc138\uc694 \uc55e\uc5d0\ubd80\ubd84\uc744 \ub2e4 \ub123\ub294\uac70\uc8e0. AS%, BS%…
\n\uc694\uc998 \ud648\ud53c\ub97c \ubcf4\uba74 \ubcf8\ubb38 \ucc3e\uae30\ub294 \uac70\uc758 \uc5c6\uc5b4\uc838\uac00\uace0 \uc788\uc8e0. \uc5d4\ucf54\uc544 \uacbd\uc6b0 \uc8fc\uc81c\uc5b4\ub97c \uac80\uc0c9\uc744..
\n6 – \ucd5c\uc801\uae30\uac00 \ud310\ub2e8<\/p>\n
-> RBO\uacbd\uc6b0 \ubb34\uc870\uac74 \ud0c0\uc8e0 \uc788\uc73c\uba74\uc740 \uc774\ub214\uc740 \uc6cc\ub099 \ubc95\uc744 \uc88b\uc544\ud574\uc11c\ub9ac..CBO\ub294 \ud1b5\uacc4\uac12\uc744 \uae30\uc900\uc73c\ub85c
\n\ud558\uae30 \ub54c\ubb38\uc5d0 DBA\uac00 \uc798 \ud574\uc57c\uaca0\uc8e0. \uadf8\ub7fc \uc6b0\ub9ac\uac00 \ud310\ub2e8\ud558\uac8c \ud560\ub77c\uba74\uc740 HINT\ub97c \uc368\uc11c \uc774\ub214\ub4e4\uc744
\n\uc778\ub3c4\ud574\uc57c\uc8e0… \uc624\ub298\ub3c4 \uc804\ub3c4\ub97c ^____^
\n\ub9c8\uc9c0\ub9c9\uc73c\ub85c \uc778\ub371\uc2a4\ub97c \uc774\uc6a9\ud55c sort \ud68c\ud53c \ubc29\ubc95\uc5d0 \ub300\ud574\uc11c \uc54c\uc544\ubcf4\uaca0\uc2b5\ub2c8\ub2e4.
\nSQL> select empno, ename, job, comm from emp order by empno –sort \uc815\ub82c
\nSQL> select empno, ename, job, comm from emp where empno >= 0 –sort \uc815\ub82c\uc81c\uac70<\/p>\n
-> \uc778\ub371\uc2a4\ub294 \uac12\uc774 \uc815\ub82c\ub418\uc5b4 \ub4e4\uc5b4\uac00\uc788\ub294 \uad6c\uc870\uc774\uae30 \ub54c\ubb38\uc5d0 \ucc98\uc74c\ubd80\ud130 \uc791\uc740\uac12 \ub9e8 \ub05d\uac12\uc740
\n\ucd5c\ub300\uac12\uc774 \uc788\uaca0\uc8e0. \uc798 \uc0ac\uc6a9\ud558\uba74 \ucd5c\uace0\uc758 \ud29c\ub2dd\uc774 \ub418\uaca0\uc8e0.<\/p>\n
–Optimizer Hint Syntax(Hint \uc0ac\uc6a9)<\/p>\n
—–> SELECT —–> \/*+ hint \uc808*\/ ——->
\nDELETE
\nUPDATE
\n\ub610\ub294
\n—–> SELECT —–> –+ hint \uc808 ——->
\nDELETE
\nUPDATE<\/p>\n
Hint \uc808\uc5d0\ub294 \ub2e4\uc74c \uc870\uac74\ub4e4\uc744 \ubd80\uc5ec\ud560 \uc218 \uc788\ub2e4.<\/p>\n
1. FULL(table_name)
\n: table\uc744 full scan\ud558\uae38 \uc6d0\ud560 \ub54c \uc0ac\uc6a9\ud568.<\/p>\n
2. INDEX(table_name index_name[index])
\n: \ud2b9\uc815 index\ub97c \uc0ac\uc6a9\ud558\ub3c4\ub85d \ud560 \ub54c \uc0ac\uc6a9\ud568.<\/p>\n
3. INDEX_ASC(table_name index_name[index])<\/p>\n
4. INDEX_DESC(table_name index_name[index])<\/p>\n
5. AND_EQUALS(table_name index_name index_name[index])<\/p>\n
6. ROWID(table_name)<\/p>\n
7. CLUSTER(table_name)<\/p>\n
8. HASH(table_name)<\/p>\n
9. ORDERED<\/p>\n
10. USE_MERGE(table_name)<\/p>\n
11. USE_NL(table_name)<\/p>\n
Hint\ub97c \uc0ac\uc6a9\ud558\uc5ec \ud2b9\uc815 index\ub97c \uc0ac\uc6a9\ud55c update\uc758 \uc608<\/p>\n
(\uc774 \uacb0\uacfc\ub294 7.3 \uc774\uc0c1\uc5d0\uc11c \uc81c\uacf5\ub418\ub294 auto trace\ub97c \uc0ac\uc6a9\ud558\ub294 \ubc29\ubc95\uc774\ub098
\nexecution plan \ub610\ub294 tkprof\ub97c \uc774\uc6a9\ud558\uc5ec \ud655\uc778\ud560 \uc218 \uc788\ub2e4.)<\/p>\n
Update \/*+ INDEX (e1 e_job) *\/
\nEmp e1
\nSET sal =
\n(SELECT –+ INDEX (e1 e_dept)
\n(e1.sal + AVG(e2.sal))\/2
\nFROM emp e2
\nWHERE e2.deptno = e1.deptno)
\nWHERE job = ‘CLERK’
\nAND deptno BETWEEN 10 AND 40;<\/p>\n