我正在使用Oracle数据库 “ Oracle数据库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,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,'YYYYMMDD')
WHERE ROW1 = 1