{"id":2844,"date":"2018-02-03T14:48:11","date_gmt":"2018-02-03T05:48:11","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2844"},"modified":"2018-02-03T14:48:11","modified_gmt":"2018-02-03T05:48:11","slug":"rebuild-%ed%95%b4%ec%95%bc-%ed%95%a0-index-%ec%b0%be%eb%8a%94-%ec%bf%bc%eb%a6%ac-base-info-%ed%86%b5%ea%b3%84%ec%a0%95%eb%b3%b4","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2844","title":{"rendered":"Rebuild \ud574\uc57c \ud560 Index \ucc3e\ub294 \ucffc\ub9ac (Base Info.\ud1b5\uacc4\uc815\ubcf4)"},"content":{"rendered":"<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">prompt Execessive Split Index List  (block &gt; 1000)\r\nprompt _______________________________________________________\r\nprompt\r\n\r\nselect \/*+ ordered *\/\r\n  u.name \t\"Owner\",\r\n  o.name \t\"Index\",\r\n  op.subname \t\"Partition\",\r\n  decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,o.subname,'') \"SubPartition\",\r\n  (1-floor(decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt)\r\n          -decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.rowcnt ,ip.obj#,ip.rowcnt ,i.rowcnt )\r\n          *(sum(h.avgcln)+10)\r\n          \/((p.value-66-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.initrans,ip.obj#,ip.initrans,i.initrans)*24)\r\n           *(1-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.pctfree$,ip.obj#,ip.pctfree$,i.pctfree$)\/100))\r\n          )\/decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt))\t\"Density\",\r\n  floor(decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt)\r\n       -decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.rowcnt ,ip.obj#,ip.rowcnt ,i.rowcnt )\r\n       *(sum(h.avgcln) + 10)\r\n       \/((p.value-66-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.initrans,ip.obj#,ip.initrans,i.initrans)*24)\r\n        *(1-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.pctfree$,ip.obj#,ip.pctfree$,i.pctfree$)\/100)))\t\"Extra.Block\"\r\nfrom\r\n  sys.ind$  i,\r\n  sys.icol$  ic,\r\n  ( select obj#,part#,bo#,ts#,rowcnt,leafcnt,initrans,pctfree$,analyzetime,flags from sys.indpart$\r\n    union all\r\n    select obj#,part#,bo#,defts#,rowcnt,leafcnt,definitrans,defpctfree,analyzetime,flags from sys.indcompart$\r\n  ) ip,\r\n  sys.indsubpart$ isp,\r\n  ( select ts#,blocksize value from sys.ts$\r\n  )  p,\r\n  sys.hist_head$  h,\r\n  sys.obj$   o,\r\n  sys.user$  u,\r\n  sys.obj$  op\r\nwhere i.obj# = ip.bo#(+)\r\n  and ip.obj# = isp.pobj#(+)\r\n  and decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt) &gt; 1\r\n  and i.type# in (1)  \/* exclude special types *\/\r\n  and i.pctthres$ is null  \/* exclude IOT secondary indexes *\/\r\n  and decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.ts#,ip.obj#,ip.ts#,i.ts#) = p.ts#\r\n  and ic.obj# = i.obj#\r\n  and h.obj# = i.bo#\r\n  and h.intcol# = ic.intcol#\r\n  and o.obj# = nvl(isp.obj#,nvl(ip.obj#,i.obj#))\r\n  and o.owner# != 0\r\n  and u.user# = o.owner#\r\n  and op.obj# = nvl(ip.obj#,i.obj#)\r\n  and u.name not in ('SYS','SYSTEM','SYSMAN','XDB','HR','ODM','OUTLN','OE','SH','PM','SYSAUX','IX','WK_TEST','PERFSTAT','DBSNMP','OLAPSYS','QS_CS','QS_CB','QS_CBADM','QS_OS','QS_WS','QS','QS_ADM','ODM_MTR' ,'WKPROXY','QS_ES','ANONYMOUS','WKSYS','WMSYS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS','FLOWS_FILES','MDDATA','MDSYS','MGMT_VIEW','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL')\r\ngroup by\r\n  u.name,\r\n  o.name,\r\n  op.subname,\r\n  decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,o.subname,''),\r\n  decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.rowcnt ,ip.obj#,ip.rowcnt ,i.rowcnt ),\r\n  decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt),\r\n  decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.initrans,ip.obj#,ip.initrans,i.initrans),\r\n  decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.pctfree$,ip.obj#,ip.pctfree$,i.pctfree$),\r\n  p.value\r\nhaving\r\n  (1-floor(decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt)\r\n          -decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.rowcnt ,ip.obj#,ip.rowcnt ,i.rowcnt )\r\n          *(sum(h.avgcln)+10)\r\n          \/((p.value-66-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.initrans,ip.obj#,ip.initrans,i.initrans)*24)\r\n           *(1-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.pctfree$,ip.obj#,ip.pctfree$,i.pctfree$)\/100))\r\n          )\/decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt)\r\n  ) &lt;= 0.75\r\n  and\r\n  floor(decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt)\r\n       -decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.rowcnt,ip.obj#,ip.rowcnt,i.rowcnt)\r\n       *(sum(h.avgcln) + 10)\r\n       \/((p.value-66-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.initrans,ip.obj#,ip.initrans,i.initrans)*24)\r\n        *(1-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.pctfree$,ip.obj#,ip.pctfree$,i.pctfree$)\/100))\r\n       ) &gt; 1000\r\norder by 6 desc,5\r\n\/<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; prompt Execessive Split Index List (block &gt; 1000) prompt _______________________________________________________ prompt select \/*+ ordered *\/ u.name &#8220;Owner&#8221;, o.name &#8220;Index&#8221;, op.subname &#8220;Partition&#8221;, decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,o.subname,&#8221;) &#8220;SubPartition&#8221;, (1-floor(decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt) -decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.rowcnt ,ip.obj#,ip.rowcnt ,i.rowcnt ) *(sum(h.avgcln)+10) \/((p.value-66-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.initrans,ip.obj#,ip.initrans,i.initrans)*24) *(1-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.pctfree$,ip.obj#,ip.pctfree$,i.pctfree$)\/100)) )\/decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt)) &#8220;Density&#8221;, floor(decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.leafcnt,ip.obj#,ip.leafcnt,i.leafcnt) -decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.rowcnt ,ip.obj#,ip.rowcnt ,i.rowcnt ) *(sum(h.avgcln) + 10) \/((p.value-66-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.initrans,ip.obj#,ip.initrans,i.initrans)*24) *(1-decode(nvl(isp.obj#,nvl(ip.obj#,i.obj#)),isp.obj#,isp.pctfree$,ip.obj#,ip.pctfree$,i.pctfree$)\/100))) &#8220;Extra.Block&#8221; from sys.ind$ i, sys.icol$ ic, ( select obj#,part#,bo#,ts#,rowcnt,leafcnt,initrans,pctfree$,analyzetime,flags from sys.indpart$ [&hellip;]<\/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":[935,934,933],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2844"}],"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=2844"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2844\/revisions"}],"predecessor-version":[{"id":2845,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2844\/revisions\/2845"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2844"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2844"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2844"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}