oracle大表建索引步骤

最新推荐文章于 2024-07-17 07:18:34 发布

bitko

最新推荐文章于 2024-07-17 07:18:34 发布

阅读量1.1w

点赞数 3

分类专栏： Oracle-Admin

本文链接：https://blog.csdn.net/gumengkai/article/details/52596656

版权

Oracle-Admin 专栏收录该内容

31 篇文章 0 订阅

订阅专栏

一. 创建测试用表 big_table，并进行表分析

创建测试用户及表空间：

SQL> create tablespace tbs_a datafile '/u01/app/oracle/oradata/orcl/tbs_a01.dbf' size 100m autoextend on next 10m maxsize unlimited;

Tablespace created.
SQL> create user test identified by oracle default tablespace tbs_a;

User created.

SQL> grant resource,connect,dba to test;

Grant succeeded.

tom big_table脚本(去掉主键语句)

create table big_table
as
select rownum id, a.*
  from all_objects a
 where 1=0
/
alter table big_table nologging;

declare
    l_cnt number;
    l_rows number := &1;
begin
    insert /*+ append */
    into big_table
    select rownum, a.*
      from all_objects a
	 where rownum <= &1;

    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;
/
exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);

执行：

SQL> @big_table;

Table created.


Table altered.

Enter value for 1: 20000000
old   3:     l_rows number := &1;
new   3:     l_rows number := 20000000;
Enter value for 1: 20000000
old   9:         where rownum <= &1;
new   9:         where rownum <= 20000000;

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL> select count(1) from big_table;

  COUNT(1)
----------
  20000000
SQL> select segment_name,sum(bytes)/1024/1024 from dba_segments where segment_name='BIG_TABLE' group by segment_name;   

SEGMENT_NAME
--------------------------------------------------------------------------------
SUM(BYTES)/1024/1024
--------------------
BIG_TABLE
                2176

二. 评估创建索引需要的数据空间，查看表空间和临时表空间的大小，不够则增加。

通过执行计划可以预估创建索引需要的空间：

SQL> explain plan for create index idx_big_table_1 on big_table(object_id);

Explained.
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2090220367

--------------------------------------------------------------------------------
----------

| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)|
Time     |

--------------------------------------------------------------------------------
----------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |                 |    20M|    95M| 85550   (1)|
00:17:07 |

|   1 |  INDEX BUILD NON UNIQUE| IDX_BIG_TABLE_1 |       |       |            |
         |

|   2 |   SORT CREATE INDEX    |                 |    20M|    95M|            |
         |

|   3 |    TABLE ACCESS FULL   | BIG_TABLE       |    20M|    95M| 75404   (1)|
00:15:05 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
----------


Note
-----
   - estimated index size: 486M bytes

14 rows selected.

数据空间可用大小：

SQL> select tablespace_name,bytes/1024/1024 from dba_free_space where tablespace_name='TBS_A';

TABLESPACE_NAME                BYTES/1024/1024
------------------------------ ---------------
TBS_A                                      113
SQL> select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name='TBS_A';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                AUT
------------------------------ ---
/u01/app/oracle/oradata/orcl/tbs_a01.dbf
TBS_A                          YES

可以看出数据文件是可扩展的，可以暂时不用考虑数据空间不够的问题。

查看临时表空间可用空间：

when you create a new segment (i.e. building an index), Oracle uses temporary extents
to initially build it and then at the end of the process -- converts (via a simple dictionary update)
the extents
into permanent ones.

SQL> select file_name,tablespace_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_temp_files where tablespace_name='TEMP';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                BYTES/1024/1024 AUT MAXBYTES/1024/1024
------------------------------ --------------- --- ------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
TEMP                                        20 NO                   0


SQL> select tablespace_name,free_space/1024/1024 from dba_temp_free_space where tablespace_name='TEMP';

TABLESPACE_NAME                FREE_SPACE/1024/1024
------------------------------ --------------------
TEMP                                             19

可以看出临时表空间大小为20M,可用空间为19m且不可扩展

增加临时表空间大小：

SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 600m;

Database altered.

三. 由于创建索引时需要对表进行全表扫描,可以适当考虑调大db_file_multiblock_read_count的值

db_file_multiblock_read_count影响Oracle在读取数据时一次读取的最大block数量，在进行一些数据量比较大的操作时，可以适当

调整当前session的db_file_multiblock_read_count值，会在IO上节省节省一些时间。

SQL> show parameter db_file                        

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     121
SQL> alter session set db_file_multiblock_read_count=256;

Session altered.

SQL> show parameter db_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     256

四. 调整排序区的大小(sort_area_size)，建立索引时要对大量数据进行排序操作

在oracle11g,如果workarea_size_policy的值为AUTO,sort_area_size将被忽略，pga_aggregate_target将被启用,pga_aggregate_target决定了整个

的pga大小，而且一个session并不能使用全部的pga大小，它受到一个隐藏参数的限制，大致能使用pga_agregate_target的5%，因此可以

考虑将workarea_size_policy的值为manual，然后设置较大的sort_area_size以满足需求。

SQL> alter system set workarea_size_policy='MANUAL';

System altered.

SQL> alter session set sort_area_size=102400;

Session altered.

SQL> show parameter sort_area_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     102400

五. 为了让尽可能的源表进入buffer cache,使用cache和full hint对源表做fts,以使它尽可能的出现在LRU的MRU一端

SQL> select /*+ cache(t) full(t) */ count(*) from big_table t;

  COUNT(*)
----------
  20000000

六. 创建索引，使用nologging选项以减少在创建索引过程中产生大量的redo,另外使用online的方式创建索引可以避免在

创建索引的过程中在表上加锁，导致DML操作不可用，使用online则只会在表上加一个共享锁，仅不允许变更表的定义。

SQL> create index ind_big_table_id on big_table(id)
  2            tablespace tbs_a
  3            pctfree 5 initrans 4 maxtrans 255 nologging
  4            storage(initial 104857600)
  5            parallel 4 online;

Index created.

Elapsed: 00:02:01.73

七. 将参数调整回原样

SQL> alter system set workarea_size_policy='AUTO';

System altered.

Elapsed: 00:00:00.02
SQL> alter session set db_file_multiblock_read_count = 121;

Session altered.

Elapsed: 00:00:00.00