{"id":354,"date":"2011-12-04T22:33:01","date_gmt":"2011-12-04T14:33:01","guid":{"rendered":"https:\/\/yangtingkun.net\/?p=354"},"modified":"2011-12-06T01:04:55","modified_gmt":"2011-12-05T17:04:55","slug":"%e7%b4%a2%e5%bc%95%e9%87%8d%e5%bb%ba%e7%9a%84%e6%95%b0%e6%8d%ae%e6%ba%90%ef%bc%88%e4%ba%8c%ef%bc%89","status":"publish","type":"post","link":"https:\/\/yangtingkun.net\/?p=354","title":{"rendered":"\u7d22\u5f15\u91cd\u5efa\u7684\u6570\u636e\u6e90\uff08\u4e8c\uff09"},"content":{"rendered":"<p>\u5bf9\u8fd9\u4e2a\u95ee\u9898\u6709\u4e86\u8fdb\u4e00\u6b65\u7684\u8ba4\u8bc6\u3002<br \/>\n\u7d22\u5f15\u91cd\u5efa\u7684\u6570\u636e\u6e90\uff1a<a href=\"http:\/\/yangtingkun.itpub.net\/post\/468\/457384\">http:\/\/yangtingkun.itpub.net\/post\/468\/457384<\/a><br \/>\n\u4e0a\u4e00\u7bc7\u6587\u7ae0\u6d4b\u8bd5\u7684\u7ed3\u679c\u8ba4\u4e3aDDL\u4e5f\u662f\u57fa\u4e8eCBO\u7684\uff0c\u4f46\u662f\u4eca\u5929\u53d1\u73b0\u95ee\u9898\u5e76\u975e\u5982\u6b64\u3002Oracle\u5728\u8bc4\u4f30REBUILD\u7d22\u5f15\u65f6\u5e76\u4e0d\u662f\u6839\u636e\u7edf\u8ba1\u4fe1\u606f\uff0c\u800c\u662f\u6839\u636e\u6570\u636e\u5b57\u5178\u4e2d\u975e\u7d22\u5f15\u5b57\u6bb5\u7684\u957f\u5ea6\uff1a<\/p>\n<pre lang='SQL'>SQL> create table t_rebuild (id number, flag char(1));\r\nTable created.\r\nSQL> insert into t_rebuild select rownum, 'a' from dba_objects;\r\n15695 rows created.\r\nSQL> commit;\r\nCommit complete.\r\nSQL> create index ind_t_rebuild_id on t_rebuild(id);\r\nIndex created.\r\nSQL> explain plan for alter index ind_t_rebuild_id rebuild;\r\nExplained.\r\nSQL> select * from table(dbms_xplan.display);\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------\r\nPlan hash value: 3014377519\r\n-------------------------------------------------------------------------------------------\r\n| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-------------------------------------------------------------------------------------------\r\n|   0 | ALTER INDEX STATEMENT  |                  |    82 |  1066 |     2   (0)| 00:00:01 |\r\n|   1 |  INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID |       |       |            |          |\r\n|   2 |   SORT CREATE INDEX    |                  |    82 |  1066 |            |          |\r\n|   3 |    TABLE ACCESS FULL   | T_REBUILD        |    82 |  1066 |     2   (0)| 00:00:01 |\r\n-------------------------------------------------------------------------------------------\r\n10 rows selected.\r\nSQL> alter table t_rebuild modify (flag char(2));\r\nTable altered.\r\nSQL> explain plan for alter index ind_t_rebuild_id rebuild;\r\nExplained.\r\nSQL> select * from table(dbms_xplan.display);\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------\r\nPlan hash value: 3014377519\r\n-------------------------------------------------------------------------------------------\r\n| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-------------------------------------------------------------------------------------------\r\n|   0 | ALTER INDEX STATEMENT  |                  |  2288 | 29744 |     7   (0)| 00:00:01 |\r\n|   1 |  INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID |       |       |            |          |\r\n|   2 |   SORT CREATE INDEX    |                  |  2288 | 29744 |            |          |\r\n|   3 |    TABLE ACCESS FULL   | T_REBUILD        |  2288 | 29744 |     7   (0)| 00:00:01 |\r\n-------------------------------------------------------------------------------------------\r\n10 rows selected.\r\nSQL> alter table t_rebuild modify (flag char(3));\r\nTable altered.\r\nSQL> explain plan for alter index ind_t_rebuild_id rebuild;\r\nExplained.\r\nSQL> select * from table(dbms_xplan.display);\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------\r\nPlan hash value: 43729923\r\n-------------------------------------------------------------------------------------------\r\n| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-------------------------------------------------------------------------------------------\r\n|   0 | ALTER INDEX STATEMENT  |                  |  4738 | 61594 |    13   (0)| 00:00:01 |\r\n|   1 |  INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID |       |       |            |          |\r\n|   2 |   SORT CREATE INDEX    |                  |  4738 | 61594 |            |          |\r\n|   3 |    INDEX FAST FULL SCAN| IND_T_REBUILD_ID |       |       |            |          |\r\n-------------------------------------------------------------------------------------------\r\n10 rows selected.<\/pre>\n<p>\u968f\u7740\u975e\u7d22\u5f15\u5217\u7684\u957f\u5ea6\u589e\u52a0\uff0c\u91cd\u5efa\u7d22\u5f15\u7684\u6267\u884c\u8ba1\u5212\u7531\u5168\u8868\u626b\u63cf\u53d8\u6210\u4e86\u7d22\u5f15\u5feb\u901f\u5168\u626b\u3002<br \/>\n\u6574\u4e2a\u8fc7\u7a0b\u5e76\u6ca1\u6709\u6536\u96c6\u8fc7\u8868\u6216\u7d22\u5f15\u7684\u7edf\u8ba1\u4fe1\u606f\uff0c\u4f46\u662f\u6267\u884c\u8ba1\u5212\u5df2\u7ecf\u53d1\u751f\u4e86\u6539\u53d8\uff0c\u4e0b\u9762\u5c1d\u8bd5\u5173\u95ed\u7edf\u8ba1\u4fe1\u606f\u52a8\u6001\u6536\u96c6\uff0c\u4ee5\u53ca\u8bbe\u7f6e\u8868\u548c\u5217\u5c5e\u6027\u7684\u65b9\u5f0f\u5f71\u54cd\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n<pre lang='SQL'>SQL> alter session set optimizer_dynamic_sampling = 0;\r\nSession altered.\r\nSQL> explain plan for alter index ind_t_rebuild_id rebuild;\r\nExplained.\r\nSQL> select * from table(dbms_xplan.display);\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------\r\nPlan hash value: 43729923\r\n-------------------------------------------------------------------------------------------\r\n| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-------------------------------------------------------------------------------------------\r\n|   0 | ALTER INDEX STATEMENT  |                  |  4738 | 61594 |    13   (0)| 00:00:01 |\r\n|   1 |  INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID |       |       |            |          |\r\n|   2 |   SORT CREATE INDEX    |                  |  4738 | 61594 |            |          |\r\n|   3 |    INDEX FAST FULL SCAN| IND_T_REBUILD_ID |       |       |            |          |\r\n-------------------------------------------------------------------------------------------\r\n10 rows selected.\r\nSQL> exec dbms_stats.set_table_stats(user, 'T_REBUILD', numrows => 1, numblks => 1, avgrlen => 2)\r\nPL\/SQL procedure successfully completed.\r\nSQL> explain plan for alter index ind_t_rebuild_id rebuild;\r\nExplained.\r\nSQL> select * from table(dbms_xplan.display);\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------\r\nPlan hash value: 43729923\r\n-------------------------------------------------------------------------------------------\r\n| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-------------------------------------------------------------------------------------------\r\n|   0 | ALTER INDEX STATEMENT  |                  |     1 |     2 |     2   (0)| 00:00:01 |\r\n|   1 |  INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID |       |       |            |          |\r\n|   2 |   SORT CREATE INDEX    |                  |     1 |     2 |            |          |\r\n|   3 |    INDEX FAST FULL SCAN| IND_T_REBUILD_ID |       |       |            |          |\r\n-------------------------------------------------------------------------------------------\r\n10 rows selected.\r\nSQL> exec dbms_stats.set_column_stats(user, 'T_REBUILD', 'FLAG', avgclen => 1)\r\nPL\/SQL procedure successfully completed.\r\nSQL> explain plan for alter index ind_t_rebuild_id rebuild;\r\nExplained.\r\nSQL> select * from table(dbms_xplan.display);\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------\r\nPlan hash value: 43729923\r\n-------------------------------------------------------------------------------------------\r\n| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |\r\n-------------------------------------------------------------------------------------------\r\n|   0 | ALTER INDEX STATEMENT  |                  |     1 |     2 |     2   (0)| 00:00:01 |\r\n|   1 |  INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID |       |       |            |          |\r\n|   2 |   SORT CREATE INDEX    |                  |     1 |     2 |            |          |\r\n|   3 |    INDEX FAST FULL SCAN| IND_T_REBUILD_ID |       |       |            |          |\r\n-------------------------------------------------------------------------------------------\r\n10 rows selected.<\/pre>\n<p>\u5f88\u660e\u663eDDL\u6267\u884c\u8ba1\u5212\u7684\u786e\u5b9a\u5176\u5b9e\u548c\u7edf\u8ba1\u4fe1\u606f\u6ca1\u6709\u4ec0\u4e48\u5173\u7cfb\uff0c\u800c\u5b8c\u5168\u662f\u6839\u636e\u6570\u636e\u5b57\u5178\u786e\u5b9a\u7684\u3002\u56e0\u6b64\u8fd9\u5b9e\u9645\u4e0a\u4e5f\u662f\u4e00\u79cdRULE\uff0c\u53ea\u4e0d\u8fc7Oracle\u5c06\u8fd9\u4e2a\u6761\u4ef6\u5199\u5230\u4e86\u4f18\u5316\u5668\u4e2d\uff0c\u5982\u679c\u5c06\u4f18\u5316\u5668\u8bbe\u7f6e\u4e3aRULE\uff0cOracle\u540c\u6837\u53ef\u4ee5\u505a\u51fa\u76f8\u540c\u7684\u5224\u65ad\uff1a<\/p>\n<pre lang='SQL'>SQL> alter session set optimizer_mode = rule;\r\nSession altered.\r\nSQL> drop table t_rebuild purge;\r\nTable dropped.\r\nSQL> create table t_rebuild (id number, flag char(1));\r\nTable created.\r\nSQL> insert into t_rebuild select rownum, 'a' from dba_objects;\r\n15695 rows created.\r\nSQL> commit;\r\nCommit complete.\r\nSQL> create index ind_t_rebuild_id on t_rebuild(id);\r\nIndex created.\r\nSQL> explain plan for alter index ind_t_rebuild_id rebuild;\r\nExplained.\r\nSQL> select * from table(dbms_xplan.display);\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------\r\nPlan hash value: 3014377519\r\n---------------------------------------------------\r\n| Id  | Operation              | Name             |\r\n---------------------------------------------------\r\n|   0 | ALTER INDEX STATEMENT  |                  |\r\n|   1 |  INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID |\r\n|   2 |   SORT CREATE INDEX    |                  |\r\n|   3 |    TABLE ACCESS FULL   | T_REBUILD        |\r\n---------------------------------------------------\r\nNote\r\n-----\r\n   - rule based optimizer used (consider using cbo)\r\n14 rows selected.\r\nSQL> alter table t_rebuild modify flag char(3);\r\nTable altered.\r\nSQL> explain plan for alter index ind_t_rebuild_id rebuild;\r\nExplained.\r\nSQL> select * from table(dbms_xplan.display);\r\nPLAN_TABLE_OUTPUT\r\n------------------------------------------------------------------------------------------\r\nPlan hash value: 43729923\r\n\r\n---------------------------------------------------\r\n| Id  | Operation              | Name             |\r\n---------------------------------------------------\r\n|   0 | ALTER INDEX STATEMENT  |                  |\r\n|   1 |  INDEX BUILD NON UNIQUE| IND_T_REBUILD_ID |\r\n|   2 |   SORT CREATE INDEX    |                  |\r\n|   3 |    INDEX FAST FULL SCAN| IND_T_REBUILD_ID |\r\n---------------------------------------------------\r\nNote\r\n-----\r\n   - rule based optimizer used (consider using cbo)\r\n14 rows selected.<\/pre>\n<p>\u4e0d\u8fc7\u7531\u4e8e\u5f88\u591aDDL\u64cd\u4f5c\u5bf9\u4e8e\u7684\u8868\u6216\u5bf9\u8c61\u672c\u8eab\u5c31\u6ca1\u6709\u7edf\u8ba1\u4fe1\u606f\uff0c\u5b8c\u5168\u4f7f\u7528CBO\u662f\u4e0d\u73b0\u5b9e\u7684\uff0c\u4e5f\u662f\u4e0d\u51c6\u786e\u7684\uff0c\u6240\u4ee5\u91c7\u7528\u8fd9\u79cd\u57fa\u4e8e\u89c4\u5219\u7684\u6267\u884c\u8ba1\u5212\u4e5f\u662f\u6709\u9053\u7406\u7684\u3002\u4e0d\u8fc7\u4e8b\u5b9e\u4e0a\uff0c\u5bf9\u4e8eDDL\u800c\u8a00\uff0c\u6709\u591a\u79cd\u6267\u884c\u8ba1\u5212\u53ef\u9009\u62e9\u7684\u5176\u5b9e\u4e5f\u5e76\u4e0d\u591a\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5bf9\u8fd9\u4e2a\u95ee\u9898\u6709\u4e86\u8fdb\u4e00\u6b65\u7684\u8ba4\u8bc6\u3002 \u7d22\u5f15\u91cd\u5efa\u7684\u6570\u636e\u6e90\uff1ahttp:\/\/yangtingkun.itpub.net\/post\/468\/457384 \u4e0a\u4e00\u7bc7\u6587\u7ae0\u6d4b\u8bd5\u7684\u7ed3\u679c\u8ba4\u4e3aDDL\u4e5f\u662f\u57fa\u4e8eCBO\u7684\uff0c\u4f46\u662f\u4eca\u5929\u53d1\u73b0\u95ee\u9898\u5e76\u975e\u5982\u6b64\u3002Oracle\u5728\u8bc4\u4f30REBUILD\u7d22\u5f15\u65f6\u5e76\u4e0d\u662f\u6839\u636e\u7edf\u8ba1\u4fe1\u606f\uff0c\u800c\u662f\u6839\u636e\u6570\u636e\u5b57\u5178\u4e2d\u975e\u7d22\u5f15\u5b57\u6bb5\u7684\u957f\u5ea6\uff1a SQL> create table t_rebuild (id number, flag char(1)); Table created. SQL> insert into t_rebuild select rownum, &#8216;a&#8217; from dba_objects; 15695 rows created. SQL> commit; Commit complete. SQL> create index ind_t_rebuild_id on t_rebuild(id); Index created. SQL> explain plan &hellip; <a href=\"https:\/\/yangtingkun.net\/?p=354\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3],"tags":[258,259],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2yiY3-5I","_links":{"self":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/354"}],"collection":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=354"}],"version-history":[{"count":2,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/354\/revisions"}],"predecessor-version":[{"id":360,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/354\/revisions\/360"}],"wp:attachment":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=354"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=354"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=354"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}