♣题目 部分
分区表在查询时如何优化？
♣答案部分
有分区表建表语句如下所示：
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)
作者：李华荣。
