Oracle数据库:索引与表分区详解

需积分: 12 3 下载量 8 浏览量 更新于2024-07-28 收藏 1.45MB PPT 举报
"Oracle数据库中的索引和表分区技术是提高数据访问效率的重要手段。索引可以加速数据查找,尤其在大型表中,而表分区则能够优化数据管理和查询性能。" 在Oracle数据库中,索引是一种特殊的数据库对象,用于加速对表中数据的查找。普通表的数据通常是按照堆组织的,如果没有索引,查找特定记录就需要进行全表扫描,这在数据量庞大的时候非常低效。索引的存在使得Oracle能够快速定位到所需数据,但同时也会在插入、删除和更新操作时增加开销,因为索引也需要同步维护。 Oracle提供了多种类型的索引,包括B树索引、B树聚集索引、Hash聚集索引、反向键值索引、位图索引以及位图联结索引。其中,B树索引是最常见的一种,适用于大多数情况,它保证了数据查找的平均时间性能。B树索引分为惟一索引和非惟一索引,前者确保索引列的唯一性,后者则允许重复值。此外,还可以创建多列复合索引,即在多个列上建立索引,以满足更复杂的查询需求。 创建索引的基本语法如下: ```sql CREATE INDEX index_name ON table_name(column_name); ``` 如果需要创建唯一索引,可以使用`UNIQUE`关键字: ```sql CREATE UNIQUE INDEX index_name ON table_name(column_name); ``` 对于组合索引,可以在多个列上创建: ```sql CREATE INDEX index_name ON table_name(column_name1, column_name2); ``` 索引的维护包括重建和删除。重建索引可以优化索引结构,而删除索引则需要注意可能对查询性能的影响。 表分区是另一种提升性能的技术,尤其是对大型表来说。通过将大表分割成逻辑上相关的小块(分区),可以实现更高效的查询和管理。例如,可以根据日期、地区或其他业务相关的字段进行分区。分区可以显著减少需要扫描的数据量,从而加速查询。 创建分区的基本语法如下: ```sql CREATE TABLE table_name (PARTITION BY RANGE (column_name) (PARTITION partition_name VALUES LESS THAN (value)) ... ); ``` 或者使用其他类型的分区策略,如列表分区、哈希分区等。 Oracle索引和表分区是数据库性能优化的关键技术。正确地创建和使用索引以及设计合理的分区策略,能够显著提高数据访问速度,降低系统资源消耗,提升整体的数据库性能。在实际应用中,需要根据业务需求和数据特性来选择合适的索引类型和分区策略。
2012-12-25 上传
支持自动ORACLE大表分区: 版本进度: 31. 20110420 V2.2 支持任意表任意时间字段分区 以下为安装部署部分: 1.分区相关脚本部署执行顺序,安装前请确保该用户拥有管理员权限, 同时请执行GRANT CREATE ANY TABLE TO DBUSER, 因为使用到了动态的CREATE TABLE语句; 01) >AGGRE_ERROR_INFO_DDL.SQL 如果日志表AGGRE_ERROR_INFO已经存在,该步骤跳过。 02) >GET_MILLISECOND.SQL 如果函数GET_MILLISECOND已经存在,该步骤跳过。 03) >GET_DATE_FROM_MILLISECOND.SQL 如果函数GET_DATE_FROM_MILLISECOND已经存在,该步骤跳过。 04) >AGGRE_PM_PARTITIONF.SQL 2.注意事项: 01) >部署完后注意检查分区维护JOB[对应存储过程为AGGRE_PM_PARTITIONM], 如果有多个相同的分区维护JOB,则请删除后面创建的JOB,只保留一个分区维护JOB。 检查脚本如下:select t.what,t.* from user_jobs t 02) >本产品中使用的分区调度表名称为AGGRE_PARTITION_TASK,可以根据该表中信息观察分区情况。 以下为说明解释部分: 2.分区改造主过程:AGGRE_PM_PARTITIONF.SQL, 意思是PARTITIONING THE FIRST TIME; 参数解释如下: -- @PARAM VARCHAR2 PARTTABLENAME---可以指定对某个表大小大于等于TABLEONSIZE_M(单位为MB)的表进行按指定时间字段的自动分区; -- @PARAM NUMBER TABLEONSIZE_M---大表自动分区起始大小,单位为兆字节(MB),如不想指定具体大小则置0即可; -- @PARAM NUMBER PARTINTERVAL----取值范围为[1/24,365],表的分区时长,单位为天,默认为1,采用一天一分区; -- @PARAM 若为7,则采用一周一分区,若为30,则采用一月一分区; -- @PARAM NUMBER PARTRESERVED----表数据保留时长,单位为天; -- @PARAM NUMBER BACKINTERVAL----取值范围为[3600,7*86400],表数据回迁时的循环步长,即一次回迁多长时间的数据,单位为秒; -- @PARAM VARCHAR2 PARTWEEKDAY-----取值范围为(SUN,MON),PARTINTERVAL为7时起作用,指定一周的起始天为星期日还是星期一; -- @PARAM VARCHAR2 PARTFIELD-------指定的分区时间字段名称 -- @PARAM VARCHAR2 FIELDFORMAT-----指定的分区时间字段的格式 -- @PARAM VARCHAR2 TISPARTITIONED--取值范围为(TRUE,FALSE),指定PARTTABLENAME参数所指定的表是否是分区表,默认为FALSE -- @PARAM VARCHAR2 PARTEXCHANGE----取值范围为(TRUE,FALSE),是否使用交换分区方法实现非分区表的分区化改造,默认为FALSE -- @PARAM 注意:当PARTEXCHANGE参数为TRUE时,TISPARTITIONED参数只能为FALSE, -- @PARAM 即已经分好区的分区表是不能够使用交换分区的方法转换为另一种分区表的; -- @PARAM VARCHAR2 DROPPABLE-------取值范围为(TRUE,FALSE),指定分区完后是否DROP掉分区备份表; 其中参数FIELDFORMAT的取值范围如下: /** * FIELDFORMAT * 0 NUMBER/CHAR MILLISECOND 1300200064000 13BITS * 1 NUMBER/CHAR SECOND 1300200064 10BITS * 2 NUMBER/CHAR YYYYMMDDHH24MISS 20110315224030 * 3 NUMBER/CHAR YYYYMMDDHH24MI 201103152240 * 4 NUMBER/CHAR YYYYMMDDHH24 2011031522 * 5 NUMBER/CHAR YYYYMMDD 20110315 * 6 NUMBER/CHAR YYYYMM 201103 * 7 NUMBER/CHAR YYYY 2011 * 8 CHAR YYYY-MM 2011-03 * 9 CHAR YYYY-MM-DD 2011-03-15 * 10 CHAR YYYY-MM-DD HH24 2011-03-15 22 * 11 CHAR YYYY-MM-DD HH24:MI 2011-03-15 22:40 * 12 CHAR YYYY-MM-DD HH24:MI:SS 2011-03-15 22:40:30 * 13 CHAR YYYY-MM-DD HH24:MI:SSXFF 2011-03-15 22:40:30.765000 * 14 CHAR YYYY"年" 2011年 * 15 CHAR YYYY"年"MM"月" 2011年03月 * 16 CHAR YYYY"年"MM"月"DD"日" 2011年03月15日 * 17 CHAR YYYY"年"MM"月"DD"日" HH24"时" 2011年03月15日 22时 * 18 CHAR YYYY"年"MM"月"DD"日" HH24"时"MI"分" 2011年03月15日 22时40分 * 19 CHAR YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒" 2011年03月15日 22时40分30秒 * 100 DATE 2011-3-15 23:00:01 * 101 TIMESTAMP 15-3月 -11 10.59.30.953000 下午 +08:00 */ -- 第一次分区尽量在数据库闲时操作,这样更能保证分区表的数据一致性; -- 通常使用的现有大表的分区方法:A.使用RENAME分区 B.使用交换分区 C.使用联机重定义 只有C方案才能保证数据的完全一致性; -- 但是经过测试发现方案B和C都存在分区过程的不透明性,对EXCEPTION不好控制,另外C方案比较适合手工操作,不适合自动运行; -- B方案比较适合将非分区表中的数据放到分区表中的一个分区中,不符合要求,所以本分区存储过程默认采用A方案; -- 当然,也支持通过新增参数PARTEXCHANGE来控制是否使用B方案;PARTEXCHANGE为TRUE,使用B方案,为FALSE,使用A方案; -- 手动运行示例: 自动对800M以上的非分区大表PM_RAW_B_RESTEST进行分区,一天一分区; -- 手动运行示例: SQL> EXEC AGGRE_PM_PARTITIONF('PM_RAW_B_RESTEST',800,1,10,3600,'SUN','DCTIME','0','FALSE','FALSE','FALSE'); -- 推荐以以上这种方式对单个表进行分区,并将DROPPABLE参数设为'FALSE', -- 这样有什么问题可以跟踪,等完后再可手动将分区备份表DROP掉; -- 注意:分区之前请确保相关表空间足够大。 -- 注意:如果在分区化改造过程中数据回迁之前抛出异常,则手动数据回迁前注意检查分区表有无主键索引。 3.分区维护主过程:AGGRE_PM_PARTITIONM.SQL, 意思是PARTITION MANAGEMENT; 4.创建分区维护JOB -- 对在分区调度表中的已经分区的表进行分区清理以及分区追加等 -- 分区维护操作由该JOB自动完成,该过程不用手动干预。