Whether Oracle count (*) is indexed

In normal work, count (*) is frequently used and determines whether indexes are used. This has a significant impact on performance! But not all count (*) can be indexed! Note:

Create table t3

(

Sid number not null primary key,

Sno number,

Sname varchar2 (10)

)

Tablespace test;

Declare

Maxrecords constant int: = 100000;

I int: = 1;

Begin

For I in 1 .. maxrecords loop

Insert into t3 values (I, I, 'ocpyang ');

End loop;

Dbms_output.put_line ('data entered successfully! ');

Commit;

End;

/

Declare

Maxrecords constant int: = 200000;

I int: = 100001;

Begin

For I in 100001 .. maxrecords loop

Insert into t3 (sid, sname) values (I, 'ocpyang ');

End loop;

Dbms_output.put_line ('data entered successfully! ');

Commit;

End;

/

Create index index_sno on t3 (sno );

Exec dbms_stats.gather_table_stats ('sys ', 't3', cascade => TRUE );

***********

1. count

***********

SQL> set autotrace traceonly explain stat;

SQL> select count (*) from t3;

Execution Plan

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

Plan hash value: 463314188

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

| Id | Operation | Name | Rows | Cost (% CPU) | Time |

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

| 0 | select statement | 1 | 2 (0) | 00:00:01 |

| 1 | sort aggregate | 1 |

| 2 | table access full | T3 | 82 | 2 (0) | 00:00:01 |

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

Note

-----

-SQL plan baseline "SQL _PLAN_27gnhfjz9qahj14fae16c" used for this statement

Statistics

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

55 recursive CILS

38 db block gets

521 consistent gets

19 physical reads

14676 redo size

527 bytes sent via SQL * Net to client

520 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

-- Implemented through full table scan.

SQL> select count (*) from t1 where sid is not null;

Execution Plan

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

Plan hash value: 1551730033

Bytes --------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

Bytes --------------------------------------------------------------------------------------

| 0 | select statement | 1 | 13 | 68 (0) | 00:00:01 |

| 1 | sort aggregate | 1 | 13 |

| 2 | index fast full scan | SYS_C0023596 | 85899 | 1090K | 68 (0) | 00:00:01 |

Bytes --------------------------------------------------------------------------------------

Note

-----

-Dynamic sampling used for this statement (level = 2)

-SQL plan baseline "SQL _PLAN_4xztry6akgpqqf2d247c8" used for this statement

Statistics

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

4 recursive cballs

0 db block gets

310 consistent gets

0 physical reads

0 redo size

527 bytes sent via SQL * Net to client

520 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

-- Implemented through the index.