{"id":1017,"date":"2006-07-13T12:00:00","date_gmt":"2006-07-13T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1017"},"modified":"2006-07-13T12:00:00","modified_gmt":"2006-07-13T12:00:00","slug":"alter-table-move-and-table-stats","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/07\/13\/alter-table-move-and-table-stats\/","title":{"rendered":"alter table … move and table stats"},"content":{"rendered":"

Howard Rogers<\/a> left a comment on my last blog<\/a>, showing an example of using alter table … move on a 10gR2 database on Linux. In his example, unlike mine, the table rebuild did not<\/strong> nullify the table’s statistics. I admit I was surprised myself when I ran my example yesterday so I was concerned that I’d messed up my test somewhere, despite being careful.<\/p>\n

This morning I’ve tried the same test by creating a script containing all of the commands from the previous blog and ran it on two different databases, one 9.2.0.6 and one 10.2.0.1, both running on Solaris 8.<\/p>\n

Here’s the 9.2 output<\/strong><\/p>\n

<\/p>\n

SQL> create user test identified by test  2  default tablespace data  3  temporary tablespace temp;
<\/p>User created.
<\/p>SQL> grant connect, resource to test;
<\/p>Grant succeeded.
<\/p>SQL> alter user test quota unlimited on data;
<\/p>User altered.
<\/p>SQL> connect test\/testConnected.SQL> create table test (long_string varchar2(2000));
<\/p>Table created.
<\/p>SQL> begin  2  \t     for ctr in 1 .. 10000 loop  3  \t\t     insert into test values (to_char(ctr));  4  \t     end loop;  5  end;  6  \/
<\/p>PL\/SQL procedure successfully completed.
<\/p>SQL> commit;
<\/p>Commit complete.
<\/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                            
<\/p>SQL> update test set long_string = lpad(long_string, 500);
<\/p>10000 rows updated.
<\/p>SQL> commit;
<\/p>Commit complete.
<\/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       9983                            
<\/p>SQL> alter table test move initrans 6;
<\/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                            
<\/p><\/pre>\n
Here’s the 10.2 output<\/strong><\/p>\n
<\/p>SYS @ CMDBD > create user test identified by test  2  default tablespace tools  3  temporary tablespace temp;
<\/p>User created.
<\/p>SYS @ CMDBD > grant connect, resource to test;
<\/p>Grant succeeded.
<\/p>SYS @ CMDBD > alter user test quota unlimited on tools;
<\/p>User altered.
<\/p>SYS @ CMDBD > connect test\/testConnected.TEST @ CMDBD > create table test (long_string varchar2(2000));
<\/p>Table created.
<\/p>TEST @ CMDBD > begin  2          for ctr in 1 .. 10000 loop  3                  insert into test values (to_char(ctr));  4          end loop;  5  end;  6  \/
<\/p>PL\/SQL procedure successfully completed.
<\/p>TEST @ CMDBD > commit;
<\/p>Commit complete.
<\/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
<\/p>TEST @ CMDBD > update test set long_string = lpad(long_string, 500);
<\/p>10000 rows updated.
<\/p>TEST @ CMDBD > commit;
<\/p>Commit complete.
<\/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       9999
<\/p>TEST @ CMDBD > alter table test move initrans 6;
<\/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":"
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 so I was concerned that… Continue reading alter table … move and table stats<\/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-1017","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1016,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/12\/table-reorgs-and-statistics\/","url_meta":{"origin":1017,"position":0},"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":1062,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/21\/resumable-storage-allocation\/","url_meta":{"origin":1017,"position":1},"title":"Resumable Storage Allocation","date":"August 21, 2006","format":false,"excerpt":"At the moment I'm writing a short 9i\/10g New Features seminar for the developers at work. The idea is to run through lots of new features very quickly to see if anything lights their candle and they can then go off and investigate further afterwards.It occurred to me while I\u2026","rel":"","context":"With 9 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":992,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/13\/production-call-out\/","url_meta":{"origin":1017,"position":2},"title":"Production Call-out","date":"June 13, 2006","format":false,"excerpt":"On Saturday morning, we ran into a problem on one of our 9.2.0.7 Production databases during an Extract, Transform and Load (ETL) batch process.I was called at 5:15am to look at a job that normally runs in a few minutes but had failed after 30 or 40 minutes when trying\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1481,"url":"http:\/\/orcldoug.com\/blog\/2009\/04\/09\/diagnosing-locking-problems-using-ash-part-4\/","url_meta":{"origin":1017,"position":3},"title":"Diagnosing Locking Problems using ASH \u2013 Part 4","date":"April 9, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.No sooner had I finished part 3 with some conclusions than I thought of another example I should have included and then someone else made a comment in an email which suggested another. (Thanks, JB!) \"It's probably worth pointing out that\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1019,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/14\/alter-table-move-and-table-stats-part-ii\/","url_meta":{"origin":1017,"position":4},"title":"alter table … move and table stats (part II)","date":"July 14, 2006","format":false,"excerpt":"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\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1562,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/17\/statistics-on-partitioned-tables-part-1\/","url_meta":{"origin":1017,"position":5},"title":"Statistics on Partitioned Tables – Part 1","date":"February 17, 2010","format":false,"excerpt":"If you've ever worked on large databases that use partitioned and subpartitioned tables, you'll be aware that there are significant challenges in maintaining up-to-date\/appropriate statistics. We've encountered a few problems at work recently and I decided it would be an idea to put together a series of posts covering the\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1017","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=1017"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1017\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1017"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1017"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1017"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}