Get the row count from index fast full scan

This post is to show using parallel_index hint to enable an index fast full scan access path to obtain the row count of a large table with primary key faster. BO_ID is the primary key column of BO_NBQQJOH table.

The following summarizes the results:

parallel full table scan 14m11s 8.8M consistent gets

parallel index fast full scan 3m49s 2.8M consistent gets

SQL> select /*+ parallel (a 6) */ count(bo_id) from ttq_dqg.bo_nbqqjoh_v a;

COUNT(BO_ID)
------------
   917384760

1 row selected.

Elapsed: 00:14:11.13

Execution Plan
----------------------------------------------------------
Plan hash value: 1529574786

------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |     1 |     7 |   341K  (1)| 01:08:17 |        |      |            |
|   1 |  SORT AGGREGATE        |            |     1 |     7 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |            |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000   |     1 |     7 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |            |     1 |     7 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |            |   912M|  6088M|   341K  (1)| 01:08:17 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| BO_NBQQJOH |   912M|  6088M|   341K  (1)| 01:08:17 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        158  recursive calls
          0  db block gets
    8846234  consistent gets
    8403973  physical reads
          0  redo size
        218  bytes sent via SQL*Net to client
        252  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          1  rows processed




SQL> select /*+ parallel_index ( a, BO_NBQQJOH_NEW_PK, 6) */ count(bo_id) from ttq_dqg.bo_nbqqjoh_v a;

COUNT(BO_ID)
------------
   917391460

1 row selected.

Elapsed: 00:03:49.60

Execution Plan
----------------------------------------------------------
Plan hash value: 1771473710

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name              | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                   |     1 |     7 |   487K  (3)| 01:37:33 |        |      |            |
|   1 |  SORT AGGREGATE           |                   |     1 |     7 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |                   |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000          |     1 |     7 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |                   |     1 |     7 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR    |                   |   912M|  6088M|   487K  (3)| 01:37:33 |  Q1,00 | PCWC |            |
|   6 |       INDEX FAST FULL SCAN| BO_NBQQJOH_NEW_PK |   912M|  6088M|   487K  (3)| 01:37:33 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------


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

SQL>