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

关注

转载最新推荐文章于 2026-09-01 11:05:48 发布 823 阅读 AI 写同款· GEO 优化 ›

oracle创建分区表以及索引一、分区表、索引的分类 1、分区表原理：对于10gR2而言，ORACLE对于分区表方式其实就是将表分段存储，一般普通表格是一个段存储，而分区表会分成多个段，所以查找数据过程都是先定位根据查询条件定位分区范围，即数据在那个分区或那几个内部，然后在分区内部去查找数据，一个分区一般保证四十多万条数据就比较正常了， 2、分区表的分类： • 　　 Range(范围)分区 range分区方式，也算是最常用的分区方式，其通过某字段或几个字段的组合的值，从小到大，按照指定的范围说明进行分区，我们在INSER 阅读详情

【原文： http://www.searchdatabase.com.cn/showcontent_12937.htm 】

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

收录于