使用左外连接时 Oracle 分区修剪不起作用

Question

我有一个表，该表按数字列 (row_id) 进行列表分区，

TABLEA (ROW_ID NUMERIC(38), TB_KEY NUMERIC(38), ROW_DATA VARCHAR(20));

当我从没有连接的表中查询时，分区修剪有效:

SELECT A.* FROM TABLEA A
WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);

当我对 TableB 进行左外连接时，分区修剪失败

SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);

当我将左外连接更改为内连接时，分区修剪有效

SELECT A.* FROM TABLEA A
INNER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);

分区修剪有效当我对 TableB 进行左外连接并且不使用 IN 子句时

SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
WHERE ROW_ID = 123;

分区修剪有效当我对 TableB 进行左外连接并为 IN 子句使用静态值时

SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
WHERE ROW_ID IN (123, 345);

有人可以解释为什么左外连接会导致分区修剪失败，当我查询列时，该表是根据子查询的结果使用 IN 子句进行分区的吗？

Answer 1

Oracle 11g 的答案是肯定的，分区修剪工作正常。

在您的设置中有三种主要的访问模式，list partitioned 表TABLEA，让我们来了解一下它们。请注意，我正在使用最简单的陈述来说明行为。

使用相等谓词或 IN 列表中的键访问

最简单的情况是在分区键的相等谓词中使用文字:

SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
WHERE A.ROW_ID = 123;

这导致以下执行计划

-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |    51 |     4   (0)| 00:00:01 |       |       |
|*  1 |  HASH JOIN OUTER       |        |     1 |    51 |     4   (0)| 00:00:01 |       |       |
|   2 |   PARTITION LIST SINGLE|        |     1 |    38 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL   | TABLEA |     1 |    38 |     2   (0)| 00:00:01 |     2 |     2 |
|   4 |   TABLE ACCESS FULL    | TABLEB |     1 |    13 |     2   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------

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

   1 - access("A"."TB_KET"="B"."TB_KEY"(+))
   3 - filter("A"."ROW_ID"=123)

仅访问 TABLEA 的相关分区(此处为分区 #2)- 请参阅列 Pstart 和 Pstop。

IN LIST

的情况有点复杂，但类似

SELECT A.* FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.TB_KET = B.TB_KEY
WHERE ROW_ID IN (123, 345);

-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |    51 |     4   (0)| 00:00:01 |       |       |
|*  1 |  HASH JOIN OUTER       |        |     1 |    51 |     4   (0)| 00:00:01 |       |       |
|   2 |   PARTITION LIST INLIST|        |     1 |    38 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  3 |    TABLE ACCESS FULL   | TABLEA |     1 |    38 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   4 |   TABLE ACCESS FULL    | TABLEB |     1 |    13 |     2   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------

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

   1 - access("A"."TB_KET"="B"."TB_KEY"(+))
   3 - filter("A"."ROW_ID"=123 OR "A"."ROW_ID"=345)

在这种情况下，可以访问更多分区，但只考虑那些包含 IN LIST 中的键的分区。

同样适用于使用绑定(bind)变量访问。

使用嵌套循环访问表中的键

更复杂的是两个表连接的情况。在对 TABLEB 中的每个键使用嵌套循环 连接时TABLEA 被访问。这意味着对于每个 key ，只有 key 所在的一个分区会被访问。

SELECT  A.* FROM TABLEA A
WHERE ROW_ID IN (SELECT ID FROM TABLEB WHERE DT_COL = SYSDATE);

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |     1 |    60 |     4  (25)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS            |        |     1 |    60 |     4  (25)| 00:00:01 |       |       |
|   2 |   SORT UNIQUE            |        |     1 |    22 |     2   (0)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS FULL     | TABLEB |     1 |    22 |     2   (0)| 00:00:01 |       |       |
|   4 |   PARTITION LIST ITERATOR|        |   100K|  3710K|     1   (0)| 00:00:01 |   KEY |   KEY |
|*  5 |    TABLE ACCESS FULL     | TABLEA |   100K|  3710K|     1   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------

再次有一个分区修剪KEY - KEY，所以只有键来自TABLEB的分区被访问，但是从嵌套的本质循环，一个分区可以访问多次(对于不同的 key )。

使用 HASH JOIN 访问表中的键

使用 HASH JOIN 是最复杂的情​​况，分区修剪必须在连接开始之前发生。这是 Bloom Filter在工作中。

它是如何工作的？在扫描 TABLEB 之后，Oracle 从中知道了所有相关的键，这些键可以映射到相关的分区和一个这些分区的 Bloom Filter (BF) 已创建(操作 3 和 2)。BF 被传递到 TABLEA 并用于对其进行分区修剪(操作 4 和 5)。

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   100K|  5859K|     5  (20)| 00:00:01 |       |       |
|*  1 |  HASH JOIN RIGHT SEMI       |         |   100K|  5859K|     5  (20)| 00:00:01 |       |       |
|   2 |   PART JOIN FILTER CREATE   | :BF0000 |     1 |    22 |     2   (0)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS FULL        | TABLEB  |     1 |    22 |     2   (0)| 00:00:01 |       |       |
|   4 |   PARTITION LIST JOIN-FILTER|         |   100K|  3710K|     2   (0)| 00:00:01 |:BF0000|:BF0000|
|   5 |    TABLE ACCESS FULL        | TABLEA  |   100K|  3710K|     2   (0)| 00:00:01 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------

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

   1 - access("ROW_ID"="ID")
   3 - filter("DT_COL"=SYSDATE@!)

将 Pstart, Pstop :BFnnnn 视为布隆过滤器的标志。