....
So anything where the optimizer thinks it is best to scan all of the blocks in the table will yield a full table scan, eg
select count(nullable_column) from T;
must visit every single block in the table, so a full table scan would be a logical choice.
....
demo@ORA12C> create table t as select * from all_objects;
Table created.
demo@ORA12C> set linesize 71
demo@ORA12C> desc t
Name Null? Type
----------------------------------- -------- -------------------------
OWNER NOT NULL VARCHAR2(128)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
demo@ORA12C> set linesize 151
demo@ORA12C> create index t_idx1 on t(data_object_id);
Index created.
demo@ORA12C> select count(*),count(data_object_id) from t;
COUNT(*) COUNT(DATA_OBJECT_ID)
---------- ---------------------
82033 359
1 row selected.
demo@ORA12C> set serveroutput off
demo@ORA12C> select /*+ gather_plan_statistics */ count(data_object_id) from t;
COUNT(DATA_OBJECT_ID)
---------------------
359
1 row selected.
demo@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID d08vqmbyt3cpg, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(data_object_id) from t
Plan hash value: 780537734
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| T_IDX1 | 1 | 82033 | 359 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------
14 rows selected.
demo@ORA12C>