【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);

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

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)

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)