select count(*), select count(0), select count(1)

最新推荐文章于 2023-11-14 10:29:17 发布

cmeggutac575152728

最新推荐文章于 2023-11-14 10:29:17 发布

阅读量178

点赞数

在统计表的行数时候，经常用到 select count(*),

然而对于行数很多的大表，这样的查询速度将会很慢。因为这样的查询对表的每一行都会进行每个列的扫描。

比较快的办法是 select count(0), 这样每一行就只是扫描行头信息。

以上2种都是进行的全表扫描。

更快的是 select count(唯一索引列)，这样的查询会走索引。

SYS@101_RP%NPE> set autotrace on;
SYS@101_RP%NPE> select count(PHASE_NUM) from OCMLM1;

COUNT(PHASE_NUM)
----------------
2442325

Elapsed: 00:00:03.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1445308463

----------------------------------------------------------------------------------
| Id | Operation             | Name     | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     6 | 1458   (6)| 00:00:18 |
|   1 | SORT AGGREGATE       |          |     1 |     6 |            |          |
|   2 |   INDEX FAST FULL SCAN| OCMLM1_P | 2505K|    14M| 1458   (6)| 00:00:18 |
----------------------------------------------------------------------------------

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

SYS@101_RP%NPE> select count(0) from OCMLM1;

COUNT(0)
----------
2442325

Elapsed: 00:30:23.14

Execution Plan
----------------------------------------------------------
Plan hash value: 36564791

---------------------------------------------------------------------
| Id | Operation          | Name   | Rows | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |   250K (1)| 00:50:10 |
|   1 | SORT AGGREGATE    |        |     1 |            |          |
|   2 |   TABLE ACCESS FULL| OCMLM1 | 2505K|   250K (1)| 00:50:10 |
---------------------------------------------------------------------

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

OCMLM1 有290个列，对于这样的大表做 select count*) 是致命的操作！

所以，查看表的记录数前最好 desc 看下列数，然后再查下有没有唯一索引。

SELECT   c.constraint_name,
           c.index_owner,
     c.index_name,
           c.constraint_type,
           l.column_name,
           l.POSITION,
           c.r_owner,
           c.r_constraint_name,
           SUBSTR (c.status, 1, 1) AS status,
           DECODE (c.validated, 'NOT VALIDATED', 'N', 'Y') AS validated
FROM     dba_cons_columns l, dba_constraints c
WHERE    c.table_name = UPPER ('$3')
AND      c.owner = UPPER ('$2')
AND      c.constraint_name = l.constraint_name
AND      c.owner = l.owner
ORDER BY c.index_name, l.POSITION;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11976525/viewspace-669819/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11976525/viewspace-669819/