Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table DEMO as select 1 A, 1 B, 1 C from dual union all select 1 A, 1 B , 1 C from dual;
2 rows affected

insert into DEMO select 1,1,1 from DEMO a, DEMO b, DEMO c, DEMO d, DEMO e, DEMO f, DEMO g, DEMO h;
256 rows affected

insert into DEMO values (2,0,0);
1 rows affected

insert into DEMO select a.A, a.B, a.C from DEMO a;
259 rows affected

create index DEMO_AB on DEMO(A,B);
begin dbms_stats.delete_table_stats('','DEMO'); end;
/
1 rows affected

select A,count(*) from DEMO group by A;

A
COUNT(*)

2
2

1
516

select /*+ gather_plan_statistics */ sum(A) from DEMO where A=2;

SUM(A)

4

select plan_table_output from v$sql,dbms_xplan.display_cursor(sql_id,child_number,format=>'allstats last') where sql_text='select /*+ gather_plan_statistics */ sum(A) from DEMO where A=2';

PLAN_TABLE_OUTPUT

SQL_ID  cw3ag7gkjpfb4, child number 0

-------------------------------------

select /*+ gather_plan_statistics */ sum(A) from DEMO where A=2

Plan hash value: 2842824019

---------------------------------------------------------------------------------------

| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |         |      1 |        |      1 |00:00:00.01 |       2 |

|   1 |  SORT AGGREGATE   |         |      1 |      1 |      1 |00:00:00.01 |       2 |

|*  2 |   INDEX RANGE SCAN| DEMO_AB |      1 |      2 |      2 |00:00:00.01 |       2 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("A"=2)

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

select /*+ gather_plan_statistics */ sum(B) from DEMO where A=2;

SUM(B)

0

select plan_table_output from v$sql,dbms_xplan.display_cursor(sql_id,child_number,format=>'allstats last') where sql_text='select /*+ gather_plan_statistics */ sum(B) from DEMO where A=2';

PLAN_TABLE_OUTPUT

SQL_ID  65cjxtvwydbpc, child number 0

-------------------------------------

select /*+ gather_plan_statistics */ sum(B) from DEMO where A=2

Plan hash value: 2842824019

---------------------------------------------------------------------------------------

| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |         |      1 |        |      1 |00:00:00.01 |       2 |

|   1 |  SORT AGGREGATE   |         |      1 |      1 |      1 |00:00:00.01 |       2 |

|*  2 |   INDEX RANGE SCAN| DEMO_AB |      1 |      2 |      2 |00:00:00.01 |       2 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("A"=2)

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

select /*+ gather_plan_statistics */ sum(C) from DEMO where A=2

SUM(C)

0

select plan_table_output from v$sql,dbms_xplan.display_cursor(sql_id,child_number,format=>'allstats last') where sql_text='select /*+ gather_plan_statistics */ sum(C) from DEMO where A=2'

PLAN_TABLE_OUTPUT

SQL_ID  1yukcgvh7d3x3, child number 0

-------------------------------------

select /*+ gather_plan_statistics */ sum(C) from DEMO where A=2

Plan hash value: 1078896418

----------------------------------------------------------------------------------------------------------

| Id  | Operation                            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                     |         |      1 |        |      1 |00:00:00.01 |       3 |

|   1 |  SORT AGGREGATE                      |         |      1 |      1 |      1 |00:00:00.01 |       3 |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEMO    |      1 |      2 |      2 |00:00:00.01 |       3 |

|*  3 |    INDEX RANGE SCAN                  | DEMO_AB |      1 |      2 |      2 |00:00:00.01 |       2 |

----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("A"=2)

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)