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