Oracle 测试常用表BIG_TABLE_oracle big table是(1)

2401_84561579

于 2024-05-16 06:30:50 发布

阅读量843

点赞数 18

文章标签：单元测试功能测试学习

本文链接：https://blog.csdn.net/2401_84561579/article/details/138938959

版权

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

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

–==============================================

– 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

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 =====================================