注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

无线时代辐射无穷

抓紧生宝宝,小心辐射

 
 
 

日志

 
 

oracle10G分区的创建与维护Oracle分区表和本地索引  

2010-01-20 10:40:59|  分类: oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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


 

  评论这张
 
阅读(550)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017