Oracle 分区修剪

分区修剪是如何工作的？我们是否需要在查询中显式指定任何内容来实现此目的，或者它会自动选择特定的分区？

使用分区修剪，Oracle仅访问与 “从” 和 “哪里” 子句匹配的分区。有两种方法可以启用此功能:

-在表名之后显式指定分区
-在where子句中的分区列上有谓词

所以如果你的表在c1上分区，你的查询是:

select * from t where c2 = 1;

不能修剪。这是因为c2 = 1可以在任何分区中。但如果你的查询是:

select * from t where c1 = 1;

c1 = 1的行只能存在于一个分区中。所以Oracle可以忽略所有其他分区。

例如:

SQL> create table t (
  2    x int,
  3     y int
  4  ) partition by range (x) interval (10) (
  5    partition p0 values less than (10)
  6  );

Table created.

SQL>
SQL> insert into t
  2    with rws as (
  3      select rownum x from dual connect by level <= 1000
  4    )
  5      select x, x from rws;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 't');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace trace exp
SQL> select * from t where x = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |     6 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |     6 |   274   (0)| 00:00:01 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL    | T    |     1 |     6 |   274   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=1)

SQL> select * from t where x between 45 and 69;

Execution Plan
----------------------------------------------------------
Plan hash value: 1571388083

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    26 |   208 |   820   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|      |    26 |   208 |   820   (1)| 00:00:01 |     5 |     7 |
|*  2 |   TABLE ACCESS FULL      | T    |    26 |   208 |   820   (1)| 00:00:01 |     5 |     7 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"<=69 AND "X">=45)

SQL> select * from t where y = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3557914527

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     8 | 27538   (1)| 00:00:02 |       |       |
|   1 |  PARTITION RANGE ALL|      |     1 |     8 | 27538   (1)| 00:00:02 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | T    |     1 |     8 | 27538   (1)| 00:00:02 |     1 |1048575|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("Y"=1)

SQL> select * from t partition (p0);

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     9 |    54 |   274   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     9 |    54 |   274   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS FULL    | T    |     9 |    54 |   274   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------

注意Pstart和Pstop值。针对Y的查询包括所有这些。但是其他的都仅限于特定的分区。

有关更多阅读，请参见:

http://docs.oracle.com/database/121/VLDBG/GUID-E677C85E-C5E3-4927-B3DF-684007A7B05D.htm#VLDBG00401