Re: simple query with strange cardinality estimate

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_at_freelists.org 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:
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l