{"id":438,"date":"2015-09-03T04:56:46","date_gmt":"2015-09-03T04:56:46","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=438"},"modified":"2018-08-16T10:55:01","modified_gmt":"2018-08-16T01:55:01","slug":"sql-%eb%ac%b8%ec%9d%98-processing-%ec%88%9c%ec%84%9c","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=438","title":{"rendered":"SQL \ubb38\uc758 Processing \uc21c\uc11c"},"content":{"rendered":"

Cursor Life Cycle<\/span><\/b><\/p>\n

\ubaa8\ub4e0 SQL(Select,Update, Delete)\uc740 Cursor\ub97c \uc0dd\uc131\ud558\uc5ec \uc2e4\ud589\ub429\ub2c8\ub2e4. Pro*C \uc640 \uac19\uc740 \ud234\uc744 \uc0ac\uc6a9\ud558\uba74 Cursor \uc758 \uac01 \ub2e8\uacc4\ub97c \ud504\ub85c\uadf8\ub7a8\uc5d0\uc11c
\n\uc81c\uc5b4\ud560 \uc218 \uc788\uace0, \uadf8\ub807\uc9c0 \uc54a\uace0 SQL*Plus \uc640 \uac19\uc740 \ud234\uc744 \uc0ac\uc6a9\ud560 \uacbd\uc6b0\uc5d0\ub294 Implicit Cursor \uac00 \ub0b4\ubd80\uc801\uc73c\ub85c \uc0dd\uc131\ub418\uc5b4, \uacb0\uacfc\ub9cc \ubcf4\uc5ec\uc9c0\uac8c \ub429\ub2c8\ub2e4.<\/span><\/p>\n

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

– Create Cursor<\/span><\/p>\n

– Parse the statement<\/span><\/p>\n

– Describe Query Result<\/span><\/p>\n

– Define Query Output<\/span><\/p>\n

– Bind Variables<\/span><\/p>\n

– Parallelise the statement<\/span><\/p>\n

– execute the statement<\/span><\/p>\n

– Fetch rows of a query<\/span><\/p>\n

– Close the cursor<\/span><\/p>\n

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

SQL <\/span><\/b>\ucc98\ub9ac\uc758 3\ub2e8\uacc4<\/span><\/b><\/p>\n

– Parse -> Execute -> Fetch<\/span><\/p>\n

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

Parse<\/span><\/b><\/p>\n

– Syntax Check<\/span><\/p>\n

– Object Resolution & Privilege Check (Dictionary <\/span>\ucc38\uc870, Recursive SQL)<\/span><\/p>\n

– Shared Pool(Shared SQL Area)<\/span>\uc5d0\uc11c \ub3d9\uc77c\ud55c SQL \ubb38\uc7a5\uc774 \uc788\ub294\uc9c0 \uac80\uc0c9<\/span><\/p>\n

– Determine Optimal Execution Plan (by Optimizer)<\/span><\/p>\n

– Parsed Tree <\/span>\uc0dd\uc131\ud558\uace0 Shared SQL Area \uc5d0 \uc800\uc7a5<\/span><\/p>\n

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

< Shared SQL Area <\/span>\uc5d0 \uc800\uc7a5\ub418\ub294 \ub0b4\uc6a9 ><\/span><\/p>\n

\u00a0 _ SQL Text<\/span><\/p>\n

\u00a0 _ Explain Plan<\/span><\/p>\n

\u00a0 _ Parsed Tree<\/span><\/p>\n

\u00a0<\/span><\/b><\/p>\n

Execute<\/span><\/b><\/p>\n

– <\/span>\uc0dd\uc131\ub41c Parsed Tree \ub97c \uc801\uc6a9\ud558\uc5ec \uc2e4\uc81c DATA \uc5d0 \ub300\ud55c I\/O\ub97c \uc218\ud589<\/span><\/p>\n

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

Fetch<\/span><\/b><\/p>\n

– SELECT <\/span>\ubb38\uc77c \uacbd\uc6b0\uc5d0 \ud589\uc744 \uac00\uc838\uc624\ub294 \uacfc\uc815<\/span><\/p>\n

– <\/span>\uc77c\ubc18\uc801\uc73c\ub85c Array Fetch \uac00 \uc0ac\uc6a9\ub428 (\uc0ac\uc6a9\ubc29\ubc95\uc740 Client Tool\uc5d0 \ub530\ub77c \ub2e4\ub984)<\/span><\/p>\n

\u00a0 SQL*Plus <\/span>\uc758 \uacbd\uc6b0 Array Size(Default 15) \ub294 \ub2e4\uc74c\uacfc \uac19\uc774 \ud655\uc778\ud558\uace0 \ubcc0\uacbd\ud560 \uc218 \uc788\ub2e4.<\/span><\/p>\n

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

SQL> show arraysize\u00a0<\/span>arraysize 15<\/span><\/p>\n

SQL> set arraysize 20<\/span><\/p>\n

SQL> show arraysize\u00a0<\/span>arraysize 20<\/span><\/p>\n

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

Cursor(SQL) <\/span><\/b>\uacf5\uc720<\/span><\/b><\/p>\n

– <\/span>\ub3d9\uc77c\ud55c SQL \uc77c \uacbd\uc6b0 Shared SQL \uc5d0 \uc788\ub294 Explain Plan \/ Parsed Tree \uc744 \uac00\uc9c0\uace0 \uc2e4\ud589\ud558\uac8c \ub418\uc5b4 Parse Time \uc774 \ud544\uc694\uc5c6\uac8c \ub418\uace0, \uba54\ubaa8\ub9ac(Shared SQL)\ub97c \uc808\uc57d\ud560 \uc218 \uc788\uc74c <\/span><\/p>\n

– <\/span>\ub3d9\uc77c\ud55c SQL \uc774\ub780?<\/span><\/p>\n

\u00a0 _ SQL Text <\/span>\uac00 \ub3d9\uc77c\ud574\uc57c \ud568(\ub300\uc18c\ubb38\uc790 \uad6c\ubcc4, Space\/Tab \ubb38\uc790, \uc8fc\uc11d \uae4c\uc9c0 \uac19\uc544\uc57c \ud568)<\/span><\/p>\n

\u00a0 _ <\/span>\uc2e4\uc81c \ucc38\uc870\ub418\ub294 Object \uac00 \ub3d9\uc77c\ud574\uc57c \ud568<\/span><\/p>\n

\u00a0 _ Bind Variable\u2019s Data Type <\/span>\uc774 \ub3d9\uc77c\ud574\uc57c \ud568<\/span><\/p>\n

– Parse <\/span>\uc758 \uccab\ub2e8\uacc4\uc5d0\uc11c \ub3d9\uc77c\ud55c SQL \ubb38\uc774 Shared SQL Area \uc5d0 \uc874\uc7ac\ud558\ub294\uc9c0 Check \ud558\ub294 \uacfc\uc815<\/span><\/p>\n

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

\n

<\/p>\n

\u00a0<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Cursor Life Cycle \ubaa8\ub4e0 SQL(Select,Update, Delete)\uc740 Cursor\ub97c \uc0dd\uc131\ud558\uc5ec \uc2e4\ud589\ub429\ub2c8\ub2e4. Pro*C \uc640 \uac19\uc740 \ud234\uc744 \uc0ac\uc6a9\ud558\uba74 Cursor \uc758 \uac01 \ub2e8\uacc4\ub97c \ud504\ub85c\uadf8\ub7a8\uc5d0\uc11c \uc81c\uc5b4\ud560 \uc218 \uc788\uace0, \uadf8\ub807\uc9c0 \uc54a\uace0 SQL*Plus \uc640 \uac19\uc740 \ud234\uc744 \uc0ac\uc6a9\ud560 \uacbd\uc6b0\uc5d0\ub294 Implicit Cursor \uac00 \ub0b4\ubd80\uc801\uc73c\ub85c \uc0dd\uc131\ub418\uc5b4, \uacb0\uacfc\ub9cc \ubcf4\uc5ec\uc9c0\uac8c \ub429\ub2c8\ub2e4. \u00a0 – Create Cursor – Parse the statement – Describe Query Result – Define Query Output – […]<\/p>\n","protected":false},"author":1,"featured_media":439,"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":[205,206,207],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/1\/cfile3.uf.211A5C4855E7D34F26676A.jpg?fit=600%2C347","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/438"}],"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=438"}],"version-history":[{"count":2,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/438\/revisions"}],"predecessor-version":[{"id":4126,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/438\/revisions\/4126"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/439"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=438"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=438"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=438"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}