Oracle LIKE predicate and cardinality estimations

A new blog post on the Databases at CERN blog about the optimizer estimations with a LIKE predicate: https://db-blog.web.cern.ch/blog/franck-pachot/2018-11-oracle-predicate-and-cardinality-estimations.

Or why the cardinality estimation here is 1346 objects like ‘DBA_OBJECTS’ rather than 2:

SQL> select count(*) from OBJ$ where name LIKE 'DBA_OBJECTS';
COUNT(*)
----------
2
Execution Plan
--------------------------------------------------------
Plan hash value: 2699154286
--------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 |
| 1 | SORT AGGREGATE | | 1 | 19 |
|* 2 | INDEX FAST FULL SCAN| I_OBJ2 | 1346 | 25574 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME" LIKE 'DBA_OBJECTS')

In summary, This LIKE ‘DBA_OBJECT’, because ‘_’ is a wildcard, is considered by the optimizer as a LIKE ‘DBA%’ for cardinality estimation.