{"id":594,"date":"2015-07-16T16:34:37","date_gmt":"2015-07-16T08:34:37","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=594"},"modified":"2015-09-10T21:53:02","modified_gmt":"2015-09-10T13:53:02","slug":"dbms_stats-import_table_stats%e4%b8%8d%e5%8f%af%e4%bb%a5%e6%8a%8a%e7%bb%9f%e8%ae%a1%e4%bf%a1%e6%81%af%e5%af%bc%e7%bb%99%e5%88%ab%e7%9a%84%e8%a1%a8","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=594","title":{"rendered":"dbms_stats.import_table_stats\u4e0d\u53ef\u4ee5\u628a\u7edf\u8ba1\u4fe1\u606f\u5bfc\u7ed9\u522b\u7684\u8868"},"content":{"rendered":"

\u4eca\u5929\u5728itpub\u770b\u5230\u4e00\u4e2a\u95ee\u9898 \u8bf4\u662f\u4f7f\u7528dbms_stats.import_table_stats\u5e76\u4e0d\u4f1a\u5bfc\u5165\u7edf\u8ba1\u4fe1\u606f \u7136\u540e\u505a\u4e86\u4e00\u4e2a\u5b9e\u9a8c\u5982\u4e0b<\/p>\n

SQL> create table test as select * from dba_objects;<\/p>\n

Table created.<\/p>\n

SQL> create index idx_test on test(object_id);<\/p>\n

Index created.<\/p>\n

SQL> begin
\ndbms_stats.gather_table_stats(ownname\u00a0 \u00a0\u00a0 \u00a0\u00a0 \u00a0 => ‘SCOTT’,
\ntabname\u00a0 \u00a0\u00a0 \u00a0\u00a0 \u00a0 => ‘TEST’,
\nestimate_percent => 100,
\ndegree\u00a0 \u00a0\u00a0 \u00a0\u00a0 \u00a0\u00a0\u00a0=> 2,
\ncascade\u00a0 \u00a0\u00a0 \u00a0\u00a0 \u00a0 => true);
\nend;\u00a0\u00a02\u00a0 \u00a0 3\u00a0 \u00a0 4\u00a0 \u00a0 5\u00a0 \u00a0 6\u00a0 \u00a0 7
\n8\u00a0\u00a0\/<\/p>\n

PL\/SQL procedure successfully completed.<\/p>\n

SQL> create table test_temp as select * from test;<\/p>\n

Table created.<\/p>\n

SQL> create index idx_test_temp on test_temp(object_id);<\/p>\n

Index created.<\/p>\n

SQL> select table_name,
\nnum_rows,
\nblocks,
\nempty_blocks,
\navg_space,
\navg_row_len,
\nsample_size
\nfrom user_tables
\nwhere table_name in (‘TEST’, ‘TEST_TEMP’);\u00a0\u00a02\u00a0 \u00a0 3\u00a0 \u00a0 4\u00a0 \u00a0 5\u00a0 \u00a0 6\u00a0 \u00a0 7\u00a0 \u00a0 8\u00a0 \u00a0 9<\/p>\n

TABLE_NAME\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\u00a0NUM_ROWS\u00a0 \u00a0\u00a0\u00a0BLOCKS EMPTY_BLOCKS\u00a0\u00a0AVG_SPACE
\n—————————— ———- ———- ———— ———-
\nAVG_ROW_LEN SAMPLE_SIZE
\n———– ———–
\nTEST\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 \u00a0\u00a0\u00a075253\u00a0 \u00a0 \u00a0 \u00a0 1098\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\u00a0 \u00a0 \u00a0 \u00a0\u00a0 \u00a0 0
\n97\u00a0 \u00a0 \u00a0 \u00a0\u00a0 \u00a075253<\/p>\n

TEST_TEMP<\/p>\n

SQL> begin
\n— dbms_stats.drop_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
\ndbms_stats.create_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
\ndbms_stats.export_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’,cascade => true);
\ndbms_stats.delete_table_stats(ownname=>’scott’,tabname=>’test_temp’);
\ndbms_stats.import_table_stats(ownname=>’scott’,tabname=>’test_temp’,stattab=>’stat_test_temp’);
\nend;\u00a0\u00a02\u00a0 \u00a0 3\u00a0 \u00a0 4\u00a0 \u00a0 5\u00a0 \u00a0 6\u00a0 \u00a0 7
\n8\u00a0\u00a0\/<\/p>\n

PL\/SQL procedure successfully completed.<\/p>\n

SQL> select table_name,
\nnum_rows,
\nblocks,
\nempty_blocks,
\navg_space,
\navg_row_len,
\nsample_size
\nfrom user_tables
\nwhere table_name in (‘TEST’, ‘TEST_TEMP’);\u00a0\u00a02\u00a0 \u00a0 3\u00a0 \u00a0 4\u00a0 \u00a0 5\u00a0 \u00a0 6\u00a0 \u00a0 7\u00a0 \u00a0 8\u00a0 \u00a0 9<\/p>\n

TABLE_NAME\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\u00a0NUM_ROWS\u00a0 \u00a0\u00a0\u00a0BLOCKS EMPTY_BLOCKS\u00a0\u00a0AVG_SPACE
\n—————————— ———- ———- ———— ———-
\nAVG_ROW_LEN SAMPLE_SIZE
\n———– ———–
\nTEST\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 \u00a0\u00a0\u00a075253\u00a0 \u00a0 \u00a0 \u00a0 1098\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\u00a0 \u00a0 \u00a0 \u00a0\u00a0 \u00a0 0
\n97\u00a0 \u00a0 \u00a0 \u00a0\u00a0 \u00a075253<\/p>\n

TEST_TEMP<\/p>\n

\u4ee5\u4e0a\u662f\u4f60\u7684\u5b9e\u9a8c<\/p>\n

\u518d\u5f80\u4e0b\u770b<\/p>\n

SQL> begin
\ndbms_stats.drop_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
\ndbms_stats.create_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
\ndbms_stats.export_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’,cascade => true);
\ndbms_stats.delete_table_stats(ownname=>’scott’,tabname=>’test’);
\n—\u00a0\u00a0dbms_stats.import_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’);
\nend;\u00a0\u00a02\u00a0 \u00a0 3\u00a0 \u00a0 4\u00a0 \u00a0 5\u00a0 \u00a0 6\u00a0 \u00a0 7
\n8\u00a0\u00a0\/<\/p>\n

PL\/SQL procedure successfully completed.<\/p>\n

SQL> select table_name,
\nnum_rows,
\nblocks,
\nempty_blocks,
\navg_space,
\navg_row_len,
\nsample_size
\nfrom user_tables
\nwhere table_name in (‘TEST’, ‘TEST_TEMP’);\u00a0\u00a02\u00a0 \u00a0 3\u00a0 \u00a0 4\u00a0 \u00a0 5\u00a0 \u00a0 6\u00a0 \u00a0 7\u00a0 \u00a0 8\u00a0 \u00a0 9<\/p>\n

TABLE_NAME\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\u00a0NUM_ROWS\u00a0 \u00a0\u00a0\u00a0BLOCKS EMPTY_BLOCKS\u00a0\u00a0AVG_SPACE
\n—————————— ———- ———- ———— ———-
\nAVG_ROW_LEN SAMPLE_SIZE
\n———– ———–
\nTEST<\/p>\n

TEST_TEMP<\/p>\n

SQL> begin
\n— dbms_stats.drop_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
\n—\u00a0\u00a0dbms_stats.create_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
\n–dbms_stats.export_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’,cascade => true);
\n—\u00a0\u00a0dbms_stats.delete_table_stats(ownname=>’scott’,tabname=>’test’);
\ndbms_stats.import_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’);
\nend;\u00a0\u00a02\u00a0 \u00a0 3\u00a0 \u00a0 4\u00a0 \u00a0 5\u00a0 \u00a0 6\u00a0 \u00a0 7
\n8\u00a0\u00a0\/<\/p>\n

PL\/SQL procedure successfully completed.<\/p>\n

SQL> select table_name,
\nnum_rows,
\nblocks,
\nempty_blocks,
\navg_space,
\navg_row_len,
\nsample_size
\nfrom user_tables
\nwhere table_name in (‘TEST’, ‘TEST_TEMP’);\u00a0\u00a02\u00a0 \u00a0 3\u00a0 \u00a0 4\u00a0 \u00a0 5\u00a0 \u00a0 6\u00a0 \u00a0 7\u00a0 \u00a0 8\u00a0 \u00a0 9<\/p>\n

TABLE_NAME\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\u00a0NUM_ROWS\u00a0 \u00a0\u00a0\u00a0BLOCKS EMPTY_BLOCKS\u00a0\u00a0AVG_SPACE
\n—————————— ———- ———- ———— ———-
\nAVG_ROW_LEN SAMPLE_SIZE
\n———– ———–
\nTEST\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 \u00a0\u00a0\u00a075253\u00a0 \u00a0 \u00a0 \u00a0 1098\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 0\u00a0 \u00a0 \u00a0 \u00a0\u00a0 \u00a0 0
\n97\u00a0 \u00a0 \u00a0 \u00a0\u00a0 \u00a075253<\/p>\n

TEST_TEMP<\/p>\n

\u7531\u6b64\u53ef\u89c1\u8fd9\u4e2a\u529f\u80fd\u4e0d\u662f\u7528\u4e8e\u628a\u4e00\u5f20\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\u7ed9\u522b\u7684\u8868 \u800c\u662f\u7528\u4e8e\u53d1\u751f\u4e86\u8fdb\u884c\u4e0d\u540c\u7edf\u8ba1\u4fe1\u606f\u7684\u6027\u80fd\u6d4b\u8bd5
\n\u6211\u4eec\u518d\u770b\u4e00\u4e2a\u5b9e\u9a8c<\/p>\n

\u5148\u5907\u4efd<\/p>\n

SQL> begin
\ndbms_stats.drop_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
\ndbms_stats.create_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
\n–dbms_stats.export_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’,cascade => true);
\n—\u00a0\u00a0dbms_stats.delete_table_stats(ownname=>’scott’,tabname=>’test’);
\n–dbms_stats.import_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’);
\nend;\u00a0\u00a02\u00a0 \u00a0 3\u00a0 \u00a0 4\u00a0 \u00a0 5\u00a0 \u00a0 6\u00a0 \u00a0 7
\n8\u00a0\u00a0\/<\/p>\n

PL\/SQL procedure successfully completed.<\/p>\n

SQL> drop table test purge;<\/p>\n

Table dropped.<\/p>\n

SQL> create table test as select * from dba_objects;<\/p>\n

Table created.<\/p>\n

SQL> create index idx_test on test(object_id);<\/p>\n

Index created.<\/p>\n

SQL> select table_name,
\nnum_rows,
\nblocks,
\nempty_blocks,
\navg_space,
\navg_row_len,
\nsample_size
\nfrom user_tables
\nwhere table_name in (‘TEST’, ‘TEST_TEMP’);\u00a0\u00a02\u00a0 \u00a0 3\u00a0 \u00a0 4\u00a0 \u00a0 5\u00a0 \u00a0 6\u00a0 \u00a0 7\u00a0 \u00a0 8\u00a0 \u00a0 9<\/p>\n

TABLE_NAME\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\u00a0NUM_ROWS\u00a0 \u00a0\u00a0\u00a0BLOCKS EMPTY_BLOCKS\u00a0\u00a0AVG_SPACE
\n—————————— ———- ———- ———— ———-
\nAVG_ROW_LEN SAMPLE_SIZE
\n———– ———–
\nTEST<\/p>\n

TEST_TEMP<\/p>\n

SQL> begin
\n–dbms_stats.drop_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
\n— dbms_stats.create_stat_table(ownname=>’scott’,stattab=>’stat_test_temp’);
\n–dbms_stats.export_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’,cascade => true);
\n—\u00a0\u00a0dbms_stats.delete_table_stats(ownname=>’scott’,tabname=>’test’);
\ndbms_stats.import_table_stats(ownname=>’scott’,tabname=>’test’,stattab=>’stat_test_temp’);
\nend;\u00a0\u00a02\u00a0 \u00a0 3\u00a0 \u00a0 4\u00a0 \u00a0 5\u00a0 \u00a0 6\u00a0 \u00a0 7
\n8\u00a0\u00a0\/<\/p>\n

PL\/SQL procedure successfully completed.<\/p>\n

SQL> select table_name,
\nnum_rows,
\nblocks,
\nempty_blocks,
\navg_space,
\navg_row_len,
\nsample_size
\nfrom user_tables
\nwhere table_name in (‘TEST’, ‘TEST_TEMP’);\u00a0\u00a02\u00a0 \u00a0 3\u00a0 \u00a0 4\u00a0 \u00a0 5\u00a0 \u00a0 6\u00a0 \u00a0 7\u00a0 \u00a0 8\u00a0 \u00a0 9<\/p>\n

TABLE_NAME\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\u00a0NUM_ROWS\u00a0 \u00a0\u00a0\u00a0BLOCKS EMPTY_BLOCKS\u00a0\u00a0AVG_SPACE
\n—————————— ———- ———- ———— ———-
\nAVG_ROW_LEN SAMPLE_SIZE
\n———– ———–
\nTEST<\/p>\n

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

\u4eca\u5929\u5728itpub\u770b\u5230\u4e00\u4e2a\u95ee\u9898 \u8bf4\u662f\u4f7f\u7528dbms_stats.import_table_stats\u5e76\u4e0d\u4f1a\u5bfc\u5165\u7edf\u8ba1 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[34,2],"class_list":["post-594","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle","tag-oracle_basics"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/594","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=594"}],"version-history":[{"count":1,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/594\/revisions"}],"predecessor-version":[{"id":897,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/594\/revisions\/897"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=594"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=594"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=594"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}