Oracle Live SQL - Script: Checking Real Execution Plan(s) of Separately Querying COUNT(*|1|id|flag) on 4 different TEST Table(s)

Statement 1

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST' table, meanwhile after finishing creating 'TEST' table and also gathering the statistics of that table to ensure the accuracy of corresponding SQL statements execution plan.

Using a procedure "crt_tab_test" of PL/SQL to dynamically create 'TEST' table containing 1e4 lines data

CREATE OR REPLACE PROCEDURE crt_tab_test 
AS 
   v_num   NUMBER; 
   v_sql_1 VARCHAR2(2000); 
   v_sql_2 VARCHAR2(2000); 
BEGIN 
   v_num := 1e4; 
   v_sql_1 := 'DROP TABLE test PURGE'; 
   v_sql_2 := q'[CREATE TABLE test  
                 SEGMENT CREATION IMMEDIATE  
                 NOLOGGING  
                 -- PARALLEL 10  
                 AS SELECT ROWNUM id  
                           , CASE WHEN ROWNUM BETWEEN  1                      AND 1/5*]' || v_num || q'[ THEN 'low' ]' 
              || q'[              WHEN ROWNUM BETWEEN  2/5*]' || v_num || q'[ AND 3/5*]' || v_num || q'[ THEN 'mid' ]' 
              || q'[              WHEN ROWNUM BETWEEN  4/5*]' || v_num || q'[ AND     ]' || v_num || q'[ THEN 'high' ]' 
              || q'[              ELSE 'unknown'  
                             END flag  
                    FROM XMLTABLE('1 to ]' || v_num || q'[')]'; 
   EXECUTE IMMEDIATE v_sql_1; 
   EXECUTE IMMEDIATE v_sql_2; 
   EXECUTE IMMEDIATE 'ANALYZE TABLE test COMPUTE STATISTICS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      EXECUTE IMMEDIATE v_sql_2; 
      EXECUTE IMMEDIATE 'ANALYZE TABLE test COMPUTE STATISTICS'; 
END crt_tab_test;

Procedure created.

Statement 2

Running procedure "crt_tab_test".

EXECUTE crt_tab_test

Statement processed.

Statement 3

SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test


COUNT(*)
10000

COUNT(*)
10000

Statement 4

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(*) FROM test;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  dctqrst2p353w, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test
 
Plan hash value: 1950795681
 
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      23 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      23 |
|   2 |   TABLE ACCESS FULL| TEST |      1 |  10000 |  10000 |00:00:00.01 |      23 |
-------------------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID dctqrst2p353w, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(*) FROM test

Plan hash value: 1950795681

-------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
-------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 23 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 23 \|
\| 2 \| TABLE ACCESS FULL\| TEST \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 23 \|
-------------------------------------------------------------------------------------

Statement 5

SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test


COUNT(1)
10000

COUNT(1)
10000

Statement 6

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(1) FROM test;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  84j14bfgc74dv, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test
 
Plan hash value: 1950795681
 
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      23 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      23 |
|   2 |   TABLE ACCESS FULL| TEST |      1 |  10000 |  10000 |00:00:00.01 |      23 |
-------------------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 84j14bfgc74dv, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(1) FROM test

Plan hash value: 1950795681

-------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
-------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 23 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 23 \|
\| 2 \| TABLE ACCESS FULL\| TEST \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 23 \|
-------------------------------------------------------------------------------------

Statement 7

SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test


COUNT(ID)
10000

COUNT(ID)
10000

Statement 8

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(id) FROM test;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  6y343jmwz8kq4, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test
 
Plan hash value: 1950795681
 
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      23 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      23 |
|   2 |   TABLE ACCESS FULL| TEST |      1 |  10000 |  10000 |00:00:00.01 |      23 |
-------------------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 6y343jmwz8kq4, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(id) FROM test

Plan hash value: 1950795681

-------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
-------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 23 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 23 \|
\| 2 \| TABLE ACCESS FULL\| TEST \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 23 \|
-------------------------------------------------------------------------------------

Statement 9

SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test


COUNT(FLAG)
10000

COUNT(FLAG)
10000

Statement 10

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(flag) FROM test;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  1qs404gtsuc3q, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test
 
Plan hash value: 1950795681
 
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      23 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      23 |
|   2 |   TABLE ACCESS FULL| TEST |      1 |  10000 |  10000 |00:00:00.01 |      23 |
-------------------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 1qs404gtsuc3q, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(flag) FROM test

Plan hash value: 1950795681

-------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
-------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 23 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 23 \|
\| 2 \| TABLE ACCESS FULL\| TEST \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 23 \|
-------------------------------------------------------------------------------------

Statement 11

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST_PK' table, meanwhile after finishing creating 'TEST_PK' table and also gathering the statistics of that table to ensure the accuracy of corresponding SQL statements execution plan.

Using a procedure "crt_tab_test_pk" of PL/SQL to dynamically create 'TEST_PK' table containing 1e4 lines data

CREATE OR REPLACE PROCEDURE crt_tab_test_pk 
AS 
   v_num   NUMBER; 
   v_sql_1 VARCHAR2(2000); 
   v_sql_2 VARCHAR2(2000); 
   v_sql_3 VARCHAR2(2000); 
BEGIN 
   v_num := 1e4; 
   v_sql_1 := 'DROP TABLE test_pk PURGE'; 
   v_sql_2 := q'[CREATE TABLE test_pk  
                 SEGMENT CREATION IMMEDIATE  
                 NOLOGGING  
                 -- PARALLEL 10  
                 AS SELECT ROWNUM id  
                           , CASE WHEN ROWNUM BETWEEN  1                      AND 1/5*]' || v_num || q'[ THEN 'low' ]' 
              || q'[              WHEN ROWNUM BETWEEN  2/5*]' || v_num || q'[ AND 3/5*]' || v_num || q'[ THEN 'mid' ]' 
              || q'[              WHEN ROWNUM BETWEEN  4/5*]' || v_num || q'[ AND     ]' || v_num || q'[ THEN 'high' ]' 
              || q'[              ELSE 'unknown'  
                             END flag  
                    FROM XMLTABLE('1 to ]' || v_num || q'[')]'; 
   v_sql_3 := 'ALTER TABLE test_pk ADD CONSTRAINT test_only_pk PRIMARY KEY (id)'; 
   EXECUTE IMMEDIATE v_sql_1; 
   EXECUTE IMMEDIATE v_sql_2; 
   EXECUTE IMMEDIATE v_sql_3; 
   EXECUTE IMMEDIATE 'ANALYZE TABLE test_pk COMPUTE STATISTICS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      EXECUTE IMMEDIATE v_sql_2; 
      EXECUTE IMMEDIATE v_sql_3; 
      EXECUTE IMMEDIATE 'ANALYZE TABLE test_pk COMPUTE STATISTICS'; 
END crt_tab_test_pk;

Procedure created.

Statement 12

Running procedure "crt_tab_test_pk".

EXECUTE crt_tab_test_pk

Statement processed.

Statement 13

SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test_pk


COUNT(*)
10000

COUNT(*)
10000

Statement 14

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(*) FROM test_pk;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  dcs0ww9r4v1bx, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test_pk
 
Plan hash value: 1614421910
 
------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |      1 |00:00:00.01 |      26 |
|   1 |  SORT AGGREGATE       |              |      1 |      1 |      1 |00:00:00.01 |      26 |
|   2 |   INDEX FAST FULL SCAN| TEST_ONLY_PK |      1 |  10000 |  10000 |00:00:00.01 |      26 |
------------------------------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID dcs0ww9r4v1bx, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(*) FROM test_pk

Plan hash value: 1614421910

------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 26 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 26 \|
\| 2 \| INDEX FAST FULL SCAN\| TEST_ONLY_PK \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 26 \|
------------------------------------------------------------------------------------------------

Statement 15

SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test_pk


COUNT(1)
10000

COUNT(1)
10000

Statement 16

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(1) FROM test_pk;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  02xh9yqws14hv, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test_pk
 
Plan hash value: 1614421910
 
------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |      1 |00:00:00.01 |      26 |
|   1 |  SORT AGGREGATE       |              |      1 |      1 |      1 |00:00:00.01 |      26 |
|   2 |   INDEX FAST FULL SCAN| TEST_ONLY_PK |      1 |  10000 |  10000 |00:00:00.01 |      26 |
------------------------------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 02xh9yqws14hv, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(1) FROM test_pk

Plan hash value: 1614421910

------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 26 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 26 \|
\| 2 \| INDEX FAST FULL SCAN\| TEST_ONLY_PK \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 26 \|
------------------------------------------------------------------------------------------------

Statement 17

SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test_pk


COUNT(ID)
10000

COUNT(ID)
10000

Statement 18

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(id) FROM test_pk;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  ccxfx9rr26ubb, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test_pk
 
Plan hash value: 1614421910
 
------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |      1 |00:00:00.01 |      26 |
|   1 |  SORT AGGREGATE       |              |      1 |      1 |      1 |00:00:00.01 |      26 |
|   2 |   INDEX FAST FULL SCAN| TEST_ONLY_PK |      1 |  10000 |  10000 |00:00:00.01 |      26 |
------------------------------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID ccxfx9rr26ubb, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(id) FROM test_pk

Plan hash value: 1614421910

------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 26 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 26 \|
\| 2 \| INDEX FAST FULL SCAN\| TEST_ONLY_PK \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 26 \|
------------------------------------------------------------------------------------------------

Statement 19

SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test_pk


COUNT(FLAG)
10000

COUNT(FLAG)
10000

Statement 20

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(flag) FROM test_pk;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  16axcpmdmc4u0, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test_pk
 
Plan hash value: 262554536
 
----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |      23 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |      23 |
|   2 |   TABLE ACCESS FULL| TEST_PK |      1 |  10000 |  10000 |00:00:00.01 |      23 |
----------------------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 16axcpmdmc4u0, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(flag) FROM test_pk

Plan hash value: 262554536

----------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
----------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 23 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 23 \|
\| 2 \| TABLE ACCESS FULL\| TEST_PK \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 23 \|
----------------------------------------------------------------------------------------

Statement 21

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST_BI' table, meanwhile after finishing creating 'TEST_BI' table and also gathering the statistics of that table to ensure the accuracy of corresponding SQL statements execution plan.

Using a procedure "crt_tab_test_bi" of PL/SQL to dynamically create 'TEST_BI' table containing 1e4 lines data

CREATE OR REPLACE PROCEDURE crt_tab_test_bi 
AS 
   v_num   NUMBER; 
   v_sql_1 VARCHAR2(2000); 
   v_sql_2 VARCHAR2(2000); 
   v_sql_3 VARCHAR2(2000); 
BEGIN 
   v_num := 1e4; 
   v_sql_1 := 'DROP TABLE test_bi PURGE'; 
   v_sql_2 := q'[CREATE TABLE test_bi  
                 SEGMENT CREATION IMMEDIATE  
                 NOLOGGING  
                 -- PARALLEL 10  
                 AS SELECT ROWNUM id  
                           , CASE WHEN ROWNUM BETWEEN  1                      AND 1/5*]' || v_num || q'[ THEN 'low' ]' 
              || q'[              WHEN ROWNUM BETWEEN  2/5*]' || v_num || q'[ AND 3/5*]' || v_num || q'[ THEN 'mid' ]' 
              || q'[              WHEN ROWNUM BETWEEN  4/5*]' || v_num || q'[ AND     ]' || v_num || q'[ THEN 'high' ]' 
              || q'[              ELSE 'unknown'  
                             END flag  
                    FROM XMLTABLE('1 to ]' || v_num || q'[')]'; 
   v_sql_3 := 'CREATE BITMAP INDEX test_only_bi ON test_bi (flag)'; 
   EXECUTE IMMEDIATE v_sql_1; 
   EXECUTE IMMEDIATE v_sql_2; 
   EXECUTE IMMEDIATE v_sql_3; 
   EXECUTE IMMEDIATE 'ANALYZE TABLE test_bi COMPUTE STATISTICS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      EXECUTE IMMEDIATE v_sql_2; 
      EXECUTE IMMEDIATE v_sql_3; 
      EXECUTE IMMEDIATE 'ANALYZE TABLE test_bi COMPUTE STATISTICS'; 
END crt_tab_test_bi;

Procedure created.

Statement 22

Running procedure "crt_tab_test_bi".

EXECUTE crt_tab_test_bi

Statement processed.

Statement 23

SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test_bi


COUNT(*)
10000

COUNT(*)
10000

Statement 24

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(*) FROM test_bi;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  57r87yd1m47xk, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test_bi
 
Plan hash value: 389383459
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE               |              |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION COUNT     |              |      1 |  10000 |      4 |00:00:00.01 |       4 |
|   3 |    BITMAP INDEX FAST FULL SCAN| TEST_ONLY_BI |      1 |        |      4 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------
 

15 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 57r87yd1m47xk, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(*) FROM test_bi

Plan hash value: 389383459

--------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
--------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 4 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 4 \|
\| 2 \| BITMAP CONVERSION COUNT \| \| 1 \| 10000 \| 4 \|00:00:00.01 \| 4 \|
\| 3 \| BITMAP INDEX FAST FULL SCAN\| TEST_ONLY_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
--------------------------------------------------------------------------------------------------------

Statement 25

SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test_bi


COUNT(1)
10000

COUNT(1)
10000

Statement 26

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(1) FROM test_bi;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  cd98u07xbwbyj, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test_bi
 
Plan hash value: 389383459
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE               |              |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION COUNT     |              |      1 |  10000 |      4 |00:00:00.01 |       4 |
|   3 |    BITMAP INDEX FAST FULL SCAN| TEST_ONLY_BI |      1 |        |      4 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------
 

15 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID cd98u07xbwbyj, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(1) FROM test_bi

Plan hash value: 389383459

--------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
--------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 4 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 4 \|
\| 2 \| BITMAP CONVERSION COUNT \| \| 1 \| 10000 \| 4 \|00:00:00.01 \| 4 \|
\| 3 \| BITMAP INDEX FAST FULL SCAN\| TEST_ONLY_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
--------------------------------------------------------------------------------------------------------

Statement 27

SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test_bi


COUNT(ID)
10000

COUNT(ID)
10000

Statement 28

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(id) FROM test_bi;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  2n74xumcvj6db, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test_bi
 
Plan hash value: 950576497
 
----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |      23 |
|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |      23 |
|   2 |   TABLE ACCESS FULL| TEST_BI |      1 |  10000 |  10000 |00:00:00.01 |      23 |
----------------------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 2n74xumcvj6db, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(id) FROM test_bi

Plan hash value: 950576497

----------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
----------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 23 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 23 \|
\| 2 \| TABLE ACCESS FULL\| TEST_BI \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 23 \|
----------------------------------------------------------------------------------------

Statement 29

SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test_bi


COUNT(FLAG)
10000

COUNT(FLAG)
10000

Statement 30

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(flag) FROM test_bi;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  6utzav62an96v, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test_bi
 
Plan hash value: 2896346787
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE               |              |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION TO ROWIDS |              |      1 |  10000 |  10000 |00:00:00.01 |       4 |
|   3 |    BITMAP INDEX FAST FULL SCAN| TEST_ONLY_BI |      1 |        |      4 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------
 

15 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 6utzav62an96v, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(flag) FROM test_bi

Plan hash value: 2896346787

--------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
--------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 4 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 4 \|
\| 2 \| BITMAP CONVERSION TO ROWIDS \| \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 4 \|
\| 3 \| BITMAP INDEX FAST FULL SCAN\| TEST_ONLY_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
--------------------------------------------------------------------------------------------------------

Statement 31

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST_PK_BI' table, meanwhile after finishing creating 'TEST_PK_BI' table and also gathering the statistics of that table to ensure the accuracy of corresponding SQL statements execution plan.

Using a procedure "crt_tab_test_pk_bi" of PL/SQL to dynamically create 'TEST_PK_BI' table containing 1e4 lines data

CREATE OR REPLACE PROCEDURE crt_tab_test_pk_bi 
AS 
   v_num   NUMBER; 
   v_sql_1 VARCHAR2(2000); 
   v_sql_2 VARCHAR2(2000); 
   v_sql_3 VARCHAR2(2000); 
   v_sql_4 VARCHAR2(2000); 
BEGIN 
   v_num := 1e4; 
   v_sql_1 := 'DROP TABLE test_pk_bi PURGE'; 
   v_sql_2 := q'[CREATE TABLE test_pk_bi  
                 SEGMENT CREATION IMMEDIATE  
                 NOLOGGING  
                 -- PARALLEL 10  
                 AS SELECT ROWNUM id  
                           , CASE WHEN ROWNUM BETWEEN  1                      AND 1/5*]' || v_num || q'[ THEN 'low' ]' 
              || q'[              WHEN ROWNUM BETWEEN  2/5*]' || v_num || q'[ AND 3/5*]' || v_num || q'[ THEN 'mid' ]' 
              || q'[              WHEN ROWNUM BETWEEN  4/5*]' || v_num || q'[ AND     ]' || v_num || q'[ THEN 'high' ]' 
              || q'[              ELSE 'unknown'  
                             END flag  
                    FROM XMLTABLE('1 to ]' || v_num || q'[')]'; 
   v_sql_3 := 'ALTER TABLE test_pk_bi ADD CONSTRAINT test_pk PRIMARY KEY (id)'; 
   v_sql_4 := 'CREATE BITMAP INDEX test_bi ON test_pk_bi (flag)'; 
   EXECUTE IMMEDIATE v_sql_1; 
   EXECUTE IMMEDIATE v_sql_2; 
   EXECUTE IMMEDIATE v_sql_3; 
   EXECUTE IMMEDIATE v_sql_4; 
   EXECUTE IMMEDIATE 'ANALYZE TABLE test_pk_bi COMPUTE STATISTICS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      EXECUTE IMMEDIATE v_sql_2; 
      EXECUTE IMMEDIATE v_sql_3; 
      EXECUTE IMMEDIATE v_sql_4; 
      EXECUTE IMMEDIATE 'ANALYZE TABLE test_pk_bi COMPUTE STATISTICS'; 
END crt_tab_test_pk_bi;

Procedure created.

Statement 32

Running procedure "crt_tab_test_pk_bi".

EXECUTE crt_tab_test_pk_bi

Statement processed.

Statement 33

SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test_pk_bi


COUNT(*)
10000

COUNT(*)
10000

Statement 34

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(*) FROM test_pk_bi;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  1p05g2wrgkb0v, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test_pk_bi
 
Plan hash value: 114976402
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE               |         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION COUNT     |         |      1 |  10000 |      4 |00:00:00.01 |       4 |
|   3 |    BITMAP INDEX FAST FULL SCAN| TEST_BI |      1 |        |      4 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------
 

15 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 1p05g2wrgkb0v, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(*) FROM test_pk_bi

Plan hash value: 114976402

---------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
---------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 4 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 4 \|
\| 2 \| BITMAP CONVERSION COUNT \| \| 1 \| 10000 \| 4 \|00:00:00.01 \| 4 \|
\| 3 \| BITMAP INDEX FAST FULL SCAN\| TEST_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
---------------------------------------------------------------------------------------------------

Statement 35

SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test_pk_bi


COUNT(1)
10000

COUNT(1)
10000

Statement 36

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(1) FROM test_pk_bi;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  35bcyj1vq9rny, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test_pk_bi
 
Plan hash value: 114976402
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE               |         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION COUNT     |         |      1 |  10000 |      4 |00:00:00.01 |       4 |
|   3 |    BITMAP INDEX FAST FULL SCAN| TEST_BI |      1 |        |      4 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------
 

15 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 35bcyj1vq9rny, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(1) FROM test_pk_bi

Plan hash value: 114976402

---------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
---------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 4 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 4 \|
\| 2 \| BITMAP CONVERSION COUNT \| \| 1 \| 10000 \| 4 \|00:00:00.01 \| 4 \|
\| 3 \| BITMAP INDEX FAST FULL SCAN\| TEST_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
---------------------------------------------------------------------------------------------------

Statement 37

SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test_pk_bi


COUNT(ID)
10000

COUNT(ID)
10000

Statement 38

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(id) FROM test_pk_bi;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  7hyu7c4jh9h6f, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test_pk_bi
 
Plan hash value: 114976402
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE               |         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION COUNT     |         |      1 |  10000 |      4 |00:00:00.01 |       4 |
|   3 |    BITMAP INDEX FAST FULL SCAN| TEST_BI |      1 |        |      4 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------
 

15 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 7hyu7c4jh9h6f, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(id) FROM test_pk_bi

Plan hash value: 114976402

---------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
---------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1 \|00:00:00.01 \| 4 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 4 \|
\| 2 \| BITMAP CONVERSION COUNT \| \| 1 \| 10000 \| 4 \|00:00:00.01 \| 4 \|
\| 3 \| BITMAP INDEX FAST FULL SCAN\| TEST_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
---------------------------------------------------------------------------------------------------

Statement 39

SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test_pk_bi


COUNT(FLAG)
10000

COUNT(FLAG)
10000

Statement 40

Checking row-source execution plan of the previous SQL statement "SELECT COUNT(flag) FROM test_pk_bi;".

SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))


PLAN_TABLE_OUTPUT
SQL_ID  375g0s50kt4du, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test_pk_bi
 
Plan hash value: 427023172
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE               |         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION TO ROWIDS |         |      1 |  10000 |  10000 |00:00:00.01 |       4 |
|   3 |    BITMAP INDEX FAST FULL SCAN| TEST_BI |      1 |        |      4 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------
 

15 rows selected.

Checking Real Execution Plan(s) of Separately Querying COUNT(*|1|id|flag) on 4 different TEST Table(s)

Additional Information