There are two parts to the calculation.
First note that Oracle has applied a character-set converion to your V
predicate:
filter(SYS_OP_C2C("A"."V")=:N1)
So "function of something is bind value" - Oracle uses one of its guesses
here, namely 1% (in real life if you had optimizer_dynamic_sampling at
level 3 Oracle would sample some rows to avoid the need for guessing).
Note that the table estimate is 452K, which is 1% of the 45M rows in the
table.
Secondly you have rownum<= 50 in the outer query, and the optimizer has
allowed for that (it's optimising for first_rows(50) as a consequence).
It knows you want 50 rows in the result, it "knows" you have a predicate
that filters out all but 1% of the data it picks up from the table.
Therefore it knows it has to allow for 5,000 rows from the index range scan
in order for the 1% survival rate to give you 50 rows in the result.
Regards
Jonathan Lewis
On Jan 19, 2021, at 1:10 PM, Tahon@xxxxxxxxxxxxx wrote:
Plan hash value: 2577482738
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|
Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
3736 (100)| 0 |00:00:00.01 | 1 |
|* 1 | COUNT STOPKEY | | 1 | |
| | 0 |00:00:00.01 | 1 |
| 2 | VIEW | | 1 | 50 | 1300 |
3736 (1)| 0 |00:00:00.01 | 1 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 452K| 20M|
3736 (1)| 0 |00:00:00.01 | 1 |
|* 4 | INDEX RANGE SCAN | PK_ID | 1 | 5000 |
| 14 (0)| 0 |00:00:00.01 | 1 |
----------------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
2 - :2 (NUMBER): 50
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=:N2)
3 - filter(SYS_OP_C2C("A"."V")=:N1)
4 - access("A"."ID">1)
So for some reason the optimizer thinks it will get 5000 rows from the
index range scan using ID > 1.
When using following query, the optimizer does know that ID > 1 gives
about 45 million rows:
