The fastest way to obtain the total number of records in a table in Oracle

The syntax for querying the total number of records in a table is select count (*) from table_name. This may be the most frequently used type of SQL statements.

This article discusses how to obtain the maximum number of records. This article is purely theoretical,ArticleA lot of content (such as constant index) has little practical significance.

Prior to the specific description, the following prerequisites are emphasized:

First, the number of records in the table cannot be too small. Otherwise, the discussion will be of little significance. In my example, the number of records is about 30 thousand. In fact, this order of magnitude is still relatively small, however, we can see some results.

The probability is relatively large based on the length of execution time. This article uses no way to determine the number of logical reads. This includes query rewriting (Analysis of a relatively large number of execution plans required) and index compression (CPU-intensive, consuming more CPU resources ), the advantages and disadvantages of each method measured by logical reading alone are certainly not very accurate, but considering the large data volume in the table, and we take the second execution result of SQL as the standard, other impacts can still be ignored.

Another premise is the accuracy of the results. Similar methods such as querying the num_rows column of user_tables are not covered in this article.

Finally, because of the Oracle cache and sharing pool mechanism, the logic read of SQL statements is generally stable only after the second execution. For the sake of space, all the following select count (*) the result of from T is the result of the second execution of the SQL statement.

If there is a materialized view whose query statement is select count (*), the fastest way is to scan this materialized view.

SQL> Create Table T (ID number not null, name varchar2 (30), type varchar2 (18 ));

The table has been created.

SQL> insert into T select rownum, object_name, object_type from dba_objects;

You have created 30931 rows.

SQL> commit;

Submitted.

SQL> Create materialized view log on T with rowid including new values;

The materialized view log has been created.

SQL> Create materialized view mv_t refresh fast on commit enable query rewrite
2 select count (*) from T;

The materialized view has been created.

SQL> alter session set query_rewrite_enabled = true;

The session has been changed.

SQL> exec dbms_stats.gather_table_stats (user, 'T ')

The PL/SQL process is successfully completed.

SQL> set autot on
SQL> select count (*) from T;

Count (*)
----------
30931

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 2 card = 82 bytes = 1066)
1 0 Table Access (full) of 'mv _ t' (cost = 2 card = 82 bytes = 1066)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
3 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

As shown in the preceding query, scanning the materialized view requires only three logical reads. However, materialized views have many restrictions on the system. First, you must create materialized view logs and set parameters at the system or session level. Many conditions such as CBO must be used to restrict the use of materialized views, and most importantly, in general, there is no materialized view that simply queries the number of full table records. The materialized view is generally created to accelerate more complex table join or aggregate queries. Therefore, even if a materialized view exists, the results are not directly obtained. Generally, the results on the materialized view are re-calculated.

If you do not consider materialized views, the fastest way to obtain the total number of records must be bitmap index scanning. The bitmap index mechanism enables queries such as Count (*) returned by the bitmap index to have the fastest response speed and the smallest logical read. As for the bitmap index mechanism, we will not repeat it here. Let's take a look at the bitmap index performance:

SQL> drop materialized view mv_t;

The materialized view has been deleted.

SQL> drop materialized view log on T;

The materialized view log has been deleted.

SQL> Create bitmap index ind_ B _t_type on T (type );

The index has been created.

SQL> exec dbms_stats.gather_index_stats (user, 'ind _ B _t_type ')

The PL/SQL process is successfully completed.

SQL> select count (*) from T;

Count (*)
----------
30931

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 2 card = 1)
1 0 sort (aggregate)
2 1 bitmap conversion (count)
3 2 bitmap index (Fast full scan) of 'ind _ B _t_type'

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
5 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

As you can see, bitmap indexes are doing very well and only five logical reads are needed. Unfortunately, bitmap indexes are more suitable for use in data warehouses. For the OLTP environment, the lock granularity of Bitmap indexes will bring serious disasters to the entire system. Therefore, bitmap indexes are not suitable for OLTP systems.

Without considering the bitmap index, the fastest speed should be the quick full scan of normal indexes, such as primary key columns.

SQL> drop index ind_ B _t_type;

The index is discarded.

SQL> ALTER TABLE t add constraint pk_t primary key (ID );

The table has been changed.

SQL> select count (*) from T;

Count (*)
----------
30931

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 4 card = 1)
1 0 sort (aggregate)
2 1 index (Fast full scan) of 'pk _ t' (unique) (cost = 4 card = 30931)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
69 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

The quick full scan of the primary key requires only 69 logical reads. However, since the primary key uses rownum, that is, the value of the primary key ranges from 1 to 30931, the number type stored in Oracle requires two to four digits. If you create a constant index, you need to save some storage space. However, when performing a quick and full index scan, some logical reads can be reduced.

SQL> Create index ind_t_con on T (1 );

The index has been created.

SQL> select count (*) from T;

Count (*)
----------
30931

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 4 card = 1)
1 0 sort (aggregate)
2 1 index (Fast full scan) of 'ind _ t_con '(NON-UNIQUE) (cost = 4 card = 30931)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
66 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

Indeed, scanning constant indexes is much smaller than scanning the primary key for logical reads. In the number type, the storage of 1 requires two places, while the storage of 0 requires only one bit. Therefore, it is better to use 0 to replace 1 to create a constant index.

SQL> Create index ind_t_con_0 on T (0 );

The index has been created.

SQL> select/* + index (T ind_t_con_0) */count (*) from T;

Count (*)
----------
30931

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 26 card = 1)
1 0 sort (aggregate)
2 1 index (Full scan) of 'ind _ t_con_0 '(NON-UNIQUE) (cost = 26 card = 30931)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
58 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

Since all the node values in the constant index are the same, if you compress it, you should be able to reduce the number of logical reads.

SQL> drop index ind_t_con_0;

The index is discarded.

SQL> Create index ind_t_con_compress on T (0) compress;

The index has been created.

SQL> select/* + index (T ind_t_con_compress) */count (*) from T;

Count (*)
----------
30931

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 26 card = 1)
1 0 sort (aggregate)
2 1 index (Full scan) of 'ind _ t_con_compress '(NON-UNIQUE) (cost = 26 card = 30931)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
49 consistent gets
0 physical reads
0 redo size
378 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

As expected, after compression, the index scan logic reads are further reduced. Now, compared with the initial primary key scan, logical reads have been reduced by 30%.

If you only want to get count (*), the compressed constant index is the best choice. However, this index does not help other queries. Therefore, it is of little practical use.