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

  • 2019 年 10 月 11 日
  • 笔记

题目部分

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

答案部分

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

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/

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

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

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

VirMach 便宜 VPS

QNews

QNews