Re: What's fast way to get total of records

From: Daniel Roy <danielroy10junk_at_hotmail.com>
Date: 26 Sep 2003 07:03:08 -0700
Message-ID: <3722db.0309260603.17dd078a@posting.google.com>

> I rather doubt there is one for Oracle.  If there is a primary key or
> otherwise not null index, it could be used in the count(*) and make
> it faster than a table scan.

I created a test case to see if Oracle would use an index to count the number of records, and it seems that we need to give a hint, otherwise it just uses a FTS. Note that I use CBO on 9.2:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> create table tst (x char(1));

Table created.

Elapsed: 00:00:01.02
SQL> set timing off
SQL> truncate table plan_table;

Table truncated.

SQL> explain plan for
2 select count(*) from tst;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 1 | | 3
(34)|
| 1 | SORT AGGREGATE | | 1 | |
|
| 2 | TABLE ACCESS FULL | TST | 41 | | 3
(34)|

8 rows selected.

SQL> alter table tst add primary key(x);

Table altered.

SQL> truncate table plan_table;

Table truncated.

SQL> explain plan for
2 select count(*) from tst;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 1 | | 3
(34)|
| 1 | SORT AGGREGATE | | 1 | |
|
| 2 | TABLE ACCESS FULL | TST | 41 | | 3
(34)|

8 rows selected.

SQL> truncate table plan_table;

Table truncated.

SQL> select index_name from user_indexes where table_name = 'TST';

INDEX_NAME

SYS_C003106 SQL> explain plan for
2 select /*+ index(tst, SYS_C003106) */ count(*) from tst;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 1 | | 27
(4)|
| 1 | SORT AGGREGATE | | 1 | |
|
| 2 | INDEX FULL SCAN | SYS_C003106 | 41 | | 27
(4)|

8 rows selected.

Therefore, if you have an index on non-null field (such as a one associated to a PK), it's probably faster to include it in a hint as I did, in order to replace the FTS by an index full scan. Note also that presumably the "sort aggregate" step would be avoided if the index is defined as unique (I didn't test that).

Daniel Received on Fri Sep 26 2003 - 09:03:08 CDT