{"id":418,"date":"2015-08-31T09:43:08","date_gmt":"2015-08-31T09:43:08","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=418"},"modified":"2015-08-31T09:43:08","modified_gmt":"2015-08-31T09:43:08","slug":"partitioned-index%ec%9d%98-%ec%a2%85%eb%a5%98-%eb%b0%8f-index-unusable-%ec%83%81%ed%83%9c-%ec%a0%95%eb%a6%ac","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=418","title":{"rendered":"PARTITIONED INDEX\uc758 \uc885\ub958 \ubc0f INDEX UNUSABLE \uc0c1\ud0dc \uc815\ub9ac"},"content":{"rendered":"

Oracle8\uc758 \uc0c8\ub85c\uc6b4 \uae30\ub2a5\uc778 partition \uc740 index\uc5d0\ub3c4 \uc801\uc6a9\ub41c\ub2e4. \uc774 partitioned index\ub294 \uc0dd\uc131\ub418\ub294 \ud615\ud0dc\uc5d0 \ub530\ub77c \uba87 \uac00\uc9c0\ub85c \ub098\ub204\uc5b4\uc9c0\ub294\ub370, \uc5ec\uae30\uc5d0\uc11c\ub294 \uc774 \uac01\uac01\uc758 <\/span><\/p>\n

\uc885\ub958\uc5d0 \ub300\ud574 \uc880 \ub354 \uc790\uc138\ud788 \uc124\uba85\ud55c\ub2e4. \uadf8\ub9ac\uace0 table\uacfc index\uac00 partition\ub428\uc5d0 \ub530\ub77c index\uac00 unusable \uc0c1\ud0dc\uac00 \ub418\ub294 \uacbd\uc6b0\uac00 \ubc1c\uc0dd\ud560 \uc218 \uc788\ub294\ub370 \uc774\ub7ec\ud55c \uc0c1\ud0dc\ub97c <\/span><\/p>\n

\uc720\ubc1c\uc2dc\ud0a4\ub294 \uacbd\uc6b0\uc640 \uc870\uce58 \ubc29\ubc95\uc5d0 \ub300\ud574\uc11c\ub3c4 \uc0b4\ud3b4\ubcf8\ub2e4.<\/p>\n

<\/p>\n

1. partitioned index\uc758 \uc885\ub958<\/p>\n

<\/p>\n

(1) LOCAL INDEX<\/p>\n

<\/p>\n

local index\ub780 index\ub97c \uc0dd\uc131\ud55c table\uacfc partitioned index\uac00 equi-partition\ub41c \uacbd\uc6b0\ub97c \ub098\ud0c0\ub0b8\ub2e4.<\/span><\/p>\n

\uc989, index\uc640 table\uc740 \uac19\uc740 \uceec\ub7fc\uc5d0 \uc758\ud574 partition\ub418\uba70, \ud558\ub098\uc758 index partition\uc774 table partition \ud558\ub098\uc640 \ub300\uc751\ub418\uba70, \ub300\uc751\ub418\ub294 index partition\uacfc <\/span>table partition\uc740 \uac01\uac01 \uac19\uc740 \ubc94\uc704\ub97c \uac16\uac8c \ub41c\ub2e4. \uacb0\uad6d \ud2b9\uc815\ud55c \ud558\ub098\uc758 index\uc5d0 <\/span>\ud3ec\ud568\ub41c \ubaa8\ub4e0 key\ub4e4\uc740 \ud558\ub098\uc758 table partition \ub0b4\uc758 data\ub9cc\uc744 \uac00\ub9ac\ud0a4\uac8c \ub41c\ub2e4.<\/span><\/p>\n

partitioned index\ub294 \ub2e4\uc2dc \ub2e4\uc74c\uacfc \uac19\uc774 prefixed\uc640 non-prefixed\ub85c \ub098\ub204\uc5b4\uc9c4\ub2e4.<\/p>\n

<\/p>\n

(1)-1 local prefixed index<\/p>\n

<\/p>\n

prefixed index\ub294 index\uc5d0\uc11c \ub9e8 \uc55e\uc5d0 \uc704\uce58\ud55c column\uc5d0 \uc758\ud574 partition\ub418\ub294 \uac83\uc774\uba70, non-prefixed index\ub294 index\uc5d0\uc11c \ub9e8 \uc55e\uc758 \uceec\ub7fc\uc744 \uc81c\uc678\ud55c \ub2e4\ub978 \uceec\ub7fc\uc5d0 <\/span>\uc758\ud574 partition\ub41c \uacbd\uc6b0\uc774\ub2e4.<\/span><\/p>\n

local prefixed index\ub294 \ub2e4\uc74c\uacfc \uac19\uc774 \uc0dd\uc131\ud560 \uc218 \uc788\ub2e4.<\/p>\n

<\/p>\n

   CREATE TABLE dept<\/p>\n

   (deptno NUMBER NOT NULL,<\/p>\n

   loc VARCHAR2(10))<\/p>\n

   PARTITION BY RANGE (deptno)<\/p>\n

   (PARTITION part1 VALUES LESS THAN(30),<\/p>\n

   PARTITION part2 values less than (MAXVALUE));<\/p>\n

   CREATE INDEX dept_idx ON dept(deptno) LOCAL;<\/p>\n

<\/p>\n

(1)-2 local non-prefixed index<\/p>\n

index columns\ub4e4 \uc911 \ub9e8 \uc55e\uc5d0 \uc788\ub294 column\uc73c\ub85c partition\ub418\uc9c0 \uc54a\uc740 \uacbd\uc6b0\ub294 non-prefixed index\uac00 \ub41c\ub2e4. \ub2e4\uc74c\uacfc \uac19\uc774 \uc0dd\uc131\ud560 \uc218 \uc788\ub2e4.<\/span><\/p>\n

<\/p>\n

   CREATE INDEX dept_locidx ON dept(loc) LOCAL;<\/p>\n

<\/p>\n

\uc774\ub7ec\ud55c non-prefixed index\ub294 \ud2b9\ud788 historical\ud55c data\ub97c \ubcf4\uad00\ud558\ub294 table\uc758 \uacbd\uc6b0 \uc720\uc6a9\ud558\ub2e4. \uc989, \ub0a0\uc9dc\uc5d0 \ub530\ub77c table\uacfc index\uc758 partition\uc740 \uc774\ub8e8\uc5b4\uc9c0\uace0, <\/span>\uc778\ub371\uc2a4\ub294 \ubcc4\ub3c4\uc758 \uc0ac\uc6d0 \ubc88\ud638\ub098 \uc81c\ud488 \ubc88\ud638\uc640 \uac19\uc774 key\uac00 \ub418\ub294 \uac83\uc5d0 \uc0dd\uc131\ud558\ub294 <\/span>\uacbd\uc6b0\uc774\ub2e4.<\/span><\/p>\n


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

(2) GLOBAL INDEX<\/p>\n

<\/p>\n

global index\ub294 table\uacfc\ub294 \ub2e4\ub974\uac8c partition\uc774 \ub41c\ub2e4. \uc989, table\uacfc \uac19\uc740 column\uc73c\ub85c partition\ub418\ub098 \uadf8 \ubc94\uc704\uac00 \ud2c0\ub9ac\uac70\ub098, \ud639\uc740 \ub2e4\ub978 \uceec\ub7fc\uc73c\ub85c partition\uc774 <\/span>\uc774\ub8e8\uc5b4\uc9c4\ub2e4.<\/span><\/p>\n

\ud558\ub098\uc758 index partition\uc5d0 \uc788\ub294 \ubaa8\ub4e0 index\ub294 \ubaa8\ub450 \ud558\ub098\uc758 table partition\uc5d0 \uc18d\ud558\uac8c \ub418\uc9c0 \uc54a\uace0, \ub450 \uac1c \uc774\uc0c1\uc758 partition\uc5d0 \ub098\ub204\uc5b4 \uc788\uc744 \uc218 \uc788\ub2e4. <\/span>\uc608\ub97c \ub4e4\uc5b4 EMP table\uc758 \uacbd\uc6b0 \ub9ce\uc740 \uc218\uc758 \uc0ac\uc6d0\uc744 \uac01 \ubd80\uc11c \ubcc4\ub85c partition\uc744 \uad6c\uc131\ud560 <\/span>\uc218 \uc788\ub2e4. \ud2b9\uc815 \ubd80\uc11c\uc5d0 \uc18d\ud55c \uc0ac\uc6d0\uc5d0 \ub300\ud55c operation\uc758 \uacbd\uc6b0 \uc774\uac83\uc740 \ub9e4\uc6b0 \ub3c4\uc6c0\uc774 <\/span>\ub420 \uc218 \uc788\ub2e4. \uadf8\ub7ec\ub098 \ub300\ubd80\ubd84 \ud68c\uc0ac\uc5d0 \ubd80\uc11c\uc758 \uc885\ub958\ub294 \uc544\uc8fc \ub9ce\uc740 \uac83\uc774 \uc544\ub2c8\uc5b4\uc11c \ubd80<\/span><\/p>\n

\uc11c \ubc88\ud638\uc5d0 index\ub97c \uac70\ub294 \uac83\uc740 \ub4dc\ubb38 \uc77c\uc774\ub2e4. \uadf8\ub7ec\ub098 \uc0ac\uc6d0 \ubc88\ud638\ub294 \uace0\uc720\ud558\uae30 \ub54c\ubb38\uc5d0 primary key\uac00 \ub418\uac70\ub098 index\ub97c \uc0dd\uc131\ud558\ub294 \uac83\uc774 \uc77c\ubc18\uc801\uc774\ub2e4. \uc774 \ub54c \uc774 \uc0ac\uc6d0 <\/span>\ubc88\ud638\uc5d0 \ubd80\uc5ec\ub41c index\ub97c partitioning\ud558\uac8c \ub418\uba74, \uc774\uac83\uc774 global index\uac00 \ub418\ub294 <\/span>\uac83\uc774\ub2e4.<\/span><\/p>\n

global index\ub294 prefixed global index\ub9cc\uc774 \uc874\uc7ac\ud558\uba70, non-prefixed global index\ub294 \uc0dd\uc131\uc774 \ubd88\uac00\ub2a5\ud558\ub2e4. \uc989, global index\ub294 \ud56d\uc0c1 index\uc758 \ub9e8 \uc55e \uceec\ub7fc \uac12\ub9cc\uc744 <\/span>\uc774\uc6a9\ud558\uc5ec partition\ub41c\ub2e4.<\/span><\/p>\n

\uc704\uc758 \uc608\ub97c \uc774\uc6a9\ud558\uc5ec global index\ub97c \uc0dd\uc131\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

<\/p>\n

   CREATE TABLE emp<\/p>\n

   (empno NUMBER NOT NULL,<\/p>\n

   ename VARCHAR2(10),<\/p>\n

   deptno NUMBER)<\/p>\n

   PARTITION BY RANGE (deptno)<\/p>\n

   (PARTITION part1 VALUES LESS THAN(30),<\/p>\n

   PARTITION part2 VALUES LESS THAN (MAXVALUE));<\/p>\n

   CREATE UNIQUE INDEX emp_pk on emp(empno)<\/p>\n

   GLOBAL PARTITION BY RANGE (empno)<\/p>\n

   (PARTITION p1 VALUES LESS THAN (“1000”),<\/p>\n

   PARTITION p2  VALUES LESS THAN (“2000”),<\/p>\n

   PARTITION p3  VALUES LESS THAN (MAXVALUE));<\/p>\n

<\/p>\n

2. index unusable<\/p>\n

<\/p>\n

non-partitioned index\ub098 partitioned index\uc758 partition\uc740 \ud2b9\uc815\ud55c operation\uc5d0 \uc758\ud574 Index Unusable(IU) \uc0c1\ud0dc\uac00 \ub420 \uc218 \uc788\ub2e4. \uc774\ub807\uac8c IU \uc0c1\ud0dc\uac00 \ub41c <\/span>index\ub098 index partition\uc744 SELECT\ud558\uac70\ub098 DML\uc744 \uc2dc\ub3c4\ud558\uba74 \uc624\ub958\uac00 \ubc1c\uc0dd\ud558\uac8c \ub41c\ub2e4. <\/span>\uc5b4\ub5a4 partition\uc774 IU \uc0c1\ud0dc\uac00 \ub418\uba74 \uadf8 partition\uc744 \uc0ac\uc6a9\ud558\uae30 \uc804\uc5d0 rebuild\ud558\uc5ec\uc57c <\/span>\ud55c\ub2e4. \uadf8\ub7ec\ub098 IU partition\uc744 \uc81c\uc678\ud55c \ub2e4\ub978 partition\ub9cc\uc744 \uc77d\uac70\ub098 DML\uc744 \uc218\ud589\ud558\ub294 <\/span>\uc791\uc5c5\uc740 IU partition\uc744 access\ud558\uc9c0 \uc54a\ub294 \ud55c \uc624\ub958\uac00 \ubc1c\uc0dd\ud558\uc9c0 \uc54a\ub294\ub2e4. <\/span>\ub2e8, IU partition\uc744 rebuild\ud558\uae30 \uc804\uc5d0 split\uc774\ub098 rename\uc774 \uac00\ub2a5\ud558\uba70, IU \uc0c1\ud0dc\uc778 <\/span>global index\ub97c drop\ud558\ub294 \uac83\ub3c4 \uac00\ub2a5\ud558\ub2e4.<\/span><\/p>\n

partition\uc744 Index Unusable \uc0c1\ud0dc\ub85c \ub9cc\ub4e4 \uc218 \uc788\ub294 \uc791\uc5c5\uc740 \ub2e4\uc74c\uacfc \uac19\uc774 6\uac00\uc9c0\ub85c \uc694\uc57d\ud560 \uc218 \uc788\ub2e4.<\/span><\/p>\n


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

(1) direct path load \uc2dc<\/p>\n

Direct path SQL*Loader \uc218\ud589 \ud6c4 index\uac00 table\uc758 \ud574\ub2f9 data\ubcf4\ub2e4 \uc774\uc804 \uac83\uc774\uba74, IU \uc0c1\ud0dc\uac00 \ub41c\ub2e4. (Oracle7\uc5d0\uc11c\ub294 Index\uac00 Direct Load State\uac00 \ub418\uc5c8\ub2e4\uace0 \ud45c\ud604\ud55c\ub2e4). <\/span><\/p>\n

index\uac00 table\uc758 data\ubcf4\ub2e4 \uc774\uc804 \uc0c1\ud0dc\ub77c\ub294 \uac83\uc740 data\ub97c load \ud6c4 index\ub97c \uc0dd\uc131 \uc911\uc5d0 space \ubd80\uc871 \ub4f1\uc758 \uc6d0\uc778\uc73c\ub85c \uc624\ub958\uac00 \ubc1c\uc0dd\ud558\uc600\uac70\ub098 SKIP_INDEX_MAINTENANCE <\/span><\/p>\n

option\uc744 \uc0ac\uc6a9\ud55c \uacbd\uc6b0\uc774\ub2e4.<\/p>\n

<\/p>\n

(2) ALTER TABLE MOVE PARTITION\uacfc \uac19\uc774 ROWID\ub97c \ubcc0\ud654\uc2dc\ud0a4\ub294 \uc791\uc5c5. <\/p>\n

    \uc601\ud5a5\ubc1b\ub294 local index\uc640 \uc804\uccb4 global index\ub97c IU \uc0c1\ud0dc\uac00 \ub418\uac8c \ud55c\ub2e4.<\/span><\/p>\n

<\/p>\n

(3) ALTER TABLE TRUNCATE PARTITION\uc774\ub098 DROP PARTITION\uacfc \uac19\uc774 table\uc758 row\ub97c \uc9c0\uc6b0\ub294 \uc791\uc5c5. <\/span><\/p>\n

    global index partition\uc744 IU \uc0c1\ud0dc\ub85c \ub9cc\ub4e0\ub2e4.<\/p>\n

<\/p>\n

(4) ALTER TABLE SPLIT PARTITION\uc740 local index\uc758 partition definition\uc740 \ubcc0\uacbd\uc2dc\ud0a4\uc9c0\ub9cc, \uc790\ub3d9\uc73c\ub85c index\ub97c \uc0c8\ub85c\uc6b4 definition\uc5d0 \ub9de\uac8c rebuild\ud558\uc9c0 \uc54a\uae30<\/span> \ub54c\ubb38\uc5d0 \uc601\ud5a5 \ubc1b\ub294 local index partition\uc744 IU \uc0c1\ud0dc\ub85c \ub9cc\ub4e0\ub2e4. \ub610\ud55c \uc774\uac83\uc740<\/span> ROWID\ub97c \ubcc0\uacbd\uc2dc\ud0a4\uae30 \ub54c\ubb38\uc5d0 \ubaa8\ub4e0 global index partition\uc744 IU \uc0c1\ud0dc\ub85c \ub9cc\ub4e0\ub2e4.<\/span><\/p>\n


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

(5) ALTER INDEX SPLIT PARTITION\uc740 index\uc758 definition\uc740 \ubcc0\uacbd\uc2dc\ud0a4\uc9c0\ub9cc, \uc601\ud5a5 \ubc1b\uc740 partition\uc740 rebuild\uc2dc\ud0a4\uc9c0 \uc54a\ub294\ub2e4. \uc774 \uc791\uc5c5\uc740 \uc601\ud5a5\ubc1b\ub294 index <\/span>partition \ubd80\ubd84\uc744 IU \uc0c1\ud0dc\ub85c \ub9cc\ub4e0\ub2e4. \uadf8\ub7ec\ub098 global index\uc758 \uacbd\uc6b0\ub294 \uadf8\ub300\ub85c <\/span>usable \uc0c1\ud0dc\ub85c \ub41c\ub2e4.<\/span><\/p>\n

<\/p>\n

3. unusable\uc0c1\ud0dc\ub97c \ud655\uc778\ud558\uace0 \ud574\uacb0\ud558\ub294 \ubc29\ubc95<\/p>\n

   os> sqlplus system\/manager<\/p>\n

   SQL>select index_owner, index_name, partition_name, status <\/p>\n

       from dba_ind_partitions<\/p>\n

       where status = “UNUSABLE” ;   <\/p>\n

   <\/p>\n

   rebuild\ud558\ub294 \ubc29\ubc95\uc740 \ud574\ub2f9 user\uc5d0\uc11c<\/p>\n

   SQL>alter index emp_pk rebuild partiton p1;<\/p>\n","protected":false},"excerpt":{"rendered":"

Oracle8\uc758 \uc0c8\ub85c\uc6b4 \uae30\ub2a5\uc778 partition \uc740 index\uc5d0\ub3c4 \uc801\uc6a9\ub41c\ub2e4. \uc774 partitioned index\ub294 \uc0dd\uc131\ub418\ub294 \ud615\ud0dc\uc5d0 \ub530\ub77c \uba87 \uac00\uc9c0\ub85c \ub098\ub204\uc5b4\uc9c0\ub294\ub370, \uc5ec\uae30\uc5d0\uc11c\ub294 \uc774 \uac01\uac01\uc758  \uc885\ub958\uc5d0 \ub300\ud574 \uc880 \ub354 \uc790\uc138\ud788 \uc124\uba85\ud55c\ub2e4. \uadf8\ub9ac\uace0 table\uacfc index\uac00 partition\ub428\uc5d0 \ub530\ub77c index\uac00 unusable \uc0c1\ud0dc\uac00 \ub418\ub294 \uacbd\uc6b0\uac00 \ubc1c\uc0dd\ud560 \uc218 \uc788\ub294\ub370 \uc774\ub7ec\ud55c \uc0c1\ud0dc\ub97c  \uc720\ubc1c\uc2dc\ud0a4\ub294 \uacbd\uc6b0\uc640 \uc870\uce58 \ubc29\ubc95\uc5d0 \ub300\ud574\uc11c\ub3c4 \uc0b4\ud3b4\ubcf8\ub2e4. 1. partitioned index\uc758 \uc885\ub958 (1) LOCAL INDEX local index\ub780 index\ub97c \uc0dd\uc131\ud55c […]<\/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":[9],"tags":[163,164,165,166],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/418"}],"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=418"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/418\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=418"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=418"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=418"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}