- 浏览: 200201 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
zoutuo1986:
翻过的帖子,这篇讲配置最细
solr -
18612536750:
Struts2 标签,取得Map的某一个key对应value值 -
zhangzhihua5:
solr -
xiaguangme:
“capacity才是真正的Entry数组的大小,即真实的En ...
源码阅读之Map和Set -
qiyang199132:
我擦民工 都没人来。。 我来捧场了
JSP 防止重复提交 防止重复刷新 防止后退问题以及处理方式
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 <to_date('01/01/2008','dd/mm/yyyy') and object_id is not null;
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 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 <to_date('01/01/2008','dd/mm/yyyy') and object_id is not null;
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 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-
从产品上说,分区技术是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 <to_date('01/01/2008','dd/mm/yyyy') and object_id is not null;
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 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 <to_date('01/01/2008','dd/mm/yyyy') and object_id is not null;
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 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-
发表评论
-
oracle基础学习
2012-11-07 15:13 1517转自(http://www.cnblogs.com ... -
oracle start with connect by 用法
2011-09-30 09:49 1361oracle 提供了start with connect by ... -
Hibernate操作Oarcle中Clob、Blob字段小结
2011-08-24 11:19 1162最近常碰到有人问如何运用Hibernate操作Oracle中的 ... -
数据库优化基本策略(转载)
2011-07-15 08:58 8761.选择正确类型的sql。 有时候你想要动态生成sql,却发现 ... -
oracle 查询数据null值排序
2011-07-11 18:16 1226sqlserver 认为 null 最小。 升序排列:nul ... -
不错的sql
2011-07-04 09:05 9801,说明:复制表(源表名a,新表名b) 法一:select ... -
oracle CLOB和BLOB
2011-06-25 21:18 1407一、区别和定义 LONG: 可变长的字符串数据, ... -
Oracle中TO_DATE格式
2011-05-19 17:34 852TO_DATE格式(以时间:2007-11-02 13:4 ... -
ORACLE错误一览表,方便大家查询!
2011-05-03 17:50 1503ORACLE错误一览表,方便 ... -
oracle 锁表
2011-04-26 16:37 1141查看锁表进程SQL语句1: select sess.sid, ... -
PL/SQL Developer 常用技巧
2011-03-18 14:10 8491、PL/SQL Developer记住登 ... -
java调用oracle存储过程
2010-12-17 11:14 1173在大型数据库系统中,有两个很重要作用的功能,那就是存储过程和触 ...
相关推荐
Oracle 创建索引的基本规则,好好研究一下有好处.
oracle创建索引很好的参考资料,好的索引能够非常大的提高数据库的查询速度
1.索引的创建与使用 2.创建索引的原则 3.索引的分类 4.创建索引的多种方法 5.管理索引 6.索引优化 7.查看、修改索引属性 8.修改索引名 9.删除索引
Oracle创建索引要做到三个适当.doc
oracle创建表,索引,表空间,触发器,schema用户,序列的Sql文
Oracle索引的创建、简介、技巧,希望可以给学习Oracle的人带来帮助!
oracle在线创建索引和重组索引。
创建学员信息表--创建唯一索引--修改学员信息表添加主键和检查约束
ORACLE索引详解及SQL优化,详细描述了几种常用索引原理以及创建方法,解读索引生效条件,以及在开发中常用的提高数据库效率、降低数据库资源消耗的方法。
[Oracle]在亿级记录表中创建索引 [Oracle]如何在亿级记录表中创建索引 [Oracle]如何在亿级记录表中创建索引
oracle海量数据中提升创建索引的速度
oracle、sqlserver数据库批量删建索引,方便好用,提高数据库查询效率,提升系统运行效率,特别是数据量比较大的情况下
简单示例实现如何为Oracle中sdo_geometry字段创建空间索引
湖南电信Oracle培训之索引创建.ppt
ORacle 全文索引
Oracle创建表空间、创建用户并指定表空间、给用户授予权限
删除索引 drop index CUS_LOWER_ENERGY_JLDNY; 新建一个索引 create index CUS_LOWER_ENERGY_JLDNY on CUS_LOWER_ENERGY (JLDBH, DLNY,HWBZ) tablespace TS_DSM_INDEX pctfree 10 initrans 2 maxtrans 255 ...
湖南电信Oracle培训\SQL优化\索引创建.pdf
创建索引:create index IX_PM_USERGROUP on T_PM_USER (fgroupid); 说明:此方式耗时间,无法在24*7环境中实现,不建议使用。 2 、直接重建: 举例: alter index indexname rebuild; 或alter index ...
Oracle在线建立超大表的索引,为在线生产系统某核心大表建立索引