【DB笔试面试472】分区表在查询时如何优化?

题目部分

分区表在查询时如何优化?

答案部分

有分区表建表语句如下所示:

CREATE TABLE TEST_RANGE_PARTITION_LHR
(
  OWNER         VARCHAR2(30),
  OBJECT_NAME   VARCHAR2(128),
  OBJECT_ID     NUMBER,
  CREATED       DATE,
  LAST_DDL_TIME DATE
)
PARTITION BY RANGE (CREATED)
(
  PARTITION PT1 VALUES LESS THAN (TO_DATE('2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
  PARTITION PT2 VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION PT3 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION PT4 VALUES LESS THAN (TO_DATE('2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION PT5 VALUES LESS THAN (MAXVALUE)
  );
INSERT INTO TEST_RANGE_PARTITION_LHR SELECT OWNER,OBJECT_NAME,OBJECT_ID,CREATED,LAST_DDL_TIME FROM DBA_OBJECTS;
COMMIT;
CREATE INDEX INS_AA ON TEST_RANGE_PARTITION_LHR (CREATED)LOCAL;
CREATE INDEX INS_DDA ON TEST_RANGE_PARTITION_LHR (OBJECT_NAME);

基于以上分区表和索引的创建语句完成如下题目:

(1) 如何判断一张表是否是分区表?

SELECT D.PARTITIONED,
       D.OWNER,
       D.TABLE_NAME,
       D.TABLE_LOCK,
       D.IOT_TYPE,
       D.TEMPORARY
FROM  DBA_TABLES D
WHERE  D.PARTITIONED = 'YES'; 

如果这个视图里的PARTITIONED列的值为YES,那么说明该表就是分区表。

(2) 如果需要查询2013年3月份的数据,那么请问SQL语句怎么写?要求单分区查询,且利用到CREATED列的索引。

LHR@TEST> SET AUTOT TRACE EXP STAT
LHR@TEST> SELECT * FROM TEST_RANGE_PARTITION_LHR PARTITION(PT3) A WHERE A.CREATED BETWEEN TO_DATE('20130301','YYYYMMDD') AND TO_DATE('20130331','YYYYMMDD');
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 678519743
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                          |     1 |   114 |     0   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                          |     1 |   114 |     0   (0)| 00:00:01 |     3 |     3 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST_RANGE_PARTITION_LHR |     1 |   114 |     0   (0)| 00:00:01 |     3 |     3 |
|*  3 |    INDEX RANGE SCAN                | INS_AA                   |     1 |       |     0   (0)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."CREATED">=TO_DATE(' 2013-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."CREATED"<=TO_DATE('
              2013-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
   - dynamic sampling used for this statement (level=2)

(3) 现在需要查询2013年10月份至2014年5月份的数据(包含201306和201405),请问SQL语句怎么写?要求不能涉及到全分区查询。

LHR@TEST> SET AUTOT TRACE EXP STAT
LHR@TEST> SELECT * FROM TEST_RANGE_PARTITION_LHR A WHERE A.CREATED BETWEEN TO_DATE('20130601','YYYYMMDD') AND TO_DATE('20140531','YYYYMMDD');
257952 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3540873190
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                          |   299K|    32M|  1039  (48)| 00:00:13 |       |       |
|   1 |  PARTITION RANGE ITERATOR|                          |   299K|    32M|  1039  (48)| 00:00:13 |     3 |     4 |
|*  2 |   TABLE ACCESS FULL      | TEST_RANGE_PARTITION_LHR |   299K|    32M|  1039  (48)| 00:00:13 |     3 |     4 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."CREATED">=TO_DATE(' 2013-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "A"."CREATED"<=TO_DATE(' 2014-05-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
   - dynamic sampling used for this statement (level=2)

本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。

About Me:小麦苗

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 作者博客地址:http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

● 题目解答若有不当之处,还望各位朋友批评指正,共同进步

本文分享自微信公众号 - DB宝(xiaomaimiaolhr)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-01-01

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

一文带你揭开Redis复制原理的神秘面纱

墨墨导读:本文在依托Redis主从环境下,针对访问的数据一致性进行分析,解开Redis复制原理的神秘面纱。‍

10420
来自专栏二狗的DBA之路

一种基于proxysql的数据脱敏思路

背景:我们这边给研发查数据的是通过phpmyadmin进行的,通常情况下研发人员查数据写法是 select * from db1.tb1 where id=xx...

13630
来自专栏二狗的DBA之路

MySQL数据全量导入PG的方法

下面会用到一个perl脚本来做数据格式的转换: https://github.com/ahammond/mysql2pgsql

9920
来自专栏二狗的DBA之路

postgres中mysql_fdw 扩展的使用

参考连接: https://www.percona.com/blog/2018/08/24/postgresql-accessing-mysql-as-a-da...

14320
来自专栏二狗的DBA之路

MongoDB中删除document的方法

> db.users.remove({z:'abc'}) 删除记录 delete from users where z="abc" 默认remove没有带选项t...

14240
来自专栏二狗的DBA之路

DBLE分库分表实战

OS版本: CentOS Linux release 7.6.1810 (Core) 

13930
来自专栏二狗的DBA之路

prometheus 统计MySQL 自增主键的剩余可用百分比

最近生产环境一套数据库因为疯狂写日志数据,造成主键值溢出的情况出现,因此有必要将这个指标监控起来。

8710
来自专栏二狗的DBA之路

PG中只读账号的授权操作

但是,只读账号稍微费事点,如果我们处理不好的话,每次新加表都要再执行一次对只读账号的重新授权操作。好在PG为我们考虑好了这个场景,也是有方法解决的。

10220
来自专栏二狗的DBA之路

windows上安装 MySQL-python包的方法

安装过程中,遇到很多问题,终于解决了,贴下我的解决步骤。也为了其它小伙伴们少采坑。

7620
来自专栏孟君的编程札记

Java SPI机制浅析

在平时开发项目的过程中,相信很多读者都看到过这样的目录,/META-INF/services目录,该目录下的文件名是接口的全称,其内容是具体的接口实现。这就是使...

7730

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励