oracle的分区表和local索引创建与维护...

Oracle的分区表和Local索引创建与维护 作者：eygle |English Version 【转载时请以超链接形式标明文章出处和作者信息及本声明】 链接：http://www.eygle.com/archives/2008/04/partition_local_index.html 站内相关文章|Related Articles Oracle Index Merge 与 and_equal 的变迁 工作日志：10 亿记录表的几个索引创建时间 工作记录: 2亿记录的导入及索引创建 CBO的魔术 - 一个错误的索引选择会带来的后果 大表之困惑 - 数据建模的前期规划十分重要 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 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 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-