200字范文 > 【SQL开发实战技巧】系列（四十七）：Oracle12C常用新特性☞非分区表转分区表onli

【SQL开发实战技巧】系列（四十七）：Oracle12C常用新特性☞非分区表转分区表onli

时间：2021-02-16 18:08:53

系列文章目录

【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事

【SQL开发实战技巧】系列（二）：简单单表查询

【SQL开发实战技巧】系列（三）：SQL排序的那些事

【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项

【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论

【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放

【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起

【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表

【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！

【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起

【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)

【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加

【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工

【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report

【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算

【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数

【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算

【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？

【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据

【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据

【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式

【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高

【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的

【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组

【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行

【SQL开发实战技巧】系列（二十七）：数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表

【SQL开发实战技巧】系列（二十八）：数仓报表场景☞人员分布问题以及不同组(分区)同时聚集如何实现

【SQL开发实战技巧】系列（二十九）：数仓报表场景☞简单的树形(分层)查询以及如何确定根节点、分支节点和叶子节点

【SQL开发实战技巧】系列（三十）：数仓报表场景☞树形(分层)查询如何排序？以及如何在树形查询中正确的使用where条件

【SQL开发实战技巧】系列（三十一）：数仓报表场景☞分层查询如何只查询树形结构某一个分支？如何剪掉一个分支？

【SQL开发实战技巧】系列（三十二）：数仓报表场景☞对表中某个字段内的值去重

【SQL开发实战技巧】系列（三十三）：数仓报表场景☞从不固定位置提取字符串的元素以及搜索满足字母在前数字在后等条件的数据

【SQL开发实战技巧】系列（三十四）：数仓报表场景☞如何对数据分级并行转为列

【SQL开发实战技巧】系列（三十五）：数仓报表场景☞根据条件返回不同列的数据以及Left /Full Join注意事项

【SQL开发实战技巧】系列（三十六）：数仓报表场景☞整理垃圾数据：查找数据的连续性时间和重叠时间的关系，初始化开始结束时间

【SQL开发实战技巧】系列（三十七）：数仓报表场景☞从表内始终只有近两年的数据，要求用两列分别显示其中一年的数据聊行转列隐含信息的重要性

【SQL开发实战技巧】系列（三十八）：数仓报表场景☞拆分字符串进行连接

【SQL开发实战技巧】系列（三十九）：Oracle12C常用新特性☞新增分页查询

【SQL开发实战技巧】系列（四十）：Oracle12C常用新特性☞可以在同样的列（列组合）上创建多个索引以及可以对DDL操作进行日志记录

【SQL开发实战技巧】系列（四十一）：Oracle12C常用新特性☞APPROX_COUNT_DISTINCT以及TEMP UNDO(临时undo记录可以存储在一个临时表中)

【SQL开发实战技巧】系列（四十二）：Oracle12C常用新特性☞With FUNCTION新特性

【SQL开发实战技巧】系列（四十三）：Oracle12C常用新特性☞转换函数的增强和不可见字段

【SQL开发实战技巧】系列（四十四）：Oracle12C常用新特性☞你知道吗Oralcle新增了类似Mysql的自增列（Identity Columns）功能

【SQL开发实战技巧】系列（四十五）：Oracle12C常用新特性☞varchar2/nvarchar2类型最大长度由4000字节扩展到32767字节

【SQL开发实战技巧】系列（四十六）：Oracle12C常用新特性☞会话级序列及数据泵(DataPump增强)支持像表一样导出视图

【SQL开发实战技巧】系列（四十七）：Oracle12C常用新特性☞非分区表在线转分区表online clause（不停业务+索引有效）

文章目录

系列文章目录前言【SQL开发实战技巧】系列（四十七）：Oracle12C常用新特性☞非分区表在线转分区表online clause（不停业务+索引有效）案例一：非分区表转分区表，索引转换为oracle内部规则。案例二:非分区表转分区表，索引转换为自己设定规则总结

前言

本篇文章主要给讲解的Oracle12C+的新特性是：Oracle 12c以前非分区表需要转换为分区, 如果不停业务的话可以使用在线重定义,只有在表进行切换的时候会有短暂的锁表。 Oracle 12c 中alter table online clause 实现了表上现有的索引有效,又不停业务。

【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

【SQL开发实战技巧】系列（四十七）：Oracle12C常用新特性☞非分区表在线转分区表online clause（不停业务+索引有效）

12c以前非分区表需要转换为分区, 如果不停业务的话可以使用在线重定义,只有在表进行切换的时候会有短暂的锁表。 12c 中alter table online clause 实现了表上现有的索引有效,又不停业务。

案例一：非分区表转分区表，索引转换为oracle内部规则。

建非分区表：

create table no_part as select * from user_objects where object_id is not null;

创建非分区表索引：

create index idx_oid on no_part( object_id );create index idx_ctime_oname on no_part( created, object_name );create bitmap index idx_b_otype on no_part(object_type);

查看表索引信息：

SQL> SELECT index_name, column_name, descend,COLUMN_POSITION FROM user_ind_columns WHERE table_name = 'NO_PART';

再看下当前索引状态：

select table_name, index_name, status, blevel, leaf_blocksfrom user_Indexeswhere table_name = 'NO_PART';

转换当前表为分区以 object_id 字段, interval分区表，直接 update index，使用ORACLE有自己的索引转换规则：

alter table NO_PART modifypartition by range (object_id) interval (5)(partition p1 values less than (73527)) onlineupdate indexes;

update index ,没有指定写local 或global选项, ORACLE有自己的索引转换规则。

现在再来看索引信息：

SELECT index_name, column_name, descend, COLUMN_POSITIONFROM user_ind_columnsWHERE table_name = 'NO_PART';

select table_name, index_name, INDEX_TYPE, status, blevel, leaf_blocksfrom user_Indexeswhere table_name = 'NO_PART';

select INDEX_OWNER, index_name, status, blevel, leaf_blocksfrom dba_ind_partitionswhere INDEX_OWNER = 'TEST'and Index_name = 'IDX_OID';

select INDEX_OWNER, index_name, status, blevel, leaf_blocksfrom dba_ind_partitionswhere INDEX_OWNER = 'TEST'and Index_name = 'IDX_B_OTYPE';

说明：user_Indexes 是主要计量一个index的相关信息的，如果分区索引为local index 它不能记录状态，就为 N/A ，分区 local index要用视图 dba_ind_partitions 查状态。

查看分区情况

select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUEfrom user_tab_partitionswhere table_name = 'NO_PART';

案例二:非分区表转分区表，索引转换为自己设定规则

drop table no_part

建非分区表：

create table NO_PART2 as select * from user_objects where object_id is not null;

创建非分区表索引：

create index idx_oid2 on NO_PART2( object_id );create index idx_ctime_oname2 on NO_PART2( created, object_name );create bitmap index idx_b_otype2 on NO_PART2(object_type);

转换当前表为分区以 object_id 字段, interval分区表，update indexes 时，自己指定local 或global选项：

alter table NO_PART2 modifypartition by range (object_id) interval (5)(partition p1 values less than (73527)) onlineupdate indexes( idx_oid2 local,idx_ctime_oname2 global partition by range (created)(partition ix2_p1 values less than (date '-05-01'),partition ix2_p2 values less than (maxvalue)));

查看表索引

SELECT index_name, column_name, descend, COLUMN_POSITIONFROM user_ind_columnsWHERE table_name = 'NO_PART2';

查看索引状态

select table_name, index_name, status, blevel, leaf_blocksfrom user_Indexeswhere table_name = 'NO_PART2';

可以看到正常~