创建与管理Oracle分区表和本地索引的相关知识是本文我们主要要介绍的内容,我们知道,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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
创建一个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.
- 1.
- 2.
- 3.
- 4.
- 5.
这个子句可以进一步调整为类似:
CREATE INDEX dbobjs_idx ON dbobjs (created) LOCAL
(PARTITION dbobjs_06 TABLESPACE users,
PARTITION dbobjs_07 TABLESPACE users
) TABLESPACE users;
- 1.
- 2.
- 3.
- 4.
通过统一的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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
我们可以通过查询来对比一下分区表和非分区表的查询性能差异:
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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
对于非分区表的测试:
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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
当增加表分区时,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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
关于创建与管理Oracle分区表和本地索引的相关知识及实例就介绍到这里了,如果您想了解更多关于Oracle数据库的知识,可以看一下这里的文章:http://database.51cto.com/oracle/,希望本次的介绍能够对您有所收获!
【编辑推荐】