www.eygle.com

« 如何对时间进行简单加减运算 | Blog首页 | peer.exe 进程是什么? »

Oracle的分区表和Local索引创建与维护

作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2008/04/partition_local_index.html

---

Oracle的分区技术在某些条件下可以极大的提高查询的性能，所以被广泛采用。
从产品上说，分区技术是Oracle企业版中独立收费的一个组件。

以下是对于分区及本地索引的一个示例。

首先根据字典表创建一个测试分区表：

SQL> connect eygle/eygle
Connected.
SQL> CREATE TABLE dbobjs
  2  (OBJECT_ID        NUMBER NOT NULL,
  3  OBJECT_NAME      varchar2(128),
  4  CREATED          DATE  NOT NULL
  5  )
  6  PARTITION BY RANGE (CREATED)
  7  (PARTITION dbobjs_06 VALUES LESS THAN (TO_DATE('01/01/2007', 'DD/MM/YYYY')),
  8  PARTITION dbobjs_07 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')));

Table created.

SQL> COL segment_name for a20
SQL> COL PARTITION_NAME for a20
SQL> SELECT segment_name, partition_name, tablespace_name
  2    FROM dba_segments
  3  WHERE segment_name = 'DBOBJS';

SEGMENT_NAME        PARTITION_NAME      TABLESPACE_NAME
-------------------- -------------------- ------------------------------
DBOBJS              DBOBJS_06            EYGLE
DBOBJS              DBOBJS_07            EYGLE

创建一个Local索引，注意这里可以将不同分区的索引指定创建到不同的表空间：

SQL> CREATE INDEX dbobjs_idx ON dbobjs (created) LOCAL
  2  (PARTITION dbobjs_06 TABLESPACE users,
  3    PARTITION dbobjs_07 TABLESPACE users
  4    );

Index created.

这个子句可以进一步调整为类似：

CREATE INDEX dbobjs_idx ON dbobjs (created) LOCAL
(PARTITION dbobjs_06 TABLESPACE users,
  PARTITION dbobjs_07 TABLESPACE users
  ) TABLESPACE users;

通过统一的tablespace子句为索引指定表空间。 

SQL> COL segment_name for a20
SQL> COL PARTITION_NAME for a20
SQL> SELECT segment_name, partition_name, tablespace_name
  2    FROM dba_segments
  3  WHERE segment_name = 'DBOBJS_IDX';

SEGMENT_NAME        PARTITION_NAME      TABLESPACE_NAME
-------------------- -------------------- ------------------------------
DBOBJS_IDX          DBOBJS_06            USERS
DBOBJS_IDX          DBOBJS_07            USERS


SQL> insert into dbobjs
  2 select object_id,object_name,created
  3 from dba_objects where created <to_date('01/01/2008','dd/mm/yyyy') and object_id is not null; 

6227 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from dbobjs partition (DBOBJS_06);

  COUNT(*)
----------
      6154

SQL> select count(*) from dbobjs partition (dbobjs_07);

  COUNT(*)
----------
        73

我们可以通过查询来对比一下分区表和非分区表的查询性能差异：

SQL> set autotrace on
SQL> select count(*) from dbobjs where created < to_date('01/01/2008','dd/mm/yyyy');

  COUNT(*)
----------
      6227


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
  1    0  SORT (AGGREGATE)
  2    1    PARTITION RANGE (ALL)
  3    2      INDEX (RANGE SCAN) OF 'DBOBJS_IDX' (NON-UNIQUE) (Cost=2 Card=8 Bytes=72)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        25  consistent gets
          0  physical reads
          0  redo size
        380  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*) from dbobjs where created < to_date('01/01/2007','dd/mm/yyyy');

  COUNT(*)
----------
      6154


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
  1    0  SORT (AGGREGATE)
  2    1    INDEX (RANGE SCAN) OF 'DBOBJS_IDX' (NON-UNIQUE) (Cost=2 Card=4 Bytes=36)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        24  consistent gets
          0  physical reads
          0  redo size
        380  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(distinct(object_name)) from dbobjs where created < to_date('01/01/2007','dd/mm/yyyy');

COUNT(DISTINCT(OBJECT_NAME))
----------------------------
                        4753


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=75)
  1    0  SORT (GROUP BY)
  2    1    TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DBOBJS' (Cost=1 Card=4 Bytes=300)
  3    2      INDEX (RANGE SCAN) OF 'DBOBJS_IDX' (NON-UNIQUE) (Cost=2 Card=1)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        101  consistent gets
          0  physical reads
          0  redo size
        400  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

对于非分区表的测试：

SQL> CREATE TABLE dbobjs2
  2  (object_id    NUMBER NOT NULL,
  3  object_name  VARCHAR2(128),
  4  created      DATE  NOT NULL
  5  );

Table created.

SQL> CREATE INDEX dbobjs_idx2 ON dbobjs2 (created);

Index created.

SQL> insert into dbobjs2
  2  select object_id,object_name,created
  3  from dba_objects where created <to_date('01/01/2008','dd/mm/yyyy') and object_id is not null;

6227 rows created.

SQL> commit;

Commit complete.

SQL> select count(distinct(object_name)) from dbobjs2 where created < to_date('01/01/2007','dd/mm/yyyy');

COUNT(DISTINCT(OBJECT_NAME))
----------------------------
                        4753


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE
  1    0  SORT (GROUP BY)
  2    1    TABLE ACCESS (BY INDEX ROWID) OF 'DBOBJS2'
  3    2      INDEX (RANGE SCAN) OF 'DBOBJS_IDX2' (NON-UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      2670  consistent gets
          0  physical reads
      1332  redo size
        400  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

当增加表分区时，LOCAL索引被自动维护：

SQL> ALTER TABLE dbobjs
  2  ADD PARTITION dbobjs_08 VALUES LESS THAN (TO_DATE('01/01/2009', 'DD/MM/YYYY'));

Table altered.

SQL> set autotrace off
SQL> COL segment_name for a20
SQL> COL PARTITION_NAME for a20
SQL> SELECT segment_name, partition_name, tablespace_name
  2    FROM dba_segments
  3  WHERE segment_name = 'DBOBJS_IDX';

SEGMENT_NAME        PARTITION_NAME      TABLESPACE_NAME
-------------------- -------------------- ------------------------------
DBOBJS_IDX          DBOBJS_06            USERS
DBOBJS_IDX          DBOBJS_07            USERS
DBOBJS_IDX          DBOBJS_08            EYGLE

SQL> SELECT segment_name, partition_name, tablespace_name
  2    FROM dba_segments
  3  WHERE segment_name = 'DBOBJS';

SEGMENT_NAME        PARTITION_NAME      TABLESPACE_NAME
-------------------- -------------------- ------------------------------
DBOBJS              DBOBJS_06            EYGLE
DBOBJS              DBOBJS_07            EYGLE
DBOBJS              DBOBJS_08            EYGLE

-The End-

By eygle on 2008-04-23 12:22 | Comments (0) | Posted to FAQ | Edit |Pageviews:

相关文章	随机文章
索引 索引 还是索引 基于主键和唯一约束的显示索引控制 索引与Null值对于Hints及执行计划的影响 使用Index提示 强制使用索引 基于自定义函数的Function-Based索引创建	Donald Knuth:串行并行、单线程及多线程 IBM ThinkPad-如何激活和关闭数字小键盘 Itpub技术丛书《Oracle数据库性能优化》已经进入印刷阶段 A Gift:Oracle DW/BI 2006 Calendar 今日华友世纪正式宣布终止与光线合并

留言 (0)

发表留言:

Name(*):
Email(*):

You Link: Remember Me? YesNo
验证码(*): (输入验证码后方可评论,谢谢支持)
Comments: