1

I'm using Oracle Database "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 64bit"

I'm facing a behavior I don't know if its right or there is something wrong with that.

for example the below query

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

Execution Plan 1

I created an Index on as below:

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

The optimizer always choose this index, but when I mentioned "Parallel" hint:

 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

Execution Plan 2

The optimizer choose to skip this index.

Solutions I tried and still the same:

  • I altered the table to parallel 8
  • I altered the index to parallel 8

When trying to force the optimizer to use the index using "INDEX" hint:

 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

Execution Plan 3

Mohamed El-Touny
  • 347
  • 1
  • 4
  • 14
  • You should post the [execution plans](https://stackoverflow.com/a/34975420/4808122) of both queries. Basically it seems you make a large *index range scan* in serial execution and switching to parallel Oracle *thinks* the `full scan` will be better. You can hint the index access in parallel mode and compare the cost to verify it. – Marmite Bomber Nov 12 '19 at 08:47
  • Looks like Oracle optimizer estimates using the index in case of parallel is an overhead. is the driving table partitioned ? Also is the query with parallel hint not performing well , why do you want it to use the index ? try giving a hint with the index name to see if that flips the plan. – Infinite Nov 12 '19 at 09:03
  • Thanks Marmite, I'll edit the post and put the 2 execution plan. – Mohamed El-Touny Nov 12 '19 at 09:33
  • Thanks Sanchit, No, This table is not partitioned. – Mohamed El-Touny Nov 12 '19 at 09:39
  • The provided link in the first comment shows you how to post the execution plans **as text** – Marmite Bomber Nov 12 '19 at 15:15
  • Due to limitation in access I couldn't get the plan in text, I captured the screen with my mobile. I think its clear and readable. – Mohamed El-Touny Nov 12 '19 at 20:02

1 Answers1

1

There is nothing wrong with that behavior. You forced the Oracle to use DOP of 8. Assuming the DOP of 8, it considered 2 access paths - parallel index (range|full|fast full) scan, and parallel full table scan. Rightly or wrongly, it decided the full table scan would be less expensive.

Note that you haven't really forced the index scan in your last example. Your hint is not specified correctly. The correct hint would be INDEX(HHH IDX_HHH_S_V_T_DATE) (no nested brackets).

Kirill Leontev
  • 10,641
  • 7
  • 43
  • 49