在程序处理分页时,有时需要使用count(*)查询,而如果表很大的时候,用此方法无疑瓶颈所在,今天发现了一个解决办法就是如果这个表有唯一列的话(比如主键),那我们可以使用主键,强制走索引来缩短查询时间。
主键:id
索引:SYS_C0029630
SQL> select /*+ index(tt SYS_C0029630) */count(id) from tt;
COUNT(ID)
----------
75651
Execution Plan
----------------------------------------------------------
Plan hash value: 3525147495
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 213 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| SYS_C0029630 | 88573 | 213 (0)| 00:00:01 |
-------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
287 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> desc tt;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
NAME VARCHAR2(2)