oracle表分区知识整理.docx

目录Oracle表分区知识整理1一、Oracle表分区的概念1二、Oracle表分区的类型41）范围分区42）哈希分区43）列表分区54）组合分区5三、Oracle表分区的实际操作51）范围分区实例52）哈希分区实例63）列表分区实例74）复合分区实例8四）Oracle表分区对应用系统程序开发的影响和优化策略91）JDBC的增删改操作92）JDBC的查询操作93）JDBC应用策略9Oracle表分区知识整理一、 Oracle表分区的概念Oracle 分区功能可以提高许多应用程序的可管理性、性能与可用性。通过分区功能，可以将表、索引和索引组织表进一步细分为段，从而能够更精确地管理和访问这些数据库对象。Oracle 提供了种类繁多的分区方案以满足每种业务要求。而且，因为在 SQL 语句中分区是完全透明的，所以该功能几乎可应用于任何应用程序。分区功能的优势分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务。通过分区,数据库设计人员和管理员能够解决前沿应用程序带来的一些难题。分区是构建千兆字节数据系统或超高可用性系统的关键工具。分区功能的基本知识分区功能能够将表、索引或索引组织表进一步细分为段。这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理。这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。表的分区是通过“分区键”来实现的，分区键指的是一些列，这些列决定了某一行所在的分区。Oracle 数据库 10g 提供了六项技术用于对表进行分区：范围分区每个分区都由一个分区键值范围指定（对于一个以日期列作为分区键的表，“2005 年 1 月”分区包含分区键值为从“2005 年 1 月 1 日”到“2005 年 1 月 31 日”的行）。列表分区每个分区都由一个分区键值列表指定（对于一个地区列作为分区键的表，“北美”分区可能包含值“加拿大”“美国”和“墨西哥”）。散列分区将散列算法用于分区键来确定指定行所在的分区。组合范围散列分区范围和散列分区技术的组合，通过该组合，首先对表进行范围分区，然后针对每个单独的范围分区再使用散列分区技术进一步细分。索引组织表只能进行范围分区。组合范围列表分区范围和列表分区技术的组合，通过该组合，首先对表进行范围分区，然后针对每个单独的范围分区再使用列表分区技术进一步细分。索引组织表可以按范围、列表或散列进行分区。Oracle 数据库 10g 还提供了三种类型的分区索引：本地索引本地索引是其分区方式与其所在基础表的分区方式一模一样的索引。本地索引的每个分区仅对应于其所在基础表的一个分区。全局分区索引全局分区索引是使用不同于其所在表的分区键进行分区的索引，其所在表可以是分区表或非分区表。全局分区的索引可以使用范围或散列分区进行分区。例如，某个表可以按月份进行范围分区，因此具有十二个分区，而该表上的索引则可以使用不同的分区键进行范围分区，从而具有不同的分区数量。全局非分区索引全局非分区索引基本上和非分区表的索引一样。索引结构是不分区的。Oracle 提供了一系列丰富的技术，可用于对表、索引和索引组织表进行分区，因此可以针对任何业务环境中的任何应用程序进行最佳的分区 Oracle 还提供一套完整的 SQL 命令，用于管理分区表。其中包括添加新分区、删除分区、分拆分区以及合并分区的命令。用分区功能提高可管理性通过 Oracle 分区功能，可将表和索引分成更多、更小的可管理单元，从而使数据库管理员能以“化整为零，个个击破”的方式管理数据。使用分区功能，维护操作可集中于表的特定部分。例如，数据库管理员可以只对表的一部分做备份，而不必对整个表做备份。对整个数据库对象的维护操作，可以在每个分区的基础上进行，从而将维护工作分解成更容易管理的小块。利用分区功能提高可管理性的一个典型用法是支持数据仓库中的滚动视窗加载进程。假设数据库管理员每周向表中加载新数据。可以对该表进行范围分区，使每个分区包含一周的数据。这样加载进程只是简单地添加新的分区。添加一个分区的操作比修改整个表效率高很多，因为 DBA 不需要修改任何其他分区。用分区功能提高性能由于限制了所检查或操作的数据数量，同时支持并行任务执行，Oracle 分区功能实现了性能上增益。这些特性包括：分区修整分区修整是用分区功能提高性能的最简单最有价值的手段。分区修整常常能够将查询性能提高几个数量级。例如，假设某个应用程序包含一个存储订单历史记录的 Orders 表，并且此表已按周分区。查询一周的订单只需访问该订单表的一个分区。如果该订单表包含两年的历史记录，这个查询只需要访问一个分区而不是一百零四个。该查询的执行速度因为分区修整而有可能快一百倍。分区修整能与所有其他 Oracle 性能特性协作。Oracle 能将分区修整功能与任何索引技术、联接技术或并行访问方法结合使用。分区智能联接分区功能可以通过称为分区智能联接的技术提高多表联接的性能。当两个表要联接在一起，而且每个表都用联接键来分区时，就可以使用分区智能联接。分区智能联接将大型联接分解成较小的发生在各个分区间的联接，从而用较少的时间完成全部联接。这就给串行和并行的执行都能带来显著的性能改善。用分区功能提高可用性分区的数据库对象具有分区独立性。该分区独立性特点可能是高可用性战略的一个重要部分，例如，如果分区表的一个分区不能用，但该表的所有其他分区仍然保持在线并可用。那么这个应用可以继续针对该分区表执行查询和事务处理，只要不是访问不可用的分区，数据库操作仍然能够成功运行。数据库管理员可以指定各分区存放在不同的表空间里，从而让管理员隔离其它表分区针对单个分区进行备份与恢复操作。还有，分区功能可以减少计划停机时间。由于分区功能改善了性能，使数据库管理员能用相对较少的时间完成大型数据库对象的维护工作。未来发展方向自从引入分区技术以来，Oracle 公司在每次推出重要版本时都会增加新的分区方法。Oracle8 引入了范围分区功能，Oracle8i 引入了散列和组合范围散列分区功能，Oracle9i 引入了列表分区功能。在最新版本 Oracle 数据库 10g 中，则增强了用于索引组织表和全局分区索引的分区策略，并且扩展了其用于所有分区维护操作的并发索引维护功能。Oracle 公司致力于不断完善分区技术，确保满足所有的业务需求。结论具有 Oracle 分区功能的 Oracle 数据库 10g 可以显著增强几乎任何数据库应用程序的可管理性、性能和可用性。分区功能可用于前沿应用程序，分区功能确实能够成为保障这些应用程序成功的关键技术成分。同时，分区功能也可用于较为普通的数据库应用，来简化这些应用的管理工作，降低管理成本。二、Oracle表分区的类型1）范围分区范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据 序号分区，根据业务记录的创建日期进行分区等。2）哈希分区散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。如将物料交易表的数据根据交易 ID散列地存放在指定的三个表空间中。3）列表分区当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。可以在每个分区的描述中为该分区列指定一列离散值，这不同于范围分区，在那里一个范 围与一个分区相关，这也不同于散列分区，在那里用户不能控制如何将行映射到分区。列表分区方法是特意为遵从离散值的模块化数据划分而设计的。范围分区或散 列分区不那么容易做到这一点。进一步说列表分区可以非常自然地将无序的和不相关的数据集进行分组和组织到一起。与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分 区方法进行分区的所有的列,都可以用列表分区方法进行分区。4）组合分区有时候我们需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再 使用散列分区的一种分区方法，如将物料交易的记录按时间分区，然后每个分区中的数据分三个子分区，将数据散列地存储在三个指定的表空间中。三、Oracle表分区的实际操作-查看s数据文件信息select * from dba_data_files;-查看用户表空间信息select * from user_tablespaces;-这里创建三个表空间，并建立相应的数据文件create tablespace xgtest_space01 datafile D:ORACLEORADATAORCLxg_partition01.DBF size 50M;create tablespace xgtest_space02 datafile D:ORACLEORADATAORCLxg_partition02.DBF size 50M;create tablespace xgtest_space03 datafile D:ORACLEORADATAORCLxg_partition03.DBF size 50M;1） 范围分区实例-创建一个产品表，这个表一产品表主键作为范围分区字段，-设置小于100000的产品主键记录放到product_p01分区表，100000-500000的记录放在product_p02分区，大于500000放到product_p03分区create table test_product( product_id number(11) primary key, product_name varchar2(64) not null, created_date date not null, company_name varchar2(128)partition by range (product_id)(partition product_p01 values less than(100000) tablespace xgtest_space01,partition product_p02 values less than(500000) tablespace xgtest_space02,partition product_p03 values less than(maxvalue) tablespace xgtest_space03);-查询建立的产品表信息select * from user_tables t where t.table_name = upper(test_product);-查询建立的产品表信息select * from user_part_tables t where t.table_name = upper(test_product);-查询建立的产品表的分区信息select * from user_tab_partitions t where t.table_name = upper(test_product);-插入几条产品表信息并做事务提交insert into test_product values(100,IBM便携式电脑T43,sysdate,IBM);insert into test_product values(1000,佳能A720相机,sysdate,Canon);insert into test_product values(105000,联想X200,sysdate,联想);insert into test_product values(300000,Iphone4,sysdate,Apple);insert into test_product values(700000,windows7系列,sysdate,microsoft);commit;-查询每个分区的分表记录信息select * from test_product partition(product_p01);select * from test_product partition(product_p02);select * from test_product partition(product_p03);-查询表信息select * from test_product;-分区表条件查询select * from test_product partition(product_p01) t where duct_id = 1000;-表条件查询select * from test_product t where duct_id = 1000;-在分区表上做更新操作update test_product partition(product_p01) t set pany_name=佳能公司 where duct_id=1000;commit;-在分区表上做删除操作delete from test_product partition(product_p01) t where duct_id=300000;commit;2） 哈希分区实例-建立一个销售订单记录表，并按照销售时间做哈希分区字段create table test_sale_order( sale_order_id number(11) primary key, product_name varchar2(64) not null, saled_date date not null, operator_name varchar2(32)partition by hash(saled_date)(partition sale_order_p01 tablespace xgtest_space01,partition sale_order_p02 tablespace xgtest_space02,partition sale_order_p03 tablespace xgtest_space03);-查询表信息以及分区信息select * from user_part_tables t where t.table_name = upper(test_sale_order);select * from user_tab_partitions t where t.table_name = upper(test_sale_order);-插入销售订单记录insert into test_sale_order values(1000, 罗技鼠标,to_date(2007-06-23,yyyy-mm-dd),许果);insert into test_sale_order values(1001, Thinkpad T60笔记本,to_date(2007-06-23,yyyy-mm-dd),fruitking);insert into test_sale_order values(1002, 海尔冰箱,to_date(2008-06-20,yyyy-mm-dd),刘德华);insert into test_sale_order values(1003, 格力空调,to_date(2007-10-09,yyyy-mm-dd),刘若英);insert into test_sale_order values(1004, 浪莎丝袜,to_date(2009-09-05,yyyy-mm-dd),孙俪);insert into test_sale_order values(1005, 美的电扇,to_date(2010-03-19,yyyy-mm-dd),小张);insert into test_sale_order values(1006, 安琪儿自行车,to_date(2015-07-28,yyyy-mm-dd),小林);commit;-查询分区记录以及表记录，分区和表的条件查询select * from test_sale_order partition(sale_order_p01);select * from test_sale_order partition(sale_order_p02);select * from test_sale_order partition(sale_order_p03);select * from test_sale_order;select * from test_sale_order partition(sale_order_p01) t where t.sale_order_id = 1000;select * from test_sale_order t where t.sale_order_id = 1000;3） 列表分区实例-创建公司表，并已列表分区create table test_company( company_id number(11) primary key, company_name varchar2(64) not null, created_date date not null, city_name varchar2(32)partition by list (city_name)(partition company_p01 values (杭州) tablespace xgtest_space01,partition company_p02 values (上海) tablespace xgtest_space02,partition company_p03 values (default) tablespace xgtest_space03);-查询表和分区信息select * from user_part_tables t where t.table_name = upper(test_company);select * from user_tab_partitions t where t.table_name = upper(test_company);-插入公司记录信息insert into test_company values(1000, 阿里巴巴,sysdate,杭州);insert into test_company values(1001, 华为,sysdate,深圳);insert into test_company values(1002, 恒生电子,sysdate,杭州);insert into test_company values(1003, 盛大,sysdate,上海);insert into test_company values(1004, 浦发银行,sysdate,上海);insert into test_company values(1005, 信雅达,sysdate,杭州);insert into test_company values(1006, 富士康,sysdate,深圳);insert into test_company values(1007, 东风汽车,sysdate,武汉);commit;-查询分区记录，表记录，分区和表的条件查询select * from test_company partition(company_p01);select * from test_company partition(company_p02);select * from test_company partition(company_p03);select * from test_company;select * from test_company partition(company_p01) t where pany_id = 1000;select * from test_company t where pany_id = 1000;4） 复合分区实例-创建一个话单表，并已组合分区（先范围分区，再哈希分区的方式）create table test_phone_order( phone_order_id number(11) primary key, phone_no varchar2(64) not null, consumed_date date not null, consumed_address varchar2(32)partition by range(consumed_date)subpartition by hash(phone_order_id)subpartitions 3 store in (xgtest_space01,xgtest_space02,xgtest_space03)(partition phone_order_p01 values less than(to_date(2006-01-01,yyyy-mm-dd),partition phone_order_p02 values less than(to_date(2010-01-01,yyyy-mm-dd),partition phone_order_p03 values less than(maxvalue);-查看表信息和分区信息select * from user_part_tables t where t.table_name = upper(test_phone_order);select * from user_tab_partitions t where t.table_name = upper(test_phone_order);-插入话单记录insert into test_phone_order values(1000,to_date(2001-06-23,yyyy-mm-dd),杭州);insert into test_phone_order values(1001,to_date(2007-06-23,yyyy-mm-dd),南昌);insert into test_phone_order values(1002,