oracle跳过检查建索引,oracle - 使用PARALLEL提示时Oracle跳过索引 - 堆栈内存溢出

我正在使用Oracle数据库“ Oracle Database 12c企业版12.1.0.2.0 64位”

我正面临一种行为，我不知道这是对还是错。

例如下面的查询

SELECT *

FROM (SELECT x, y, z, ROW_NUMBER() OVER (PARTITION BY x ORDER BY last_date DESC) ROW1

FROM HHH

WHERE s = 0

AND v_Date <= TO_DATE('20191110','YYYYMMDD')

AND t_Date >= TO_DATE('20191110','YYYYMMDD')

WHERE ROW1 = 1

我创建了一个索引，如下所示：

CREATE INDEX IDX_HHH_S_V_T_DATE ON HHH (S, v_date desc, t_date desc) compute statistics

优化器总是选择该索引，但是当我提到“并行”提示时：

SELECT *

FROM (SELECT /*+ PARALLEL(8) */ x, y, z, ROW_NUMBER() OVER (PARTITION BY x ORDER BY last_date DESC) ROW1

FROM HHH

WHERE s = 0

AND v_Date <= TO_DATE('20191110','YYYYMMDD')

AND t_Date >= TO_DATE('20191110','YYYYMMDD')

WHERE ROW1 = 1

优化器选择跳过该索引。

我尝试过的解决方案仍然相同：

我将表更改为平行8

我将索引更改为并行8

当尝试通过“ INDEX”提示强制优化器使用索引时：

SELECT *

FROM (SELECT /*+ PARALLEL(8) INDEX(HHH (IDX_HHH_S_V_T_DATE))*/ x, y, z, ROW_NUMBER() OVER (PARTITION BY x ORDER BY last_date DESC) ROW1

FROM HHH

WHERE s = 0

AND v_Date <= TO_DATE('20191110','YYYYMMDD')

AND t_Date >= TO_DATE('20191110','YYYYMMDD')

WHERE ROW1 = 1