优化器选择完整分区扫描而不是分区索引扫描

Question

我在dt列上有一个表(table1(每月分区。我已经在dt列上创建了一个本地索引。当我运行下面的查询时，我看到优化器正在进行完整的分区扫描，而不是使用dt列上的索引。

WITH 
A AS 
(
Select * from table1 
WHERE  
EXISTS (SELECT u_id FROM table2
WHERE u_id=UPPER('ABC'))
)
SELECT DISTINCT 
A.id,
A.dt
FROM
A
WHERE 
A.dt BETWEEN timestamp '2022-04-01 00:00:00' AND timestamp '2022-04-01 23:59:59.999000000'

Explain plan
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                   |   755K|    67M|       | 34447   (1)| 00:00:02 |       |       |
|   1 |  HASH UNIQUE             |                   |   755K|    67M|    75M| 34447   (1)| 00:00:02 |       |       |
|*  2 |   FILTER                 |                   |       |       |       |            |          |       |       |
|   3 |    PARTITION RANGE SINGLE|                   |   755K|    67M|       | 18298   (1)| 00:00:01 |     5 |     5 |
|*  4 |     TABLE ACCESS FULL    | TABLE1            |   755K|    67M|       | 18298   (1)| 00:00:01 |     5 |     5 |
|*  5 |    INDEX UNIQUE SCAN     | SYS_C0099684      |     1 |    15 |       |     0   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------

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

2 - filter( EXISTS (SELECT 0 FROM "TEST"."TABLE2" 
"TABLE2" WHERE "U_ID"=U'ABC'))
4 - filter("TABLE1"."DT"<=TIMESTAMP' 2022-04-02 23:59:59.999000000' AND 
"TABLE1"."DT">=TIMESTAMP' 2022-04-02 00:00:00.000000000')
5 - access("U_ID"=U'ABC')

Answer 1

首先，我不确定INDEX RANGE SCAN在这里会更好：我们不知道你的平均行长度，所以使用多块读取的全分区扫描可能比索引和分区的顺序单块读取(通过rowid访问表(更快，尤其是考虑到你需要1/30部分的分区。只需考虑以下示例：

• 分区大小=100 GB
• 分区中的行=4亿
• 平均行长度=100GB/400M=~250字节

由于我们需要1天，我们应该得到约400M/30=13.3M行。这意味着在索引范围扫描的情况下，我们只需要13.3M的单块读取来获得列"0"；id"；从索引中按rowid从表中读取(按rowid访问表(，甚至不计算索引扫描所需的单个块读取。假设您在该系统中的平均单块读取时间为3ms，因此仅在索引访问的情况下读取表行就需要超过13.3M*3ms=40000秒。现在考虑完整分区扫描。如果您的multiblock_read_count=64(如果我没有记错的话，默认情况下是128(，块大小默认为8kB，那么您将需要执行100GB/(64*8kB(=195k多块读取。假设您的多块读取时间为8ms，则完整分区扫描只需~1500秒