I’m using oracle 19c
I have a table containing tens of millions of records.
I have the following query

 select /*+ gather_plan_statistics */  id  from MY_TABLE mytable WHERE
 mytable.STARTDATE <= to_date('20032024','DDMMYYYY') AND mytable.ENDDATE >= to_date('20032024','DDMMYYYY');

It gives the following plan

SQL_ID  98f475hv01xwg, child number 0
-------------------------------------
select   /*+ gather_plan_statistics */           id from 
MY_TABLE mytable  WHERE  mytable.STARTDATE <= 
to_date('20032024','DDMMYYYY') AND mytable.ENDDATE >= 
to_date('20032024','DDMMYYYY')
 
Plan hash value: 1075412610
 
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                       |      1 |        |     50 |00:00:00.02 |      28 |     15 |       |       |          |
|*  1 |  TABLE ACCESS STORAGE FULL| MY_TABLE |      1 |     33M|     50 |00:00:00.02 |      28 |     15 |  1028K|  1028K| 3096K (0)|
-------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage(("MYTABLE"."ENDDATE">=TO_DATE(' 2024-03-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "MYTABLE"."STARTDATE"<=TO_DATE(' 
              2024-03-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
       filter(("MYTABLE"."ENDDATE">=TO_DATE(' 2024-03-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "MYTABLE"."STARTDATE"<=TO_DATE(' 
              2024-03-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Why is the expected rows (33M) much bigger than the actual rows?from where oracle is getting this statistics?

I have than decided to create an index on the COLUMN ENDDATE:

create index MY_TABLE_TEST on MY_TABLE(ENDDATE);

I have run the same query but oracle is still using full table access!
I have than added a hint to force my new index:

 select  /*+ INDEX(mytable,MY_TABLE_TEST) */
      /*+ gather_plan_statistics */
 id  from MY_TABLE mytable
 
WHERE  mytable.STARTDATE <= to_date('20032024','DDMMYYYY')
AND mytable.ENDDATE >= to_date('20032024','DDMMYYYY');

And I got the following plan:

SQL_ID  0knxk82yhkruw, child number 0
-------------------------------------
select /*+ INDEX(mytable,MY_TABLE_TEST) */  /*+ 
gather_plan_statistics */            id  from 
MY_TABLE mytable  WHERE  mytable.STARTDATE <= 
to_date('20032024','DDMMYYYY') AND mytable.ENDDATE >= 
to_date('20032024','DDMMYYYY')
 
Plan hash value: 2267489923
 
-----------------------------------------------------------------------------------
| Id  | Operation                           | Name                       | E-Rows |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                            |        |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE      |     33M|
|*  2 |   INDEX RANGE SCAN                  | MY_TABLE_TEST |     33M|
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("MYTABLE"."STARTDATE"<=TO_DATE(' 2024-03-20 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))
   2 - access("MYTABLE"."ENDDATE">=TO_DATE(' 2024-03-20 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

I was surprised :

• Why I’m no longer getting A-Rows column like in the first query
• Why is my index range scan expecting the same 33M records although in reality, it should return only few thousands of records-this is a new index I was expecting to get accurate statistics