In oracle, 12c consumes more than 10 GB index return tables.

The problem is as follows:
In 12c, we tested two cases:
The first is to add hint, so that the 12c execution plan is similar to 10 GB, but because of the 12c nlj_batching, a nestloop is added. However, the execution plan is essentially the same, and the index S_CONTACT_X_U1 returns the table query.
The second is to use 10g outline hint, OFE = 10g, and the execution plan is exactly the same.

However, we found that, no matter which case in 12c, the index PA_S_SRV_REQ_1_X of the drive table S_SRV_REQ returned results are significantly different from the full index scan results?

 

12c-first case:

12c-Case 2:

10 GB database:

###################################

In addition, according to the filter conditions, 10 GB and 12c return results are similar. And the consumption is almost the same!

12c:

10g:

(1) first, there is a problem with the SQL logic. See the attachment in my attachment 10g.txt, which contains the complete SQL text.

 

The main function of SQL is paging. The customer writes rownum and order by in a link. So this is wrong.

(2) Even if A logic error occurs, why is A-rows small when 10 GB of data runs fast?
This is because rownum <= 10 in the SQL statement, 10 rows of records returned for 10 GB, and 4 rows of records returned for 12c.
In the case of rownum <= 10, 10 rows are enough after 264 rows are scanned during 10 GB of scanning, so we can return the results. In the case of 12c, the index is scanned, there are only four rows, that is, 10 rows have not been scanned from the beginning to the end. Therefore, you need to scan the entire index. Therefore, the consumption is high.
In fact, I changed the SQL statement to rownum <= 4, which can return more records in 3 seconds. Or, if I change the SQL statement to rownum <= 8000, it will be slow to run in 10 GB.

 

(3) after the SQL logic is corrected, the customer's SQL still runs slowly for 15 minutes and finds that the index used by the hint written by the customer is not the optimal execution plan, after using the acct_open_dt field index (or you do not need to specify it, oracle's own CBO will select this index), you also need to move 1/3 to the right and the result will come out in less than one second. (Note: If 1/3 is not moved to the right, it will still be slow .)

In this case, we can learn the following knowledge points:
1. To analyze the logic of SQL statements, you cannot simply look at the execution plan.
2. Pay attention to the count stopkey and a-rows values. Otherwise, you may wonder why the system stops scanning 264 rows at 10 GB.
3. The other party said that the data source is the same and cannot be completely believed. The preceding example shows that the actual data source of the same statement is still different. One returns 10 rows and the other returns 4 rows. This causes a large difference in execution efficiency.
4. There is an operational relationship between the index fields. You need to place the calculation to the right. To use indexes normally.