8.1.7.4<\/strong> <\/p>\n<\/p>\n
SQL> analyze table test compute statistics<\/p>Table analyzed.
<\/p>SQL> select table_name, num_rows, chain_cnt from user_tables;
<\/p>TABLE_NAME NUM_ROWS CHAIN_CNT ------------------------------ ---------- ---------- TEST 10000 9983
<\/p>SQL> alter table test move;
<\/p>Table altered.
<\/p>SQL> select table_name, num_rows, chain_cnt from user_tables;
<\/p>TABLE_NAME NUM_ROWS CHAIN_CNT ------------------------------ ---------- ---------- TEST
<\/p>SQL> analyze table test compute statistics;
<\/p>Table analyzed.
<\/p>SQL> select table_name, num_rows, chain_cnt from user_tables;
<\/p>TABLE_NAME NUM_ROWS CHAIN_CNT ------------------------------ ---------- ---------- TEST 10000 0 <\/pre>\n
9.2.0.7<\/strong><\/p>\n<\/p>\nSQL> analyze table test compute statistics;<\/p>Table analyzed.
<\/p>SQL> select table_name, num_rows, chain_cnt from user_tables;
<\/p>TABLE_NAME NUM_ROWS CHAIN_CNT ------------------------------ ---------- ---------- TEST 10000 9999
<\/p>SQL> alter table test move;
<\/p>Table altered.
<\/p>SQL> select table_name, num_rows, chain_cnt from user_tables;
<\/p>TABLE_NAME NUM_ROWS CHAIN_CNT ------------------------------ ---------- ---------- TEST
<\/p>SQL> analyze table test compute statistics;
<\/p>Table analyzed.
<\/p>SQL> select table_name, num_rows, chain_cnt from user_tables;
<\/p>TABLE_NAME NUM_ROWS CHAIN_CNT ------------------------------ ---------- ---------- TEST 10000 0 <\/pre>\n<\/p>\n
10.2.0.1<\/strong><\/p>\n<\/strong><\/p>\nTEST @ CMDBD > analyze table test compute statistics;<\/p>Table analyzed.
<\/p>TEST @ CMDBD > select table_name, num_rows, chain_cnt from user_tables;
<\/p>TABLE_NAME NUM_ROWS CHAIN_CNT------------------------------ ---------- ----------TEST 10000 9999
<\/p>TEST @ CMDBD > alter table test move;
<\/p>Table altered.
<\/p>TEST @ CMDBD > select table_name, num_rows, chain_cnt from user_tables;
<\/p>TABLE_NAME NUM_ROWS CHAIN_CNT------------------------------ ---------- ----------TEST 10000 9999
<\/p>TEST @ CMDBD > analyze table test compute statistics;
<\/p>Table analyzed.
<\/p>TEST @ CMDBD > select table_name, num_rows, chain_cnt from user_tables;
<\/p>TABLE_NAME NUM_ROWS CHAIN_CNT------------------------------ ---------- ----------TEST 10000 0<\/pre>\n","protected":false},"excerpt":{"rendered":"
Following up on another comment from Howard, I took the initrans change off the alter table … move so that it was the most basic variation and then ran it on 8i, 9i and 10g. I’ve trimmed lots of the output this time, but I have the log files if anyone wants a copy. It… Continue reading alter table … move and table stats (part II)<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1019","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1017,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/13\/alter-table-move-and-table-stats\/","url_meta":{"origin":1019,"position":0},"title":"alter table … move and table stats","date":"July 13, 2006","format":false,"excerpt":"Howard Rogers left a comment on my last blog, showing an example of using alter table ... move on a 10gR2 database on Linux. In his example, unlike mine, the table rebuild did not nullify the table's statistics. I admit I was surprised myself when I ran my example yesterday\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1016,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/12\/table-reorgs-and-statistics\/","url_meta":{"origin":1019,"position":1},"title":"Table reorgs and statistics","date":"July 12, 2006","format":false,"excerpt":"While working on the ITL deadlock problem (which looks like it's been fixed by the initrans increase and table rebuild), the developers highlighted another table as hitting this problem in the past. When I investigated, I found that initrans had already been set to 6 so this had obviously happened\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1000,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/18\/saving-optimiser-stats-10g\/","url_meta":{"origin":1019,"position":2},"title":"Saving Optimiser Stats – 10g","date":"June 18, 2006","format":false,"excerpt":"In my previous blog I showed how you can save your current optimiser stats into a seperate table whenever you refresh them, so that they can be restored should the new statistics lead to poor SQL execution plans. Oracle have added an improved version of this facility to 10g (which\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":999,"url":"http:\/\/orcldoug.com\/blog\/2012\/06\/18\/saving-optimiser-stats-9i\/","url_meta":{"origin":1019,"position":3},"title":"Saving Optimiser Stats – 9i","date":"June 18, 2012","format":false,"excerpt":"In a recent blog I described how a mis-timed optimiser statistics collection job led to a bad execution plan for one of the SQL statements in a regular batch jobIt's no coincidence that we were already in the process of implementing a change to our stats collection period to retain\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1570,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/28\/statistics-on-partitioned-tables-part-5\/","url_meta":{"origin":1019,"position":4},"title":"Statistics on Partitioned Tables – Part 5","date":"February 28, 2010","format":false,"excerpt":"Actually, before looking at any recent features, let me introduce one more aspect of the existing aggregation approach used by Oracle. The examples used to date have been based on INSERTing new rows into subpartitions and, although that's the approach used for some of our tables and will suit some\u2026","rel":"","context":"With 18 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1596,"url":"http:\/\/orcldoug.com\/blog\/2010\/04\/22\/statistics-on-partitioned-tables-part-6a-copy_table_stats-intro\/","url_meta":{"origin":1019,"position":5},"title":"Statistics on Partitioned Tables – Part 6a – COPY_TABLE_STATS – Intro","date":"April 22, 2010","format":false,"excerpt":"[Phew. At last. The first draft of this was dated more than two weeks ago .... One of the problems with blogging about copying stats was the balance between explaining it and pointing out some of the problems I've encountered. So I've broken up this post, with a little explanation\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1019","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1019"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1019\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1019"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1019"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1019"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}