`

oracle海量数量一

阅读更多
目录:
Oracle数据完整性和锁机制
索引及优化之表分析
表分析、约束及表间关系
Oracle体系结构1
Oracle体系结构2
海量数据库及分区1
海量数据库及分区2
海量数据库及分区3
海量数据库及分区4
高级SQL优化(一) 
高级SQL优化(二) 
高级SQL优化(三) 常用优化工具
PPT和源码下载:    http://sishuok.com/forum/posts/list/6365.html
配套视频课程
    Oracle性能优化 http://sishuok.com/product/601
    海量数据库和高级SQL优化 http://sishuok.com/product/602


管理分区
增加索引分区
本地索引无法明确的增加分区,其增加只能是基表增加分区,此时新增加的索引分区的名字是Oracle自命名的,但可以给其重新命名。
也可是使用ALTER INDEX index_name MODIFY DEFAULT ARRTIBUTES  TABLASEPACE tablespace_name修改本地索引分区默认的表空间后,再使用ADD PARTITION增加表的分区,则基表增加分区带来索引分区的增加,会自动将新增加的索引分区指向该表空间。
接合分区
  分区接合是针对散列分区或者*-散列子分区的,目的是减少分区数。当某个散列分区接合后,Oracle将其分区的数据分散到其它分区中。被接合的分区是由数据库选择的,接合完成后该分区会被删除,且如果没有使用UPDATE INDEX子句,本地索引和全局索引均将变成不可用,一般需要重建索引。
1.散列分区表的散列分区接合
  使用语法 ALTER TABLE  COALESCE PARTITION。

2.散列子分区表的散列子分区集合
  使用语法 ALTER TABLE MODIFY PARTITION  COALESCE SUBPARTITION。                        参见附件脚本1
 
删除表分区
  只针对范围和列表分区或者组合*-范围和组合*-列表分区,散列分区不能删除,替代的方式是接合分区。
分区或子分区删除后,其中的数据也被删除,同样,基于这些分区或者子分区的本地索引中相应的分区和子分区也会被删除;对于全局索引除非使用了UPDATE INDEXS子句,删除分区后其会变成不可用,一般需要重构。如果要防止数据被删除替代的方式是使用合并分区MERGE PARTITION。
删除分区的语句是ALTER TABLE DROP PARTITION,删除子分区的语句时ALTER TABLE DROP SUBPARTITION。
1.从包含数据和全局索引的表中删除分区
  此时指的是表中有数据,且包含一个或几个全局索引。
  (1).方法一(推荐的方法)
    先删除分区,再一个个的重构全部索引,此时可以解决范围分区的全局索引的问题(删除后全部变成不可用)。这么做的原因是不要考虑全局索引是什么分区的。一般的方法是编写一个工具子程序,通过动态SQL开重构索引:
  ALTER TABLE table_name DROP PARTITION partition_name;
ALTER INDEX index_name1_on_ table_name REBUILD;
  …
ALTER INDEX index_namen_on_ table_name REBUILD;
(2).方法二
    先删除该分区的数据(因为删除数据会重构全局分区),再删除分区,一般针对数据不是特别多的表:
  DELETE FROM table_name PARTITION(partition_name);
ALTER TABLE table_name  PARTITION partition_name;
(3).方法三
    使用UPDATE INDEXES子句,此时Oracle会自动重构该全局索引:
     ALTER TABLE table_name  PARTITION partition_name UPDATE INDEXES;

2.从包含数据和参照完整性(外键)的表中删除分区
  (1).方法一
    如果要删除的分区中的数据没有被参照引用,先禁用该参照完整性约束,再删除分区,最后再启用该参照完整性约束。
(2).方法二
如果要删除的分区中的数据被参照引用,先删除该分区中数据,再删除分区。
删除索引分区
无法显式的删除本地索引的分区,删除的唯一方式是本地索引分区基表的分区被删除时由Oracle自动的隐式删除。
如果全局索引分区是空的,则可以显式的删除它,使用的语句是ALTER INDEX index_name DROP PARTITION partition_name。但是,如果全局索引分区包含数据,删除则会引起更高级的分区(即下一个分区)变得不可用。如果非要这么做,则对更高级的分区需要重构,语法是:
ALTER INDEX index_name REUBILT PARTITION nextpartition_name
 
交换分区

   可以将一个分区(子分区)和非分区表进行数据交换,oracle交换的方法是其实是对逻辑存储段进行交换。同样,散列|范围|列表分区可以与复合*-散列|*-范围|*-列表分区间也可以进行数据交换。当应用中需要将非分区表的数据转换进入分区表的分区时非常高效实用。使用INCLUDEING INDEXES子句可以同步将本地索引也进行交换,使用WITH VALIDATATION子句还可以实现行数据的验证。
交换分区时如果不带UPDATE INDEXES子句,则全局索引或全局索引基于的分区将变为不可用。
1.三种单级分区与非分区表的交换
使用ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE nonpartition_name
交换前:
 
1.三种单级分区与非分区表的交换
交换后:

如再次交换,则二者的数据会再次互换,变成第一次交换前的状态

2. 单级散列分区表与复合*-散列分区的交换
此时要求单级散列分区表的分区键与复合*-散列分区表的子分区键相同,且两个交换的散列分区数也得相同,此外也不能指定单级散列分区表的某一个分区进行交换。
交换前:
 

2. 单级散列分区表与复合*-散列分区的交换
交换后:
 
再交换后,回到原始状态:
 
参见附件脚本3


3. 复合*-散列分区中的散列子分区交换
   使用ALTER TABLE ... EXCHANGE SUBPARTITION与非分区表进行交换,且只能跟非分区表进行交换。 参见附件脚本4
交换前:
 
交换后:
 

4.单级列表分区表与复合*-列表分区的交换
   此时要求List分区表的分区键和*-List表的子分区键相匹配,前者的List分区数与后者的List子分区相同。 参见附件脚本5
5. 复合*-列表分区中的列表子分区交换
同样也是使用ALTER TABLE ... EXCHANGE SUBPARTITION与非分区表进行交换,且只能跟非分区表进行交换。 参见附件脚本6
6.单级范围分区表与复合*-范围分区的交换
此时要求Range分区表的分区键和*-Range表的子分区键相匹配,前者的Range分区数与后者的Range子分区相同。 参见附件脚本7
7. 复合*-范围分区中的范围子分区交换
    同样也是使用ALTER TABLE ... EXCHANGE SUBPARTITION与非分区表进行交换,且只能跟非分区表进行交换。 参见附件脚本
列表分区值的增加
仅有列表分区或列表子分区存在该功能,如果要增加的值在其它分区或者当前分区的其它子分区中存在,则不能增加,因此对于存在default的List分区/子分区,本功能不可用。本操作完成后,本地索引和全局索引保留为可用。 参 见附件脚本9
1.列表分区
ALTER TABLE table_name MODIFY PARTITION partition_name
ADD VALUES
2.列表子分区
ALTER TABLE table_name MODIFY SUBPARTITION subpartition_name
ADD VALUES

列表分区值的删除

也是仅有列表分区或列表子分区存在该功能,如果要删除的值在当前分区子分区中存在,则不能报错,必须先删除该值对应的记录才能再删除,如果一个分区中只有一个值,无论其有否数据,也不能删除该值,因此对于存在default的List分区/子分区,本功能不可用。本操作完成后,本地索引和全局索引保留为可用。 参见附件脚本10
1.列表分区
ALTER TABLE table_name MODIFY PARTITION partition_name
DROP VALUES
2.列表子分区
ALTER TABLE table_name MODIFY SUBPARTITION subpartition_name
DROP VALUES

移动分区
移动分区的作用包括:

l重新聚合数据,以减少碎片
l将分区移动到另外一个表空间(MODIFY命令搞不定)
l修改“建立时间”属性
l对有压缩属性的,将数据压缩后存储,因此对压缩分区表分写入后需要调用本命令

一般来说,该命令执行完后,本地索引和全局索引变成不可用,需要重新构建。
1.移动表分区
ALTER TABLE table_name MOVE PARTITION partition_name
[TABLESPACE new_tablespace_name][NOLOGGING|LOGGING][COMPRESS]

1.移动表分区
ALTER TABLE table_name MOVE PARTITION partition_name
[TABLESPACE new_tablespace_name][NOLOGGING|LOGGING][COMPRESS];
执行该命令后,即使没有指定新的表空间,也会删除旧的分区段,创建一个新的分区段。
2.移动子分区
针对复合分区表,同上,关键字改成SUBPARTIOTION  subpartition_name,此外此时只能移动子分区,对分区的移动是非法的。
3.移动索引分区
切记不要通过MOVE指令来执行,虽然Oracle支持,建议一律先删除索引再重建。
参见附件脚本11



重构索引分区
重构索引分区的理由一般包括:
l恢复空间或改善性能
l修复因介质原因而损坏的索引分区
l通过SQL*Loader或导入工具装载数据后重构本地索引
l重构被标记为UNUSABLE的索引分区
l启用b-tree索引中的键压缩
1.重构全局索引分区
有以下两种方法:
(1).ALTER INDEX index_name REBUILD PARTITION partition_name
  (2).删除索引,再重建。推荐这种方法
  参见附件脚本12.1


2.重构本地索引分区  参见附件脚本12.2
有以下两种方法:
(1).ALTER INDEX index_name  REBUILD
       {PARTITION partition_name| SUBPARTITION subpartition_name}
  (2). ALTER TABLE table_name  MODIFY
       [{PARTITION partition_name| SUBPARTITION subpartition_name}
       REBUILD UNUSABLE LOCAL INDEXES
      
重命名分区

使用重命名分区有两种需求,一种是将分区名改成更有意义的,第二种是将系统自动生成的分区名改成自己想要的名字。
1.重命名分区/子分区
  ALTER TABLE table_name RENAME PARTITION old_name TO new_name;
  ALTER TABLE table_name RENAME SUBPARTITION old_name TO new_name;
2.重索引分区
  除关键字改成ALTER TABLE table_name 改成ALTER INDEX index_name外,其它同重命名分区/子分区


参见附件脚本13


拆分分区

当一个分区变得很大时会带来备份、恢复和维护操作的长时性,此时可以对分区进行拆分,拆分可以将一个分区拆分成两个,拆分的分区如果包含数据,完成拆分后索引会变得不可用,一般需要重构。
1.拆分Range分区表的Range分区
  ALTER TABLE table_name SPLIT PARTITION partition_name
  AT(values) INTO(PARTITION new_ partition_name1,                  
                  PARTITION new_partition_name2);
  其中拆分完分区后的第一个分区的值小于AT值,第二个小于原来分区的值。如果拆分完的分区没有指定名字,系统使用SYS_Pn自动命名。拆分完成后如果存在索引,还需要重构。
参见附件脚本14.1

2.拆分List分区表的List分区
  ALTER TABLE table_name SPLIT PARTITION partition_name
  VALUES(values) INTO(PARTITION new_ partition_name1,                  
                  PARTITION new_partition_name2);
  其中values值是定义中枚举值范围的子集,该值写入到拆分后的第一个分区,未包含的写入到第二个分区。也可以对default分区进行拆分,方法同上。

参见附件脚本14.2

3.拆分*-hash分区表的的分区

子分区可以采用SUBPARTITIONS n方式,也可以采用PARTITION partition_name方式。如果不指定SUBPARTITION子句,则从父分区去继承子分区数。需要注意的是拆分时继承的属性与合并时不同,合并时继承的是表级的属性,原因是合并时有两个父分区。其中关键字values或at取决于分区的类型,即range为at,list为values
参见附件14.3


4.拆分*-List分区表的分区
  此时分区级和子分区级均可拆分
(1).拆分*-List分区
如果是R-L类型,则与拆分范围分区类似,如果是L-L类型,则跟拆分列表分区类似。无需指定子分区语句,子分区属性从拆分的父分区处继承,此时拆分后新子分区的名字无法指定。
(2).拆分*-List子分区
  ALTER TABLE table_name SPLIT SUBPARTITION subpartition_name
  VALUES(values) INTO(SUBPARTITION new_subpartition_name1,                  
                  SUBPARTITION new_subpartition_name2);
参见附件脚本14.4


5.拆分*-Range分区表的分区   参见附件脚本14.5
  此时也是分区级和子分区级均可拆分,新分区的子分区的范围值不可指定,新分区的子分区属性继承自父分区。
(1).拆分*-Range分区
如果是R-R类型,则与拆分范围分区类似,如果是L-R类型,则跟拆分列表分区类似。无需指定子分区语句,子分区属性从拆分的父分区处继承,此时拆分后新子分区的名字无法指定。
(2).拆分*-Range子分区
  ALTER TABLE table_name SPLIT SUBPARTITION subpartition_name
  AT(values) INTO(SUBPARTITION new_subpartition_name1,                  
                  SUBPARTITION new_subpartition_name2);


6.拆分索引分区
  本地索引分区无法显式的拆分,其拆分的唯一途径是基表的分区拆分时由Oracle隐式的进行拆分。
全局索引分区可以拆分,拆分完成后需要重构,但我们不推荐这么做,如下例:
ALTER INDEX quon1 SPLIT
PARTITION canada AT ( 100 ) INTO
PARTITION canada1 ...,
PARTITION canada2 ...);
ALTER INDEX quon1 REBUILD PARTITION canada1;
ALTER INDEX quon1 REBUILD PARTITION canada2;


清空分区

   使用ALTER TABLE table_name TRUNCATE {PARTITION partition_name| SUBPARTITION subpartition_name}清空分区会清除掉分区中的数据,类似清空表。但是索引分区不可清空,清空分区同步会清空索引分区在该分区的数据。
1.清空表分区
会清空分区数据,但不会回收空间,有两种情况:
(1).包含数据和全局索引的分区
  方法一是先清空该分区再重构基于该分区的全局索引;第二种方法是先删除该分区的数据再清空该分区;第三种方法是使用UPDATE INDEXES子句。推荐第二种方式。


1.清空表分区
(2).包含数据和代参考完整性约束的分区
  方法一先禁用该约束再清空分区最后启用该约束;第二种方法是先删除该分区的数据再清空该分区。推荐第一种方式。
2.清空子分区
直接使用该语句,同步的本地索引数据也会删除。



相关数据字典
视图 说明 视图 说明
DBA_PART_TABLES
ALL_PART_TABLES
USER_PART_TABLES
分区表
DBA_TAB_PARTITIONS
ALL_TAB_PARTITIONS
USER_TAB_PARTITIONS
分区
DBA_TAB_SUBPARTITIONS
ALL_TAB_SUBPARTITIONS
USER_TAB_SUBPARTITIONS
子分区
DBA_PART_KEY_COLUMNS
ALL_PART_KEY_COLUMNS
USER_PART_KEY_COLUMNS
分区键
DBA_SUBPART_KEY_COLUMNS
ALL_SUBPART_KEY_COLUMNS
USER_SUBPART_KEY_COLUMNS

子分区键
DBA_PART_COL_STATISTICS
ALL_PART_COL_STATISTICS
USER_PART_COL_STATISTICS
分区的列和柱状图统计信息


视图 说明 视图 说明
DBA_SUBPART_COL_STATISTICS
ALL_SUBPART_COL_STATISTICS
USER_SUBPART_COL_STATISTICS
子分区的列和柱状图统计信息
DBA_PART_HISTOGRAMS
ALL_PART_HISTOGRAMS
USER_PART_HISTOGRAMS
分区柱状图数据
DBA_SUBPART_HISTOGRAMS
ALL_SUBPART_HISTOGRAMS
USER_SUBPART_HISTOGRAMS
子分区柱状图数据
DBA_PART_INDEXES
ALL_PART_INDEXES
USER_PART_INDEXES
分区索引


按范围分区和列表分区

1.按范围分区
l大表且对数据的扫描经常按使用范围时,如日期和时间
l对数据的维护需要使用滚动窗口时,如在数据仓库中,经常按月度需要装载最近3年(36个月)的数据。滚动窗口是指一般是周期性的需要装载新数据并清除旧数据的时间窗口
2.列表分区
l列的取值范围是离散且可以枚举时,如按地理区域分区、按流程状态分区
散列分区
   无明确的按范围或者列表方式分区时,可考虑在以下情况下使用散列分区:

l想要数据均衡分布,并可启用部分/全部分区智能连接功能时
l分区键作为主要的唯一值或者值列表时,如使用序列为主键
l想要数据随机均衡分布在不同的分区以便规避I/O瓶颈时

当一个列或多个列构成唯一值时,使用这些列为分区键来建立散列分区,并且将分区数尽量设置为2的幂,如2、4、8、16、32等,一般会获得比较好的性能。

1.复合范围-范围分区

l两个均能按范围分区的维度时,如两个维度的时间(如起始时间、截止时间)

2.复合范围-散列分区

l存储有大量历史数据,且这些历史数据经常要跟另外一个大表连接时。如销售历史表和客户表一期关联对比分析趋势数据时
l传统意义上的散列分区数据经常需要滚动操作时,如某个系统访问的日志信息,需要统计不同客户端ip各个时期的各种记录(如访问记录次数、在线时长等)时


3.复合列表-范围分区

l一般用在第一个维度是枚举值,第二个维度是范围值时,如捐赠数额表,可以先按币种列表分区,再按金额范围分区

4.复合列表-散列分区

l通常用在第一个维度是枚举值,第二个维度无法按范围或列表分区时,如某个信用卡记录表,可以先按区域列表分区、再按卡号进行散列分区


5.复合列表-列表分区
l一般用两个维度都是不连续的离散值时,如前述信用卡账户表,可以先按区域列表分区,再按省份或账户类型(白金卡、金卡、银卡、普通卡等)列表分区

习题
1.本地分区索引的分区可以增加吗,为什么?
2.散列分区要减少分区数如何处理?范围分区和列表分区呢?请试着举例说明。
3.假定表A基于列colA有3个范围单击分区,且建立了一个全局分区索引,表中有数据,如果要删除某个分区,有几种方法?
4.本地索引分区可以删除吗,为什么?如果某个表存在全局分区索引,删除某个分区后,需要接下来执行什么操作,为什么?
5.单级散列分区表与复合*-散列分区交换时,需要满足什么条件?
6.举例说明复合*-散列分区中的散列子分区交换为什么只能跟非分区表进行交换。
7.对分区执行移动操作的两大主要目的是什么?
8.列表归纳各种分区的拆分限制条件,并举例说明其语法。
9.散列分区、范围分区、*-Hash和*-List分区分别建议在什么情况下使用?
10.练习本课程的分区管理操作,增强对分区管理的直观理解。

转载请注明私塾在线【 http://sishuok.com/forum/blogPost/list/0/6411.html】
分享到:
评论

相关推荐

    oracle海量图像数据的存储

    ### Oracle海量图像数据的存储 #### 一、背景与挑战 在现代信息技术的应用场景中,尤其是在涉及媒体资源管理、档案管理、医疗影像等领域时,如何有效地存储和管理海量图像数据成为一个亟待解决的问题。传统的存储...

    Oracle全文检索技术在海量数据挖掘中的应用.pdf

    在对“Oracle全文检索技术在海量数据挖掘中的应用.pdf”这一文档内容进行梳理后,可以总结出以下IT知识点: 1. 海量数据与数据挖掘的概念: 海量数据指的是庞大的、超出常规处理能力的数据集合,民航部门等业务...

    Oracle海量数据自动化归档平台的研究与实现.pdf

    本研究旨在设计并实现一个Oracle海量数据自动化归档平台,通过平台实现数据的自动化归档与清理,提高数据管理的效率和性能。 二、系统设计架构 Oracle海量数据自动化归档平台系统主要由三个部分构成:生产库、归档...

    基于Oracle数据库海量数据的查询优化研究 (1).pdf

    在Oracle数据库中,面对海量数据的查询优化是一项关键任务。通过SQL语句优化、分区技术和索引技术的应用,可以显著提高查询效率,减少系统资源消耗。而分页查询技术则为处理大规模数据提供了有效的解决方案,确保...

    让Oracle跑得更快2:基于海量数据的数据库设计与优化

    不过,根据标题《让Oracle跑得更快2:基于海量数据的数据库设计与优化》以及描述中的作者名“谭怀远”,我们可以推测该文档应是一本专注于Oracle数据库性能提升和海量数据处理的优化指南。以下将基于这一主题,结合...

    海量数据环境下的Oracle数据库系统架构设计和优化思路

    ### 海量数据环境下Oracle数据库系统架构设计与优化思路 #### 一、分布式数据库系统架构设计 在海量数据处理环境中,传统的单机数据库系统已经难以满足高性能和高可用性的需求。因此,分布式数据库系统成为了一种...

    海量数据环境下的Oracle数据库系统架构设计和优化思路.pdf

    ### 海量数据环境下Oracle数据库系统架构设计与优化的关键知识点 #### 一、大数据时代的背景与挑战 在当前的大数据时代背景下,随着社会信息化进程的不断深入和社会经济活动的日益数字化,企业面临的最大挑战之一...

    Oracle数据库详细讲解

    Oracle数据库是全球领先的数据库管理系统之一,由Oracle公司开发,广泛应用于企业级数据存储与处理场景。下面,我们将围绕Oracle数据库的核心概念、安装配置、基本操作以及性能优化等方面进行详细解析。 ### Oracle...

    oracle删除数据方法。

    1. **游标(Cursor)**: 在Oracle中,游标是一种用于处理查询结果集的逻辑结构。它可以指向一个SQL语句的结果集,并支持对这些结果进行逐行或批量处理。 2. **BULK COLLECT**: 这是Oracle提供的一个特性,允许一次性...

    Oracle:企业大数据解决方案(PDF)

    在这一背景下,Oracle推出了其企业级大数据解决方案,旨在帮助企业更好地处理、分析非传统且结构较为松散的数据类型,如网页日志、社交媒体数据、电子邮件、传感器数据以及图像等。这些数据类型通常无法通过传统的...

    让Oracle跑的更快1、2集合

    并行处理是Oracle的一大特色,通过并行执行计划,可以将大任务分解为多个小任务并发处理,大幅缩短处理时间。但并行度的选择需要根据硬件资源和工作负载来平衡,过高可能导致资源竞争,过低则无法充分利用多核优势。...

    ORACLE学习

    - **大型数据库**:例如Sybase、Oracle、DB2等,适用于处理海量数据的应用场景,成本较高但安全性更好,通常应用于金融、电信等领域。 #### 四、数据库管理员(DBA)的工作职责 数据库管理员的主要职责包括但不限于:...

    Oracle8i PL_SQL外部过程在海量数据处理中的应用.pdf

    Oracle数据库是全球范围内广泛使用的数据库管理系统之一,它支持复杂的数据管理需求,并能够处理海量数据。在Oracle 8i版本中,PL/SQL(Procedural Language extension to SQL)作为Oracle数据库服务器端的编程语言...

    从大数据到大智慧:Oracle_大数据解决方案

    为了解决这一问题,Oracle等公司提出了以经济便宜的X86服务器为基础的“海量数据经济方案”。 其次,多结构化数据(Variety)涵盖文本、图片、视频、文档等多种类型的数据。这些数据的格式和结构差异巨大,给数据的...

    ORACLE分区表操作大全

    Oracle分区表是Oracle数据库中一种优化大数据处理的高级特性,它通过将一个大表分成多个逻辑部分,即分区,来提高查询性能、简化管理和增强可用性。分区技术在处理海量数据时尤其有用,因为它们允许数据库仅扫描与...

    oracle数据库设计规范

    #### 一、数据库设计规范概览 Oracle数据库设计规范旨在为数据库设计、规划、开发及维护提供技术参考。规范涵盖了物理设计原则与逻辑设计原则两个方面,确保数据库系统的高效稳定运行。 #### 二、数据库物理设计...

    oracle exadata dababase machine数据库一体机

    Oracle Exadata Database Machine 是一款集成了高性能硬件与专有软件的数据仓库和在线事务处理(OLTP)解决方案。这款产品针对现代企业面临的海量数据处理需求而设计,通过其独特的架构解决了传统数据库系统的瓶颈问题...

    oracle 分组函数

    1. COUNT():这个函数用于计算一组数据的数量,可以统计非NULL值或所有记录。 ```sql SELECT customer_id, COUNT(order_id) FROM orders GROUP BY customer_id; ``` 2. SUM():用于计算指定列的总和。 ```sql ...

    Oracle数据库查询优化的方法

    在特殊情况下,比如对于数据相对稳定、字段数量较少的表,可以考虑创建一个包含所有查询列的复合索引,也称作充分索引。但在频繁进行插入和删除操作的表上,过多的索引会影响这些操作的性能,因此不建议建立过多索引...

Global site tag (gtag.js) - Google Analytics