创建测试用表BIG_TABLE

于 2013-04-22 09:47:54 发布
阅读量738 收藏
点赞数
分类专栏： SQL/PLSQL基础 SQLshell脚本 文章标签： SQLPLSQL基础 SQLshell脚本
本文介绍如何在Oracle环境下创建并优化一个包含所有对象的大表，针对Oracle10g及11g版本进行详细步骤演示，包括创建表、调整模式、填充数据、添加主键、收集统计信息和检查总行数等关键步骤。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

创建测试用表，DBA经常用到，通常都是基于dba_objects来创建的比较多。本文根据Tom大师的big_table进行了整理，供大家参考。

一、基于Oracle 10g下的big_table

[sql] view plain copy print ?
  1. --==============================================   
  2. -- Create a test table for Oracle 10g   
  3. -- File   : cr_big_tb_10g.sql   
  4. -- Author : Robinson   
  5. -- Blog   : http://blog.csdn.net/robinson_0612   
  6. --==============================================   
  7.   
  8. prompt  
  9. prompt     Create a big table from all_objects  
  10. prompt    ======================================  
  11. CREATE TABLE big_table  
  12. AS   
  13. SELECT ROWNUM id, a.*  
  14. FROM all_objects a  
  15. WHERE 1=0;  
  16.    
  17. prompt  
  18. prompt  Modify table to nologgming mode  
  19. prompt  ==========================  
  20. ALTER TABLE big_table NOLOGGING;  
  21.    
  22. prompt   
  23. prompt     Please input rows number to fill into big_table  
  24. prompt     ============================================  
  25. DECLARE  
  26.         l_cnt NUMBER;  
  27.         l_rows NUMBER := &1;  
  28. BEGIN  
  29.         INSERT /*+ append */  
  30.         INTO big_table  
  31.                 SELECT rownum, a.*  
  32.                 FROM all_objects a;  
  33.         l_cnt := SQL%ROWCOUNT;  
  34.         COMMIT;  
  35.         WHILE (l_cnt < l_rows)  
  36.         LOOP  
  37.                 INSERT /*+ APPEND */  
  38.                 INTO big_table  
  39.                         SELECT rownum + l_cnt  
  40.                              ,owner  
  41.                              ,object_name  
  42.                              ,subobject_name  
  43.                              ,object_id  
  44.                              ,data_object_id  
  45.                              ,object_type  
  46.                              ,created  
  47.                              ,last_ddl_time  
  48.                              ,TIMESTAMP  
  49.                              ,status  
  50.                              ,temporary  
  51.                              ,generated  
  52.                              ,secondary  
  53.                         FROM big_table  
  54.                         WHERE rownum <= l_rows - l_cnt;  
  55.                 l_cnt := l_cnt + SQL%ROWCOUNT;  
  56.                 COMMIT;  
  57.         END LOOP;  
  58. END;   
  59. /  
  60.    
  61. prompt   
  62. prompt      Add primary key for  big table   
  63. prompt     =====================================  
  64. ALTER TABLE big_table ADD CONSTRAINT   
  65. big_table_pk PRIMARY KEY (id);  
  66.    
  67. prompt   
  68. prompt      Gather statistics for big_table  
  69. prompt     =====================================  
  70. BEGIN  
  71.         dbms_stats.gather_table_stats(ownname => USER,  
  72.                                      tabname => 'BIG_TABLE',  
  73.                                      method_opt => 'for all indexed columns',  
  74.                                      cascade => TRUE);  
  75. END;   
  76. /  
  77.    
  78. prompt   
  79. prompt      check total rows  for big_table   
  80. prompt     ====================================  
  81. SELECT COUNT(*)  
  82. FROM big_table;  
--==============================================
-- Create a test table for Oracle 10g
-- File   : cr_big_tb_10g.sql
-- Author : Robinson
-- Blog   : http://blog.csdn.net/robinson_0612
--==============================================

prompt
prompt     Create a big table from all_objects
prompt    ======================================
CREATE TABLE big_table
AS 
SELECT ROWNUM id, a.*
FROM all_objects a
WHERE 1=0;
 
prompt
prompt  Modify table to nologgming mode
prompt  ==========================
ALTER TABLE big_table NOLOGGING;
 
prompt 
prompt     Please input rows number to fill into big_table
prompt     ============================================
DECLARE
        l_cnt NUMBER;
        l_rows NUMBER := &1;
BEGIN
        INSERT /*+ append */
        INTO big_table
                SELECT rownum, a.*
                FROM all_objects a;
        l_cnt := SQL%ROWCOUNT;
        COMMIT;
        WHILE (l_cnt < l_rows)
        LOOP
                INSERT /*+ APPEND */
                INTO big_table
                        SELECT rownum + l_cnt
                             ,owner
                             ,object_name
                             ,subobject_name
                             ,object_id
                             ,data_object_id
                             ,object_type
                             ,created
                             ,last_ddl_time
                             ,TIMESTAMP
                             ,status
                             ,temporary
                             ,generated
                             ,secondary
                        FROM big_table
                        WHERE rownum <= l_rows - l_cnt;
                l_cnt := l_cnt + SQL%ROWCOUNT;
                COMMIT;
        END LOOP;
END; 
/
 
prompt 
prompt      Add primary key for  big table 
prompt     =====================================
ALTER TABLE big_table ADD CONSTRAINT 
big_table_pk PRIMARY KEY (id);
 
prompt 
prompt      Gather statistics for big_table
prompt     =====================================
BEGIN
        dbms_stats.gather_table_stats(ownname => USER,
                                     tabname => 'BIG_TABLE',
                                     method_opt => 'for all indexed columns',
                                     cascade => TRUE);
END; 
/
 
prompt 
prompt      check total rows  for big_table 
prompt     ====================================
SELECT COUNT(*)
FROM big_table;

二、基于Oracle 11g下的big_table

[sql] view plain copy print ?
  1. --==============================================   
  2. -- Create a test table for Oracle 11g   
  3. -- File   : cr_big_tb_11g.sql   
  4. -- Author : Robinson   
  5. -- Blog   : http://blog.csdn.net/robinson_0612   
  6. --==============================================   
  7.   
  8. prompt  
  9. prompt     Create a big table from all_objects  
  10. prompt    ======================================  
  11. CREATE TABLE big_table  
  12. AS   
  13. SELECT ROWNUM id, a.*  
  14. FROM all_objects a  
  15. WHERE 1=0;  
  16.    
  17. prompt  
  18. prompt  Modify table to nologgming mode  
  19. prompt  ==========================  
  20. ALTER TABLE big_table NOLOGGING;  
  21.    
  22. prompt   
  23. prompt      Please input rows number to fill into big_table  
  24. prompt     ============================================  
  25. DECLARE  
  26.         l_cnt NUMBER;  
  27.         l_rows NUMBER := &1;  
  28. BEGIN  
  29.         INSERT /*+ append */  
  30.         INTO big_table  
  31.                 SELECT rownum, a.*  
  32.                 FROM all_objects a;  
  33.         l_cnt := SQL%ROWCOUNT;  
  34.         COMMIT;  
  35.         WHILE (l_cnt < l_rows)  
  36.         LOOP  
  37.                 INSERT /*+ APPEND */  
  38.                 INTO big_table  
  39.                         SELECT rownum + l_cnt  
  40.                              ,owner  
  41.                              ,object_name  
  42.                              ,subobject_name  
  43.                              ,object_id  
  44.                              ,data_object_id  
  45.                              ,object_type  
  46.                              ,created  
  47.                              ,last_ddl_time  
  48.                              ,TIMESTAMP  
  49.                              ,status  
  50.                              ,temporary  
  51.                              ,generated  
  52.                              ,secondary  
  53.                              ,namespace  
  54.                              ,edition_name  
  55.                         FROM big_table   
  56.                         WHERE rownum <= l_rows - l_cnt;  
  57.                 l_cnt := l_cnt + SQL%ROWCOUNT;  
  58.                 COMMIT;  
  59.         END LOOP;  
  60. END;   
  61. /  
  62.    
  63. prompt   
  64. prompt      Add primary key for  big table   
  65. prompt     =====================================  
  66. ALTER TABLE big_table ADD CONSTRAINT   
  67. big_table_pk PRIMARY KEY (id);  
  68.    
  69. prompt   
  70. prompt      Gather statistics for big_table  
  71. prompt     =====================================  
  72. BEGIN  
  73.         dbms_stats.gather_table_stats(ownname => USER,  
  74.                                      tabname => 'BIG_TABLE',  
  75.                                      method_opt => 'for all indexed columns',  
  76.                                      cascade => TRUE);  
  77. END;   
  78. /  
  79.    
  80. prompt   
  81. prompt      check total rows  for big_table   
  82. prompt     ====================================  
  83. SELECT COUNT(*)  
  84. FROM big_table;  
--==============================================
-- Create a test table for Oracle 11g
-- File   : cr_big_tb_11g.sql
-- Author : Robinson
-- Blog   : http://blog.csdn.net/robinson_0612
--==============================================

prompt
prompt     Create a big table from all_objects
prompt    ======================================
CREATE TABLE big_table
AS 
SELECT ROWNUM id, a.*
FROM all_objects a
WHERE 1=0;
 
prompt
prompt  Modify table to nologgming mode
prompt  ==========================
ALTER TABLE big_table NOLOGGING;
 
prompt 
prompt      Please input rows number to fill into big_table
prompt     ============================================
DECLARE
        l_cnt NUMBER;
        l_rows NUMBER := &1;
BEGIN
        INSERT /*+ append */
        INTO big_table
                SELECT rownum, a.*
                FROM all_objects a;
        l_cnt := SQL%ROWCOUNT;
        COMMIT;
        WHILE (l_cnt < l_rows)
        LOOP
                INSERT /*+ APPEND */
                INTO big_table
                        SELECT rownum + l_cnt
                             ,owner
                             ,object_name
                             ,subobject_name
                             ,object_id
                             ,data_object_id
                             ,object_type
                             ,created
                             ,last_ddl_time
                             ,TIMESTAMP
                             ,status
                             ,temporary
                             ,generated
                             ,secondary
                             ,namespace
                             ,edition_name
                        FROM big_table 
                        WHERE rownum <= l_rows - l_cnt;
                l_cnt := l_cnt + SQL%ROWCOUNT;
                COMMIT;
        END LOOP;
END; 
/
 
prompt 
prompt      Add primary key for  big table 
prompt     =====================================
ALTER TABLE big_table ADD CONSTRAINT 
big_table_pk PRIMARY KEY (id);
 
prompt 
prompt      Gather statistics for big_table
prompt     =====================================
BEGIN
        dbms_stats.gather_table_stats(ownname => USER,
                                     tabname => 'BIG_TABLE',
                                     method_opt => 'for all indexed columns',
                                     cascade => TRUE);
END; 
/
 
prompt 
prompt      check total rows  for big_table 
prompt     ====================================
SELECT COUNT(*)
FROM big_table;

三、说明
1、该校本根据Tom大师的原big_table整理而成。
2、Oracle 11g all_objects 比Oracle 10g 多出两列，因此使用了2个不同的版本。
3、big_table的id列为唯一值，并在之上创建了primary key。
4、对于该表测试redo等相关信息是应启用logging模式。        

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值