{"id":476,"date":"2015-09-23T08:14:26","date_gmt":"2015-09-23T08:14:26","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=476"},"modified":"2015-09-23T08:14:26","modified_gmt":"2015-09-23T08:14:26","slug":"sql-%ed%8a%9c%eb%8b%9d%ec%9d%98-%ea%b8%b0%eb%b3%b8-%ec%a7%80%ec%8b%9d","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=476","title":{"rendered":"SQL \ud29c\ub2dd\uc758 \uae30\ubcf8 \uc9c0\uc2dd"},"content":{"rendered":"
\n\n\n<\/colgroup>\n\n\n\n
\n

\u261eHow to Use Indexs<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25cf<\/span>  \uc778\ub371\uc2a4 \uc0dd\uc131 \uc2dc\uae30
\u25cf<\/span>  \uc778\ub371\uc2a4 \uc0dd\uc131 \ub300\uc0c1 column \uc120\ud0dd
\u25cf<\/span>  \uacb0\ud569 \uc778\ub371\uc2a4 \uc0ac\uc6a9 \ubc29\ubc95
\u25cf<\/span>  \uc778\ub371\uc2a4 \uc0ac\uc6a9\uc744 \uc704\ud55c SQL\ubb38 \uc791\uc131\ubc95 <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n

\n\n\n<\/colgroup>\n\n\n\n\n
\n

\u2663 When to Create Indexes <\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  \uc9c8\uc758\uc5b4\uc5d0\uc11c select\ud558\ub294 row\uc758 \ube44\uc728\uc774 \uc801\uc744 \uacbd\uc6b0, \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\uba74 \uc9c8\uc758\uc758 \uc131\ub2a5\uc774 \ud5a5\uc0c1
\u25b6<\/span>  \ud14c\uc774\ube14\uc5d0 \uc778\ub371\uc2a4\ub97c \uc0dd\uc131\ud558\ub294 \uc9c0\uce68 : table\uc758 \uc804\uccb4 row\uc911\uc758 2%\uc5d0\uc11c 4%\uc774\ud558\ub97c \uac80\uc0c9\ud558\ub294 \uc9c8\uc758\uac00 \uc790\uc8fc \ubc1c\uc0dd\ud560 \uacbd\uc6b0
\u25b6<\/span>  \uc774 \uc9c0\uce68\uc740 \ub2e4\uc74c \uac00\uc815\uc744 \uae30\ubc18\uc73c\ub85c \ud568. <\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u00b7<\/span> \uc9c8\uc758\uc5d0\uc11c \uc120\ud0dd\ub41c column\uc758 \uac12\uc774 \ub3d9\uc77c\ud55c Rows\ub4e4\uc740 \uadf8 Table\uc5d0 \ud560\ub2f9\ub41c data block\uc5d0 \uade0\uc77c\ud558\uac8c \ubd84\uc0b0\ub418\uc5b4\uc788\uc74c.<\/span>
\u00b7<\/span> Table\uc758 row\ub294 \uc9c8\uc758\ub418\ub294 column\uc5d0 \ub300\ud558\uc5ec \ubd88\uaddc\uce59\uc801\uc73c\ub85c \ubd84\ud3ec\ub418\uc5b4 \uc788\uc74c.<\/span>
\u00b7<\/span> Table\uc5d0 \ud560\ub2f9\ub41c \uac01 data block\uc740 \ucd5c\uc18c\ud55c 10\uac1c\uc758 row\ub97c \ud3ec\ud568<\/span>
\u00b7<\/span> Table\uc740 \uc0c1\ub2f9\ud788 \uc791\uc740 \uc218\uc758 column\uc744 \uac00\uc9c4\ub2e4.<\/span>
\u00b7<\/span> Table\uc5d0 \ub300\ud55c \ub300\ubd80\ubd84\uc758 \uc9c8\uc758\ub4e4\uc740 \ube44\uad50\uc801 \ub2e8\uc21c\ud55c WHERE\uc808\uc744 \uac00\uc9c4\ub2e4.<\/span>
\u00b7<\/span> Cache hit ratio\ub294 \ub0ae\uace0 operating system cache\ub294 \uc5c6\ub2e4.<\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 How to Choose Columns to Index <\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  \uc778\ub371\uc2a4\ub97c \uc0dd\uc131\ud560 column\uc744 \uc120\ud0dd\ud558\uae30 \uc704\ud55c \uc9c0\uce68<\/strong>
\n\t\t\t\t\t\t\t\t
\u00b7<\/span>  Where\uc808\uc5d0\uc11c \uc790\uc8fc \uc0ac\uc6a9\ub418\ub294 column\uc5d0 \ub300\ud55c \uc778\ub371\uc2f1 \uace0\ub824 <\/span>
\u00b7<\/span> SQL\ubb38\uc5d0\uc11c join\uc744 \uc704\ud574 \uc790\uc8fc \uc0ac\uc6a9\ub418\ub294 column\uc5d0 \ub300\ud55c \uc778\ub371\uc2f1 \uace0\ub824 <\/span>
\u00b7<\/span> \uc88b\uc740 \ubd84\ud3ec\ub3c4\ub97c \uac00\uc9c0\ub294 column\uc5d0 \ub300\ud55c \uc778\ub371\uc2f1<\/span><\/p>\n

      \u25a0 NOTE<\/strong><\/span>
         \u00b7<\/span>  Oracle\uc740 intergrity constraints\ub85c \uc815\uc758\ud55c \ubaa8\ub4e0 unique key\uc640 primary keys\ub97c \uac00\uc9c4 column\uc5d0 \ub300\ud574
            \uc778\ub371\uc2a4\ub97c \uc790\ub3d9\uc801\uc73c\ub85c \uc0dd\uc131.
         \u00b7  \uc774\ub7ec\ud55c \uc778\ub371\uc2a4\ub4e4\uc740 \ucd5c\uace0\uc758 \ubd84\ud3ec\ub3c4\ub97c \uac00\uc9c0\uba70, \uc131\ub2a5 \ucd5c\uc801\ud654 \uacfc\uc815\uc5d0\uc11c \uac00\uc7a5 \ud6a8\uacfc\uc801\uc774\ub2e4.
         \u00b7 \uc778\ub371\uc2a4\ub41c column\uc5d0 \ub300\ud55c \ubd84\ud3ec\ub3c4<\/strong> : \ud14c\uc774\ube14\uc758 \uc804\uccb4 rows \uc218\uc640 \ud574\ub2f9 column\uc758 \uc11c\ub85c \ub2e4\ub978 \uac12\uc758 \uc218\ub85c \ub098\ub208 \ube44\uc728
         \u00b7  ANALYZE \uba85\ub839\uc744 \uc0ac\uc6a9\ud558\uc5ec \ubd84\ud3ec\ub3c4\ub97c \uad6c\ud568.
\n<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

\u00b7 \uc11c\ub85c \ub2e4\ub978 \uac12\uc774 \uac70\uc758 \uc5c6\ub294 column\uc5d0 \ub300\ud574\uc11c\ub294 \uc778\ub371\uc2a4\ub97c \uc0dd\uc131\ud558\uc9c0 \uc54a\ub294\ub2e4.
\u00b7 \uc790\uc8fc \uc218\uc815\ub418\ub294 column\uc740 \uc778\ub371\uc2a4\ub97c \uc0dd\uc131\ud558\uc9c0 \uc54a\ub294\ub2e4.
\u00b7 WHERE\uc808\uc5d0\uc11c function\uc774\ub098 operator\ub0b4\uc5d0\uc11c\ub9cc \uc0ac\uc6a9\ub418\ub294 column\uc5d0 \ub300\ud574\uc11c\ub294 \uc778\ub371\uc2a4\ub97c \uc0dd\uc131\ud560 \ud544\uc694\uac00 \uc5c6\ub2e4.
\u00b7 \ub2e4\uc218\uc758 INSERT, UPDATE, DELETE\ubb38\uc774 parent\uc640 child table\uc744 \ub3d9\uc2dc\uc5d0 access\ud558\ub294 \uacbd\uc6b0\uc5d0\ub294 referential integrity constraints\uc758 foreign key\ub97c \uc778\ub371\uc2f1\ud558\ub294 \uac83\uc744 \uace0\ub824\ud574\uc57c \ud55c\ub2e4.
\u00b7 column\uc5d0 \ub300\ud55c \uc778\ub371\uc2a4 \uc0dd\uc131\uc5ec\ubd80\ub294, \uc9c8\uc758\uc5d0 \ub300\ud55c \uc131\ub2a5 \uc774\ub4dd, INSERT, UPDATE, DELETE\ubb38 \ub300\ud55c \uc131\ub2a5 \uc190\uc2e4, \uadf8\ub9ac\uace0 \uc778\ub371\uc2a4\uc5d0 \ub300\ud55c \uc800\uc7a5 \uacf5\uac04\uc744 \uace0\ub824\ud558\uc5ec\uc57c \ud55c\ub2e4.<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n

\n\n\n<\/colgroup>\n\n\n\n\n\n\n
\n

\u2663 How to Choose Composite Indexes<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25a0\uacb0\ud569\uc778\ub371\uc2a4(Composite index)<\/strong><\/span>
\u00b7 \uacb0\ud569 \uc778\ub371\uc2a4(Composite index)<\/em><\/strong><\/span> : <\/span>\ud558\ub098<\/span> \uc774\uc0c1\uc758 \uce7c\ub7fc\uc73c\ub85c \uc0dd\uc131\ub41c \uc778\ub371\uc2a4
\n<\/span><\/p>\n

\u25a0\uacb0\ud569\uc778\ub371\uc2a4\uc758 \uc7a5\uc810<\/strong><\/span>
\u00b7 \uc88b\uc740 \ubd84\ud3ec\ub3c4 <\/em><\/strong><\/span>: <\/span>\ub098\uc05c<\/span> \ubd84\ud3ec\ub3c4\ub97c \uac00\uc9c4 column\uc744 \uacb0\ud569\ud55c \uacb0\ud569 \uc778\ub371\uc2a4\uac00 \ub354 \uc88b\uc740 \ubd84\ud3ec\ub3c4\ub97c \uac00\uc9c8 \uc218 \uc788\ub2e4.
\u00b7 \uc800\uc7a5 \uacf5\uac04\uc758 \ud6a8\uc728\uc131 <\/em><\/strong><\/span>: <\/span>\ud55c \uc9c8\uc758\uc5d0 \uc758\ud574 \uc120\ud0dd\ub41c \ubaa8\ub4e0 \uce7c\ub7fc\uc774 \uacb0\ud569\uc778\ub371\uc2a4\uc5d0 \uc788\uc744 \uacbd\uc6b0, table\uc744 access\ud558\uc9c0 \uc54a\uace0 \uacb0\ud569 \uc778\ub371\uc2a4 \ub9cc\uc73c\ub85c \uc6d0\ud558\ub294 \uac12\uc744 \uac00\uc838\uc62c \uc218 \uc788\ub2e4.<\/span><\/p>\n<\/td>\n<\/tr>\n

\n


\u25a0\uacb0\ud569\uc778\ub371\uc2a4\uc758 \uc0ac\uc6a9 <\/strong><\/span>
\u00b7<\/span>  \uacb0\ud569 \uc778\ub371\uc2a4\uc758 \uc120\ud589 \ubd80\ubd84(leading portion)\uc744 \uc0ac\uc6a9\ud558\ub294 SQL\ubb38\uc778 \uacbd\uc6b0, \uacb0\ud569 \uc778\ub371\uc2a4\uc5d0 \uc758\ud55c access path \uc0ac\uc6a9 \uac00\ub2a5 <\/span>
\u00b7<\/span>  \uc120\ud589\ubd80\ubd84<\/em>
\n\t\t\t\t\t\t\t\t\t\t<\/strong><\/span>: CREATE INDEX<\/span>\ubb38\uc5d0 \uc758\ud574 \uc0dd\uc131\ub41c \uacb0\ud569 \uc778\ub371\uc2a4\ub97c \uad6c\uc131\ud558\ub294 column list\uc5d0 \uc788\ub294 \uccab \ubc88\uc9f8\ub97c \ud3ec\ud568\ud55c \uc5f0\uc18d\uc801\uc778 \uce7c\ub7fc\uc758 \uc9d1\ud569.
\u00b7CREATE INDEX comp_ind
              ON tab1(x, y, z);
<\/strong>\u00b7<\/span> <\/span>\uc5ec\uae30\uc11c<\/span> X, XY, XYZ\ub294 \uacb0\ud569 \uc778\ub371\uc2a4\uc758 \uc120\ud589 \ubd80\ubd84\uc774\ub2e4. \uadf8\ub7ec\ub098 YZ, Z\ub294 \uc120\ud589\ubd80\ubd84\uc774 \uc544\ub2c8\ub2e4. <\/span><\/p>\n<\/td>\n<\/tr>\n

\n


\u25b6<\/span>  \uacb0\ud569 \uc778\ub371\uc2a4\uc758 column\uc120\ud0dd\uc744 \uc704\ud55c \uc9c0\uce68 <\/strong><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u00b7<\/span> \uac01 column\uc758 \ubd84\ud3ec\ub3c4 \ubcf4\ub2e4 \uacb0\ud569 \uc778\ub371\uc2a4\uc5d0\uc11c \uacb0\ud569\ub41c \ubd84\ud3ec\ub3c4\uac00 \ub354 \uc88b\uc744 \uacbd\uc6b0
\u00b7<\/span> \uc5ec\ub7ec \uc9c8\uc758\uc5d0\uc11c \ud558\ub098\uc774\uc0c1\uc758 \uce7c\ub7fc\uac12\uc744 \uac00\uc9c4 \uce7c\ub7fc\uc758 \ub3d9\uc77c\ud55c \uc9d1\ud569\uc744 \uc9c8\uc758\ud560 \uacbd\uc6b0, \uc774\ub4e4 \ubaa8\ub4e0 \uce7c\ub7fc\uc744 \ud3ec\ud568\ud558\ub294 \uacb0\ud569 \uc778\ub371\uc2a4 \uc0dd\uc131\uc744 \uace0\ub824\ud55c\ub2e4. <\/p>\n

\u25b6<\/span>  \uacb0\ud569 \uc778\ub371\uc2a4 \uad6c\uc131\uc2dc column\uc21c\uc11c \ubc30\uce58\ub97c \uc704\ud55c \uc9c0\uce68 <\/strong>
\u00b7<\/span> WHERE\uc808\uc5d0 \uc0ac\uc6a9\ub41c \uce7c\ub7fc\uc744 \uc120\ud589\ubd80\ubd84\uc73c\ub85c \ub9cc\ub4e4\uae30 \uc704\ud55c \uacb0\ud569 \uc778\ub371\uc2a4\ub97c \uc0dd\uc131
\u00b7<\/span> \uce7c\ub7fc\uc758 \uc77c\ubd80\uac00 WHERE\uc808\uc5d0\uc11c \uc790\uc8fc \uc0ac\uc6a9\ub420 \uacbd\uc6b0 => <\/span>\uc790\uc8fc select\ub418\ub294 column\uc744 \uc120\ud589\ubd80\ubd84\uc62c \ub9cc\ub4e4\uc5b4\uc11c \uc774 column\ub9cc\uc73c\ub85c \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub3c4\ub85d \ud55c\ub2e4.
\u00b7<\/span> \ubaa8\ub4e0 \uce7c\ub7fc\uc774 WHERE\uc808\uc5d0\uc11c \ub3d9\uc77c\ud558\uac8c \uc0ac\uc6a9\ub418\uba74 => <\/span>\uc9c8\uc758 \uc131\ub2a5\uc744 \uac1c\uc120\ud558\uae30 \uc704\ud558\uc5ec CREATE INDEX statement\uc5d0\uc11c \ubd84\ud3ec\ub3c4\uac00 \uc88b\uc740 \uc21c\uc11c\ub300\ub85c \ubc30\uc5f4
\u00b7<\/span> \ubaa8\ub4e0 \uce7c\ub7fc\uc774 WHERE\uc808\uc5d0\uc11c \ub3d9\uc77c\ud558\uac8c \uc790\uc8fc \uc0ac\uc6a9\ub418\uc9c0\ub9cc \ub370\uc774\ud130\uac00 \ud55c column\uc5d0 \ub300\ud574 \ubb3c\ub9ac\uc801\uc73c\ub85c \uc815\ub82c\ub418\uc5b4 \uc788\uc73c\uba74 => <\/span>\uadf8 column\uc744 \uacb0\ud569 \uc778\ub371\uc2a4\uc758 \uccab\ubc88\uc9f8\ub85c \uad6c\uc131<\/span><\/p>\n


<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 How to Write Statements That Use Indexes<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  \uc778\ub371\uc2a4 \uc0dd\uc131 \ud6c4, optimizer\ub294 \ub2e8\uc21c\ud788 \uc778\ub371\uc2a4\uac00 \uc874\uc7ac\ud55c\ub2e4\ub294 \uc774\uc720\ub9cc\uc73c\ub85c \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\ub294 access path\ub97c \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uc74c.
\u25b6<\/span>  Optimizer\ub294 SQL\ubb38\uc774 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub294 \ud615\ud0dc\ub97c \uac00\uc9c0\ub294 \uacbd\uc6b0\uc5d0\ub9cc \uadf8 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\ub294 access path\ub97c \uc120\ud0dd\ud560 \uc218 \uc788\ub2e4.
\u25b6<\/span>  SQL\ubb38\uc774 Index\ub97c \uc0ac\uc6a9\ud558\ub294 access path\uc758 \uc0ac\uc6a9\uc744 \uc704\ud558\uc5ec, \uadf8 SQL\ubb38\uc774 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub294 \ud615\ud0dc\ub97c \ud3ec\ud568 \ud574\uc57c \ud568.
\u25b6<\/span>  Cost-based approach\ub97c \uc0ac\uc6a9\ud560 \uacbd\uc6b0\uc5d0\ub294 \ud574\ub2f9 \uc778\ub371\uc2a4\uc5d0 \ub300\ud55c statistics\ub97c \uc0dd\uc131\ud574\uc57c \ud568.
\u25b6<\/span>  SQL\ubb38\uc774 \ud2b9\uc815 access path\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub3c4\ub85d \uad6c\uc131\ud558\uc600\ub354\ub77c\ub3c4 \ub2e4\ub978 \uc885\ub958\uc758 access path\uc5d0 \ub300\ud55c \uc720\uc6a9\uc131\uc5d0 \ub530\ub77c \uadf8 access path\ub97c \uc120\ud0dd\ud558\uac70\ub098 \uc120\ud0dd\ud558\uc9c0 \uc54a\uc744 \uc218 \uc788\uc74c.<\/span><\/p>\n


<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

\n\n\n<\/colgroup>\n\n\n\n\n
\n

\u2663 How to Write Statements That Avoid Using Indexes<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  \uacbd\uc6b0\uc5d0 \ub530\ub77c\uc11c SQL\ubb38\uc5d0 \ub300\ud574\uc11c \uae30\uc874 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\ub294 access path\uc758 \uc0ac\uc6a9\uc744 \uac15\uc81c\uc801\uc73c\ub85c \uc5b5\uc81c \uac00\ub2a5, (\ud2b9\uc815 \uc778\ub371\uc2a4\uc758 \ubd84\ud3ec\ub3c4\uac00 \ub108\ubb34 \ub098\ube60\uc11c \uc624\ud788\ub824 full table scan\uc774 \ub354 \ud6a8\uacfc\uc801\uc778 \uacbd\uc6b0\uc5d0 \uc778\ub371\uc2a4\uc758 \uc0ac\uc6a9\uc744 \uc5b5\uc81c \uac00\ub2a5.)
\u25b6<\/span>  \ub9cc\uc77c SQL\ubb38\uc774 \uc778\ub371\uc2a4 access path\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub294 \ud615\ud0dc\ub85c \uc8fc\uc5b4\uc9c4 \uacbd\uc6b0\uc5d0\ub294 \ub2e4\uc74c \ubc29\ubc95\uc911\uc758 \ud558\ub098\ub85c Optimizer\uac00 \uac15\uc81c\ub85c full table scan\ud558\ub3c4\ub85d \uc720\ub3c4\ud560 \uc218 \uc788\ub2e4. <\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u00b7<\/span> SQL\ubb38\uc758 \uc758\ubbf8\ub97c \ubc14\uafb8\uc9c0 \uc54a\uace0 SQL\ubb38\uc744 \uc218\uc815 <\/span>
\u00b7<\/span> FULL hint\ub97c \uc0ac\uc6a9\ud558\uc5ec Optimizer\uac00 \uc778\ub371\uc2a4 scan \ub300\uc2e0\uc5d0 full table scan\uc744 \uc120\ud0dd\ud558\uac8c \ud568. <\/span>
\u00b7<\/span> INDEX\ub098 AND_EQUAL hint\ub97c \uc0ac\uc6a9\ud558\uc5ec Optimizer\uac00 \ub2e4\ub978 \uc778\ub371\uc2a4 \ub300\uc2e0\uc5d0 \ud558\ub098\uc758 \uc778\ub371\uc2a4\ub098 \uc778\ub371\uc2a4\uc758 \uc9d1\ud569\uc744 \uc0ac\uc6a9\ud558\uac8c \ud568 <\/span><\/p>\n

\u25a0 1. SELECT * FROM tab1 WHERE col1=’A’ <\/span>
\u25a0 2. SELECT * FROM tab1 WHERE col1=’B’;<\/span>
<\/strong><\/span>
\u00b7<\/span>  tab1\uc740 1000\uac1c\uc758 row\ub97c \uac00\uc9c0\uace0 col1\uc758 \uac12\uc740 A~Z\uae4c\uc9c0\uc758 \ubb38\uc790.<\/span>
\u00b7<\/span>  col1\uc758 75%\ub294 ‘A’\uac12\uc744 \uac00\uc9c0\uace0, \ub2e4\ub978 \ubb38\uc790\ub4e4\uc740 \uc804\uccb4 row\uc758 1%\ub9cc\ud07c\uc529 \ub098\ud0c0\ub09c\ub2e4\ub77c\uace0 \uac00\uc815<\/span>
\u00b7<\/span>  1\ubc88 \uc9c8\uc758\uc5d0\uc11c\ub294 ‘A’ \uac12\uc774 \uc804\uccb4 row\uc758 75%\ub97c \ucc28\uc9c0\ud558\ubbc0\ub85c col1\uc5d0 \ub300\ud55c \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\ub294 \uc778\ud14d\uc2a4 scan\ubcf4\ub2e4 full table scan\uc744 \uc0ac\uc6a9\ud558\ub294 \uac83\uc774 \ub354 \ube60\ub974\ub2e4.
\u00b7  2\ubc88 \uc9c8\uc758\uc5d0\uc11c\ub294 \uc804\uccb4 row\uc5d0\uc11c ‘B’\uc758 \uac12\uc740 1%\uc774\ubbc0\ub85c full table scan\ubcf4\ub2e4 index scan\uc774 \ub354 \ube60\ub974\ub2e4.
\u00b7  2\ubc88 \uc9c8\uc758\ub97c \uc704\ud574\uc11c\ub294 index\uc758 \uc0dd\uc131\uc774 \ud544\uc694\ud558\uc9c0\ub9cc 1\ubc88 \uc9c8\uc758\uc5d0 \ub300\ud574\uc11c\ub294 \uc778\ub371\uc2a4\uc758 \uc0ac\uc6a9\uc774 \ubc14\ub78c\uc9c1\ud558\uc9c0\uc54a\ub2e4. <\/span><\/span><\/span><\/span><\/span><\/strong>
\u00b7  \uc9c8\uc758 \uacb0\uacfc\uc758 \ud37c\uc13c\ud2b8\uac00 \ub2e4\ub984\uc5d0\ub3c4 \ubd88\uad6c\ud558\uace0 Optimzer\ub294 \uac01 \uce7c\ub7fc \uac12\uc758 \ubc1c\uc0dd\ube48\ub3c4\ub97c \uc54c \uc218 \uc5c6\uae30 \ub54c\ubb38\uc5d0 \uc774\ub4e4 \ub450 \uc9c8\uc758\ub4e4\uc5d0 \ub300\ud574 \ub3d9\uc77c\ud55c access path\ub97c \uc120\ud0dd\ud560 \uac83\uc774\ub2e4. <\/p>\n

\u25a0\ud574\uacb0\ubc29\ubc95<\/strong>
\u00b7 CREATE INDEX col1_ind ON tab1(col1); <\/strong>
\u00b7 1\ubc88 \uc9c8\uc758\uc5d0\uc11c\ub294 COL1_IND \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\ub294 access path\ub97c \uc720\uc6a9\ud558\uc9c0 \uc54a\uac8c \ub9cc\ub4e4\uae30 \uc704\ud574 WHERE\uc808\uc744 \ub2e4\uc74c\uacfc \uac19\uc774 \uc218\uc815
\u00b7(SELECT * FROM tab1 WHERE col1 || ”=’A’; <\/strong>)
\u00b7 \uc774\uac83\uc740 \uc774 \uc9c8\uc758\uac00 COL1_IND\uc5d0 \uc758\ud574 \uc81c\uacf5\ub418\ub294 access path\uc758 \uc0ac\uc6a9\uc744 \uc5b5\uc81c\ud568.
\u00b7 WHERE\uc808\uc5d0\uc11c \uc778\ub371\uc2a4\ub41c \uce7c\ub7fc\uc5d0 \ub300\ud558\uc5ec function\uc774\ub098 operation\uc744 \uc801\uc6a9\ud558\uba74 \uc778\ub371\uc2a4 access path\ub294 \uc0ac\uc6a9\ud560 \uc218 \uc5c6\uac8c \ub41c\ub2e4. \ub530\ub77c\uc11c Optimizer\ub294 \uc774 \uc9c8\uc758\uc5d0 \ub300\ud574 full table scan\uc744 \ucd5c\uc801\uc758 access path\ub85c \uc120\ud0dd\ud55c\ub2e4. <\/p>\n

      \u25a0NOTE : <\/em><\/strong>
           \u00b7  WHERE\uc808\uc5d0\uc11c\uc758 \uc774\ub7ec\ud55c \ubcc0\ud654\ub294 \uc870\uac74\uc5d0 \uc758\ud55c \uacb0\uacfc\ub97c \ubcc0\ud558\uac8c \ud558\uc9c0\ub294 \uc54a\uc73c\ubbc0\ub85c \uc9c8\uc758 \uacb0\uacfc \uc5ed\uc2dc \ub3d9\uc77c\ud558\ub2e4.
           \u00b7  \uadf8\ub9ac\uace0 Number\ub098 Date type\uc758 \uce7c\ub7fc\uc5d0 \ub300\ud574\uc11c\ub294 \ud574\ub2f9 \uce7c\ub7fc \uac12\uc5d0 0\uc744 \ub354\ud558\uc5ec \ub3d9\uc77c\ud55c \ubaa9\ud45c\ub97c \ub2ec\uc131\ud560 \uc218 \uc788\ub2e4. <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

\n\n\n<\/colgroup>\n\n\n
\n

\u261eHow to Use Clusters<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 \ud14c\uc774\ube14\ub4e4\uc744 \ud074\ub7ec\uc2a4\ud130\ub9c1\ud558\uae30 \uc704\ud55c \uc9c0\uce68 <\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25a0 \ud074\ub7ec\uc2a4\ud130\ub9c1\uc774 \uc720\uc6a9\ud55c \uacbd\uc6b0 <\/strong><\/span>
\u00b7<\/span>  Join<\/span>\ubb38\uc5d0\uc11c \uc790\uc8fc access\ub418\ub294 \ud14c\uc774\ube14\ub4e4\uc744 \ud074\ub7ec\uc2a4\ud130\ub9c1
\u00b7<\/span>  master \ub808\ucf54\ub4dc\uc640 \ub300\uc751\ud558\ub294 detail \ub808\ucf54\ub4dc\ub97c \uc790\uc8fc select\ud558\ub294 \uacbd\uc6b0\uc5d0\ub294 master \ud14c\uc774\ube14\uacfc detail \ud14c\uc774\ube14\uc744 \ud568\uaed8 \ud074\ub7ec\uc2a4\ud130\ub9c1
\u00b7<\/span>  \ub3d9\uc77c\ud55c master\uc5d0 \ub300\ud55c \ub300\ub7c9\uc758 detail \ub808\ucf54\ub4dc\ub97c \uc790\uc8fc \uc120\ud0dd\ud558\ub294 \uacbd\uc6b0\uc5d0\ub294 \ud074\ub7ec\uc2a4\ud130\ub0b4\uc5d0\uc11c detail \ud14c\uc774\ube14\uc744 \ubcc4\ub3c4\ub85c \uc800\uc7a5 <\/p>\n

\u25a0\ud074\ub7ec\uc2a4\ud130\ub9c1\uc774 \uc720\uc6a9\ud558\uc9c0 \uc54a\uc740 \uacbd\uc6b0 <\/strong><\/span>
\u00b7<\/span>  <\/span>\uc751\uc6a9\uc5d0\uc11c \ub9e4\uc6b0 \ub4dc\ubb3c\uac8c Join\ub418\uac70\ub098 Join\uc5d0 \ucc38\uc5ec\ud558\ub294 \uacf5\ud1b5 column\uac12\ub4e4\uc774 \ube48\ubc88\ud558\uac8c \uc218\uc815\ub420 \uacbd\uc6b0
\u00b7<\/span>  \uc751\uc6a9\uc5d0\uc11c \ud074\ub7ec\uc2a4\ud130\ub41c \ud14c\uc774\ube14\ub4e4\uc911\uc5d0\uc11c \ud55c \ud14c\uc774\ube14\uc5d0 \ub300\ud574\uc11c \uc790\uc8fc full table scan\uc744 \ud558\ub294 \uacbd\uc6b0
\u00b7<\/span>  \ub3d9\uc77c\ud55c \ud074\ub7ec\uc2a4\ud130 key\uac12\uc744 \uac00\uc9c4 \ubaa8\ub4e0 \ud14c\uc774\ube14\uc758 \ub370\uc774\ud0c0\uac00 \ud558\ub098\uc774\uc0c1\uc758 Oracle block\ubcf4\ub2e4 \ub354 \ud074 \uacbd\uc6b0\uc5d0\ub294 \ud074\ub7ec\uc2a4\ud130\ub9c1 \ud558\uc9c0\uc54a\ub294 \uac83\uc774 \uc88b\ub2e4.
\n<\/span><\/p>\n

\u25b6<\/span> \uc751\uc6a9\uc758 \ud544\uc694\uc5d0 \ub530\ub77c \uc694\uad6c\ub418\ub294 \ub3d9\uc77c\ud55c \ud074\ub7ec\uc2a4\ud130\ub9c1\uc758 \uc7a5\ub2e8\uc815\uc744 \uc798 \uace0\ub824\ud574\uc57c \ud55c\ub2e4.
\u25b6  Join \ubb38\uc5d0 \uc758\ud574 \uc5bb\uc5b4\uc9c0\ub294 \uc131\ub2a5 \uc774\ub4dd\uc774 cluster key\uac12\uc758 \uc218\uc815\uc5d0 \ub530\ub978 \uc131\ub2a5 \uc190\uc2e4\ubcf4\ub2e4 \ud070\uc9c0\ub97c \uacb0\uc815\ud574\uc57c\ud560 \uac83\uc774\ub2e4.
\u25b6  \uc774\ub97c \uc704\ud574\uc11c \ud074\ub7ec\uc2a4\ud130\ub41c \ud14c\uc774\ube14\uacfc \ubcc4\ub3c4\ub85c \uc800\uc7a5\ub41c \ud14c\uc774\ube14\uc758 \ucc98\ub9ac\uc2dc\uac04\uc744 \ube44\uad50\ud558\uae30 \uc704\ud55c \uc2e4\ud5d8\uc774 \ud544\uc694\ud558\ub2e4.
\u25b6  \ud074\ub7ec\uc2a4\ud130\ub97c \uc0dd\uc131\ud558\ub824\uba74 CREATE CLUSTER\uba85\ub839\uc744 \uc0ac\uc6a9\ud558\uba74 \ub41c\ub2e4. <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

\n\n\n<\/colgroup>\n\n\n
\n

\u261eHow to Use Hashing<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 Hash cluster\ub97c \uc0ac\uc6a9\ud558\uae30 \uc704\ud55c \uc9c0\uce68 <\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25a0<\/span>  Hash cluster\uc0ac\uc6a9\uc774 \uc720\uc6a9\ud55c \uacbd\uc6b0 <\/strong>
\u00b7<\/span>  WHERE\uc808\uc5d0\uc11c \ub3d9\uc77c\ud55c column\uc774\ub098 column\uc758 \uc870\ud569\uc5d0 \ub300\ud55c \ub3d9\uce58 \ube44\uad50\ub97c \ud3ec\ud568\ud558\ub294 \uc9c8\uc758\uac00 \ube48\ubc88\ud560 \uacbd\uc6b0
\u00b7<\/span>  \uc8fc\uc5b4\uc9c4 \ud074\ub7ec\uc2a4\ud2b8 \ud0a4 \uac12\uc744 \uac00\uc9c4 \ubaa8\ub4e0 row\ub97c \uc0bd\uc785\ud558\uae30 \uc704\ud574 \ud544\uc694\ud55c \uc800\uc7a5 \uacf5\uac04\uc744 \uacb0\uc815\ud560 \uc218 \uc788\uc73c\uba74 \ud558\ub098\uc758 Hash cluster\uc5d0 \ud558\ub098\uc758 table\uc5d0 \uc800\uc7a5
\u00b7<\/span>  \uc784\uc758\uc758 \ud14c\uc774\ube14\uc774 \ub2e4\ub978 \ud14c\uc774\ube14\ub4e4\uacfc \uc790\uc8fc \uc870\uc778\ub418\ub294 \uac83\uacfc \uad00\uacc4\uc5c6\uc774 Hashing \uc870\uac74\uc5d0 \uc801\uc808\ud558\uba74, \uadf8 \ud14c\uc774\ube14\uc744 \ud558\ub098\uc758 Hash cluster\uc5d0 \uc800\uc7a5\ud558\ub294 \uac83\uc774 \uc720\ub9ac\ud568.<\/p>\n

\u25a0<\/span>  Hash cluster\uc0ac\uc6a9\uc774 \uc720\uc6a9\ud558\uc9c0 \uc54a\uc740 \uacbd\uc6b0 <\/strong>
\u00b7<\/span>  \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc5d0 \uc788\ub294 \uacf5\uac04\uc774 \ubd80\uc871\ud558\uc5ec \ucd94\uac00\ub85c \uc0bd\uc785\ud560 row\ub97c \uc704\ud574\uc11c \ud544\uc694\ud55c \uc5ec\uc720 \uacf5\uac04\uc774 \uc5c6\uc744 \uacbd\uc6b0
\u00b7<\/span>  \ud56d\uc0c1 \uc99d\uac00\ub418\ub294 \ud14c\uc774\ube14\uc77c \uacbd\uc6b0
\u00b7<\/span>  \uc751\uc6a9\uc5d0\uc11c \ud14c\uc774\ube14\uc758 full scan\uc744 \uc790\uc8fc \uc2e4\ud589\ud558\uace0 \ucd94\ud6c4 \ud14c\uc774\ube14\uc774 \ud06c\uac8c \uc99d\uac00\ub420 \uac83\uc744 \uc608\uc0c1\ud558\uc5ec \ud574\uc26c \ud074\ub7ec\uc2a4\ud130\uc5d0 \ud070 \uc5ec\uc720 \uacf5\uac04\uc744 \ud560\ub2f9\ud574\uc57c\ub9cc\ud560 \uacbd\uc6b0
\u00b7<\/span>  \uc751\uc6a9\uc5d0\uc11c \ud074\ub7ec\uc2a4\ud130 \ud0a4 \uac12\uc744 \uc790\uc8fc \uc218\uc815\ud558\ub294 \uacbd\uc6b0<\/p>\n

\u25b6<\/span>  \uc751\uc6a9\uc758 \ud544\uc694\uc5d0 \ub530\ub77c Hash cluster\uc758 \uc7a5\ub2e8\uc815\uc744 \uc798 \uace0\ub824\ud574\uc57c \ud568.
\u25b6<\/span>  Hash cluster\uc5d0 \uc800\uc7a5\ub41c \ud14c\uc774\ube14\uacfc \uc778\ub371\uc2a4\uc5d0 \uc758\ud574 \ubcc4\ub3c4\ub85c \uc800\uc7a5\ub41c \ud14c\uc774\ube14\uc758 \ucc98\ub9ac\uc2dc\uac04\uc744 \ube44\uad50\ud558\uae30 \uc704\ud55c \uc2e4\ud5d8\uc774 \ud544\uc694
\u25b6<\/span>  Hash cluster\ub97c \uc0dd\uc131\ud558\ub824\uba74 CREATE CLUSTER\uba85\ub839\uc5d0\uc11c HASH\uc640 HASHKEYS \ud30c\ub77c\uba54\ud130\ub97c \uc0ac\uc6a9 <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 How to Determine How Many Hash Value to Use <\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  Hash cluster\ub97c \uc0dd\uc131\ud560 \ub54c \ud574\uc26c \uac12\uc758 \uc218\ub97c \uc815\ud558\uae30 \uc704\ud558\uc5ec CREATE CLUSTER\ubb38\uc758 HASHKEYS \ud30c\ub77c\uba54\ud0c0\ub97c \uc0ac\uc6a9
\u25b6<\/span>  \ud574\uc26c scan\uc758 \ucd5c\ub300 \uc131\ub2a5\uc744 \uc704\ud574 \ucd5c\uc18c\ud55c cluster key\uac12 \uc218\ub9cc\ud07c \ud070 hashkey\uac12\uc744 \uc120\ud0dd\ud574\uc57c \ud568.
\u25b6<\/span>  Collision\uc740 hash scan \uc218\ud589\ud6c4\uc5d0 \uac01 \ube14\ub7ed\uc5d0 \uc788\ub294 row\uc5d0 \ub300\ud558\uc5ec cluster key \uac12\uc774 \uc62c\ubc14\ub978\uc9c0 \uc5ec\ubd80\ub97c \uac80\uc0ac\ud558\uac8c \ub418\uc5b4 hash scans\uc758 \uc131\ub2a5\uc744 \uac10\uc18c\uc2dc\ud0b4
\u25b6<\/span>  Oracle\uc5d0\uc11c\ub294 \uc2e4\uc81c Hash \uac12\uc758 \uc218\ub97c \uad6c\ud558\uae30 \uc704\ud558\uc5ec \uc0ac\uc6a9\uc790\uac00 \uc9c0\uc815\ud55c HASHKEYS \uac12\uacfc \uac00\uc7a5 \uadfc\uc811\ud55c prime number\ub85c \ucc98\ub9ac\ud558\uc5ec Collisions\uc744 \uac10\uc18c\uc2dc\ud0b4 <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

  <\/p>\n

\n\n\n<\/colgroup>\n\n\n\n
\n

\u261eHow to Use Hints<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25cf<\/span>  \uc751\uc6a9\uc124\uacc4\uc790\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<\/span>  \uc751\uc6a9\uc124\uacc4\uc790\ub294 optimizer\ubcf4\ub2e4 \ub354 \uc88b\uc740 execution plan\uc744 \uc120\ud0dd\ud560 \uc218 \uc788\uc74c.
\u25cf<\/span>  \uc751\uc6a9\uc124\uacc4\uc790\ub294 hint\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 optimizer\uc5d0\uac8c \uc9c0\uc2dc \uac00\ub2a5. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

\n\n\n<\/colgroup>\n\n\n\n\n\n\n
\n

\u2663 Hints\uc758 \uc0ac\uc6a9 <\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25cf<\/span>  Hints\uc758 \uc0ac\uc6a9\ubc94\uc704<\/strong><\/p>\n

\u25b6<\/span>  SQL\ubb38\uc744 \uc704\ud55c Optimization approach
\u25b6<\/span>  SQL\ubb38\uc5d0 \ub300\ud55c Cost-based approach\uc758 goal(best throughput, best response time)
\u25b6<\/span>  SQL\ubb38\uc5d0 \uc758\ud574 \uc811\uadfc\ub418\ub294 \ud14c\uc774\ube14\uc5d0 \ub300\ud55c access path
\u25b6<\/span>  join\ubb38\uc5d0 \ub300\ud55c join\uc21c\uc11c
\u25b6<\/span>  join\ubb38\uc744 \ucc98\ub9ac\ud558\uae30 \uc704\ud55c join operation <\/span><\/p>\n<\/td>\n<\/tr>\n

\n


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

\u25b6<\/span>  \uac04\ub2e8\ud55c SELECT, UPDATE, DELETE SQL\ubb38
\u25b6<\/span>  \ud55c parent SQL\ubb38\ub098 complex SQL\ubb38 \ub0b4\uc758 subquery
\u25b6<\/span>  \ud55c compound query\uc911\uc758 \ud55c \ubd80\ubd84
\u00b7<\/span> 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.)
\u00b7<\/span> \uccab \ubc88\uc9f8 component query\uc5d0 \uc788\ub294 hint\ub294 \uccab \ubc88\uc9f8 component\uc758 optimizer\uc5d0\ub9cc \uc801\uc6a9
\u00b7<\/span> \ub450 \ubc88\uc9f8 component query\uc5d0 \ub300\ud55c optimizer\uc5d0\ub294 \uc801\uc6a9\ub418\uc9c0 \uc54a\ub294\ub2e4. <\/span><\/p>\n<\/td>\n<\/tr>\n

\n


\u25cf<\/span>  hint\ub97c \ud3ec\ud568\ud558\ub294 Syntax<\/strong>
\n\t\t\t\t\t\t\t<\/span><\/p>\n


\n\t\t\t\t\t\t\t<\/span><\/p>\n

\u00b7DELETE, SELECT, UPDATE<\/em> : SQL\ubb38 block\uc744 \uc2dc\uc791\ud558\ub294 DELETE, SELECT, UPDATE \ud0a4\uc6cc\ub4dc\uc774\ub2e4. Hint\ub97c \ud3ec\ud568\ud558\ub294 comments\ub294 \uc774 \ud0a4\uc6cc\ub4dc\ub4e4\uc774 \ub098\ud0c0\ub09c \ud6c4\uc5d0 \uc368\uc57c\ud55c\ub2e4. <\/span>
\u00b7+<\/em> : \uc624\ub77c\ud074\uc774 \uadf8 comment\ub97c \ud574\uc11d\ud560 \uc218 \uc788\uac8c \ud574\uc8fc\ub294 \uae30\ub2a5\uc744 \ud55c\ub2e4. \uc774 ‘ + ‘\ub294 comment \ud45c\uc2dc \ud6c4\uc5d0 \ube48\uce78(blank)\uc774 \uc5c6\uc774 \uc989\uc2dc \ub530\ub77c\uc640\uc57c\ub9cc \ud55c\ub2e4. <\/span>
\u00b7hint<\/em> : \uc774\uc808\uc5d0\uc11c \uc5b8\uae09\ub418\ub294 hint\uc911\uc758 \ud558\ub098\uc774\ub2e4. \ub9cc\uc57d comment\uac00 \uc5ec\ub7ec hint\ub97c \uac00\uc9c4\ub2e4\uba74 \uadf8 hint\ub4e4\uc740 \ucd5c\uc18c\ud55c \ud558\ub098\uc758 \uacf5\uac04\uc73c\ub85c\uc11c \ubd84\ub9ac\ub418\uc5b4 \uc788\uc5b4\uc57c\ub9cc \ud55c\ub2e4. <\/span>
\u00b7text<\/em> : hint\uc5d0 \ub300\ud55c \uc124\uba85<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n


\u25cf<\/span>  hint\ub97c \ubb34\uc2dc\ud558\ub294 \uacbd\uc6b0<\/strong><\/p>\n

\u25b6<\/span>  hint\uac00 \uc4f0\uc778 comment\uac00 DELETE, SELECT, UPDATE \ud0a4\uc6cc\ub4dc \ub4a4\uc5d0 \uc624\uc9c0 \uc54a\uace0, \ub2e4\ub978 \uacf3\uc5d0 \uc4f0\uc5ec \uc788\ub294 \uacbd\uc6b0
\u25b6<\/span>  hint\uac00 Syntax error\ub97c \uac00\uc9c4 \uacbd\uc6b0
     (\ub3d9\uc77c\ud55c comment\ub0b4\uc5d0 error\ub97c \uac00\uc9c0\uc9c0 \uc54a\uace0 \uc62c\ubc14\ub85c \ud45c\ud604\ub41c \ub2e4\ub978 hint\ub4e4\uc740 \uc778\uc815)
\u25b6<\/span>  \ucda9\ub3cc\ud55c hint\uc758 \uc870\ud569\ub4e4\uc740 \ubb34\uc2dc(\ud55c comment\ub0b4\uc5d0 \ub450 \uac1c \uc774\uc0c1\uc758 hint\uac00 \uc788\uc744 \uacbd\uc6b0, Optimizer\uac00 \uc5b4\ub5a4 hint\ub97c \uba3c\uc800 \uc368\uc57c\ud558\ub294\uc9c0 \ubaa8\ub974\ub294 \uacbd\uc6b0)
      (\ub3d9\uc77c\ud55c comment\ub0b4\uc5d0 \ucda9\ub3cc\ud55c hint\uc678\uc5d0 \ub2e4\ub978 hint\ub294 \uc778\uc815.) <\/p>\n

\u00b7<\/span> \uc624\ub77c\ud074\uc740 SQL*Forms Version 3 Trigger\uc640 \uac19\uc740 PL\/SQL Version1\uc744 \uc0ac\uc6a9\ud558\ub294 \ud658\uacbd\uc5d0\uc11c\ub294 \ubaa8\ub4e0 SQL \ubb38\uc5d0 \uc788\ub294 hint\ub4e4\uc744 \ubb34\uc2dc.
\u00b7<\/span> Optimizer\ub294 cost-based approach\ub97c \uc0ac\uc6a9\ud558\ub294 \uacbd\uc6b0\uc5d0 hint\ub97c \uc778\uc2dd.
\u00b7<\/span> \ud55c SQL\ubb38 block\uc5d0 RULE hint\ub97c \uc81c\uc678\ud55c \uc5b4\ub5a4 hint\ub4e4\uc774 \ud3ec\ud568\ub418\uc5b4 \uc788\uc73c\uba74, optimizer \ub294 \uc790\ub3d9\uc801\uc73c\ub85c cost-based approach\ub97c \uc0ac\uc6a9. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

<\/p>\n\n\n<\/colgroup>\n\n\n\n\n
 <\/td>\n<\/tr>\n
\n

\u261e
\n\t\t\t\t\t\t\t\t\t\t\tHint for Optimization Approaches and Goals<\/strong><\/span>
\n\t\t\t\t\t\t\t\t\t<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6  hint\ub294 cost-based approach\uc640 rule-based optimization approach \uc911\uc5d0 \ud558\ub098\ub97c \uc120\ud0dd
\u25b6  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
\u25b6  SQL\ubb38\uc774 optimization approach \uc640 goal\uc744 \uae30\uc220\ud55c \ud55c\uac1c\uc758 hint\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. <\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/span><\/span><\/p>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 ALL_ROWS<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  ALL_ROWS<\/strong><\/span> hint\ub294 best throughput<\/span>(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
\u25b6<\/span>  optimizer\ub294 best throughput\uc744 \ubaa9\uc801\uc73c\ub85c SQL\ubb38\ub97c \ucd5c\uc801\ud654    \ud558\ub824\uba74
\u00b7SELECT \/*+ ALL_ROWS *\/ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566; <\/span><\/span><\/strong><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 FIRST_ROWS<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  FIRST_ROWS<\/strong><\/span> hint\ub294 best response time<\/span>\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)
\u25b6<\/span>  \uc774 hint\ub294 \uc544\ub798 \ub0b4\uc6a9\uc744 \uc774\ud589\ud560 \uc218 \uc788\ub294 optimizer\ub97c \uc0dd\uc131
\u00b7<\/span> Index scan\uc744 \uc4f8\uc218 \uc788\ub2e4\uba74, optimizer\ub294 full table scan\ubcf4\ub2e4\ub294 Index scan\uc744 \uc0ac\uc6a9.
\u00b7<\/span> 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.
\u00b7<\/span> 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. <\/p>\n

\u25b6<\/span>  optimizer\ub294 best response time\uc744 \ubaa9\uc801\uc73c\ub85c \uc544\ub798 SQL\ubb38\ub97c \ucd5c\uc801\ud654\ud558\ub824\uba74.
\u00b7SELECT \/*+ FIRST_ROWS *\/ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566; <\/span><\/span><\/strong>
<\/span>
\u25b6<\/span>  Optimizer<\/span>\ub294 \uc544\ub798 Syntax\ub97c \uac00\uc9c0\ub294 DELETE\uc640 UPDATE SQL\ubb38 blocks\uacfc SELECT SQL\ubb38 blocls\uc5d0 \uc788\ub294 hint\ub294 \ubb34\uc2dc.
\u00b7 set operators(UNION, INTERSECT, MINUS, UNION ALL) <\/strong><\/span>
\u00b7 GROUP BY\uc808<\/strong><\/span>
\u00b7FOR UPDATE \uc808<\/strong><\/span>
\u00b7group functions<\/strong><\/span>
\u00b7DISTINCT operator<\/strong><\/span>
\u25b6<\/span>  <\/span>\uc774\ub4e4 SQL\ubb38\ub294 best response\ub97c \ubaa9\uc801\uc73c\ub85c \ucd5c\uc801\ud654\ub420 \uc218 \uc5c6\ub2e4.
\u25b6<\/span>  \uc704\uc758 \uacbd\uc6b0 \uccab \ubc88\uc9f8 row\ub97c \ubc18\ud658\ud558\uae30 \uc804\uc5d0 SQL\ubb38\uc5d0 \uc811\uadfc\ub418\ub294 \ubaa8\ub4e0 \ud589\ub4e4\uc744 retrieve\ud574\uc57c\ub9cc \ud568.
\u25b6<\/span>  \uc774\ub7f0 SQL\ubb38\uc5d0 \ub300\ud574 hint\ub97c \uc4f0\uba74 optimizer\ub294 cost-based approach\ub97c \uc0ac\uc6a9\ud558\uace0, best throughput\uc744 \ubaa9\uc801\uc73c\ub85c \ucd5c\uc801\ud654. <\/p>\n

\u25b6<\/span>  SQL\ubb38\uc5d0 ALL_ROWS\ub098 FIRST_ROWS hint\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.
\u25b6<\/span>  \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.
\u25b6<\/span>  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.
\u25b6<\/span>  Access path\ub97c \uc704\ud55c hint \ub610\ub294 ALL_ROWS\ub098 FIRST_ROWS hint\ub97c \uac00\uc9c0\ub294 join operation\uc744 \uae30\uc220\ud55c\ub2e4\uba74, optimizer\ub294 hint\uc5d0 \uc758\ud574 \uae30\uc220\ub41c join operation\ub4e4\uacfc access paths\ub97c \uc6b0\uc120\uc801\uc73c\ub85c \ucde8\ud568. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 CHOOSE<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  CHOOSE<\/strong><\/span> hint\ub294 statistics\uac00 \uc874\uc7ac\ud558\uace0, SQL\ubb38\uc5d0 \uc758\ud574 \uc811\uadfc\ub41c \ud14c\uc774\ube14\uc5d0 \ub300\ud574 rule-based approach\ub098 cost-based approach\uc911 \uc5b4\ub5a4 \uac83\uc744 \uc4f8 \uac83\uc778\uc9c0 optimizer\uac00 \uc120\ud0dd<\/span>.
\u25b6<\/span>  data dictionary\uac00 \uc774 table\ub4e4 \uc911 \ucd5c\uc18c\ud55c \ud558\ub098\uc5d0 \ub300\ud55c statistics\ub97c \uac00\uc9c4\ub2e4\uba74<\/strong> optimizer\ub294 cost-based approach<\/strong>\ub97c \uc0ac\uc6a9.
\u25b6<\/span>  data dictionary\uac00 \uc774 table\ub4e4 \uc911 \uc5b4\ub5a4 \uac83\uc5d0 \ub300\ud574\uc11c\ub3c4 statistics\ub97c \uac00\uc9c0\uc9c0 \uc54a\ub294\ub2e4\uba74 optimizer\ub294 rule-based approach\ub97c \uc0ac\uc6a9.
\u25b6<\/span>  \uc544\ub798 SQL\ubb38\uc5d0\uc11c \ub9cc\uc57d EMP \ud14c\uc774\ube14\uc5d0 \ub300\ud574 statistics\uac00 \uc788\ub2e4\uba74 optimizer\ub294 cost-based approach\ub97c \uc0ac\uc6a9.
\u25b6<\/span>  data dictionary\uc5d0 EMP \ud14c\uc774\ube14\uc5d0 \ub300\ud574 \uc5b4\ub5a4 statistics\ub3c4 \uc874\uc7ac\ud558\uc9c0 \uc54a\ub294\ub2e4\uba74 optimizer\ub294 rule-based approach\ub97c \uc0ac\uc6a9.
\u00b7SELECT \/*+ CHOOSE *\/ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566; <\/span><\/span><\/strong><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 RULE<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  RULE<\/span> hint\ub294 \ud55c SQL\ubb38 block\uc5d0 \ub300\ud574 rule-based optimization\uc744 \uc120\ud0dd.
\u25b6<\/span>  \uc774 hint\ub294 optimizer\uac00 SQL\ubb38 block\uc5d0 \ub300\ud574 \uae30\uc220\ud55c \ub2e4\ub978 hint\ub4e4\uc744 \ubb34\uc2dc.
\u25b6<\/span>  Optimizer\ub294 \uc544\ub798 SQL\ubb38\ub97c \uc704\ud574\uc11c rule-based approach\ub97c \uc0ac\uc6a9.
\u00b7SELECT –+ RULE
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t
               empno, ename, sal, job
              FROM emp
              WHERE empno = 7566; <\/span><\/span><\/strong>
\u25b6<\/span>  rule-based approach<\/span>\ub97c<\/span> \uc4f0\ub294 RULE hint\ub294 \uc624\ub77c\ud074\uc758 \ub2e4\uc74c \ubc84\uc804\uc5d0\uc11c\ub294 \uc0ac\uc6a9\ud558\uc9c0 \uc54a\ub294\ub2e4.<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u261e
\n\t\t\t\t\t\t\t\t\t\t\tHint for Access Methods<\/strong><\/span>
\n\t\t\t\t\t\t\t\t\t<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6  \uac01 hint\ub4e4\uc740 table\uc5d0 \ub300\ud55c access method\ub97c \uc81c\uc548
\u25b6  hint\uc911\uc758 \ud558\ub098\ub97c \uae30\uc220\ud558\ub294 \uac83\uc740 access path\uac00 \uc778\ub371\uc2a4\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
\u25b6  \ud78c\ud2b8\uac00 access path\ub97c \uc774\uc6a9\ud560\uc218 \uc5c6\ub2e4\uba74 optimizer\ub294 \uadf8\uac83\uc744 \ubb34\uc2dc.
\u25b6  SQL\ubb38\uc5d0\uc11c \uc815\ud655\ud558\uac8c access\ub418\ub294 \ud14c\uc774\ube14\uc744 \uae30\uc220
\u25b6  SQL\ubb38\uc774 table\uc5d0 \ub300\ud55c alias\ub97c \uc0ac\uc6a9\ud558\uba74, hint\uc5d0\uc11c table\uc758 \uc774\ub984\ubcf4\ub2e4\ub294 alias\ub97c \uc0ac\uc6a9.
\u25b6  \ud14c\uc774\ube14\uc758 \uc774\ub984\uc774\ub098 alias \ub294 local database\uc5d0 \uc788\ub294 \ud55c \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \ud558\ub098\uc758 synonym\uc774\ub098 \ud558\ub098\uc758 table\uc744 \uc758\ubbf8\ud568. <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 FULL<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

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

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 ROWID<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  ROWID<\/strong><\/span> hint\ub294 \ud14c\uc774\ube14\uc5d0 \ub300\ud574 ROWID\uc5d0 \uc758\ud55c table scan\uc744 \uc120\ud0dd
\u25b6<\/span>  ROWID hint\uc758 \ubb38\ubc95\uc740 ROWID(table)<\/em><\/strong><\/span>
\u25b6<\/span>  (table)<\/em><\/strong><\/span>\uc5d0\ub294 ROWID\uc5d0 \uc758\ud55c table scan\uc774 \uc774\ud589\ub418\uc5b4\uc9c0\ub294 table\uc758 alias\ub098 \uc774\ub984\uc744 \uae30\uc220. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 CLUSTER<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  CLUSTER<\/strong><\/span> hint\ub294 \ud14c\uc774\ube14\uc5d0 \ub300\ud574 cluster scan\uc744 \uc120\ud0dd
\u25b6<\/span>  CLUSTER hint\uc758 \ubb38\ubc95\uc740 CLUSTER(table)<\/em><\/strong><\/span>
\u25b6<\/span>  (table)<\/em><\/strong><\/span>\uc5d0\ub294 cluster scan\uc5d0 \uc758\ud574 \uc811\uadfc\ub418\ub294 \ud14c\uc774\ube14\uc758 \uc774\ub984\uc774\ub098 alias\ub97c \uae30\uc220. <\/p>\n

\u00b7SELECT –+ CLUSTER emp, ename, deptno
              FROM emp, dept
              WHERE deptno = 10 AND emp.deptno = dept.deptno; <\/span><\/span><\/strong><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 HASH<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  HASH<\/strong><\/span> hint\ub294 \ud14c\uc774\ube14\uc5d0 \ub300\ud574 HASH scan\uc744 \uc120\ud0dd
\u25b6<\/span>  HASH hint\uc758 \ubb38\ubc95\uc740 HASH(table)<\/em><\/strong><\/span>
\u25b6<\/span>  (table)<\/em><\/strong><\/span>\uc5d0\ub294 hash scan\uc5d0 \uc758\ud574 \uc811\uadfc\ub418\ub294 \ud14c\uc774\ube14\uc758 \uc774\ub984\uc774\ub098 alias\ub97c \uae30\uc220 <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 INDEX<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  INDEX<\/strong><\/span> hint\ub294 \ud14c\uc774\ube14\uc5d0 \ub300\ud574 index scan\uc744 \uc120\ud0dd
\u25b6<\/span>  INDEX hint\uc758 \ubb38\ubc95\uc740
\n<\/span><\/p>\n


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


\u25b6<\/span>  hint\ub294 \ud558\ub098\uc774\uc0c1\uc758 indexes\ub4e4\uc744 \uae30\uc220
\u00b7<\/span> hint\uac00 \ud558\ub098\uc758 \uc0ac\uc6a9\uac00\ub2a5\ud55c index\ub97c \uae30\uc220\ud55c\ub2e4\uba74, optimizer\ub294 index\uc5d0\uc11c \ud55c\uac1c\uc758 scan\uc744 \uc218\ud589.
\u00b7<\/span> optimizer\ub294 full table scan\uc774\ub098 \ud14c\uc774\ube14\uc5d0 \uc788\ub294 \ub2e4\ub978 index\uc5d0 \ub300\ud55c scan\uc740 \uc218\ud589\ud558\uc9c0 \uc54a\uc74c.
\u00b7<\/span> hint\uac00 \uc0ac\uc6a9\uac00\ub2a5\ud55c index\uc758 \ub9ac\uc2a4\ud2b8\ub97c \uae30\uc220\ud55c\ub2e4\uba74, optimizer\ub294 \ub9ac\uc2a4\ud2b8\uc5d0 \uc788\ub294 \uac01\uac01\uc758 \uc778\ub371\uc2a4\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
\u00b7<\/span> \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 \uc778\ub371\uc2a4\ub97c scan\ud558\uace0 \uadf8 \uacb0\uacfc\ub4e4\uc744 merge.
\u00b7<\/span> optimizer\ub294 full table scan\uc774\ub098 hint\uc5d0 \uc788\uc9c0 \uc54a\uc740 index scan\uc740 \uace0\ub824\ud558\uc9c0 \uc54a\uc74c.
\u00b7<\/span> hint\uac00 \uc5b4\ub5a4 index\ub3c4 \uae30\uc220\ud558\uc9c0 \uc54a\uc558\ub2e4\uba74, optimizer\ub294 \ud14c\uc774\ube14\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.
\u00b7<\/span> \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.
\u25b6<\/span>  SELECT name, height, weight
              FROM patients
              WHERE sex=’M’ <\/strong><\/span><\/p>\n

\u00b7<\/span>  sex\uc758 \uc5f4\uc740 index\ub418\uc5b4 \uc788\uace0, \uc774 \uce7c\ub7fc\uc740 ‘M’\uacfc ‘F’\uc758 \uac12\uc744 \uac00\uc9d0.
\u00b7<\/span>  \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 \ud14c\uc774\ube14\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 \ub41c\ub2e4.
\u00b7<\/span>  \ubcd1\uc6d0\uc758 \ud658\uc790 \uc911 \ub0a8\uc790\uc758 \ube44\uc728\uc774 \ub9e4\uc6b0 \uc801\ub2e4\uba74, \uc9c8\uc758\ub294 \uad00\ub828\ub41c \ud14c\uc774\ube14\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\ub974\ub2e4.
\u25b6<\/span>  \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.)
\u25b6<\/span> cost-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 \ud55c\ub2e4.
\u25b6<\/span>  \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\ud55c\ub2e4 \uadf8\ub798\uc11c cost-based approcah\ub294 index scan\ubcf4\ub2e4\ub294 full table scan\uc744 \uc120\ud0dd\ud558\uace4 \ud55c\ub2e4.
\u25b6<\/span>  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, hint\uc5d0 index scan\uc744 \uc0ac\uc6a9\ud558\uc5ec optimizer\uac00 \uac15\uc81c\ub85c index scan\uc744 \uc0ac\uc6a9\ud558\uac8c \ud560\uc218 \uc788\ub2e4.
\u25b6<\/span>  \uc544\ub798 \ubb38\uc7a5\uc5d0\uc11c INDEX hint\ub294 SEX_INDEX\uc5d0 \ub300\ud574 index scan\uc744 \uc120\ud0dd\ud55c\ub2e4. <\/p>\n

\u00b7 SELECT \/*+ INDEX(patients sex_index) Use SEX_INDEX, since there are few male patients *\/
              name, height, weight
              FROM patients
              WHERE sex = ‘M’; <\/strong><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 FULL hint\uc640 INDEX hint\uc758 \ube44\uad50 \uc608\uc81c<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  1. EMP\ud14c\uc774\ube14\uc758 \ucd1d\uac74\uc218\ub294 10,000\uac74, DEPT\ud14c\uc774\ube14\uc758 \ucd1d\uac74\uc218\ub294 2400\uac74.
\u25b6<\/span>  2. EMP\ud14c\uc774\ube14\uc5d0\uc11c empno, ename, sal\uc744 select
\u25b6<\/span>  3. WHERE\uc808\uc758 \uc870\uac74\uc740 JOB \ud544\ub4dc\uc758 ‘SALESMAN’\uac12\uc744 \uac80\uc0ac
\u25b6<\/span>  4. JOB_INDEX<\/strong><\/span> \uc874\uc7ac.
\u25b6<\/span>  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<\/span>  EMP, DEPT \ud14c\uc774\ube14\uc744 ANALYZE<\/strong><\/span>\ud568.
\u00b7<\/span>  sql_trace\ub97c true\ub85c
\u00b7<\/span>  OPTIMIZER_GOAL \uc740 ALL_ROWS<\/strong><\/span>\ub85c
\n<\/span><\/p>\n

\u25b6<\/span>  \uc608\uc81c 1
\n<\/span><\/p>\n

analyze table emp estimate statistics;
analyze table dept estimate statistics;
\n<\/strong><\/span><\/p>\n

alter session set sql_trace = true;
alter session set optimizer_goal = all_rows;
\n<\/span><\/span><\/strong><\/span><\/p>\n

select empno, ename, sal
from emp
where job = ‘SALESMAN’;
\n<\/strong><\/span><\/p>\n

select \/*+ full(emp) *\/ empno, ename, sal
from emp
where job = ‘SALESMAN’;
\n<\/span><\/span><\/strong><\/span><\/p>\n

select \/*+ index(emp job_index) *\/ empno, ename, sal
from emp
where job = ‘SALESMAN’;
<\/span><\/span><\/strong>\u00b7<\/span>  trace file \uacb0\uacfc<\/a><\/span>
\n\t\t\t\t\t\t\t\t\t<\/span><\/p>\n

\u00b7<\/span>  OPTIMIZER_GOAL \uc740 FIRST_ROWS<\/strong><\/span>\ub85c
\n<\/span><\/p>\n

\u25b6<\/span>  \uc608\uc81c 2
\n<\/span><\/p>\n

analyze table emp estimate statistics;
analyze table dept estimate statistics;
\n<\/strong><\/span><\/p>\n

alter session set sql_trace = true;
alter session set optimizer_goal = first_rows;
\n<\/span><\/span><\/strong><\/span><\/p>\n

select empno, ename, sal
from emp
where job = ‘SALESMAN’;
\n<\/strong><\/span><\/p>\n

select \/*+ full(emp) *\/ empno, ename, sal
from emp
where job = ‘SALESMAN’;
\n<\/span><\/span><\/strong><\/span><\/p>\n

select \/*+ index(emp job_index) *\/ empno, ename, sal
from emp
where job = ‘SALESMAN’;
\u00b7  trace file \uacb0\uacfc<\/a><\/span><\/span><\/span><\/span><\/strong><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 INDEX_ASC<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  INDEX_ASC<\/strong><\/span> hint\ub294 \ud14c\uc774\ube14\uc5d0 \ub300\ud55c index scan\uc744 \uc120\ud0dd
\u25b6<\/span>  \uc774 SQL\ubb38\uc774 index range scan\uc744 \uc0ac\uc6a9\ud55c\ub2e4\uba74, Oracle\uc740 index\ub41c \uac12\ub4e4\uc744 \uc624\ub984\ucc28\uc21c\uc73c\ub85c \uc815\ub82c\ud55c index entry\ub4e4\uc744 scan.
\u25b6<\/span>  INDEX_ASC hint\uc758 \ubb38\ubc95\uc740
\n<\/span><\/p>\n


\n\t\t\t\t\t\t\t\t\t<\/span><\/p>\n


\u25b6<\/span>  \uac01 \ud30c\ub77c\uba54\ud0c0\ub294 INDEX hint\uc5d0\uc11c\uc640 \uac19\uc740 \ubaa9\uc801\uc744 \uc758\ubbf8.
\u25b6<\/span>  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 hint\ub294 \uc77c\ubc18\uc801\uc73c\ub85c index hint\ubcf4\ub2e4 \ub354 \ub098\uc740 \uc810\uc740 \uc5c6\ub2e4. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 INDEX_DESC<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  INDEX_DESC<\/strong><\/span> hint\ub294 \ud14c\uc774\ube14\uc5d0 \ub300\ud574 index scan\uc744 \uc120\ud0dd
\u25b6<\/span>  \ub9cc\uc57d SQL \ubb38\uc774 index range scan\uc744 \uc0ac\uc6a9\ud55c\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.
\u25b6<\/span>  INDEX_desc hint\uc758 \ubb38\ubc95\uc740
\n<\/span><\/p>\n


\n\t\t\t\t\t\t\t\t\t<\/span><\/p>\n


\u25b6<\/span>  \uac01 \ud30c\ub77c\uba54\ud0c0\ub294 INDEX hint\uc5d0\uc11c\uc640 \uac19\uc740 \ubaa9\uc801\uc744 \uc758\ubbf8.
\u25b6<\/span>  \uc774 hint\ub294 \ud14c\uc774\ube14\ubcf4\ub2e4 INDEX\ub97c \ub354 \ub9ce\uc774 \uc811\uadfc\ud558\ubbc0\ub85c SQL\ubb38\uc5d0 \uc601\ud5a5\uc744 \uc8fc\uc9c0 \uc54a\uc74c.
\u25b6<\/span>  \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
\u00b7CREATE TABLE tank_readings (
              time DATE CONSTAINT un_time UNIQUE,
               temperature NUMBER); <\/strong><\/span>
<\/span>
\u25b6<\/span>  \ud14c\uc774\ube14\uc758 \uac01 \ud589\ub4e4\uc740 \ud55c \uc2dc\uc810\uc5d0\uc11c \uc2dc\uac04\uacfc \uc628\ub3c4\ub97c \uc800\uc7a5.
\u25b6<\/span>  TIME\uce7c\ub7fc\uc5d0 \ub300\ud574 UNIQUE \uc81c\uc57d\uc744 \uc8fc\uba74 \ud14c\uc774\ube14\uc774 \ub3d9\uc77c\ud55c \uc2dc\uc810\uc5d0\uc11c \ud55c\ubc88\ub9cc \ub0b4\uc6a9\uc744 \uc77d\ub3c4\ub85d \ud55c\ub2e4.
\u25b6<\/span>  \uc624\ub77c\ud074\uc740 TIME \uce7c\ub7fc\uc5d0 \uac15\uc81c\ub85c \uc778\ub371\uc2a4\ub97c \uc218\ud589.
\u25b6<\/span>  \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.
\u25b6<\/span>  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.
\u25b6<\/span>  Parent query\ub294 \uadf8 \uc2dc\uac04\uc5d0 \ub300\ud55c \uc628\ub3c4\ub97c \ucc3e\ub294\ub2e4.
\u00b7SELECT temperature
              FROM tank_readings
              WHERE time = (SELECT MAX(time)
\n<\/strong><\/span><\/span><\/span><\/p>\n

              FROM tank_readings
              WEHRE time <= TO_DATE(:t) );
\n<\/strong><\/span><\/p>\n


\u25b6<\/span>  \uc704 SQL\ubb38\uc5d0 \ub300\ud55c execution plan\uc740 \uc544\ub798\uadf8\ub9bc\uacfc \uac19\ub2e4.
\n<\/span><\/p>\n


\n\t\t\t\t\t\t\t\t\t<\/span><\/p>\n


\u25b6<\/span>  \uc704 SQL\ubb38\uc7a5\uc744 \uc2e4\ud589\uc2dc\ud0a4\uba74, \uc624\ub77c\ud074\uc740 \uc544\ub798\uc640 \uac19\uc740 operations\ub97c \uc2e4\ud589.
\u00b7<\/span> step 4\uc640 3\uc740 subquery\ub97c \uc2e4\ud589.
\n<\/strong><\/span><\/p>\n

– <\/span>step 4\ub294 \uc2dc\uac04 T\uc640 \uac19\uac70\ub098 \ub354 \uc801\uc740 \ubaa8\ub4e0 TIME \uac12\ub4e4\uc744 \ubc18\ud658\ud560 UN_TIME \uc778\ub371\uc2a4\uc758 range scan\uc744 \uc218\ud589.
– <\/span>step 3\ub294 step 4\ub85c\ubd80\ud130 \ucd5c\ub300 TIME\uac12\uc744 \uc120\ud0dd\ud558\uace0 \uadf8 \uac12\uc744 \ubc18\ud658.
\n<\/span><\/p>\n

\u00b7<\/span>step 2 \uc640 1\uc740 parent query\ub97c \uc2e4\ud589.
\n<\/strong><\/span><\/p>\n

– <\/span>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.
– <\/span>step 1\uc740 step 2\uc5d0 \uc758\ud574 \ubc18\ud658\ub41c ROWID\ub97c \uc0ac\uc6a9\ud558\uc5ec TANK_READING \ud14c\uc774\ube14\uc744 \uc811\uadfc\ud558\uace0 TEMPERATURE \uac12\uc744 \ubc18\ud658.
\n<\/span><\/p>\n


\u25b6<\/span> Step 4\uc5d0\uc11c \uc624\ub77c\ud074\uc740 \uc624\ub984\ucc28\uc21c\uc73c\ub85c \uc815\ub82c\ub41c \uc778\ub371\uc2a4\uc5d0 \uc788\ub294 TIME \uac12\uc744 scan.
\u25b6<\/span>  \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.
\u25b6<\/span>  INDEX_DESC hint\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.
\u00b7SELECT \/*+ INDEX_DESC(tank_readings un_time) *\/ temperature
              FROM tank_readings
              WHERE time <= TO_DATE(:t)
              AND ROWNUM = 1;
              ORDER BY time DESC;<\/strong><\/span>
\n\t\t\t\t\t\t\t\t\t\t<\/span>
\u25b6<\/span>  \uc774\uac83\uc758 execution plan\uc744 \uc544\ub798 \uadf8\ub9bc\uacfc \uac19\ub2e4.
\n<\/span><\/p>\n


\n\t\t\t\t\t\t\t\t\t<\/span><\/p>\n

\u00b7<\/span> step3 \uc740 T\uc640 \ub3d9\uc77c\ud558\uac70\ub098 \ub354 \uc801\uc740 TIME\uac12\uc744 \ucc3e\uae30\uc704\ud574 UN_TIME \uc778\ub371\uc2a4\ub97c range scan\ud558\uace0 \uadf8\uc640 \uad00\ub828\ub41c ROWID\ub97c \ubc18\ud658.
\u00b7<\/span>step 2\ub294 step 3\uc5d0 \uc758\ud574 \ubc18\ud658\ub41c ROWID\uac12\ub4e4\ub85c\uc11c TANK_READING \ud14c\uc774\ube14\uc5d0 \uc811\uadfc.
\u00b7<\/span> 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.
\u25b6<\/span>  INDEX_DESC hint \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.
\u25b6<\/span>  scan\ub41c \uccab \ubc88\uc9f8 TIME\uac12\uc740 T\uc774\uac70\ub098 T\uac12\ubcf4\ub2e4\ub294 \uc801\uc740 \ucd5c\ub300 TIME\uac12\uc774\ub2e4.
\u25b6<\/span>  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.
\u25b6<\/span>  default \ud589\ub3d9\uc774 \uc624\ub984\ucc28\uc21c index scan\uc774\ubbc0\ub85c INDEX_DESC hint\uc5c6\uc774 \uc774 \uc9c8\uc758\ub97c \uc218\ud589\ud558\uba74 \uc624\ub77c\ud074\uc740 \ud14c\uc774\ube14\uc5d0\uc11c T\uc640 \uac19\uac70\ub098 \uadf8\ubcf4\uc790 \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 \ub41c\ub2e4.
\u25b6<\/span>  \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 hint\ub97c \uc0ac\uc6a9\ud574\uc57c\ub9cc \ud55c\ub2e4. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 AND_EQUAL<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  AND_EQUAL<\/strong><\/span> hint\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
\u25b6<\/span>  \uc774 AND_EQUAL hint\uc758 \ubb38\ubc95\uc740
\n<\/span><\/p>\n


table<\/em><\/strong><\/span> : merge\ud560 \uc778\ub371\uc2a4\uc640 \uc5f0\uad00\ub41c \ud14c\uc774\ube14\uc758 \uc774\ub984\uc774\ub098 alias\ub97c \uae30\uc220.
index<\/em><\/strong><\/span> : index scan\uc744 \uc218\ud589\ud558\ub294 index\ub97c \uae30\uc220
\n<\/span><\/p>\n

\u25b6<\/span> \ucd5c\uc18c 2\uac1c \uc774\uc0c1 \ucd5c\ub300 5\uac1c \uc774\ud558\uc758 index\ub97c \uae30\uc220\ud574\uc57c \ud55c\ub2e4. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 USE_CONCAT<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  USE_CONCAT hint\ub294 OR \uc870\uac74\uc744 UNION ALL set operator\ub97c \uc0ac\uc6a9\ud558\ub294 compound query\ub85c \ubcc0\ud658
\u25b6<\/span>  \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<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u261eHint for join Orders<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

ORDERED hint\ub294 join order\ub97c \uc81c\uc548 <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 ORDERED<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  ORDERED hint\ub294 FROM\uc808\uc5d0 table\uc774 \ub098\ud0c0\ub098\ub294 \uc21c\uc11c\ub300\ub85c \ud14c\uc774\ube14\uc744 join\uc2dc\ud0a8\ub2e4.
\u25b6<\/span>  \uc608\ub97c\ub4e4\uc5b4, \uc544\ub798 SQL\ubb38\uc740 \ud14c\uc774\ube14 TAB1\uacfc \ud14c\uc774\ube14 TAB2\ub97c \uc870\uc778\ud55c \ud6c4\uc5d0 \uadf8 \uacb0\uacfc\uc640 \ud14c\uc774\ube14 TAB3\uc744 \uc870\uc778\ud55c\ub2e4. <\/p>\n

\u00b7SELECT \/*+ ORDERED *\/ tab1.col1, tab2.col2, tab3.col3
              FROM tab1, tab2, tab3
              WHERE tab1.col1 = tab2.col1 AND tab2.col1 = tab3.col1; <\/strong><\/span>
<\/span>
\u25b6<\/span>  SQL\ubb38\uc5d0\uc11c ORDERED hint\ub97c \uc0dd\ub7b5\ud558\uace0 join\ub97c \uc218\ud589\ud558\uba74, optimizer\uac00 table\uc744 join\ud560 \uc21c\uc11c\ub97c \uc120\ud0dd
\u25b6<\/span>  \uac01 \ud14c\uc774\ube14\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 hint\ub97c \uc0ac\uc6a9\ud558\ub294 \uac83\uc774 \uc88b\ub2e4.
\u25b6<\/span>  \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\ub2e4. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u261e
\n\t\t\t\t\t\t\t\t\t\t\tHint for Join Operations<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25cf<\/span> \uc774\uc808\uc5d0\uc11c\uc758 hint\ub294 \ud14c\uc774\ube14\uc744 \uc704\ud55c join operation\uc744 \uc5b8\uae09
\u25cf<\/span> SQL\ubb38\uc5d0\uc11c \ub098\ud0c0\ub098\ub294 \uc870\uc778\ub41c table\uc744 \uc815\ud655\ud558\uac8c \uae30\uc220\ud574\uc57c\ub9cc \ud55c\ub2e4.
\u25cf<\/span> SQL\ubb38\uc774 \ud14c\uc774\ube14\uc758 alias\ub97c \uc0ac\uc6a9\ud55c\ub2e4\uba74 hint\uc5d0\uc11c\ub3c4 \ud14c\uc774\ube14\uc758 \uc774\ub984\ubcf4\ub2e4\ub294 alias\ub97c \uc0ac\uc6a9\ud574\uc57c\ub9cc \ud55c\ub2e4.
\u25cf<\/span> \ud14c\uc774\ube14\uc758 \uc774\ub984\uc774\ub098 alias\ub294 local database\uc5d0 \ud14c\uc774\ube14\uc758 synonym\uc774\ub098 \ud14c\uc774\ube14\uc744 \uac00\uc9c0\uace0 \uc788\uc5b4\uc57c\ub9cc \ud55c\ub2e4.
\u25cf<\/span> USE_NL\uacfc USE_MERGE hint\ub294 ORDERED hint\ub97c \uc0ac\uc6a9\ud574\uc57c\ub9cc \ud55c\ub2e4.
\u25cf<\/span> \uc624\ub77c\ud074\uc740 \ucc38\uc870\ub41c \ud14c\uc774\ube14\uc774 \uc870\uc778\uc5d0\uc11c \uac15\uc81c\ub85c inner\ud14c\uc774\ube14\uc774 \ub420 \ub54c \uc774 hint\ub97c \uc0ac\uc6a9\ud574\uc57c\ub9cc \ud55c\ub2e4.
\u25cf<\/span> \ucc38\uc870\ub41c \ud14c\uc774\ube14\uc774 outer table\uc774\ub77c\uba74 \uc774 hint\ub4e4\uc744 \ubb34\uc2dc\ud55c\ub2e4. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 USE_NL<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  USE_NL hint\ub294 \ud14c\uc774\ube14\uc744 \uc801\ub294 \ubd80\ubd84\uc5d0 \ud14c\uc774\ube14 \uae30\uc220\ub41c table\uc740 inner table\ub85c\uc11c \uc0ac\uc6a9\ud558\uc5ec nested loops\ub85c\uc11c \ub2e4\ub978 \ud14c\uc774\ube14\uc758 row source\uc640 \uae30\uc220\ub41c table\uc744 join\ud558\uac8c \ud55c\ub2e4.
\u25b6<\/span>  USE_NL hint\uc758 \ubb38\ubc95\uc740
\n<\/span><\/p>\n


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

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

\u00b7SELECT accounts.balance, customers.last_name, customers.first_name
              FROM accounts, customers
              WHERE accounts.custno = customers.custo; <\/strong><\/span>
\u25b6<\/span>  cost-based approach<\/span>\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\ud55c\ub2e4.
\u25b6<\/span>  \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.
\u25b6<\/span>  \uadf8\ub807\uac8c \ud558\ub824\uba74 USE_NL hint\ub97c \uc0ac\uc6a9\ud568\uc73c\ub85c\uc11c optimizer\uac00 nested loops join\uc744 \uac15\uc81c\ub85c \uc120\ud0dd\ud558\uac8c \ud560 \uc218 \uc788\ub2e4.
\u25b6<\/span>  SQL\ubb38\uc5d0\uc11c USE_NL hint\ub294 CUSTOMERS\ud14c\uc774\ube14\uc744 inner table\ub85c \uac00\uc9c0\ub294 nested loop\ub97c \uc120\ud0dd
\u00b7SELECT \/*+ ORDERED USE_NL(customers) Use N-L to get first row faster *\/
              accounts.balance, customers.last_name, customers.first_name
              FROM accounts, customers
              WHERE accounts.custno = customers.custno; <\/strong><\/span>
<\/span>
\u25b6<\/span>  \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\ud55c\ub2e4.
\u25b6<\/span>  Nested loop join\uc740 \ud55c \ud14c\uc774\ube14\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 \ud14c\uc774\ube14\uc5d0\uc11c \uccab \ubc88\uc9f8\ub85c \uc77c\uce58\ud558\ub294 \ud589\uc744 \ucc3e\uace0 \uadf8\uac83\ub4e4\uc744 \uacb0\ud569\ud55c\ub2e4.
\u25b6<\/span>  \ubc18\uba74\uc5d0 sort-merge join\uc740 \uc591 \ud14c\uc774\ube14\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\ub2e4. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 USE_MERGE<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  USE_MERGE hint\ub294 \uc624\ub77c\ud074\uc774 sort-merge join\uc73c\ub85c \uac01 \ud14c\uc774\ube14\uc744 \uc870\uc778\ud558\uac8c\ub054 \ud558\ub294 \ubc29\ubc95\uc774\ub2e4.
\u25b6<\/span>  USE_MERGE hint\uc758 \ubb38\ubc95\uc740
\n<\/span><\/p>\n

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

\n <\/p>\n\n\n<\/colgroup>\n\n\n
\n

\u261e
\n\t\t\t\t\t\t\t\t\t\t\tHints for Parallel Query Execution<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 CACHE<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  CACHE hint\ub294 full table scan\uc774 \uc218\ud589\ub410\uc744 \ub54c hint\uc5d0 \uc788\ub294 \ud14c\uc774\ube14\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
\u25b6<\/span>  \uc774 option\uc740 small lookup table\uc5d0 \uc720\uc6a9\ud558\ub2e4. CACHE hint\ub294 \ud14c\uc774\ube14\uc758 default caching specification\uc744 \ubb34\uc2dc
\u00b7SELECT\/*+ FULL (scoot_emp) CACHE(scott_emp) *\/ ename
              FROM scott.emp scott_emp; <\/strong><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 NOCACHE<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  NOCACHE hint\ub294 full table scan\uc774 \uc218\ud589\ub410\uc744 \ub54c hint\uc5d0 \uc788\ub294 \ud14c\uc774\ube14\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
\u25b6<\/span>  \ubc84\ud37c\uce90\uc218\uc5d0 blocks\uc758 \uc77c\ubc18\uc801\uc778 \ud589\ub3d9
\u00b7SELECT\/*+ FULL (scoot_emp) NOCACHE(scott_emp) *\/ ename
              FROM scott.emp scott_emp; <\/strong><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n<\/p>\n<\/p>\n\n\n<\/colgroup>\n\n\n\n\n
 <\/td>\n<\/tr>\n
\n

\u261e
\n\t\t\t\t\t\t\t\t\t\t\t\tConsidering Alternative Syntax<\/strong><\/span>
\n\t\t\t\t\t\t\t\t\t\t<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25cf  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\uc774\ub2e4.
\u25cf  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\ub974\ub2e4.
\u25cf  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\ub2e4. <\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/p>\n<\/p>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 \ub450 SQL\ubb38\uc5d0 \ub300\ud55c execution plan \ube44\uad50<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25cf<\/span>  \uccab \ubc88\uc9f8 SQL\ubb38\uacfc \uadf8\uac83\uc5d0 \ub300\ud55c execution plan<\/strong>
\u00b7SELECT dname, deptno
              FROM dept
              WHERE deptno NOT IN
              (SELECT deptno FROM emp); <\/strong><\/span>
\u25b6<\/span> Execution Plan with Two Full Table Scans
\n<\/span><\/span><\/p>\n


\n\t\t\t\t\t\t\t\t\t\t<\/span><\/p>\n

\u25b6<\/span> step3\uc744 \ud1b5\ud574 \uc624\ub77c\ud074\uc740 DEPTNO \uce7c\ub7fc\uc5d0 \uc778\ub371\uc2a4\uac00 \uc788\uc74c\uc5d0\ub3c4 \ubd88\uad6c\ud558\uace0 EMP\ud14c\uc774\ube14\uc744 full table scan\ud568\uc73c\ub85c\uc11c \uc704 SQL\ubb38\uc744 \uc2e4\ud589\ud55c\ub2e4\ub294 \uac83\uc744 \uc54c\uc218 \uc788\ub2e4.
\u25b6<\/span> full table scan\uc740 time-consuming operation<\/strong>\uc744 \ud560 \uc218 \uc788\ub2e4.
\u25b6<\/span> EMP\ud14c\uc774\ube14\uc744 \ucc3e\ub294 subquery\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\ub2e4. <\/p>\n

\u25b6<\/span>  \uadf8\ub7ec\ub098, \uc544\ub798\uc758 SQL\ubb38\uc740 \uc778\ub371\uc2a4\uc5d0 \uc811\uadfc\ud568\uc73c\ub85c\uc11c \ub3d9\uc77c\ud55c \ud589\uc744 SELECT\ud55c\ub2e4.
\u00b7SELECT dname, deptno
              FROM dept
              WHERE NOT EXISTS
              (SELECT deptno
              FROM emp
              WHERE dept.deptno = emp.deptno);<\/strong><\/span>
\u25b6<\/span> Execution Plan with a Full Table Scan and an Index Scan
\n<\/span><\/span><\/p>\n


\n\t\t\t\t\t\t\t\t\t\t<\/span><\/p>\n

\u25b6<\/span> subquery\uc758 WHERE\uc808\uc774 EMP\ud14c\uc774\ube14\uc758 DEPTNO\uce7c\ub7fc\uc744 \uc0ac\uc6a9\ud558\ubbc0\ub85c DEPTNO_INDEX\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4.
\u25b6<\/span> \uc778\ub371\uc2a4\uc758 \uc0ac\uc6a9\uc740 execution plan\uc758 step3\uc5d0\uc11c \ud558\uac8c\ub41c\ub2e4
\u25b6<\/span>  DEPTNO_INDEX\uc758 index range scan\uc740 \uccab \ubc88\uc9f8 \ubb38\uc7a5\uc5d0\uc11c EMP\ud14c\uc774\ube14\uc758 full scan\ud558\ub294 \uac83\ubcf4\ub2e4 \uc2dc\uac04\uc774 \ub354 \uc801\uac8c \uac78\ub9b0\ub2e4. <\/strong>
\u25b6<\/span>  \uccab \ubc88\uc9f8 query\ub294 DEPT \ud14c\uc774\ube14\uc5d0\uc11c \ubaa8\ub4e0 DEPTNO\ub97c \uac00\uc838\uc624\uae30 \uc704\ud574 EMP\ud14c\uc774\ube14\uc744 \ud55c\ubc88 full scan\ud55c\ub2e4.
\u25b6<\/span> \uc774\ub7f0 \uc774\uc720\ub85c \ub450 \ubc88\uc9f8 SQL \ubb38\uc740 \uccab \ubc88\uc9f8\ubcf4\ub2e4\ub294 \ub354 \ube60\ub974\ub2e4.
\u25b6<\/span> Application\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 \uc778\ub371\uc2a4\uac00 \uc788\ub2e4\uba74 \uadf8 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\uac8c \ud574\uc904 \uac83\uc774\ub2e4. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

   <\/span><\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u261eHow to Tune Existing SQL Statements<\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25cf<\/span>  \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)\uc774\ub2e4.<\/strong>
\u25cf<\/span>  \ube44\ub85d \uc694\uad6c\ub41c \uc9c0\uc2dd\uc774 \uac19\uc744\uc9c0\ub77c\ub3c4 \ud504\ub85c\uc138\uc2a4\ub294 \ub2e4\ub974\ub2e4. <\/strong>
\u25cf<\/span>  \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 \ud55c\ub2e4. <\/strong>
\u00b7<\/span> application\uc5d0 \uc775\uc219\ud558\ub77c.
\u00b7<\/span> SQL trace\ub97c \uc774\uc6a9\ud558\uc5ec \ud2b9\ubcc4\ud55c \ubb38\uc81c \ubb38\uc7a5\uc744 \ubd84\ub9ac\ud558\ub77c.
\u25cf<\/span> \uc774\uc804 \uc808\uc5d0\uc11c \ub17c\uc758\ud588\ub358 \uc0c8\ub85c\uc6b4 SQL \ubb38\uc7a5\uc744 \uae30\ub85d\ud558\uace0, \uc778\ub371\uc2a4, \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\ub2e4. <\/strong><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 Know the Application <\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  application\uacfc SQL \ubb38\uc7a5\uacfc data\uc5d0 \uc775\uc219\ud574\uc838\uc57c \ud55c\ub2e4.
\u25b6<\/span>  \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.
\u25b6<\/span>  application\uc774 \ud558\ub294 \uac83\uc744 \uae30\ub85d\ud558\ub77c.
\u00b7<\/span> application\uc744 \uc774\uc6a9\ud558\ub294 SQL \ubb38\uc740 \ubb34\uc5c7\uc778\uac00?
\u00b7<\/span> application\uc774 \ucc98\ub9ac\ud558\ub294 \ub370\uc774\ud0c0\ub294 \ubb34\uc5c7\uc778\uac00?
\u00b7<\/span> \ub370\uc774\ud0c0\uc758 \ud2b9\uc9d5\uacfc distribution\uc740 \ubb34\uc5c7\uc778\uac00?
\u00b7<\/span> applicatin\uc758 \ubb34\uc2a8 operation\uc774 data\ub97c \uc2e4\ud589\ud558\ub294\uac00?
\u25b6<\/span>  application \uc0ac\uc6a9\uc790\ub4e4\uacfc \uc131\ub2a5\uc5d0 \ub300\ud574 \uc774\uc57c\uae30 \ud574\ubcf4\ub77c.
\u25b6<\/span>  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.
\u25b6<\/span>  \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. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 Use the SQl Trace Facility <\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  \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\ud55c\ub2e4
\u25b6<\/span>  appllicatin\uc744 \ud29c\ub2dd\ud558\ub294\ub370 \ud2b9\ubcc4\ud788 \ub3c4\uc6c0\uc774 \ub418\ub294 tool\uc911\uc5d0 \ud558\ub098\ub294 SQL trace \uc774\ub2e4.
\u25b6<\/span>  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\ud55c\ub2e4.
\u25b6<\/span>  \ud1b5\uacc4\ub294 \ub2e4\uc74c\uc744 \ub098\ud0c0\ub0b8\ub2e4.
\u00b7<\/span> SQL \ubb38\uc7a5\uc774 \ud30c\uc2f1, \uc2e4\ud589, \ud398\uce58\ub41c \ud69f\uc218
\u00b7<\/span> \uac01 SQL \ubb38\uc7a5\uc744 \ucc98\ub9ac\ud558\ub294\ub370 \ud544\uc694\ud55c \uc2dc\uac04
\u00b7<\/span> \uac01 SQL \ubb38\uc7a5\uacfc \uad00\ub828\ub41c \uba54\ubaa8\ub9ac\uc640 \ub514\uc2a4\ud06c access
\u00b7<\/span> \uac01 SQL \ubb38\uc7a5\uc744 \ucc98\ub9ac\ud558\ub294 row\uc758 \uc218
\u25b6<\/span>  SQL trace facility\ub294 \ub610\ud55c EXPLAIN PLAN \uba85\ub839\uc744 \uc774\uc6a9\ud558\uc5ec execution plan\uc744 \uc0dd\uc131\ud560\uc218 \uc788\ub2e4. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n <\/p>\n\n\n<\/colgroup>\n\n\n\n
\n

\u2663 Tuning Individual SQL statements <\/strong><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

\u25b6<\/span>  \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.
\u25b6<\/span>  \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\ud55c\ub2e4.
\u25b6<\/span>  \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\ub2e4.
\u25b6<\/span>  \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.
\u25b6<\/span>  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\ud55c\ub2e4.
\u25b6<\/span>  \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\ud55c\ub2e4.
\u25b6<\/span>  \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\ud55c\ub2e4\ub294 \uac83\uc744 \uba85\uc2ec\ud558\ub77c.
\u25b6<\/span>  \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\ud55c\ub2e4. <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

\u261eHow to Use Indexs \u25cf  \uc778\ub371\uc2a4 \uc0dd\uc131 \uc2dc\uae30 \u25cf  \uc778\ub371\uc2a4 \uc0dd\uc131 \ub300\uc0c1 column \uc120\ud0dd \u25cf  \uacb0\ud569 \uc778\ub371\uc2a4 \uc0ac\uc6a9 \ubc29\ubc95 \u25cf  \uc778\ub371\uc2a4 \uc0ac\uc6a9\uc744 \uc704\ud55c SQL\ubb38 \uc791\uc131\ubc95 \u2663 When to Create Indexes \u25b6  \uc9c8\uc758\uc5b4\uc5d0\uc11c select\ud558\ub294 row\uc758 \ube44\uc728\uc774 \uc801\uc744 \uacbd\uc6b0, \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\uba74 \uc9c8\uc758\uc758 \uc131\ub2a5\uc774 \ud5a5\uc0c1\u25b6  \ud14c\uc774\ube14\uc5d0 \uc778\ub371\uc2a4\ub97c \uc0dd\uc131\ud558\ub294 \uc9c0\uce68 : table\uc758 \uc804\uccb4 row\uc911\uc758 2%\uc5d0\uc11c 4%\uc774\ud558\ub97c \uac80\uc0c9\ud558\ub294 \uc9c8\uc758\uac00 \uc790\uc8fc […]<\/p>\n","protected":false},"author":1,"featured_media":477,"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":[233,234],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/1\/cfile2.uf.212E104F56025F3E3494A0.png?fit=384%2C56","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/476"}],"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=476"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/476\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/477"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=476"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=476"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=476"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}