创建与管理Oracle分区表和本地索引的实例解析

数据库 Oracle
本文我们主要介绍了创建与管理Oracle分区表和本地索引的相关知识,并用一些实例对这一过程加以说明与解析,希望能够对您有所帮助。

创建与管理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/,希望本次的介绍能够对您有所收获!

【编辑推荐】

  1. SQL Server日期时间格式转换字符串详解
  2. SQL Server数据库流水号的使用方法详解
  3. SQL Server 2005学习笔记之数据修改心得
  4. SQL Server 2000如何更改账户默认数据库?
  5. SQL Server 2005数据库排序的SQL实例解析
责任编辑:赵鹏 来源: CSDN博客
相关推荐

2009-06-24 10:26:41

Oracle约束分区表

2010-11-16 10:21:25

Oracle创建表

2021-01-20 08:07:52

oracle分区单表

2022-01-11 08:46:56

Oracle 在线重定义数据库

2021-09-07 17:54:04

OpenGauss分区表索引

2010-04-16 13:41:14

Oracle表分区

2010-04-19 14:01:22

Oracle查看分区表

2011-07-20 16:03:06

SQL Server数分区表

2023-10-11 13:42:21

2021-04-19 08:16:38

Hive数据类型大数据技术

2011-04-11 11:32:29

Oracle分区表磁盘IO冲突

2010-04-28 11:56:09

Oracle临时表

2023-01-30 09:13:17

Oracle分区表技术

2010-11-10 14:47:11

SQL Server创

2010-10-11 10:16:17

Mysql分区表

2010-11-22 15:06:46

MySQL分区表

2017-08-30 16:59:54

PostgreSQL分区表

2011-03-22 14:57:52

Oracle数据库普通表分区表

2010-04-12 16:50:47

Oracle索引聚簇表

2010-10-11 09:50:32

Mysql分区表
点赞
收藏

51CTO技术栈公众号