oracle 19C count（）不走索引一例

SQL> alter session set container= pdb1;

SQL> alter pluggable database pdb1  open;

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     3 PDB1               READ WRITE NO
 

drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create index idx1_object_id on t(object_id);
set autotrace on

SQL> set autotrace on
SQL> select count(*) from t;

  COUNT(*)
----------
     72356

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Cost (%CPU)| Time      |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    1 |   383   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |    1 |           |      |
|   2 |   TABLE ACCESS FULL| T      | 78457 |   383   (1)| 00:00:01 |
-------------------------------------------------------------------

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

Statistics
----------------------------------------------------------
     48  recursive calls
      0  db block gets
       1541  consistent gets
      0  physical reads
      0  redo size
    552  bytes sent via SQL*Net to client
    384  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      4  sorts (memory)
      0  sorts (disk)
      1  rows processed

办法1 --为啥用不到索引，因为索引不能存储空值，所以加上一个is not null,再试验看看 ? 

SQL> set linesize 360;
SQL> select count(*) from t where object_id is not null;

  COUNT(*)
----------
     72356

Execution Plan
----------------------------------------------------------
Plan hash value: 1296839119

----------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    13 |    45     (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |            |     1 |    13 |        |           |
|*  2 |   INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 78457 |   996K|    45     (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID" IS NOT NULL)

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

Statistics
----------------------------------------------------------
      0  recursive calls    
      0  db block gets
    168  consistent gets   
      0  physical reads
      0  redo size
    552  bytes sent via SQL*Net to client
    412  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> 
办法2  SQL> select count(OBJECT_ID) from t ;

办法3 SQL> alter table t modify object_id number not null;  （当字段中尚未有空值时）

办法4 如果OBJECT_ID本身就是主键，就不用改了