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

View All Public Scripts Login and Run Script

Script Name Checking Real Execution Plan(s) of Separately Querying COUNT(*|1|id|flag) on 4 different TEST3 Table(s)
Visibility Unlisted - anyone with the share link can access
Description This demo usually uses to check the real execution plan of separately querying COUNT(*), COUNT(1), COUNT(id) and COUNT(flag) on 4 different tables 'TEST3', 'TEST3_PK', 'TEST3_BI' and 'TEST3_PK_BI'. By the way those tables are going to be dynamically created by using several oracle PL/SQL procedures. I just use the standard SQL syntax 'CREATE TABLE' to create my TEST3 tables with two physical properties (SEGMENT CREATION IMMEDIATE and NOLOGGING) and a table property (NO PARALLEL) and then also create corresponding PRIMARY KEY or BITMAP INDEX on those different TEST3 tables. Finally inserting several test data to my all TEST3 tables via using the hint 'append'. On one hand, each table all has the same two columnns both 'id' and 'flag', column 'id' is continuously increasing from 1 to '1e4' and column 'flag' has 4 types of duplicate values 'low', 'mid', 'high' and 'unknown'; on the other hand, table 'TEST3' has neither PRIMARY KEY nor BITMAP INDEX, table 'TEST3_PK' has only PRIMARY KEY, table 'TEST3_BI' has only BITMAP INDEX, and table 'TEST3_PK_BI' has both PRIMARY KEY and BITMAP INDEX.
Area SQL General / SQL Query
Contributor Quanwen Zhao
Created Thursday December 26, 2019

Statement 1

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

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

CREATE OR REPLACE PROCEDURE crt_tab_test3 
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 test3 PURGE'; 
   v_sql_2 := q'[CREATE TABLE test3 (  
                    id     NUMBER      NOT NULL  
                    , flag VARCHAR2(7) NOT NULL  
                 )  
                 SEGMENT CREATION IMMEDIATE  
                 NOLOGGING  
                 -- PARALLEL 10 
                ]'; 
   v_sql_3 := q'[INSERT /*+ append */ INTO test3  
                 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 v_sql_3; 
   COMMIT; 
   EXECUTE IMMEDIATE 'ANALYZE TABLE test3 COMPUTE STATISTICS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      EXECUTE IMMEDIATE v_sql_2; 
      EXECUTE IMMEDIATE v_sql_3; 
      COMMIT; 
      EXECUTE IMMEDIATE 'ANALYZE TABLE test3 COMPUTE STATISTICS'; 
END crt_tab_test3;

Procedure created.

Statement 2
Running procedure "crt_tab_test3".
```
EXECUTE crt_tab_test3
```
Statement processed.

Statement 3

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

COUNT(*)
10000

Statement 4

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

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

PLAN_TABLE_OUTPUT
SQL_ID 04n5f2gu8q9pj, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(*) FROM test3

Plan hash value: 1832809687

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

14 rows selected.

Statement 5

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

COUNT(1)
10000

Statement 6

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

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

PLAN_TABLE_OUTPUT
SQL_ID 8w0rnm99c1u7f, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(1) FROM test3

Plan hash value: 1832809687

--------------------------------------------------------------------------------------
\| 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\| TEST3 \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 23 \|
--------------------------------------------------------------------------------------

14 rows selected.

Statement 7

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

COUNT(ID)
10000

Statement 8

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

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

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

Plan hash value: 1832809687

--------------------------------------------------------------------------------------
\| 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\| TEST3 \| 1 \| 10000 \| 10000 \|00:00:00.01 \| 23 \|
--------------------------------------------------------------------------------------

14 rows selected.

Statement 9

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

COUNT(FLAG)
10000

Statement 10

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

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

PLAN_TABLE_OUTPUT
SQL_ID f0kjh8aufq94a, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(flag) FROM test3

Plan hash value: 1832809687

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

14 rows selected.

Statement 11

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST3_PK' table, meanwhile after finishing creating 'TEST3_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_test3_pk" of PL/SQL to dynamically create 'TEST3_PK' table containing 1e4 lines data

CREATE OR REPLACE PROCEDURE crt_tab_test3_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 test3_pk PURGE'; 
   v_sql_2 := q'[CREATE TABLE test3_pk (  
                    id     NUMBER      NOT NULL CONSTRAINT test3_only_pk PRIMARY KEY  
                    , flag VARCHAR2(7) NOT NULL  
                 )  
                 SEGMENT CREATION IMMEDIATE  
                 NOLOGGING  
                 -- PARALLEL 10 
                ]'; 
   v_sql_3 := q'[INSERT /*+ append */ INTO test3_pk  
                 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 v_sql_3; 
   COMMIT; 
   EXECUTE IMMEDIATE 'ANALYZE TABLE test3_pk COMPUTE STATISTICS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      EXECUTE IMMEDIATE v_sql_2; 
      EXECUTE IMMEDIATE v_sql_3; 
      COMMIT; 
      EXECUTE IMMEDIATE 'ANALYZE TABLE test3_pk COMPUTE STATISTICS'; 
END crt_tab_test3_pk;

Procedure created.

Statement 12
Running procedure "crt_tab_test3_pk".
```
EXECUTE crt_tab_test3_pk
```
Statement processed.

Statement 13

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

COUNT(*)
10000

Statement 14

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

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

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

Plan hash value: 925588815

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

14 rows selected.

Statement 15

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

COUNT(1)
10000

Statement 16

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

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

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

Plan hash value: 925588815

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

14 rows selected.

Statement 17

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

COUNT(ID)
10000

Statement 18

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

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

PLAN_TABLE_OUTPUT
SQL_ID 20cbn62hgh7wr, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(id) FROM test3_pk

Plan hash value: 925588815

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

14 rows selected.

Statement 19

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

COUNT(FLAG)
10000

Statement 20

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

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

PLAN_TABLE_OUTPUT
SQL_ID 59snudb8cf4qd, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(flag) FROM test3_pk

Plan hash value: 925588815

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

14 rows selected.

Statement 21

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST3_BI' table, meanwhile after finishing creating 'TEST3_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_test3_bi" of PL/SQL to dynamically create 'TEST3_BI' table containing 1e4 lines data

CREATE OR REPLACE PROCEDURE crt_tab_test3_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 test3_bi PURGE'; 
   v_sql_2 := q'[CREATE TABLE test3_bi (  
                    id     NUMBER      NOT NULL  
                    , flag VARCHAR2(7) NOT NULL  
                 )  
                 SEGMENT CREATION IMMEDIATE  
                 NOLOGGING  
                 -- PARALLEL 10 
                ]'; 
   v_sql_3 := 'CREATE BITMAP INDEX test3_only_bi ON test3_bi (flag)'; 
   v_sql_4 := q'[INSERT /*+ append */ INTO test3_bi  
                 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 v_sql_3; 
   EXECUTE IMMEDIATE v_sql_4; 
   COMMIT; 
   EXECUTE IMMEDIATE 'ANALYZE TABLE test3_bi COMPUTE STATISTICS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      EXECUTE IMMEDIATE v_sql_2; 
      EXECUTE IMMEDIATE v_sql_3; 
      EXECUTE IMMEDIATE v_sql_4; 
      COMMIT; 
      EXECUTE IMMEDIATE 'ANALYZE TABLE test3_bi COMPUTE STATISTICS'; 
END crt_tab_test3_bi;

Procedure created.

Statement 22
Running procedure "crt_tab_test3_bi".
```
EXECUTE crt_tab_test3_bi
```
Statement processed.

Statement 23

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

COUNT(*)
10000

Statement 24

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

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

PLAN_TABLE_OUTPUT
SQL_ID 18rz1mu8uf657, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(*) FROM test3_bi

Plan hash value: 841407947

---------------------------------------------------------------------------------------------------------
\| 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\| TEST3_ONLY_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
---------------------------------------------------------------------------------------------------------

15 rows selected.

Statement 25

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

COUNT(1)
10000

Statement 26

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

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

PLAN_TABLE_OUTPUT
SQL_ID 260fkmg5gtkk9, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(1) FROM test3_bi

Plan hash value: 841407947

---------------------------------------------------------------------------------------------------------
\| 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\| TEST3_ONLY_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
---------------------------------------------------------------------------------------------------------

15 rows selected.

Statement 27

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

COUNT(ID)
10000

Statement 28

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

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

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

Plan hash value: 841407947

---------------------------------------------------------------------------------------------------------
\| 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\| TEST3_ONLY_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
---------------------------------------------------------------------------------------------------------

15 rows selected.

Statement 29

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

COUNT(FLAG)
10000

Statement 30

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

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

PLAN_TABLE_OUTPUT
SQL_ID 60zrk3jbk7pur, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(flag) FROM test3_bi

Plan hash value: 841407947

---------------------------------------------------------------------------------------------------------
\| 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\| TEST3_ONLY_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
---------------------------------------------------------------------------------------------------------

15 rows selected.

Statement 31

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST3_PK_BI' table, meanwhile after finishing creating 'TEST3_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_test3_pk_bi" of PL/SQL to dynamically create 'TEST3_PK_BI' table containing 1e4 lines data

CREATE OR REPLACE PROCEDURE crt_tab_test3_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 test3_pk_bi PURGE'; 
   v_sql_2 := q'[CREATE TABLE test3_pk_bi (  
                    id     NUMBER      NOT NULL CONSTRAINT test3_pk PRIMARY KEY  
                    , flag VARCHAR2(7) NOT NULL  
                 )  
                 SEGMENT CREATION IMMEDIATE  
                 NOLOGGING  
                 -- PARALLEL 10 
                ]'; 
   v_sql_3 := 'CREATE BITMAP INDEX test3_bi ON test3_pk_bi (flag)'; 
   v_sql_4 := q'[INSERT /*+ append */ INTO test3_pk_bi  
                 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 v_sql_3; 
   EXECUTE IMMEDIATE v_sql_4; 
   COMMIT; 
   EXECUTE IMMEDIATE 'ANALYZE TABLE test3_pk_bi COMPUTE STATISTICS'; 
EXCEPTION 
   WHEN OTHERS THEN 
      EXECUTE IMMEDIATE v_sql_2; 
      EXECUTE IMMEDIATE v_sql_3; 
      EXECUTE IMMEDIATE v_sql_4; 
      COMMIT; 
      EXECUTE IMMEDIATE 'ANALYZE TABLE test3_pk_bi COMPUTE STATISTICS'; 
END crt_tab_test3_pk_bi;

Procedure created.

Statement 32
Running procedure "crt_tab_test3_pk_bi".
```
EXECUTE crt_tab_test3_pk_bi
```
Statement processed.

Statement 33

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

COUNT(*)
10000

Statement 34

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

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

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

Plan hash value: 3257137296

----------------------------------------------------------------------------------------------------
\| 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\| TEST3_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
----------------------------------------------------------------------------------------------------

15 rows selected.

Statement 35

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

COUNT(1)
10000

Statement 36

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

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

PLAN_TABLE_OUTPUT
SQL_ID 6j4h96xy1gzwv, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(1) FROM test3_pk_bi

Plan hash value: 3257137296

----------------------------------------------------------------------------------------------------
\| 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\| TEST3_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
----------------------------------------------------------------------------------------------------

15 rows selected.

Statement 37

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

COUNT(ID)
10000

Statement 38

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

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

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

Plan hash value: 3257137296

----------------------------------------------------------------------------------------------------
\| 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\| TEST3_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
----------------------------------------------------------------------------------------------------

15 rows selected.

Statement 39

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

COUNT(FLAG)
10000

Statement 40

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

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

PLAN_TABLE_OUTPUT
SQL_ID a83xvzrxgk0jy, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics / COUNT(flag) FROM test3_pk_bi

Plan hash value: 3257137296

----------------------------------------------------------------------------------------------------
\| 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\| TEST3_BI \| 1 \| \| 4 \|00:00:00.01 \| 4 \|
----------------------------------------------------------------------------------------------------

15 rows selected.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials