{"id":2702,"date":"2018-02-02T22:22:38","date_gmt":"2018-02-02T13:22:38","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2702"},"modified":"2018-02-02T22:22:38","modified_gmt":"2018-02-02T13:22:38","slug":"table-%ed%81%ac%ea%b8%b0-%ec%b6%94%ec%a0%95-%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2702","title":{"rendered":"TABLE \ud06c\uae30 \ucd94\uc815 \ubc29\ubc95"},"content":{"rendered":"

TABLE(NON-CLUSTED TABLE)\uc758 \ucd08\uae30 \uc0ac\uc6a9\ub7c9\uc744 \ucd94\uc815\ud558\ub294 \ubc29\ubc95\uc744 \uc0b4\ud3b4\ubcf4\uc790.<\/p>\n

 <\/p>\n

\uc5ec\uae30\uc11c\ub294ORACLE7 SERVER\ub97c \uae30\uc900\uc73c\ub85c \ud558\uc5ec \uc124\uba85 \ud558\uae30 \ud558\uba70, TABLE\uc758 \ucd08\uae30 \uc0ac\uc6a9\ub7c9\uc744 \uacc4\uc0b0\ud558\ub294 \ub2e8\uacc4\ub97c \ub2e4\uc74c\uacfc \uac19\uc774 \ub098\ub20c\uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

STEP 1 – \ucd1d \ube14\ub7ed \ud5e4\ub4dc \ud06c\uae30(BLOCK HEADER SIZE)\ub97c \uacc4\uc0b0<\/p>\n

STEP 2 – \ub370\uc774\ud0c0 \ube14\ub7ed \ub2f9 \uc0ac\uc6a9 \uac00\ub2a5\ud55c \ub370\uc774\ud0c0 \uc601\uc5ed\uc744 \uacc4\uc0b0<\/p>\n

STEP 3 – \ud3c9\uade0ROW\uc758 \uc804\uccb4 \uceec\ub7fc\uc758 \uae38\uc774(COMBINED COLUMN LENGTH)\ub97c \uacc4\uc0b0<\/p>\n

STEP 4 – \ucd1d \ud3c9\uade0ROW \ud06c\uae30\ub97c \uacc4\uc0b0<\/p>\n

STEP 5 – \ub370\uc774\ud0c0 \ube14\ub7ed\ub0b4\uc758 \ud3c9\uade0ROW \uc218\ub97c \uacc4\uc0b0<\/p>\n

STEP 6 – \ud14c\uc774\ube14\uc5d0\uc11c \uc694\uad6c\ub418\ub294 \ube14\ub7ed\uacfc \ubc14\uc774\ud2b8 \uc218\ub97c \uacc4\uc0b0<\/p>\n

 <\/p>\n

 <\/p>\n

    \n
  1. [ STEP 1 ] : \ucd1d \ube14\ub7ed \ud5e4\ub4dc \ud06c\uae30(TOTAL BLOCK HEADER SIZE)\ub97c \uacc4\uc0b0<\/li>\n<\/ol>\n

     <\/p>\n

    1) total block header<\/p>\n

     <\/p>\n

    total block header = block header, part A +<\/p>\n

    block header, part B<\/p>\n

     <\/p>\n

    block header, part A = (fixed header + variable transaction header)<\/p>\n

    block header, part B = (table directory + row directory)<\/p>\n

     <\/p>\n

    ** fixed header\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 : 57 bytes (\uace0\uc815\ub41c \ube14\ub7ed \ud5e4\ub4dc)<\/p>\n

    variable transaction\u00a0\u00a0 : 23 * I<\/p>\n

    header\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 ( I\ub294 \ud574\ub2f9TABLE\uc758INITRANS\uc758 \uac12)<\/p>\n

     <\/p>\n

    table directory\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : 4<\/p>\n

    row directory\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0: 2 * R<\/p>\n

    ( R\uc740 \ube14\ub7ed\uc758ROW \uc218, STEP 5\uc5d0\uc11c \uacc4\uc0b0)<\/p>\n

     <\/p>\n

    2) \uc608\ub97c \ub4e4\uc5b4, INITRANS = 1\uc77c\ub54c \uacbd\uc6b0\uc758 total block header<\/p>\n

    total block header = (57 + (23*1)) + (4 + (2*R))<\/p>\n

    = 80 + (4 + (2*R)) bytes<\/p>\n

     <\/p>\n

     <\/p>\n

      \n
    1. [ STEP 2 ] : \ub370\uc774\ud0c0 \ube14\ub7ed \ub2f9 \uc0ac\uc6a9 \uac00\ub2a5\ud55c \ub370\uc774\ud0c0 \uc601\uc5ed<\/li>\n<\/ol>\n

      (DATA SPACE PER DATA BLOCK)\uc744 \uacc4\uc0b0<\/p>\n

       <\/p>\n

      1) available data space<\/p>\n

       <\/p>\n

      available data space = (block size – total block header) –<\/p>\n

      ((block size – block header, part A) *<\/p>\n

      (PCTFREE\/100))<\/p>\n

       <\/p>\n

      ** block size \u00a0 : \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc758 \ube14\ub7ed \ud06c\uae30<\/p>\n

      (SQL*DBA\uc758SHOW PARAMETER\uc5d0\uc11c<\/p>\n

      db_block_size\ub97c \ud655\uc778)<\/p>\n

       <\/p>\n

      2) \uc608\ub97c \ub4e4\uc5b4, db_block_size=2K, PCTFREE=10 \uc77c \uacbd\uc6b0<\/p>\n

      available data space = (2048 – (80 + (4 + 2R)) –<\/p>\n

      ((2048 – 80) * (10 \/ 100))<\/p>\n

      = (1964 – 2R) – (1968 * 0.1)<\/p>\n

      = (1964 – 2R – 196)<\/p>\n

      = (1768 – 2R) bytes<\/p>\n

       <\/p>\n

        \n
      1. [ STEP 3 ] : \ud3c9\uade0ROW\uc758 \uc804\uccb4 \uceec\ub7fc\uc758 \uae38\uc774<\/li>\n<\/ol>\n

        (COMBINED COLUMN LENGTH)\ub97c \uacc4\uc0b0<\/p>\n

         <\/p>\n

        ROW \uae38\uc774\ub97c \uacc4\uc0b0\ud558\uae30 \uc704\ud558\uc5ecTABLE \uc815\uc758\uc5d0\uc11c \uceec\ub7fc \uc218, \uac01 \uceec\ub7fc\uc758 \ub370\uc774\ud0c0 \ud0c0\uc785, \uac00\ubcc0 \uae38\uc774 \uceec\ub7fc\uc758 \ud3c9\uade0 \ud06c\uae30\ub4f1\uc744 \ucc38\uc870\ud55c\ub2e4.<\/p>\n

         <\/p>\n

        1) D (data space\/average row) : table T \uac00 \ub2e4\uc74c\uc758\u00a0\u00a0 \ud0a4\ub9c8\ub85c \uc815\uc758\ub418\uc5b4<\/p>\n

        \uc788\uc744 \uacbd\uc6b0\uc758ROW\uc758 \ud3c9\uade0 \uae38\uc774<\/p>\n

        – create table T ( A char(10), B date, C number(10,2))<\/p>\n

         <\/p>\n

         <\/p>\n

        D (data space\/average row) = ( A + B + C )<\/p>\n

         <\/p>\n

         <\/p>\n

        ** A(\uceec\ub7fcA\uc758 \uae38\uc774) : 10 bytes —–> CHAR \ud0c0\uc785\uc778 \uacbd\uc6b0 fixed length<\/p>\n

        B(\u00a0\u00a0\u00a0B\uc758 \uae38\uc774) : 7 bytes —–> DATE \ud0c0\uc785\uc778 \uacbd\uc6b0<\/p>\n

        C(\u00a0\u00a0\u00a0C\uc758 \uae38\uc774) : 5 bytes = (P \/ 2) + 1<\/p>\n

        —–>NUMBER \ud0c0\uc785\uc778 \uacbd\uc6b0\uc5d0\ub294<\/p>\n

        PRECISION\uc744 \uace0\ub824\ud574\uc11c \uacc4\uc0b0\ub428<\/p>\n

        (\ub2e8, PRECISION\uc5d0\uc11cNUMBER\uc758 \uae38<\/p>\n

        \uc774\ub97c \ub098\ud0c0\ub0b4\ubbc0\ub85cSCALE\uc740 \ubb34\uad00\ud568)<\/p>\n

         <\/p>\n

          \n
        1. [ STEP 4 ] : \ucd1d \ud3c9\uade0ROW \ud06c\uae30\ub97c \uacc4\uc0b0(TOTAL AVERAGE ROW SIZE)<\/li>\n<\/ol>\n

           <\/p>\n

          \uc5ec\uae30\uc11c\ub294 \ud14c\uc774\ube14\uc758ROW\uc5d0 \uc758\ud574 \uc694\uad6c\ub418\ub294 \ucd5c\uc18c \uc0ac\uc6a9 \uc601\uc5ed\uc744 \uacc4\uc0b0\ud558\ub294 \uacf5\uc2dd\uc744 \uc81c\uacf5\ud55c\ub2e4.<\/p>\n

           <\/p>\n

          1) bytes\/row<\/p>\n

           <\/p>\n

          bytes\/row = row header + F + V + D<\/p>\n

          ** row header : ROW \ub2f9 3 bytes ( NON-CLUSTERED TABLE)<\/p>\n

          F\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : 250 bytes \uc774\ud558\ub97c \uc0ac\uc6a9\ud558\ub294 \uceec\ub7fc\uc758 TOTAL LENGTH BYTES<\/p>\n

          ( \uac01\u00a0\u00a0 \ub7fc \ub2f9 1 bytes )<\/p>\n

          V\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : 250 bytes \uc774\uc0c1\uc744 \uc0ac\uc6a9\ud558\ub294 \uceec\ub7fc\uc758 TOTAL LENGTH BYTES<\/p>\n

          ( \uac01 \uceec\ub7fc \ub2f9 3 bytes )<\/p>\n

          D\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : [STEP 3]\uc758 D (data space\/average row)<\/p>\n

           <\/p>\n

          2) \uc608\ub97c \ub4e4\uc5b4, [STEP 3]\uc758 \ud14c\uc774\ube14T \uc758 TOATL AVERAGE ROW SIZE<\/p>\n

          – 250 byte\u00a0\ud558\uc758 \uceec\ub7fc \uc218\u00a0\u00a0 = 3<\/p>\n

          250 byte \uc774\uc0c1\uc758 \uceec\ub7fc \uc218\u00a0\u00a0 \u00a0 = 0<\/p>\n

          D (data space\/average row) = 22 \uc778 \uacbd\uc6b0<\/p>\n

          – avg. row size, table T = ( 3 + (1 * 3) + (3 * 0) + 22)<\/p>\n

          = 28 bytes<\/p>\n

           <\/p>\n

            \n
          1. [ STEP 5 ] : \ub370\uc774\ud0c0 \ube14\ub7ed\ub0b4\uc758 \ud3c9\uade0ROW \uc218(AVERAGE ROWS PER BLOCK)\ub97c \uacc4\uc0b0<\/li>\n<\/ol>\n

             <\/p>\n

             <\/p>\n

            1) R (avg. # of rows\/block)<\/p>\n

             <\/p>\n

            R (avg. # of rows\/block) = available space \/ average row size<\/p>\n

             <\/p>\n

             <\/p>\n

            ** available space \u00a0 : [STEP 2]\uc758 available data space<\/p>\n

            average row size : [STEP 4]\uc758 bytes\/row<\/p>\n

             <\/p>\n

            2) \uc608\ub97c \ub4e4\uc5b4, [STEP 2]\uc758available space\uac00(1768 – 2R) bytes\uc774\uace0,<\/p>\n

            [STEP 4]\uc758 \ud14c\uc774\ube14T\uc758bytes\/row\uac0028bytes\uc77c \uacbd\uc6b0<\/p>\n

             <\/p>\n

            –\u00a0\u00a0 \u00a0 R = (1768 – 2R)bytes \/ 28bytes<\/p>\n

            28R = 1768 – 2R<\/p>\n

            30R = 1768<\/p>\n

            R = 58 rows\/block<\/p>\n

             <\/p>\n

              \n
            1. [ STEP 6 ] : \ud14c\uc774\ube14\uc5d0\uc11c \uc694\uad6c\ub418\ub294 \ube14\ub7ed\uacfc \ubc14\uc774\ud2b8 \uc218\ub97c \uacc4\uc0b0<\/li>\n<\/ol>\n

              (NUMBER OF BLOCKS AND BYTES)<\/p>\n

               <\/p>\n

              1) # blocks for table\uacfc # bytes for table<\/p>\n

               <\/p>\n

              # blocks for table = # rows \/ R<\/p>\n

               <\/p>\n

              ** # rows : TABLE\uc758ROW \uc218<\/p>\n

              R \u00a0\u00a0\u00a0\u00a0\u00a0: [STEP 5]\uc758 R rows\/block<\/p>\n

               <\/p>\n

              # bytes for table = # blocks for table \u00a0 * 2048 bytes\/block<\/p>\n

               <\/p>\n

              ** 2048 bytes\/block : \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\uc758 \ube14\ub7ed \ud06c\uae30 (db_block_size)<\/p>\n

              2) \uc608\ub97c \ub4e4\uc5b4, \ud14c\uc774\ube14T\uac0010000\uac1c\uc758rows\uc744 \uac00\uc9c8 \uacbd\uc6b0\uc758 \ud14c\uc774\ube14 \ub2f9 \ube14\ub7ed \uc218<\/p>\n

               <\/p>\n

              – # blocks for table T = 10000 rows \u00a0 \/ 58 rows\/block<\/p>\n

              = 173 blocks<\/p>\n

               <\/p>\n

              – # bytes for table T = 173 blocks * \u00a0 2048 bytes\/block<\/p>\n

              = 354304 bytes<\/p>\n

              = 346 Kbytes<\/p>\n","protected":false},"excerpt":{"rendered":"

              TABLE(NON-CLUSTED TABLE)\uc758 \ucd08\uae30 \uc0ac\uc6a9\ub7c9\uc744 \ucd94\uc815\ud558\ub294 \ubc29\ubc95\uc744 \uc0b4\ud3b4\ubcf4\uc790.   \uc5ec\uae30\uc11c\ub294ORACLE7 SERVER\ub97c \uae30\uc900\uc73c\ub85c \ud558\uc5ec \uc124\uba85 \ud558\uae30 \ud558\uba70, TABLE\uc758 \ucd08\uae30 \uc0ac\uc6a9\ub7c9\uc744 \uacc4\uc0b0\ud558\ub294 \ub2e8\uacc4\ub97c \ub2e4\uc74c\uacfc \uac19\uc774 \ub098\ub20c\uc218 \uc788\ub2e4.   STEP 1 – \ucd1d \ube14\ub7ed \ud5e4\ub4dc \ud06c\uae30(BLOCK HEADER SIZE)\ub97c \uacc4\uc0b0 STEP 2 – \ub370\uc774\ud0c0 \ube14\ub7ed \ub2f9 \uc0ac\uc6a9 \uac00\ub2a5\ud55c \ub370\uc774\ud0c0 \uc601\uc5ed\uc744 \uacc4\uc0b0 STEP 3 – \ud3c9\uade0ROW\uc758 \uc804\uccb4 \uceec\ub7fc\uc758 \uae38\uc774(COMBINED COLUMN LENGTH)\ub97c […]<\/p>\n","protected":false},"author":1,"featured_media":0,"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":[3],"tags":[891],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2702"}],"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=2702"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2702\/revisions"}],"predecessor-version":[{"id":2703,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2702\/revisions\/2703"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2702"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2702"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2702"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}