查看: 506|回复: 3

[每日一题] PL/SQL Challenge 每日一题:2019-6-6 范围分区

[复制链接]
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2019-6-13 05:23 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处:
http://www.plsqlchallenge.com/

作者:Chris Saxon

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

你创建了如下的表来存储交易明细:

create table qz_transactions (
  transaction_id     integer not null primary key,
  transaction_value  number(10, 2) not null,
  processed_datetime date
) ##REPLACE##

一开始交易是未处理的,所以processed_datetime为NULL。

你想要在processed_datetime上对这张表分区,并且当你设置processed_datetime的时候允许数据移动到合适的分区。

下列哪些选项可以用来取代上述的 ##REPLACE##,使得下列的INSERT和UPDATE都能成功:

insert into qz_transactions (
  transaction_id, transaction_value, processed_datetime
) values (
  1, 100, null
);

update qz_transactions
set    processed_datetime = date'2019-04-01'
where  transaction_id = 1;

(A)
partition by range ( processed_datetime ) (
  partition p2019 values less than ( date'2019-01-01' ),
  partition p2020 values less than ( date'2020-01-01' ),
  partition pmax values less than ( maxvalue )
);

(B)
partition by range ( processed_datetime ) (
  partition p2019 values less than ( date'2019-01-01' ),
  partition p2020 values less than ( date'2020-01-01' ),
  partition pmax values less than ( maxvalue )
) enable row movement;

(C)
partition by range ( processed_datetime ) (
  partition p2019 values less than ( date'2019-01-01' ),
  partition pnull values less than ( null )
) enable row movement;

(D)
partition by range ( processed_datetime )
  interval ( interval '1' year ) (
  partition p2019 values less than ( date'2019-01-01' ),
  partition pmax values less than ( maxvalue )
) enable row movement;

(E)
enable row movement;

alter table qz_transactions add
  processed_year integer as (
    coalesce (
      extract ( year from processed_datetime ),
      2000
    )
  );
  
alter table qz_transactions modify
  partition by range ( processed_year )
  interval ( 1 ) (
    partition p2019 values less than ( 2020 )
  );
认证徽章
论坛徽章:
0
发表于 2019-6-13 12:23 | 显示全部楼层
答案是B
A:修改分区列的时候,如果列值会影响到数据所在的分区变动,必须要在表定义上加上enable row movement关键字;
B:insert语句插入null值,首先放入pmax分区中,update之后,会移动到p2020分区中;正解
C:分区边界不能是null
D:maxvalue不能用在interval子句中,interval子句本来就是为了在新加入数据自动扩展分区的,加上maxvalue多次一举;
E:修改普通表为分区表方法错误,可以使用在线重定义。

使用道具 举报

回复
论坛徽章:
469
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2019-6-13 13:36 | 显示全部楼层

答案:BE

A: 第一个插入,NULL 好像视为无限大,被插到 pmax 分区了; UPDATE 后,行应该被归为 p2020 分区,但因没有enable row movement
   报错 ORA-14402: updating partition key column would cause a partition change
   
B: 行移动主要应用于分区表。 它允许行能够实现跨分区移动。表的默认状态下,行移动是disable的,在update中,你不能移动一个行
   纠正了A
   
C: partition pnull values less than ( null ) ,这个本意估计是存储processed_datetime 为NULL的分区,但这个不符合语法
   报错 ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE
   事实上,像A中解释的,NULL 好像视为无限大,被插到 pmax 分区了
   
D: 报错 ORA-14761: MAXVALUE [sub]partition cannot be specified for INTERVAL [sub]partitioned objects.
   即指定了分区或子分区为间隔分区,maxvalue就不能再被指定
   如果去掉 maxvalue,  如下可以创建
   create table qz_transactions (
  transaction_id     integer not null primary key,
  transaction_value  number(10, 2) not null,
  processed_datetime date
) partition by range ( processed_datetime )
  interval ( interval '1' year ) (
  partition p2019 values less than ( date'2019-01-01' )
) enable row movement;

  但是,
  insert into qz_transactions (
  transaction_id, transaction_value, processed_datetime
) values (
  1, 100, null
);
NULL 视为无限大,又找不到分区
报错:ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions
   
E:  给表先启用行移动 enable row movement,  
    再定义一个基于时间 processed_datetime 的虚拟列 processed_year,保存年份,如果字段processed_datetime 为NULL,则 processed_year = 2000
        最后,再修改表为间隔分区
        当插入为NULL,processed_year = 2000,所有数据在分区 p2019 中
        UPDATE 后,date'2019-04-01' 的年份不变,2019,所有还是在分区 p2019中

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2019-6-14 04:26 | 显示全部楼层
答案BE, 3楼得奖。

A: processed_datetime为空的行会进入到maxvalue分区。但是行迁移在这张表上被屏蔽了!所以修改这个列不能改变这个行所在的分区,UPDATE语句会报错:
"ORA-14402: updating partition key column would cause a partition change".
B: pmax分区的上限是maxvalue。这比其他所有的分区上限值都更大,包括NULL。所以当你插入了NULL值,它会进入这个分区,将processed_datetime重新设置后则会将行移动到适当的分区。

C: 你不能将NULL用作范围分区的边界。所以建表会报错:
"ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE".

D: 在使用间隔分区(interval partitioning)时,你不能将maxvalue用作分区边界。所以建表会报错:
"ORA-14761: MAXVALUE [sub]partition cannot be specified for INTERVAL [sub]partitioned objects."

E: 这会增加一个虚拟列,将processed_datetimes的NULL值映射成2030年。然后使用了12.2的分区语句将表在此列上进行分区。因为分区键再也不会存储NULL值,你可以使用间隔分区(interval partitioning)分区。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表