{"id":3241,"date":"2018-02-04T09:21:41","date_gmt":"2018-02-04T00:21:41","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=3241"},"modified":"2018-02-04T10:04:08","modified_gmt":"2018-02-04T01:04:08","slug":"sql-hint-%eb%a5%bc-%ec%9d%b4%ec%9a%a9%ed%95%98%ec%97%ac-sql-tunning-%ec%9d%84-%ed%95%98%eb%8a%94-%eb%b0%a9%eb%b2%95-2","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=3241","title":{"rendered":"SQL HINT \ub97c \uc774\uc6a9\ud558\uc5ec SQL Tunning \uc744 \ud558\ub294 \ubc29\ubc95"},"content":{"rendered":"

 <\/p>\n

\n\n\n<\/colgroup>\n\n\n\n
SQL \ud78c\ud2b8\uc758 \uc0ac\uc6a9 \ubc29\ubc95<\/strong><\/td>\n<\/tr>\n
\u25cf\u00a0 DBA\ub294 \ud2b9\uc815\ub370\uc774\ud130\uc5d0 \ub300\ud558\uc5ec optimizer\uac00 \uc54c \uc218 \uc5c6\ub294 \uc815\ubcf4\ub97c \uac00\uc9c8 \uc218 \uc788\uc74c.
\n\u25cf\u00a0 DBA\ub294 optimizer\ubcf4\ub2e4 \ub354 \uc88b\uc740 execution plan\uc744 \uc120\ud0dd\ud560 \uc218 \uc788\uc74c.
\n\u25cf\u00a0 DBA\ub294 \ud78c\ud2b8\ub97c \uc0ac\uc6a9\ud558\uc5ec \uac15\uc81c\uc801\uc73c\ub85c \uc0ac\uc6a9\uc790\uac00 \uc120\ud0dd\ud55c execution plan\uc744 \uc0dd\uc131\ud558\ub3c4\ub85d\u00a0 optimizer\uc5d0\uac8c \uc9c0\uc2dc\uac00\ub2a5.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

 <\/p>\n

\n\n\n<\/colgroup>\n\n\n\n\n\n\n
\u222e\ud78c\ud2b8\uc758 \uc0ac\uc6a9<\/strong><\/td>\n<\/tr>\n
\u25cf\u00a0 <\/strong>\ud78c\ud2b8\uc758 \uc0ac\uc6a9\ubc94\uc704<\/strong>
\n\u2015\u00a0 SQL\ubb38\uc744 \uc704\ud55c Optimization approach\u2015\u00a0 SQL\ubb38\uc5d0 \ub300\ud55c Cost-based approach\uc758 goal(best throughput, best response time)\u2015\u00a0 SQL\ubb38\uc5d0 \uc758\ud574 \uc811\uadfc\ub418\ub294 table\uc5d0 \ub300\ud55c access path\u2015\u00a0 join\ubb38\uc5d0 \ub300\ud55c join\uc21c\uc11c<\/p>\n

\u2015\u00a0 join\ubb38\uc744 \ucc98\ub9ac\ud558\uae30 \uc704\ud55c join operation<\/p>\n

 <\/td>\n<\/tr>\n

\u25cf\u00a0 Statement Block<\/strong><\/p>\n

\u2015\u00a0 \uac04\ub2e8\ud55c SELECT , UPDATE, DELETE SQL\ubb38
\n\u2015\u00a0 \ud55c parent SQL\ubb38\ub098 complex SQL\ubb38\u00a0 \ub0b4\uc758 subquery
\n\u2015\u00a0 \ud55c compound query\uc911\uc758 \ud55c \ubd80\ubd84<\/p>\n

\u00b7 UNION operator\uc5d0 \uc758\ud574 \uc870\ud569\ub41c \ub450 \uac1c\uc758 component query\ub85c \uad6c\uc131\ud55c \ud55c compound query\ub294 \ub450 \uac1c\uc758 SQL\ubb38 block\uc744 \uac00\uc9d0. (\uac01 component query\ub97c \uc704\ud574 \ud558\ub098\uc758 SQL\ubb38 block\uc774 \uc0dd\uae40.)
\n\u00b7 \uccab \ubc88\uc9f8 component query\uc5d0 \uc788\ub294 \ud78c\ud2b8\ub294 \uccab \ubc88\uc9f8 component\uc758 optimizer\uc5d0\ub9cc \uc801\uc6a9
\n\u00b7 \ub450 \ubc88\uc9f8 component query\uc5d0 \ub300\ud55c optimizer\uc5d0\ub294 \uc801\uc6a9\ub418\uc9c0 \uc54a\ub294\ub2e4.<\/td>\n<\/tr>\n

\u25cf\u00a0 \ud78c\ud2b8\ub97c \ud3ec\ud568\ud558\ub294<\/strong><\/p>\n

Syntax<\/strong><\/p>\n

<\/p>\n

\u00b7DELETE,
\nSELECT , UPDATE : SQL<\/strong>\ubb38 block\uc744 \uc2dc\uc791\ud558\ub294 DELETE,
\nSELECT , UPDATE \ud0a4\uc6cc\ub4dc\uc785\ub2c8\ub2e4. \ud78c\ud2b8\ub97c \ud3ec\ud568\ud558\ub294 comments\ub294\u00a0 \uc774 \ud0a4\uc6cc\ub4dc\ub4e4\uc774 \ub098\ud0c0\ub09c \ud6c4\uc5d0 \uc368\uc57c\ud569\ub2c8\ub2e4.<\/strong><\/p>\n

 <\/p>\n

\u00b7+ : \uc624\ub77c\ud074\uc774 \uadf8 comment\ub97c \ud574\uc11d\ud560 \uc218 \uc788\uac8c \ud574\uc8fc\ub294 \uae30\ub2a5\uc744\ud569\ub2c8\ub2e4. \uc774 ‘ + ‘\ub294 comment \ud45c\uc2dc\ud6c4\uc5d0 \ube48\uce78(blank)\uc774 \uc5c6\uc774 \uc989\uc2dc \ub530\ub77c\uc640\uc57c\ub9cc \ud569\ub2c8\ub2e4.<\/strong><\/p>\n

 <\/p>\n

\u00b7\ud78c\ud2b8 : \uc774\uc808\uc5d0\uc11c\uc5b8\uae09\ub418\ub294 \ud78c\ud2b8\uc911\uc758 \ud558\ub098\uc785\ub2c8\ub2e4. \ub9cc\uc57d comment\uac00 \uc5ec\ub7ec \ud78c\ud2b8\ub97c \uac00\uc9c4\ub2e4\uba74\uadf8 \ud78c\ud2b8\ub4e4\uc740 \ucd5c\uc18c\ud55c \ud558\ub098\uc758 \uacf5\uac04\uc73c\ub85c\uc11c \ubd84\ub9ac\ub418\uc5b4 \uc788\uc5b4\uc57c\ub9cc \ud569\ub2c8\ub2e4. <\/strong><\/p>\n

 <\/p>\n

\u00b7text : \ud78c\ud2b8\uc5d0 \ub300\ud55c \uc124\uba85<\/strong><\/td>\n<\/tr>\n

\u25cf\u00a0 <\/strong>\ud78c\ud2b8\ub97c\u00a0<\/strong>\ubb34\uc2dc\ud558\ub294 \uacbd\uc6b0<\/strong>
\n\u2015\u00a0 \ud78c\ud2b8\uac00 \uc4f0\uc778 comment\uac00 DELETE,
\nSELECT , UPDATE \ud0a4\uc6cc\ub4dc \ub4a4\uc5d0 \uc624\uc9c0 \uc54a\uace0, \ub2e4\ub978 \uacf3\uc5d0 \uc4f0\uc5ec \uc788\ub294\uacbd\uc6b0
\n\u2015\u00a0 \ud78c\ud2b8\uac00 Syntax error\ub97c \uac00\uc9c4 \uacbd\uc6b0\u00a0(\ub3d9\uc77c\ud55c comment\ub0b4\uc5d0 error\ub97c \uac00\uc9c0\uc9c0 \uc54a\uace0 \uc62c\ubc14\ub85c \ud45c\ud604\ub41c \ub2e4\ub978 \ud78c\ud2b8\ub4e4\uc740 \uc778\uc815)
\n\u2015\u00a0 \ucda9\ub3cc\ud55c \ud78c\ud2b8\uc758 \uc870\ud569\ub4e4\uc740 \ubb34\uc2dc(\ud55c comment\ub0b4\uc5d0 \ub450 \uac1c \uc774\uc0c1\uc758 \ud78c\ud2b8\uac00 \uc788\uc744 \uacbd\uc6b0, Optimizer\uac00 \uc5b4\ub5a4 \ud78c\ud2b8\ub97c \uba3c\uc800 \uc368\uc57c\ud558\ub294\uc9c0 \ubaa8\ub974\ub294 \uacbd\uc6b0)(\ub3d9\uc77c\ud55c comment\ub0b4\uc5d0 \ucda9\ub3cc\ud55c \ud78c\ud2b8\uc678\uc5d0 \ub2e4\ub978 \ud78c\ud2b8\ub294 \uc778\uc815.)
\n\u00b7 \uc624\ub77c\ud074\uc740 SQL*Forms Version 3 Trigger \uc640 \uac19\uc740 PL\/SQL Version 1 \uc744 \uc0ac\uc6a9\ud558\ub294 \ud658\uacbd\uc5d0\uc11c\ub294\ubaa8\ub4e0 SQL \ubb38\uc5d0 \uc788\ub294 \ud78c\ud2b8\ub4e4\uc744 \ubb34\uc2dc.\u00b7 Optimizer\ub294 cost-based approach\ub97c \uc0ac\uc6a9\ud558\ub294 \uacbd\uc6b0\uc5d0 \ud78c\ud2b8\ub97c\uc778\uc2dd.<\/p>\n

\u00b7 \ud55c SQL\ubb38 block\uc5d0 RULE \ud78c\ud2b8\ub97c \uc81c\uc678\ud55c \uc5b4\ub5a4 \ud78c\ud2b8\ub4e4\uc774 \ud3ec\ud568\ub418\uc5b4 \uc788\uc73c\uba74, optimizer \ub294 \uc790\ub3d9\uc801\uc73c\ub85c cost-based approach\ub97c \uc0ac\uc6a9.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n\n\n<\/colgroup>\n\n\n\n\n\n
<\/td>\n<\/tr>\n
\uc635\ud2f0\ub9c8\uc774\uc800\ub97c \uc704\ud55c \ud78c\ud2b8\uc640 \uc0ac\uc6a9 \ubaa9\ud45c<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 \ud78c\ud2b8\ub294 cost-based approach\uc640 rule-based optimization approach \uc911\uc5d0 \ud558\ub098\ub97c \uc120\ud0dd<\/p>\n

\u2015\u00a0 cost-based approach\ub97c \uc120\ud0dd\ud55c \uacbd\uc6b0\uc5d0\ub294 best throughput\uc640 best reponse time\uc0ac\uc774\uc5d0 \ud558\ub098\ub97c \uc120\ud0dd<\/p>\n

\u2015\u00a0 SQL\ubb38\uc774 optimization approach \uc640 goal\uc744 \uae30\uc220\ud55c \ud55c\uac1c\uc758 \ud78c\ud2b8\ub97c \uac00\uc9c4\ub2e4\uba74, optimizer\ub294 statistics\uc758 \uc874\uc7ac\uc5ec\ubd80\uc640 ALTER SESSSION\uba85\ub839\uc5d0 \uc788\ub294 OPTIMIZER_GOAL\uc640 OPIMIZER_MODE \ucd08\uae30 \ud30c\ub77c\uba54\ud0c0\uc758 \uac12\uacfc\ub294 \uc0c1\uad00\uc5c6\uc774 \uae30\uc220\ub41c approach\ub97c \uc0ac\uc6a9.<\/td>\n<\/tr>\n

<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222e\u00a0ALL_ROWS<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 ALL_ROWS\u00a0<\/strong>\ud78c\ud2b8\ub294 best throughput(minimum total resource consumption)\uc744 \ubaa9\uc801\uc73c\ub85c SQL\ubb38 block\uc744 \ucd5c\uc801\ud654\ud558\uae30 \uc704\ud574 cost-based approach\ub97c \uc120\ud0dd
\n\u2015\u00a0 optimizer\ub294 best throughput\uc744 \ubaa9\uc801\uc73c\ub85c SQL\ubb38\ub97c \ucd5c\uc801\ud654
\nSELECT \u00a0<\/strong>\/*+ ALL_ROWS *\/ empno, ename, sal, job \u00a0FROM emp WHERE empno = 566; <\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222e\u00a0FIRST_ROWS<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 FIRST_ROWS\u00a0<\/strong>\ud78c\ud2b8\ub294 best response time\uc744 \ubaa9\uc801\uc73c\ub85c SQL\ubb38 block\uc744 \ucd5c\uc801\ud654\ud558\uae30 \uc704\ud574 cost-based approach\ub97c \uc120\ud0dd.(minimum resource usage to return first row)
\n\u2015\u00a0 \uc774 \ud78c\ud2b8\ub294 \uc544\ub798 \ub0b4\uc6a9\uc744 \uc774\ud589\ud560 \uc218 \uc788\ub294 optimizer\ub97c \uc0dd\uc131
\n\u00b7 Index scan\uc744 \uc4f8\uc218 \uc788\ub2e4\uba74, optimizer\ub294 full table scan\ubcf4\ub2e4\ub294 Index scan\uc744 \uc0ac\uc6a9.
\n\u00b7 Index scan\uc744 \uc4f8\uc218 \uc788\ub2e4\uba74, optimizer\ub294 \uc5f0\uad00\ub41c table\uc774 nested loop\uc758 inner table\uc77c \ub54c\ub9c8\ub2e4, sort-merge join\ubcf4\ub2e4\ub294 nested loops join\uc744 \uc120\ud0dd.
\n\u00b7 Index scan\uc774 ORDER BY\uc808\uc5d0 \uc758\ud574 \uc4f0\uc5ec\uc9c0\uba74, optimizer\ub294 sort operation\uc744 \ud53c\ud558\uae30 \uc704\ud574 index scan\uc744 \uc120\ud0dd.
\n\u2015\u00a0 optimizer\ub294 best response time\uc744 \ubaa9\uc801\uc73c\ub85c \uc544\ub798 SQL\ubb38\ub97c \ucd5c\uc801\ud654
\nSELECT \u00a0<\/strong>\/*+ FIRST_ROWS *\/ empno, ename, sal, job FROM emp WHERE empno = 566;<\/strong>
\n\u2015\u00a0 Optimizer\ub294 \uc544\ub798 Syntax\ub97c \uac00\uc9c0\ub294 DELETE\uc640 UPDATE SQL\ubb38 blocks\uacfcSELECT SQL\ubb38 block \uc5d0 \uc788\ub294 \ud78c\ud2b8\ub294 \ubb34\uc2dc.\u00b7 set operators(UNION, INTERSECT, MINUS, UNION ALL) <\/strong>\u00b7 GROUP BY<\/strong>\uc808<\/strong>\u00b7FOR UPDATE <\/strong>\uc808<\/strong>\u00b7group functions<\/strong>\u00b7DISTINCT operator<\/strong>
\n\u2015\u00a0 \uc774\ub4e4 SQL\ubb38\ub294 best response\ub97c \ubaa9\uc801\uc73c\ub85c \ucd5c\uc801\ud654\ub420 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4.
\n\u2015\u00a0 \uc704\uc758 \uacbd\uc6b0 \uccab \ubc88\uc9f8 row\ub97c \ubc18\ud658\ud558\uae30 \uc804\uc5d0 SQL\ubb38\uc5d0 \uc811\uadfc\ub418\ub294\u00a0 \ubaa8\ub4e0 \ud589\ub4e4\uc744 retrieve\ud574\uc57c\ub9cc\ud568.
\n\u2015\u00a0 \uc774\ub7f0 SQL\ubb38\uc5d0 \ub300\ud574 \ud78c\ud2b8\ub97c \uc4f0\uba74 optimizer\ub294 cost-based approach\ub97c\u00a0 \uc0ac\uc6a9\ud558\uace0, best throughput\uc744 \ubaa9\uc801\uc73c\ub85c \ucd5c\uc801\ud654.
\n\u2015\u00a0 SQL\ubb38\uc5d0 ALL_ROWS\ub098 FIRST_ROWS \ud78c\ud2b8\ub97c \uae30\uc220\ud558\uace0, data dictionary\uac00 \uadf8 SQL\ubb38\uc5d0\uc758\ud574 \uc811\uadfc\ub418\ub294 table\uc5d0 \ub300\ud574 \uc5b4\ub5a4 statistics\ub3c4 \ud3ec\ud568\ud558\uc9c0 \uc54a\ub294\ub2e4\uba74, optimizer\ub294 \ub0b4\ubd80\uc801\uc73c\ub85c execution plan\uc744 \uc120\ud0dd\ud558\uace0, missing statistics\ub97c \ucd94\uc815\ud560 default statistics \uac12\uc744 \uc0ac\uc6a9.
\n\u2015\u00a0 \uc774 \ucd94\uc815\uce58(estimates)\ub294 ANALYZE\uba85\ub839\uc5d0 \uc758\ud574 \uc0dd\uc131\ub418\uc5b4\uc9c4 \uac83\ub9cc\ud07c \uc815\ud655\ud558\uc9c0\ub294 \uc54a\uc74c.
\n\u2015\u00a0 ANALYZE\uba85\ub839\uc744 \uc0ac\uc6a9\ud558\uc5ec Cost-based optimization\uc744 \uc0ac\uc6a9\ud558\ub294 SQL\ubb38\uc5d0 \uc758\ud574 \uc811\uadfc\ub418\ub294\ubaa8\ub4e0 table\uc5d0 \ub300\ud55c statistics\ub97c \uc0dd\uc131\ud574\uc57c \ud568.
\n\u2015\u00a0 Access path\ub97c \uc704\ud55c \ud78c\ud2b8 \ub610\ub294 ALL_ROWS\ub098 FIRST_ROWS \ud78c\ud2b8\ub97c \uac00\uc9c0\ub294 join operation\uc744 \uae30\uc220\ud569\ub2c8\ub2e4\uba74, optimizer\ub294 \ud78c\ud2b8\uc5d0 \uc758\ud574 \uae30\uc220\ub41c join operation\ub4e4\uacfc access paths\ub97c \uc6b0\uc120\uc801\uc73c\ub85c \ucde8\ud568.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\uc2e4\ud589 \uc21c\uc11c \ubc29\ubc95\uc744 \uc704\ud55c SQL \ud78c\ud2b8<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 \uac01 \ud78c\ud2b8\ub4e4\uc740 table\uc5d0 \ub300\ud55c access method\ub97c \uc81c\uc548
\n\u2015\u00a0 \ud78c\ud2b8\uc911\uc758 \ud558\ub098\ub97c \uae30\uc220\ud558\ub294 \uac83\uc740 access path\uac00 index \ub098 \ud074\ub7ec\uc2a4\ud130\uc640 SQL\ubb38\uc758 \uc758\ubbf8\uad6c\uc870\uc758\uc874\uc7ac\ub97c \uae30\ubcf8\uc801\uc73c\ub85c \uc774\uc6a9\ud560\uc218 \uc788\ub2e4\uba74 \uae30\uc220\ub41c access path\ub97c \uc120\ud0dd
\n\u2015\u00a0 \ud78c\ud2b8\uac00 access path\ub97c \uc774\uc6a9\ud560\uc218 \uc5c6\ub2e4\uba74 optimizer\ub294\u00a0 \uadf8\uac83\uc744 \ubb34\uc2dc.
\n\u2015\u00a0 SQL\ubb38\uc5d0\uc11c \uc815\ud655\ud558\uac8c access\ub418\ub294 table\uc744 \uae30\uc220
\n\u2015\u00a0 SQL\ubb38\uc774 table\uc5d0 \ub300\ud55c alias\ub97c \uc0ac\uc6a9\ud558\uba74, \ud78c\ud2b8\uc5d0\uc11c table\uc758 \uc774\ub984\ubcf4\ub2e4\ub294 alias\ub97c \uc0ac\uc6a9.
\n\u2015\u00a0 table\uc758 \uc774\ub984\uc774\ub098 alias \ub294 local database\uc5d0 \uc788\ub294 \ud55c table\uc5d0 \ub300\ud55c \ud558\ub098\uc758 synonym\uc774\ub098\ud558\ub098\uc758 table\uc744 \uc758\ubbf8\ud568.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222e\u00a0FULL<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 FULL\u00a0<\/strong>\ud78c\ud2b8\ub294 table\uc5d0 \ub300\ud574 full table scan\uc744 \uc120\ud0dd
\n\u2015\u00a0 FULL \ud78c\ud2b8\uc758 \ubb38\ubc95\uc740\u00a0FULL(table) <\/strong>
\n\u2015\u00a0 (table)<\/strong>\uc5d0\ub294 full table scan\uc744 \uc218\ud589\ud558\ub294 table\uc758 alias\ub098 name\uc744 \uae30\uc220\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 \uc608 : ACCOUNT table\uc5d0 WHERE\uc808\uc758 \uc870\uac74\uc5d0 \uc758\ud574 \uc0ac\uc6a9\uac00\ub2a5\ud55c ACCNO \uce7c\ub7fc\uc5d0 \ub300\ud55c index\uac00\uc788\uc74c\uc5d0\ub3c4 \ubd88\uad6c\ud558\uace0, \uc624\ub77c\ud074\uc740 \uc774 SQL \ubb38\uc744 \uc2e4\ud589\ud560 ACCOUNTS table\uc5d0 full table scan\uc744 \uc218\ud589.
\nSELECT \u00a0<\/strong>\/*+ FULL(a) Don’t use the index on ACCNO *\/ accno, bal FROM accounts a\u00a0\u00a0 WHERE accno = 7086854;<\/strong>\u25a0 NOTE<\/strong>\u00b7\u00a0 ACCONTStable\uc774 alias A\ub97c \uac00\uc9c0\uae30 \ub54c\ubb38\uc5d0 \ud78c\ud2b8\ub294 table\uc758 \uc774\ub984\uc774 \uc544\ub2cc alias\ub85c table\uc744\ud45c\ud604.\u00b7\u00a0 FROM \uc808\uc5d0 table\uc758 \uc774\ub984\uc774 \uae30\uc220\ub418\uc5c8\uc74c\uc5d0\ub3c4 \ubd88\uad6c\ud558\uace0, \ud78c\ud2b8\uc5d0\uc11c \ub294 schema names\uc744 \uae30\uc220\ud558\uc9c0 \uc54a\ub294\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eROWID<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 ROWID<\/strong>\ud78c\ud2b8\ub294 table\uc5d0\ub300\ud574 ROWID\uc5d0\uc758\ud55c table scan\uc744\uc120\ud0dd
\n\u2015\u00a0 ROWID \ud78c\ud2b8\uc758\ubb38\ubc95\uc740ROWID(table)<\/strong>
\n\u2015\u00a0 (table)<\/strong>\uc5d0\ub294 ROWID\uc5d0\uc758\ud55c table scan\uc774\uc774\ud589\ub418\uc5b4\uc9c0\ub294 table\uc758 alias\ub098\uc774\ub984\uc744\uae30\uc220.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eCLUSTER<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 CLUSTER<\/strong>\ud78c\ud2b8\ub294 table\uc5d0\ub300\ud574 cluster scan\uc744\uc120\ud0dd
\n\u2015\u00a0 CLUSTER \ud78c\ud2b8\uc758\ubb38\ubc95\uc740CLUSTER(table)<\/strong>
\n\u2015\u00a0 (table)<\/strong>\uc5d0\ub294 cluster scan\uc5d0\uc758\ud574\uc811\uadfc\ub418\ub294 table\uc758\uc774\ub984\uc774\ub098 alias\ub97c\uae30\uc220.SELECT –+ CLUSTER emp, ename, deptnoFROM emp, deptWHERE deptno = 10 AND emp.deptno = dept.deptno;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eHASH<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 HASH<\/strong>\ud78c\ud2b8\ub294 table\uc5d0\ub300\ud574 HASH scan\uc744\uc120\ud0dd
\n\u2015\u00a0 HASH \ud78c\ud2b8\uc758\ubb38\ubc95\uc740 HASH(table)<\/strong>
\n\u2015\u00a0 (table)<\/strong>\uc5d0\ub294 hash scan\uc5d0\uc758\ud574\uc811\uadfc\ub418\ub294 table\uc758\uc774\ub984\uc774\ub098 alias\ub97c\uae30\uc220<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eINDEX<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 INDEX<\/strong>\ud78c\ud2b8\ub294 table\uc5d0\ub300\ud574 index scan\uc744\uc120\ud0dd<\/p>\n

\u2015\u00a0 INDEX \ud78c\ud2b8\uc758\ubb38\ubc95\uc740<\/p>\n

<\/p>\n

table<\/strong> : scan\ub420 index\uc640\uad00\ub828\uc788\ub294 table\uc758\uc774\ub984\uc774\ub098 alias\ub97c\uae30\uc220<\/p>\n

index<\/strong> : index scan\uc774\uc218\ud589\ub420 index\ub97c\uae30\uc220<\/p>\n

\u2015\u00a0 \ud78c\ud2b8\ub294\ud558\ub098\uc774\uc0c1\uc758 indexes\ub4e4\uc744\uae30\uc220
\n\u00b7 \ud78c\ud2b8\uac00\ud558\ub098\uc758\uc0ac\uc6a9\uac00\ub2a5\ud55c index\ub97c\uae30\uc220\ud569\ub2c8\ub2e4\uba74, optimizer\ub294 index\uc5d0\uc11c\ud55c\uac1c\uc758 scan\uc744\uc218\ud589.
\n\u00b7 optimizer\ub294 full table scan\uc774\ub098 table\uc5d0\uc788\ub294\ub2e4\ub978 index\uc5d0\ub300\ud55c scan\uc740\uc218\ud589\ud558\uc9c0\uc54a\uc74c.
\n\u00b7 \ud78c\ud2b8\uac00\uc0ac\uc6a9\uac00\ub2a5\ud55c index\uc758\ub9ac\uc2a4\ud2b8\ub97c\uae30\uc220\ud569\ub2c8\ub2e4\uba74, optimizer\ub294\ub9ac\uc2a4\ud2b8\uc5d0\uc788\ub294\uac01\uac01\uc758 index\uc5d0\ub300\ud55c scan\uc744\ud558\ub294\ub370\ub4dc\ub294\ube44\uc6a9\uc744\uace0\ub824\ud55c\ud6c4\uc5d0\uac00\uc7a5\uc801\uc740\ube44\uc6a9\uc774\ub4dc\ub294 index scan\uc744\uc774\ud589
\n\u00b7 \uc774 access path\uac00\ucd5c\uc800\uc758\ube44\uc6a9\uc744\uac00\uc9c4\ub2e4\uba74 optimizer\ub294\uc774\ub9ac\uc2a4\ud2b8\ub85c\ubd80\ud130\uc5ec\ub7ec index\ub97c scan\ud558\uace0\uadf8\uacb0\uacfc\ub4e4\uc744 merge.
\n\u00b7 optimizer\ub294 full table scan\uc774\ub098\ud78c\ud2b8\uc5d0\uc788\uc9c0\uc54a\uc740 index scan\uc740\uace0\ub824\ud558\uc9c0\uc54a\uc74c.
\n\u00b7 \ud78c\ud2b8\uac00\uc5b4\ub5a4 index\ub3c4\uae30\uc220\ud558\uc9c0\uc54a\uc558\ub2e4\uba74, optimizer\ub294 table\uc5d0\uc788\ub294\uc0ac\uc6a9\uac00\ub2a5\ud55c index\ub97c\uac01\uac01 scan\ud55c\ube44\uc6a9\uc744\uace0\ub824\ud55c\ud6c4\uc5d0 lowest cost\ub97c\uac00\uc9c4 index scan\uc744\uc218\ud589.
\n\u00b7 \uc774 access path\uac00\ucd5c\uc800\ube44\uc6a9\uc744\uac00\uc9c4\ub2e4\uba74 optimizer\ub294 muliple index\ub97c scan\ud558\uace0\uadf8\uacb0\uacfc\uac12\uc744 merge.<\/p>\n

SELECT name, height, weightFROM patientsWHERE sex=’M’\u00b7\u00a0 sex\uc758\uc5f4\uc740 index\ub418\uc5b4\uc788\uace0, \uc774\uce7c\ub7fc\uc740 ‘M’\uacfc ‘F’\uc758\uac12\uc744\uac00\uc9d0.\u00b7\u00a0 \ubcd1\uc6d0\uc5d0\ub0a8\uc790\uc758\uc218\uc640\uc5ec\uc790\uc758\uc218\uac00\ub3d9\uc77c\ud558\ub2e4\uba74, \uc774\uc9c8\uc758\ub294\uc5f0\uad00\ub41c table\uc758\ud589\uc758\ucd5c\ub2e4\ud37c\uc13c\ud2b8\ub97c\ubc18\ud658\ud558\uace0, full table scan\uc774 index scan\ubcf4\ub2e4\ub294\ub354\ube60\ub974\uac8c\ub429\ub2c8\ub2e4.\u00b7\u00a0 \ubcd1\uc6d0\uc758\ud658\uc790\uc911\ub0a8\uc790\uc758\ube44\uc728\uc774\ub9e4\uc6b0\uc801\ub2e4\uba74, \uc9c8\uc758\ub294\uad00\ub828\ub41c table\uc758\ud589\uc5d0\ub300\ud574\uc801\uc740\ube44\uc728\uc744\ubc18\ud658\ud558\uace0, \uc774\uacbd\uc6b0\uc5d0\ub294 index scan\uc774 full table scan\ubcf4\ub2e4\ub354\ube60\ub985\ub2c8\ub2e4.
\n\u2015\u00a0 \uac01 disinct column value\uc758\ubc1c\uc0dd\uc218\ub294 optimizer\uc5d0\uac8c\ubcc4\ub85c\uc720\uc6a9\ud558\uc9c0\uc54a\ub2e4(\ub3c4\uc6c0\uc774\ub418\uc9c0\uc54a\ub294\ub2e4.)\u2015\u00a0cost-based approach\ub294\uac01\uac01\uc758\uac12\ub4e4\uc774\uac01\uac01\uc758\ud589\uc5d0\uc11c\ub098\ud0c0\ub098\ub294\ube48\ub3c4\uc218\uac00\ub3d9\uc77c\ud558\ub2e4\uace0\uac00\uc815\uc744\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 \ud55c\uce7c\ub7fc\uc774\ub2e8 2\uac1c\uc758\ub2e4\ub978\uac12\ub4e4(distinct values)\uc744\uac00\uc9c4\ub2e4\uba74 optimizer\ub294\uadf8\ub450\uac12\ub4e4\uc774\uac01\uac01 row\uc758 50%\ub85c\uc815\ub3c4\ub098\ud0c0\ub09c\ub2e4\uace0\uac00\uc815\ud569\ub2c8\ub2e4\uadf8\ub798\uc11c cost-based approcah\ub294 index scan\ubcf4\ub2e4\ub294 full table scan\uc744\uc120\ud0dd\ud558\uace4\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 WHERE\uc808\uc5d0\uc788\ub294\uac12\uc774\ubaa8\ub4e0 row\uc5d0\ub300\ud574\ub9e4\uc6b0\uc801\uc740\ud37c\uc13c\ud2b8\ub97c\uac00\uc9c4\ub2e4\uba74, \ud78c\ud2b8\uc5d0 index scan\uc744\uc0ac\uc6a9\ud558\uc5ec optimizer\uac00\uac15\uc81c\ub85c index scan\uc744\uc0ac\uc6a9\ud558\uac8c\ud560\uc218\uc788\uc2b5\ub2c8\ub2e4.
\n\u2015\u00a0 \uc544\ub798\ubb38\uc7a5\uc5d0\uc11c INDEX \ud78c\ud2b8\ub294 SEX_INDEX\uc5d0\ub300\ud574 index scan\uc744\uc120\ud0dd\ud569\ub2c8\ub2e4.\u00b7<\/p>\n

SELECT \/*+ INDEX(patients sex_index) Use SEX_INDEX, since there are few male patients *\/name, height, weightFROM patientsWHERE sex = ‘M’;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eFULL <\/strong>\ud78c\ud2b8\uc640 INDEX \ud78c\ud2b8\uc758\ube44\uad50\uc608\uc81c<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 1. EMPtable\uc758\ucd1d\uac74\uc218\ub294 10,000\uac74, DEPTtable\uc758\ucd1d\uac74\uc218\ub294 2400\uac74.
\n\u2015\u00a0 2. EMPtable\uc5d0\uc11c empno, ename, sal\uc744
\nSELECT
\n\u2015\u00a0 3. WHERE\uc808\uc758\uc870\uac74\uc740 JOB \ud544\ub4dc\uc758 ‘SALESMAN’\uac12\uc744\uac80\uc0ac
\n\u2015\u00a0 4. JOB_INDEX<\/strong>\uc874\uc7ac.
\n\u2015\u00a0 5. JOB\ud544\ub4dc\uc5d0\ub294 2\uac1c\uc758\uac12\uc774\uc874\uc7ac(MANAGER-(9,751\uac74\/10,000\uac74), SALESMAN-(249\uac74\/10,000\uac74))\u00b7\u00a0 EMP, DEPT table\uc744ANALYZE<\/strong>\ud568.\u00b7\u00a0 sql_trace\ub97c true\ub85c\u00b7\u00a0 OPTIMIZER_GOAL \uc740ALL_ROWS<\/strong>\ub85c
\n\u2015\u00a0 \uc608\uc81c 1analyze table emp estimate statistics;analyze table dept estimate statistics;<\/strong>alter session set sql_trace = true;alter session set optimizer_goal = all_rows;<\/strong>SELECT empno, ename, salfrom empwhere job = ‘SALESMAN’;SELECT \/*+ full(emp) *\/ empno, ename, salfrom empwhere job = ‘SALESMAN’;<\/p>\n

SELECT \/*+ index(emp job_index) *\/ empno, ename, salfrom empwhere job = ‘SALESMAN’;<\/p>\n

\u00b7\u00a0 OPTIMIZER_GOAL \uc740FIRST_ROWS<\/strong>\ub85c
\n\u2015\u00a0 \uc608\uc81c 2analyze table emp estimate statistics;analyze table dept estimate statistics;<\/strong>alter session set sql_trace = true;alter session set optimizer_goal = first_rows;<\/strong>SELECT empno, ename, salfrom empwhere job = ‘SALESMAN’;<\/p>\n

SELECT \/*+ full(emp) *\/ empno, ename, salfrom empwhere job = ‘SALESMAN’;<\/p>\n

SELECT \/*+ index(emp job_index) *\/ empno, ename, salfrom empwhere job = ‘SALESMAN’;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222e\u00a0INDEX_ASC<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 INDEX_ASC<\/strong>\ud78c\ud2b8\ub294 table\uc5d0\ub300\ud55c index scan\uc744\uc120\ud0dd
\n\u2015\u00a0 \uc774 SQL\ubb38\uc774 index range scan\uc744\uc0ac\uc6a9\ud569\ub2c8\ub2e4\uba74, Oracle\uc740 index\ub41c\uac12\ub4e4\uc744\uc624\ub984\ucc28\uc21c\uc73c\ub85c\uc815\ub82c\ud55c index entry\ub4e4\uc744 scan.<\/p>\n

\u2015\u00a0 INDEX_ASC \ud78c\ud2b8\uc758\ubb38\ubc95\uc740<\/p>\n

<\/p>\n

\u2015\u00a0 \uac01\ud30c\ub77c\uba54\ud0c0\ub294 INDEX \ud78c\ud2b8\uc5d0\uc11c\uc640\uac19\uc740\ubaa9\uc801\uc744\uc758\ubbf8.<\/p>\n

\u2015\u00a0 range scan\uc5d0\ub300\ud574\uc11c\uc624\ub77c\ud074\uc758 default behavior\ub294 index\ub41c\uac12\uc5d0\ub300\ud574\uc624\ub984\ucc28\uc21c\uc73c\ub85c\uc815\ub82c\ud558\uace0\uadf8 index entry\ub4e4\uc744 scan\ud558\ub294\uac83\uc774\ubbc0\ub85c\uc774\ud78c\ud2b8\ub294\uc77c\ubc18\uc801\uc73c\ub85c index \ud78c\ud2b8\ubcf4\ub2e4\ub354\ub098\uc740\uc810\uc740\uc5c6\uc2b5\ub2c8\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222e\u00a0INDEX_DESC<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 INDEX_DESC<\/strong>\ud78c\ud2b8\ub294 table\uc5d0\ub300\ud574 index scan\uc744\uc120\ud0dd<\/p>\n

\u2015\u00a0 \ub9cc\uc57d SQL \ubb38\uc774 index range scan\uc744\uc0ac\uc6a9\ud569\ub2c8\ub2e4\uba74, \uc624\ub77c\ud074\uc740 index\ub41c\uac12\ub4e4\uc744\ub0b4\ub9bc\ucc28\uc21c\uc73c\ub85c\uc815\ub82c\ud55c index entry\ub4e4\uc744 scan.<\/p>\n

\u2015\u00a0 INDEX_desc \ud78c\ud2b8\uc758\ubb38\ubc95\uc740<\/p>\n

 <\/p>\n

<\/p>\n

\u2015\u00a0 \uac01\ud30c\ub77c\uba54\ud0c0\ub294 INDEX \ud78c\ud2b8\uc5d0\uc11c\uc640\uac19\uc740\ubaa9\uc801\uc744\uc758\ubbf8.
\n\u2015\u00a0 \uc774\ud78c\ud2b8\ub294 table\ubcf4\ub2e4 INDEX\ub97c\ub354\ub9ce\uc774\uc811\uadfc\ud558\ubbc0\ub85c SQL\ubb38\uc5d0\uc601\ud5a5\uc744\uc8fc\uc9c0\uc54a\uc74c.
\n\u2015\u00a0 \uc544\ub798\uc758 SQL\ubb38\uc740 index\ub41c\uac12\uc5d0\ub300\ud574\uc11c\ud56d\uc0c1\uc624\ub984\ucc28\uc21c\uc73c\ub85c\uc815\ub82c\ub41c index range scan\uc744\uc218\ud589<\/p>\n

CREATE TABLE tank_readings (time DATE CONSTAINT un_time UNIQUE,temperature NUMBER);<\/strong><\/p>\n

\u2015\u00a0 table\uc758\uac01\ud589\ub4e4\uc740\ud55c\uc2dc\uc810\uc5d0\uc11c\uc2dc\uac04\uacfc\uc628\ub3c4\ub97c\uc800\uc7a5.
\n\u2015\u00a0 TIME\uce7c\ub7fc\uc5d0\ub300\ud574 UNIQUE \uc81c\uc57d\uc744\uc8fc\uba74 table\uc774\ub3d9\uc77c\ud55c\uc2dc\uc810\uc5d0\uc11c\ud55c\ubc88\ub9cc\ub0b4\uc6a9\uc744\uc77d\ub3c4\ub85d\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 \uc624\ub77c\ud074\uc740 TIME \uce7c\ub7fc\uc5d0\uac15\uc81c\ub85c index\ub97c\uc218\ud589.
\n\u2015\u00a0 \ud2b9\ubcc4\ud55c T\uc2dc\uac04\uc5d0\ub300\ud574\uc11c\uc77d\uc740\uac00\uc7a5\ucd5c\uadfc\uc758\uc628\ub3c4\ub97c SELECT \ud558\ub294 complex query\ub97c\uc0dd\uac01\ud574\ubcf4\uc790.
\n\u2015\u00a0 Subquery\ub294\uc628\ub3c4\ub97c\uc77d\ub294\uc2dc\uc810 T\ub098 T\uc774\uc804\uc758\uac00\uc7a5\ucd5c\uadfc\uc2dc\uac04\uc758\uac12\uc744\ubc18\ud658.
\n\u2015\u00a0 Parent query\ub294\uadf8\uc2dc\uac04\uc5d0\ub300\ud55c\uc628\ub3c4\ub97c\ucc3e\ub294\ub2e4.<\/p>\n


\nSELECT <\/strong>temperatureFROM tank_readingsWHERE time = (<\/strong>SELECT MAX(time)<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM tank_readingsWEHRE time <= TO_DATE(:t) );<\/strong><\/p>\n

 <\/p>\n

\u2015\u00a0 \uc704 SQL\ubb38\uc5d0\ub300\ud55c execution plan\uc740\uc544\ub798\uadf8\ub9bc\uacfc\uac19\uc2b5\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

<\/p>\n

\u2015\u00a0 \uc704 SQL\ubb38\uc7a5\uc744\uc2e4\ud589\uc2dc\ud0a4\uba74, \uc624\ub77c\ud074\uc740\uc544\ub798\uc640\uac19\uc740 operations\ub97c\uc2e4\ud589.<\/p>\n

 <\/p>\n

\u00b7 step 4<\/strong>\uc640 3\uc740 subquery\ub97c\uc2e4\ud589.<\/strong><\/p>\n

– step 4\ub294\uc2dc\uac04 T\uc640\uac19\uac70\ub098\ub354\uc801\uc740\ubaa8\ub4e0 TIME \uac12\ub4e4\uc744\ubc18\ud658\ud560 UN_TIME index\uc758 range scan\uc744\uc218\ud589.<\/p>\n

– step 3\ub294 step 4\ub85c\ubd80\ud130\ucd5c\ub300 TIME\uac12\uc744\uc120\ud0dd\ud558\uace0\uadf8\uac12\uc744\ubc18\ud658.\u00a0\u00b7step 2 <\/strong>\uc640 1\uc740 parent query\ub97c\uc2e4\ud589.<\/strong><\/p>\n

– step 2\ub294 step 3\uc5d0\uc758\ud574\ubc18\ud658\ub41c TIME \uac12\uc5d0\ub9de\ub294 UN_TIME index\uc758 unique scan\uc744\uc218\ud589\ud558\uace0\uad00\ub828\ub41c ROWID\ub97c\ubc18\ud658.<\/p>\n

– step 1\uc740 step 2\uc5d0\uc758\ud574\ubc18\ud658\ub41c ROWID\ub97c\uc0ac\uc6a9\ud558\uc5ec TANK_READING table\uc744\uc811\uadfc\ud558\uace0 TEMPERATURE \uac12\uc744\ubc18\ud658.<\/p>\n

 <\/p>\n

\u2015\u00a0Step 4\uc5d0\uc11c\uc624\ub77c\ud074\uc740\uc624\ub984\ucc28\uc21c\uc73c\ub85c\uc815\ub82c\ub41c index\uc5d0\uc788\ub294 TIME \uac12\uc744 scan.
\n\u2015\u00a0 \uc624\ub77c\ud074\uc740\uccab\ubc88\uc9f8 TIME\uac12\uc774 T\ubcf4\ub2e4\ub354\ud070\uacbd\uc6b0\uc5d0 scaning\uc744\uc911\uc9c0\ud558\uace0\uadf8\ud6c4\uc5d0 step 3\uc5d0\uc11c T\uac12\uacfc\uac19\uac70\ub098\ub354\uc801\uc740\ubaa8\ub4e0\uac12\uc744\ubc18\ud658.
\n\u2015\u00a0 INDEX_DESC \ud78c\ud2b8\ub97c\uc0ac\uc6a9\ud558\uba74 index\ub85c\ubd80\ud130\ub2e8\ud558\ub098\uc758 TIME\uac12\uc744\uc77d\uc5b4\uc624\ub294\uc9c8\uc758\uc0ac\uc6a9\uac00\ub2a5.<\/p>\n


\nSELECT <\/strong>\/*+ INDEX_DESC(tank_readings un_time) *\/ temperature FROM tank_readings WHERE time <= TO_DATE(:t) AND ROWNUM = 1;ORDER BY time DESC;<\/strong><\/p>\n

\u2015\u00a0 \uc774\uac83\uc758 execution plan\uc744\uc544\ub798\uadf8\ub9bc\uacfc\uac19\uc2b5\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

<\/p>\n

\u00b7 step 3\uc740 T\uc640\ub3d9\uc77c\ud558\uac70\ub098\ub354\uc801\uc740 TIME\uac12\uc744\ucc3e\uae30\uc704\ud574 UN_TIME index\ub97c range scan\ud558\uace0\uadf8\uc640\uad00\ub828\ub41c ROWID\ub97c\ubc18\ud658.
\n\u00b7 step 2\ub294 step 3\uc5d0\uc758\ud574\ubc18\ud658\ub41c ROWID\uac12\ub4e4\ub85c\uc11c TANK_READING table\uc5d0\uc811\uadfc.
\n\u00b7 step 1\uc740 step 2\ub85c\ubd80\ud130\ub2e8\ud558\ub098\uc758\ud589\uc744\uc694\uad6c\ud568\uc73c\ub85c ROWNUM=1\uc774\ub77c\ub294\uc870\uac74\uc744\uc218\ud589.<\/p>\n

\u2015\u00a0 INDEX_DESC \ud78c\ud2b8\ub54c\ubb38\uc5d0 step 3\uc740 T\uac12\ubd80\ud130\uc2dc\uc791\ud558\ub294\ub0b4\ub9bc\ucc28\uc21c\uc73c\ub85c\uc815\ub82c\ub41c index\uc5d0\uc11c TIME\uac12\uc744 scan.
\n\u2015\u00a0 scan\ub41c\uccab\ubc88\uc9f8 TIME\uac12\uc740 T\uc774\uac70\ub098 T\uac12\ubcf4\ub2e4\ub294\uc801\uc740\ucd5c\ub300 TIME\uac12\uc785\ub2c8\ub2e4.
\n\u2015\u00a0 step 1\uc740\ub2e8\ud558\ub098\uc758\ud589\uc744\uc694\uad6c\ud55c\ud6c4\ubd80\ud130\ub294 , step 3\uc740\uccab\ubc88\uc9f8 TIME\uac12\uc774\ud6c4\uc758 index entry\uc5d0\ub300\ud574\ub354\uc774\uc0c1 scan\ud558\uc9c0\uc54a\ub294\ub2e4.
\n\u2015\u00a0 default \ud589\ub3d9\uc774\uc624\ub984\ucc28\uc21c index scan\uc774\ubbc0\ub85c INDEX_DESC \ud78c\ud2b8\uc5c6\uc774\uc774\uc9c8\uc758\ub97c\uc218\ud589\ud558\uba74\uc624\ub77c\ud074\uc740 table\uc5d0\uc11c T\uc640\uac19\uac70\ub098\uadf8\ubcf4\ub2e4\uc801\uc740\ucd5c\ub300\uc2dc\uac04\uc744\ucc98\uc74c scaning\ud558\ub294\uac83\ubcf4\ub2e4\ucd5c\ucd08\uc758\uc2dc\uac04\uc744 scaning\ud568\uc73c\ub85c\uc11c\uc2dc\uc791\ud558\uac8c\ub428. step1\uc740\ucd5c\ucd08\uc758\uc2dc\uac04\uc5d0\ub300\ud55c\uc628\ub3c4\ub97c\ubc18\ud658\ud558\uac8c\ub429\ub2c8\ub2e4.
\n\u2015\u00a0 \uc704\uc9c8\uc758\uc5d0\uc11c\uc880\ub354\ube68\ub9ac\uc774\ubcf5\ud569\uc9c8\uc758\uc5d0\uc11c\uc694\uad6c\ud558\ub294\uc628\ub3c4\ub97c\ubc18\ud658\ud558\ub824\uba74 INDEX_DESC \ud78c\ud2b8\ub97c\uc0ac\uc6a9\ud574\uc57c\ub9cc\ud569\ub2c8\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eAND_EQUAL<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 AND_EQUAL<\/strong>\ud78c\ud2b8\ub294\uba87\uba87\uc758 single-column index\uc5d0\ub300\ud55c scan\uc744 merge\ud558\ub294 access path\ub97c\uc0ac\uc6a9\ud558\ub294 execution plan\uc744\uc120\ud0dd
\n\u2015\u00a0 \uc774 AND_EQUAL \ud78c\ud2b8\uc758\ubb38\ubc95\uc740<\/p>\n

table<\/strong> : merge\ud560 index\uc640\uc5f0\uad00\ub41c table\uc758\uc774\ub984\uc774\ub098 alias\ub97c\uae30\uc220.<\/p>\n

index<\/strong> : index scan\uc744\uc218\ud589\ud558\ub294 index\ub97c\uae30\uc220\u2015\u00a0\ucd5c\uc18c 2\uac1c\uc774\uc0c1\ucd5c\ub300 5\uac1c\uc774\ud558\uc758 index\ub97c\uae30\uc220\ud574\uc57c\ud569\ub2c8\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eUSE_CONCAT<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 USE_CONCAT \ud78c\ud2b8\ub294 OR \uc870\uac74\uc744 UNION ALL set operator\ub97c\uc0ac\uc6a9\ud558\ub294 compound query\ub85c\ubcc0\ud658
\n\u2015\u00a0 \uc774\ubcc0\ud658\uc740 UNION ALL set operations\uc744\uc0ac\uc6a9\ud558\ub294\uc9c8\uc758\uac00\uc774\ub97c\uc0ac\uc6a9\ud558\uc9c0\uc54a\uc744\ub54c\ubcf4\ub2e4\ube44\uc6a9\uc774\ub354\uc801\uc744\uacbd\uc6b0\uc5d0\ub9cc\ubc1c\uc0dd<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\uc870\uc778\uc21c\uc11c\ub97c\uc704\ud55c SQL \ud78c\ud2b8<\/strong><\/td>\n<\/tr>\n
ORDERED \ud78c\ud2b8\ub294 join order\ub97c\uc81c\uc548<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eORDERED<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 ORDERED \ud78c\ud2b8\ub294 FROM\uc808\uc5d0 table\uc774\ub098\ud0c0\ub098\ub294\uc21c\uc11c\ub300\ub85c table\uc744 join\uc2dc\ud0a8\ub2e4.
\n\u2015\u00a0 \uc608\ub97c\ub4e4\uc5b4, \uc544\ub798 SQL\ubb38\uc740 table TAB1\uacfc table TAB2\ub97c\uc870\uc778\ud55c\ud6c4\uc5d0\uadf8\uacb0\uacfc\uc640 table TAB3\uc744\uc870\uc778\ud569\ub2c8\ub2e4.
\nSELECT <\/strong>\/*+ ORDERED *\/ tab1.col1, tab2.col2, tab3.col3FROM tab1, tab2, tab3WHERE tab1.col1 = tab2.col1 AND tab2.col1 = tab3.col1;<\/strong><\/p>\n

\u2015\u00a0 SQL\ubb38\uc5d0\uc11c ORDERED \ud78c\ud2b8\ub97c\uc0dd\ub7b5\ud558\uace0 join\ub97c\uc218\ud589\ud558\uba74, optimizer\uac00 table\uc744 join\ud560\uc21c\uc11c\ub97c\uc120\ud0dd<\/p>\n

\u2015\u00a0 \uac01 table\uc5d0\uc11c SELECT \ud574\uc624\ub294\ud589\uc758\uc218\uc5d0\ub300\ud574\uc54c\uace0\uc788\ub2e4\uba74 join \uc21c\uc11c\ub97c\uae30\uc220\ud558\ub294 ORDERED \ud78c\ud2b8\ub97c\uc0ac\uc6a9\ud558\ub294\uac83\uc774\uc88b\ub2e4.<\/p>\n

\u2015\u00a0 \uc0ac\uc6a9\uc790\uac00 inner \uc640 outer table\uc744\uc120\ud0dd\ud558\ub294\uac83\uc774 optimizer\uac00\ud560\uc218\uc788\ub294\uac83\ubcf4\ub2e4\ub098\uc744\uc218\ub3c4\uc788\uc2b5\ub2c8\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
Table \uc870\uc778\ubc29\ubc95 SQL \ud78c\ud2b8<\/strong><\/td>\n<\/tr>\n
\u25cf\u00a0SQL\ubb38\uc5d0\uc11c\ub098\ud0c0\ub098\ub294\uc870\uc778\ub41c table\uc744\uc815\ud655\ud558\uac8c\uae30\uc220\ud574\uc57c\ub9cc\ud569\ub2c8\ub2e4.<\/p>\n

\u25cf\u00a0SQL\ubb38\uc774 table\uc758 alias\ub97c\uc0ac\uc6a9\ud569\ub2c8\ub2e4\uba74\ud78c\ud2b8\uc5d0\uc11c\ub3c4 table\uc758\uc774\ub984\ubcf4\ub2e4\ub294 alias\ub97c\uc0ac\uc6a9\ud574\uc57c\ub9cc\ud569\ub2c8\ub2e4.<\/p>\n

\u25cf\u00a0table\uc758\uc774\ub984\uc774\ub098 alias\ub294 local database\uc5d0 table\uc758 synonym\uc774\ub098 table\uc744\uac00\uc9c0\uace0\uc788\uc5b4\uc57c\ub9cc\ud569\ub2c8\ub2e4.<\/p>\n

\u25cf\u00a0USE_NL\uacfc USE_MERGE \ud78c\ud2b8\ub294 ORDERED \ud78c\ud2b8\ub97c\uc0ac\uc6a9\ud574\uc57c\ub9cc\ud569\ub2c8\ub2e4.<\/p>\n

\u25cf\u00a0\uc624\ub77c\ud074\uc740\ucc38\uc870\ub41c table\uc774\uc870\uc778\uc5d0\uc11c\uac15\uc81c\ub85c innertable\uc774\ub420\ub54c\uc774\ud78c\ud2b8\ub97c\uc0ac\uc6a9\ud574\uc57c\ub9cc\ud569\ub2c8\ub2e4.<\/p>\n

\u25cf\u00a0\ucc38\uc870\ub41c table\uc774 outer table\uc774\ub77c\uba74\uc774\ud78c\ud2b8\ub4e4\uc744\ubb34\uc2dc\ud569\ub2c8\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eUSE_NL<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 USE_NL \ud78c\ud2b8\ub294 table\uc744\uc801\ub294\ubd80\ubd84\uc5d0 table \uae30\uc220\ub41c table\uc740 inner table\ub85c\uc11c\uc0ac\uc6a9\ud558\uc5ec nested loops\ub85c\uc11c\ub2e4\ub978 table\uc758 row source\uc640\uae30\uc220\ub41c table\uc744 join\ud558\uac8c\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 USE_NL \ud78c\ud2b8\uc758\ubb38\ubc95\uc740<\/p>\n

 <\/p>\n

<\/p>\n

 <\/p>\n

table<\/strong> : nested loops join\uc758 inner table\ub85c\uc11c\uc0ac\uc6a9\ub420 table\uc758\uc774\ub984\uacfc alias\uc785\ub2c8\ub2e4.<\/p>\n

\u2015\u00a0 \uc608\ub97c\ub4e4\uc5b4\uc11c, ACCOUNTS\uc640 CUSTOMERS table\uc744\uc870\uc778\ud558\ub294 SQL\ubb38\uc774\uc788\ub2e4\uace0\uc0dd\uac01\ud574\ubcf4\uc790. \uc774\ub4e4 table\ub4e4\uc740 cluster\uc5d0\ud568\uaed8\uc800\uc7a5\ub418\uc9c0\uc54a\uc558\ub2e4\uace0\uac00\uc815\ud569\ub2c8\ub2e4.<\/p>\n

SELECT accounts.balance, customers.last_name, customers.first_nameFROM accounts, customersWHERE accounts.custno = customers.custo;<\/p>\n

\u2015\u00a0 cost-based approach\uc758 default \ubaa9\uc801\uc740 best throughput\uc774\ubbc0\ub85c\uc774 optimizer\ub294\uc880\ub354\ube68\ub9ac\uc9c8\uc758\uc5d0\uc758\ud574 SELECT \ub41c\ubaa8\ub4e0\ud589\ub4e4\uc744\ubc18\ud658\ud558\uae30\uc704\ud574 nested loops operation\uc774\ub098 sort-merge operation\uc911\ud558\ub098\ub97c\uc120\ud0dd\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 \uadf8\ub7ec\ub098\uc9c8\uc758\uc5d0\uc758\ud574\uc120\ud0dd\ub41c\uccab\ubc88\uc9f8\ud589\ub9cc\ubc18\ud658\ud560\ub54c\ud544\uc694\uc2dc\uac04\uc774\ub9e4\uc6b0\uc801\uc5b4\uc57c\ud560\uacbd\uc6b0\uc5d0\ub294 best throughput\ubcf4\ub2e4 best response time\uc73c\ub85c SQL\ubb38\uc744\ucd5c\uc801\ud654\ud558\ub294\uac83\uc774\ub354\ub0ab\ub2e4.
\n\u2015\u00a0 \uadf8\ub807\uac8c\ud558\ub824\uba74 USE_NL \ud78c\ud2b8\ub97c\uc0ac\uc6a9\ud568\uc73c\ub85c\uc11c optimizer\uac00 nested loops join\uc744\uac15\uc81c\ub85c\uc120\ud0dd\ud558\uac8c\ud560\uc218\uc788\uc2b5\ub2c8\ub2e4.
\n\u2015\u00a0 SQL\ubb38\uc5d0\uc11c USE_NL \ud78c\ud2b8\ub294 CUSTOMERStable\uc744 inner table\ub85c\uac00\uc9c0\ub294 nested loop\ub97c\uc120\ud0dd<\/p>\n


\nSELECT <\/strong>\/*+ ORDERED USE_NL(customers) Use N-L to get first row faster *\/accounts.balance, customers.last_name, customers.first_name FROM accounts, customers\u00a0 WHERE accounts.custno = customers.custno;<\/strong><\/p>\n

\u2015\u00a0 \ub9ce\uc740\uacbd\uc6b0\uc5d0 nested loops join\uc740 sort-merge join\ubcf4\ub2e4\ub294\ub354\ube68\ub9ac\uccab\ubc88\uc9f8\ud589\uc744\ubc18\ud658\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 Nested loop join\uc740\ud55c table\ub85c\ubd80\ud130\uccab\ubc88\ube7c SELECT \ud55c\ud589\uc744\uc77d\uc740\ud6c4\uc5d0\uccab\ubc88\uc9f8\ud589\uc744\ubc18\ud658\ud560\uc218\uc788\uace0, \ub2e4\ub978 table\uc5d0\uc11c\uccab\ubc88\uc9f8\ub85c\uc77c\uce58\ud558\ub294\ud589\uc744\ucc3e\uace0\uadf8\uac83\ub4e4\uc744\uacb0\ud569\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 \ubc18\uba74\uc5d0 sort-merge join\uc740\uc591 table\uc5d0\uc11c SELECT \ud55c\ubaa8\ub4e0 row\ub4e4\uc744\uc77d\uace0\uc815\ub82c\ud55c\ud6c4\uac01\uac01\uc758\uc800\uc7a5\ub41c row source\uc758\uccab\ubc88\uc9f8\ud589\ub4e4\uc744\uacb0\ud569\ud560\ub54c\uae4c\uc9c0\uccab\ubc88\uc9f8\ud589\uc744\ubc18\ud658\ud560\uc218\uc5c6\uc2b5\ub2c8\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eUSE_MERGE<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 USE_MERGE \ud78c\ud2b8\ub294\uc624\ub77c\ud074\uc774 sort-merge join\uc73c\ub85c\uac01 table\uc744\uc870\uc778\ud558\uac8c\ub054\ud558\ub294\ubc29\ubc95\uc785\ub2c8\ub2e4.
\n\u2015\u00a0 USE_MERGE \ud78c\ud2b8\uc758\ubb38\ubc95\uc740<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n
\ubcd1\ub82c\ucffc\ub9ac\uc2e4\ud589\uc744\uc704\ud55c SQL \ud78c\ud2b8<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eCACHE<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 CACHE \ud78c\ud2b8\ub294 full table scan\uc774\uc218\ud589\ub410\uc744\ub54c\ud78c\ud2b8\uc5d0\uc788\ub294 table\uc5d0\ub300\ud574 retrieve\ub41c\ube14\ub85d\ub4e4\uc740\ubc84\ud37c\uce90\uc2dc\uc5d0\uc788\ub294 LRU list\uc758\uac00\uc7a5\ucd5c\uadfc(most recently)\uc5d0\uc0ac\uc6a9\ub418\uc5b4\uc9c4\uac83\uc758\ub05d\uc5d0\uc704\uce58
\n\u2015\u00a0 \uc774 option\uc740 small lookup table\uc5d0\uc720\uc6a9\ud558\ub2e4. CACHE \ud78c\ud2b8\ub294 table\uc758 default caching specification\uc744\ubb34\uc2dc
\nSELECT \/*+ FULL (scoot_emp) CACHE(scott_emp) *\/ enameFROM scott.emp scott_emp; <\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eNOCACHE<\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 NOCACHE \ud78c\ud2b8\ub294 full table scan\uc774\uc218\ud589\ub410\uc744\ub54c\ud78c\ud2b8\uc5d0\uc788\ub294 table\uc5d0\ub300\ud574 retrieve\ub41c\ube14\ub85d\ub4e4\uc740\ubc84\ud37c\uce90\uc2dc\uc5d0\uc788\ub294 LRU list\uc758\uac00\uc7a5\uc624\ub798\uc804(least recently)\uc5d0\uc0ac\uc6a9\ub418\uc5b4\uc9c4\uac83\uc758\ub05d\uc5d0\uc704\uce58
\n\u2015\u00a0 \ubc84\ud37c\uce90\uc218\uc5d0 blocks\uc758\uc77c\ubc18\uc801\uc778\ud589\ub3d9
\nSELECT \/*+ FULL (scoot_emp) NOCACHE(scott_emp) *\/ enameFROM scott.emp scott_emp;<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n<\/colgroup>\n\n\n\n\n
<\/td>\n<\/tr>\n
\uad6c\ubb38\uc5f0\uacc4\ub97c\uace0\ub824\ud558\ub294 SQL \ud78c\ud2b8<\/strong><\/td>\n<\/tr>\n
\u25cf\u00a0 SQL\uc774\uc735\ud1b5\uc131\uc774\uc788\ub294\uc5b8\uc5b4\uc774\uae30\ub54c\ubb38\uc5d0\ud558\ub098\uc774\uc0c1\uc758 SQL\ubb38\uc774 Application\uc744\ud544\uc694\ub85c\ud560\uac83\uc785\ub2c8\ub2e4.
\n\u25cf\u00a0 2\uac1c\uc758 SQL\ubb38\uc774\ub3d9\uc77c\ud55c\uacb0\uacfc\ub97c\uc0b0\ucd9c\ud568\uc5d0\ub3c4\ubd88\uad6c\ud558\uace0, \uc624\ub77c\ud074\uc740 2\uc911\uc758\ud558\ub098\uac00\ub354\ucc98\ub9ac\uc18d\ub3c4\uac00\ube60\ub985\ub2c8\ub2e4.
\n\u25cf\u00a0 execution plans\uc744\ube44\uad50\ud558\uae30\uc704\ud574 EXPLAIN PLAN SQL\ubb38\uc758\uacb0\uacfc\uc640\ub450 SQL\ubb38\uc758\ube44\uc6a9\ub4e4\uc744\uc0ac\uc6a9\ud560\uc218\uc788\uc2b5\ub2c8\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222e\ub450 SQL\ubb38\uc5d0\ub300\ud55c execution plan \ube44\uad50<\/strong><\/td>\n<\/tr>\n
\u25cf\u00a0\uccab\ubc88\uc9f8 SQL\ubb38\uacfc\uadf8\uac83\uc5d0\ub300\ud55c execution plan<\/strong><\/p>\n

SELECT dname, deptnoFROM deptWHERE deptno NOT IN(<\/p>\n

SELECT deptno FROM emp);<\/p>\n

 <\/p>\n

\u2015\u00a0Execution Plan with Two Full Table Scans<\/p>\n

 <\/p>\n

<\/p>\n

 <\/p>\n

\u2015\u00a0step3\uc744\ud1b5\ud574\uc624\ub77c\ud074\uc740 DEPTNO \uce7c\ub7fc\uc5d0 index\uac00\uc788\uc74c\uc5d0\ub3c4\ubd88\uad6c\ud558\uace0 EMPtable\uc744 full table scan\ud568\uc73c\ub85c\uc11c\uc704 SQL\ubb38\uc744\uc2e4\ud589\ud569\ub2c8\ub2e4\ub294\uac83\uc744\uc54c\uc218\uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

\u2015\u00a0full table scan\uc740time-consuming operation<\/strong>\uc744\ud560\uc218\uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

\u2015\u00a0EMPtable\uc744\ucc3e\ub294subquery<\/strong>\uc5d0 index\uc0ac\uc6a9\uc774\uac00\ub2a5\ud55c WHERE\uc808\uc774\uc5c6\uae30\ub54c\ubb38\uc5d0<\/strong>\uc624\ub77c\ud074\uc740 index\ub97c\uc0ac\uc6a9\ud560\uc218\uc5c6\uc2b5\ub2c8\ub2e4.<\/p>\n

\u2015\u00a0 \uadf8\ub7ec\ub098, \uc544\ub798\uc758 SQL\ubb38\uc740 index\uc5d0\uc811\uadfc\ud568\uc73c\ub85c\uc11c\ub3d9\uc77c\ud55c\ud589\uc744 SELECT \ud569\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

SELECT dname, deptnoFROM deptWHERE NOT EXISTS(<\/p>\n

 <\/p>\n

SELECT deptnoFROM empWHERE dept.deptno = emp.deptno);<\/p>\n

 <\/p>\n

\u2015\u00a0Execution Plan with a Full Table Scan and an Index Scan<\/p>\n

<\/p>\n

\u2015\u00a0subquery\uc758 WHERE\uc808\uc774 EMPtable\uc758 DEPTNO\uce7c\ub7fc\uc744\uc0ac\uc6a9\ud558\ubbc0\ub85c DEPTNO_INDEX\ub97c\uc0ac\uc6a9\ud560\uc218\uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

\u2015\u00a0index\uc758\uc0ac\uc6a9\uc740 execution plan\uc758 step3\uc5d0\uc11c\ud558\uac8c\ub429\ub2c8\ub2e4<\/p>\n

\u2015\u00a0 DEPTNO_INDEX<\/strong>\uc758 index range scan\uc740\uccab\ubc88\uc9f8\ubb38\uc7a5\uc5d0\uc11c EMP table\uc758 full scan\ud558\ub294\uac83\ubcf4\ub2e4\uc2dc\uac04\uc774\ub354\uc801\uac8c\uac78\ub9b0\ub2e4<\/strong>. <\/strong>
\n\u2015\u00a0 \uccab\ubc88\uc9f8 query\ub294 DEPT table\uc5d0\uc11c\ubaa8\ub4e0 DEPTNO\ub97c\uac00\uc838\uc624\uae30\uc704\ud574 EMP table\uc744\ud55c\ubc88 full scan\ud569\ub2c8\ub2e4.<\/p>\n

\u2015\u00a0\uc774\ub7f0\uc774\uc720\ub85c\ub450\ubc88\uc9f8 SQL \ubb38\uc740\uccab\ubc88\uc9f8\ubcf4\ub2e4\ub294\ub354\ube60\ub985\ub2c8\ub2e4.<\/p>\n

\u2015\u00a0Application\uc5d0 NOT IN operator\ub97c\uc0ac\uc6a9\ud558\ub294 SQL\ubb38\ub97c\uac00\uc9c4\ub2e4\uba74 NOT EXISTS operator\ub97c\uc0ac\uc6a9\ud574\uc11c\uadf8\uac83\ub4e4\uc744\ub2e4\uc2dc\uc368\ub77c. \uc774\uac83\uc740 index\uac00\uc788\ub2e4\uba74\uadf8 index\ub97c\uc0ac\uc6a9\ud560\uc218\uc788\uac8c\ud574\uc904\uac83\uc785\ub2c8\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\uae30\uc874 SQL\ubb38\uc744\ud29c\ub2dd\ud558\ub294\ubc29\ubc95<\/strong><\/td>\n<\/tr>\n
\u25cf\u00a0 <\/strong>\uc874\uc7ac\ud558\ub294 application\uc5d0\uc11c SQL \ubb38\uc744\ud29c\ub2dd\ud558\ub294\uac83\uc740\uc0c8\ub85c\uc6b4\ubb38\uc7a5\uc744\uae30\ub85d\ud558\ub294\uac83\uacfc\ub294\ub2e4\ub978\uc791\uc5c5(task)\uc785\ub2c8\ub2e4<\/strong>.<\/strong><\/p>\n

\u25cf\u00a0 <\/strong>\ube44\ub85d\uc694\uad6c\ub41c\uc9c0\uc2dd\uc774\uac19\uc744\uc9c0\ub77c\ub3c4\ud504\ub85c\uc138\uc2a4\ub294\ub2e4\ub974\ub2e4<\/strong>. <\/strong><\/p>\n

\u25cf\u00a0 <\/strong>\ud29c\ub2dd\ud558\uae30\uc704\ud55c application\uc5d0\uc11c\ud2b9\ubcc4\ud55c\ubb38\uc7a5\uc744\ub2e4\uc74c\uacfc\uac19\uc774\ud568\uc73c\ub85c\uc368\ubd84\ub9ac\ud574\uc57c\ud569\ub2c8\ub2e4<\/strong>. <\/strong><\/p>\n

\u00b7 application\uc5d0\uc775\uc219\ud558\ub77c.
\n\u00b7 SQL trace\ub97c\uc774\uc6a9\ud558\uc5ec\ud2b9\ubcc4\ud55c\ubb38\uc81c\ubb38\uc7a5\uc744\ubd84\ub9ac\ud558\ub77c.<\/p>\n

\u25cf\u00a0<\/strong>\uc774\uc804\uc808\uc5d0\uc11c\ub17c\uc758\ud588\ub358\uc0c8\ub85c\uc6b4 SQL \ubb38\uc7a5\uc744\uae30\ub85d\ud558\uace0, index, \ud074\ub7ec\uc2a4\ud130, \ud574\uc2f1, \ud78c\ud2b8\ub97c \ud558\uae30 \uc704\ud55c\uc9c0\uce68\uc744 \uc774\uc6a9\ud558\uc5ec\ubb38\uc7a5\uc744 \ud29c\ub2dd \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n\n
\u222eKnow the Application <\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 application\uacfc SQL \ubb38\uc7a5\uacfc data\uc5d0\uc775\uc219\ud574\uc838\uc57c\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 \ub9cc\uc57d\ub2f9\uc2e0\uc774 application\uc744\uc124\uacc4\ud558\uace0\uac1c\ubc1c\ud558\uc9c0\uc54a\uc558\ub2e4\uba74\uadf8\uac83\uc744\ud55c\uc0ac\ub78c\uc5d0\uac8c\uc758\uacac\uc744\ubb3c\uc5b4\ubcf4\ub77c.
\n\u2015\u00a0 application\uc774\ud558\ub294\uac83\uc744\uae30\ub85d\ud558\ub77c.<\/p>\n

\u00b7 application\uc744\uc774\uc6a9\ud558\ub294 SQL \ubb38\uc740\ubb34\uc5c7\uc778\uac00?<\/p>\n

\u00b7 application\uc774\ucc98\ub9ac\ud558\ub294\ub370\uc774\ud0c0\ub294\ubb34\uc5c7\uc778\uac00?<\/p>\n

\u00b7 \ub370\uc774\ud0c0\uc758\ud2b9\uc9d5\uacfc distribution\uc740\ubb34\uc5c7\uc778\uac00?<\/p>\n

\u00b7 applicatin\uc758\ubb34\uc2a8 operation\uc774 data\ub97c\uc2e4\ud589\ud558\ub294\uac00?<\/p>\n

\u2015\u00a0 application \uc0ac\uc6a9\uc790\ub4e4\uacfc\uc131\ub2a5\uc5d0\ub300\ud574\uc774\uc57c\uae30\ud574\ubcf4\ub77c.
\n\u2015\u00a0 application\uc758\uc5b4\ub5a4\ubd80\ubd84\uc744\uc2dd\ubcc4\ud558\uae30\uc704\ud574\uc11c\uadf8\ub4e4\uc774\uc131\ub2a5\ud5a5\uc0c1\uc758\ud544\uc694\ub97c\ub290\ub07c\ub294\uc9c0\ub97c\ubb3c\uc5b4\ubcf4\ub77c.
\n\u2015\u00a0 \uac00\ub2a5\ud558\uba74\uac1c\uc778\uc801\uc778 SQL \ubb38\uc7a5\uc744\uc704\ud574\uc11c\uc774\ubd80\ubd84\ub4e4\uc774\uc801\uc5b4\uc9c8\ub54c\uae4c\uc9c0\uc881\ud600\ub77c.<\/td>\n<\/tr>\n

<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eUse the SQl Trace Facility <\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 \uc624\ub77c\ud074\uc740\uc131\ub2a5\uc744\uce21\uc815\ud558\uae30\uc704\ud558\uc5ec\uc9c4\ub2e8\uc5d0\ub3c4\uc6c0\uc774\ub418\ub294 tool\uc744\uba87\uac00\uc9c0\uc81c\uacf5\ud569\ub2c8\ub2e4
\n\u2015\u00a0 appllicatin\uc744\ud29c\ub2dd\ud558\ub294\ub370\ud2b9\ubcc4\ud788\ub3c4\uc6c0\uc774\ub418\ub294 tool\uc911\uc5d0\ud558\ub098\ub294 SQL trace \uc785\ub2c8\ub2e4.
\n\u2015\u00a0 SQL trace facililty\ub294\uc624\ub77c\ud074\uc5d0\uc758\ud574\ucc98\ub9ac\ub41c\uac01 SQL \ubb38\uc7a5\uc744\uc704\ud55c\ud1b5\uacc4\ub97c\uc0dd\uc131\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 \ud1b5\uacc4\ub294\ub2e4\uc74c\uc744\ub098\ud0c0\ub0b8\ub2e4.<\/p>\n

\u00b7 SQL \ubb38\uc7a5\uc774\ud30c\uc2f1, \uc2e4\ud589, \ud398\uce58\ub41c\ud69f\uc218
\n\u00b7 \uac01 SQL \ubb38\uc7a5\uc744\ucc98\ub9ac\ud558\ub294\ub370\ud544\uc694\ud55c\uc2dc\uac04
\n\u00b7 \uac01 SQL \ubb38\uc7a5\uacfc\uad00\ub828\ub41c\uba54\ubaa8\ub9ac\uc640\ub514\uc2a4\ud06c access
\n\u00b7 \uac01 SQL \ubb38\uc7a5\uc744\ucc98\ub9ac\ud558\ub294 row\uc758\uc218
\n\u2015\u00a0 SQL trace facility\ub294\ub610\ud55c EXPLAIN PLAN \uba85\ub839\uc744\uc774\uc6a9\ud558\uc5ec execution plan\uc744\uc0dd\uc131\ud560\uc218\uc788\uc2b5\ub2c8\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n<\/colgroup>\n\n\n\n
\u222eTuning Individual SQL statements <\/strong><\/td>\n<\/tr>\n
\u2015\u00a0 \uc0ac\uc6a9\uc911\uc778\uc0ac\uc6a9\uc790\uc751\uc6a9\uc744\uc218\uc815\ud558\uc9c0\uc54a\uace0 SQL\ubb38\uc758\ubcc0\uacbd\ub41c\ubb38\ubc95\uc744\ud0d0\uc0c9\ud560\uc218\uc788\uc74c\uc744\uba85\uc2ec\ud558\ub77c.
\n\u2015\u00a0 \uc0ac\uc6a9\uc790\uac00\uc874\uc7ac\ud558\ub294 SQL\ubb38\uc758\ube44\uc6a9\uacfc\uc2e4\ud589\uacc4\ud68d\uc744\ube44\uad50\ud558\uace0\uace0\ub824\ud558\ub294\ubb38\uc7a5\uc744\ubcc0\uacbd\ud558\ub294\uac83\uc740\ub2e8\uc21c\ud788 EXPLIAN PLAN\uc744\uc774\uc6a9\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 \ub9cc\uc57d\uc0ac\uc6a9\uc790\uac00 SQL\ubb38\uc758 tune\uc744\uc704\ud574\uc0c8\ub85c\uc6b4 index\ub97c\uc0dd\uc131\ud558\uba74\uc0ac\uc6a9\uc790\ub294 optimizer\uac00\uc751\uc6a9\uc774\uc2e4\ud589\ub420\ub54c\ub9c8\ub2e4 index\ub97c\uc0ac\uc6a9\ud560\uac83\uc778\uc9c0\ub97c\uacb0\uc815\ud558\uae30\uc704\ud574 EXPLAIN PLAN \uba85\ub839\uc744\uc0ac\uc6a9\ud560\uc218\uc788\uc2b5\ub2c8\ub2e4.
\n\u2015\u00a0 \ub9cc\uc57d\uc0ac\uc6a9\uc790\uac00\ud604\uc7ac parse\ub41c\ubb38\uc7a5\uc744 tune\ud558\uae30\uc704\ud574\uc0c8\ub85c\uc6b4 index\ub97c\uc0dd\uc131\ud558\uba74 Oracle\uc740 SQL \ubb38\uc5d0\uc11c\uc774 index\ub97c\uc0ac\uc6a9\ud558\uc9c0\uc54a\ub294\ub2e4.
\n\u2015\u00a0 SQL \ubb38\uc7a5\uc774\ub2e4\uc2dc\uc2e4\ud589\ub418\uba74 optimizer\ub294\uc790\ub3d9\uc801\uc73c\ub85c\uc0c8\ub85c\uc0dd\uc131\ub41c index\ub97c\uc0ac\uc6a9\ud558\uae30\uc704\ud574\uc0c8\ub85c\uc6b4\uc2e4\ud589\uacc4\ud68d\uc744\uc120\ud0dd\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 \ub9cc\uc57d\uc0ac\uc6a9\uc790\uac00\ubd84\uc0b0 SQL \ubb38\uc744 tune \ud558\uae30\uc704\ud574\uc6d0\uaca9\ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc758 index\ub97c\uc0dd\uc131\ud558\uba74 optimizer\ub294 SQL\ubb38\uc774\ub2e4\uc2dc\uc2e4\ud589\ub420\ub54c\uc774 index\ub97c\uace0\ub824\ud569\ub2c8\ub2e4.
\n\u2015\u00a0 \ub610\ud55c\uc0ac\uc6a9\uc790\ub294 SQL \ubb38\uc7a5\uc5d0\ub300\ud55c\uc2e4\ud589\uacc4\ud68d\uc774 optimizer \ubcf4\ub2e4\ub354\ud6a8\uacfc\uc801\uc73c\ub85c\uc0ac\uc6a9\ud560\uc218\uc788\ub3c4\ub85d tune\ud569\ub2c8\ub2e4\ub294\uac83\uc744\uba85\uc2ec\ud558\ub77c.
\n\u2015\u00a0 \uc608\ub97c\ub4e4\uc5b4\uc0ac\uc6a9\uc790\uac00\ud558\ub098\uc758 SQL\ubb38\uc744\uc0ac\uc6a9\ud558\uae30\uc704\ud574 index\ub97c\uc0dd\uc131\ud558\uba74 optimizer\ub294\uc0ac\uc6a9\uc790\uc758\uc751\uc6a9\uc5d0\uc11c\ub2e4\ub978\ubb38\uc7a5\uc744\uc2e4\ud589\ud558\uae30\uc704\ud574\uadf8 index\ub97c\uc120\ud0dd\ud569\ub2c8\ub2e4.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

  SQL \ud78c\ud2b8\uc758 \uc0ac\uc6a9 \ubc29\ubc95 \u25cf\u00a0 DBA\ub294 \ud2b9\uc815\ub370\uc774\ud130\uc5d0 \ub300\ud558\uc5ec optimizer\uac00 \uc54c \uc218 \uc5c6\ub294 \uc815\ubcf4\ub97c \uac00\uc9c8 \uc218 \uc788\uc74c. \u25cf\u00a0 DBA\ub294 optimizer\ubcf4\ub2e4 \ub354 \uc88b\uc740 execution plan\uc744 \uc120\ud0dd\ud560 \uc218 \uc788\uc74c. \u25cf\u00a0 DBA\ub294 \ud78c\ud2b8\ub97c \uc0ac\uc6a9\ud558\uc5ec \uac15\uc81c\uc801\uc73c\ub85c \uc0ac\uc6a9\uc790\uac00 \uc120\ud0dd\ud55c execution plan\uc744 \uc0dd\uc131\ud558\ub3c4\ub85d\u00a0 optimizer\uc5d0\uac8c \uc9c0\uc2dc\uac00\ub2a5.   \u222e\ud78c\ud2b8\uc758 \uc0ac\uc6a9 \u25cf\u00a0 \ud78c\ud2b8\uc758 \uc0ac\uc6a9\ubc94\uc704 \u2015\u00a0 SQL\ubb38\uc744 \uc704\ud55c Optimization approach\u2015\u00a0 SQL\ubb38\uc5d0 \ub300\ud55c Cost-based approach\uc758 goal(best […]<\/p>\n","protected":false},"author":1,"featured_media":2857,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[230,233,234],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/oracle-business-intelligence.jpg?fit=973%2C615","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3241"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3241"}],"version-history":[{"count":17,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3241\/revisions"}],"predecessor-version":[{"id":3259,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3241\/revisions\/3259"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2857"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3241"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3241"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3241"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}