Using a procedure "crt_tab_test4" of PL/SQL to dynamically create 'TEST4' table containing 1e4 lines data
CREATE OR REPLACE PROCEDURE crt_tab_test4
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 test4 PURGE';
v_sql_2 := q'[CREATE TABLE test4 (
id NUMBER NOT NULL
, flag VARCHAR2(7) NOT NULL
)
SEGMENT CREATION IMMEDIATE
NOLOGGING
PARALLEL 10
]';
v_sql_3 := q'[INSERT /*+ append */ INTO test4
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 test4 COMPUTE STATISTICS';
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE v_sql_2;
EXECUTE IMMEDIATE v_sql_3;
COMMIT;
EXECUTE IMMEDIATE 'ANALYZE TABLE test4 COMPUTE STATISTICS';
END crt_tab_test4; Procedure created.
EXECUTE crt_tab_test4Statement processed.
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test4| COUNT(*) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID br9kx1rgv8c93, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test4 | Plan hash value: 2402724102 | --------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | --------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 29 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 29 | | | 2 | PX COORDINATOR | | 1 | | 1 |00:00:00.01 | 29 | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 1 | 1 |00:00:00.01 | 24 | | | 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 24 | | | 5 | PX BLOCK ITERATOR | | 1 | 10000 | 10000 |00:00:00.01 | 24 | | |* 6 | TABLE ACCESS FULL| TEST4 | 1 | 10000 | 10000 |00:00:00.01 | 24 | | --------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 6 - access(:Z>=:Z AND :Z<=:Z) | Note | ----- | - Degree of Parallelism is 10 because of table property |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test4| COUNT(1) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 8m2r2bcckfgfp, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test4 | Plan hash value: 2402724102 | --------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | --------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 29 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 29 | | | 2 | PX COORDINATOR | | 1 | | 1 |00:00:00.01 | 29 | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 1 | 1 |00:00:00.01 | 24 | | | 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 24 | | | 5 | PX BLOCK ITERATOR | | 1 | 10000 | 10000 |00:00:00.01 | 24 | | |* 6 | TABLE ACCESS FULL| TEST4 | 1 | 10000 | 10000 |00:00:00.01 | 24 | | --------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 6 - access(:Z>=:Z AND :Z<=:Z) | Note | ----- | - Degree of Parallelism is 10 because of table property |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test4| COUNT(ID) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID b2y24h9ttftgp, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test4 | Plan hash value: 2402724102 | --------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | --------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 30 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 30 | | | 2 | PX COORDINATOR | | 1 | | 1 |00:00:00.01 | 30 | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 1 | 1 |00:00:00.01 | 25 | | | 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 25 | | | 5 | PX BLOCK ITERATOR | | 1 | 10000 | 10000 |00:00:00.01 | 25 | | |* 6 | TABLE ACCESS FULL| TEST4 | 1 | 10000 | 10000 |00:00:00.01 | 25 | | --------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 6 - access(:Z>=:Z AND :Z<=:Z) | Note | ----- | - Degree of Parallelism is 10 because of table property |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test4| COUNT(FLAG) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID c986q59htr2uv, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test4 | Plan hash value: 2402724102 | --------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | --------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 29 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 29 | | | 2 | PX COORDINATOR | | 1 | | 1 |00:00:00.01 | 29 | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 1 | 1 |00:00:00.01 | 24 | | | 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 24 | | | 5 | PX BLOCK ITERATOR | | 1 | 10000 | 10000 |00:00:00.01 | 24 | | |* 6 | TABLE ACCESS FULL| TEST4 | 1 | 10000 | 10000 |00:00:00.01 | 24 | | --------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 6 - access(:Z>=:Z AND :Z<=:Z) | Note | ----- | - Degree of Parallelism is 10 because of table property |
|---|
Using a procedure "crt_tab_test4_pk" of PL/SQL to dynamically create 'TEST4_PK' table containing 1e4 lines data
CREATE OR REPLACE PROCEDURE crt_tab_test4_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 test4_pk PURGE';
v_sql_2 := q'[CREATE TABLE test4_pk (
id NUMBER NOT NULL CONSTRAINT test4_only_pk PRIMARY KEY
, flag VARCHAR2(7) NOT NULL
)
SEGMENT CREATION IMMEDIATE
NOLOGGING
PARALLEL 10
]';
v_sql_3 := q'[INSERT /*+ append */ INTO test4_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 test4_pk COMPUTE STATISTICS';
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE v_sql_2;
EXECUTE IMMEDIATE v_sql_3;
COMMIT;
EXECUTE IMMEDIATE 'ANALYZE TABLE test4_pk COMPUTE STATISTICS';
END crt_tab_test4_pk; Procedure created.
EXECUTE crt_tab_test4_pkStatement processed.
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test4_pk| COUNT(*) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 93tx396hacvg0, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test4_pk | Plan hash value: 3300041866 | --------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | --------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 30 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 30 | | | 2 | PX COORDINATOR | | 1 | | 1 |00:00:00.01 | 30 | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 1 | 1 |00:00:00.01 | 25 | | | 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 25 | | | 5 | PX BLOCK ITERATOR | | 1 | 10000 | 10000 |00:00:00.01 | 25 | | |* 6 | TABLE ACCESS FULL| TEST4_PK | 1 | 10000 | 10000 |00:00:00.01 | 25 | | --------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 6 - access(:Z>=:Z AND :Z<=:Z) | Note | ----- | - Degree of Parallelism is 10 because of table property |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test4_pk| COUNT(1) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 90k6pd1nwd1y0, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test4_pk | Plan hash value: 3300041866 | --------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | --------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 30 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 30 | | | 2 | PX COORDINATOR | | 1 | | 1 |00:00:00.01 | 30 | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 1 | 1 |00:00:00.01 | 25 | | | 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 25 | | | 5 | PX BLOCK ITERATOR | | 1 | 10000 | 10000 |00:00:00.01 | 25 | | |* 6 | TABLE ACCESS FULL| TEST4_PK | 1 | 10000 | 10000 |00:00:00.01 | 25 | | --------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 6 - access(:Z>=:Z AND :Z<=:Z) | Note | ----- | - Degree of Parallelism is 10 because of table property |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test4_pk| COUNT(ID) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID cx6ndhy8c8udm, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test4_pk | Plan hash value: 3300041866 | --------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | --------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 29 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 29 | | | 2 | PX COORDINATOR | | 1 | | 1 |00:00:00.01 | 29 | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 1 | 1 |00:00:00.01 | 24 | | | 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 24 | | | 5 | PX BLOCK ITERATOR | | 1 | 10000 | 10000 |00:00:00.01 | 24 | | |* 6 | TABLE ACCESS FULL| TEST4_PK | 1 | 10000 | 10000 |00:00:00.01 | 24 | | --------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 6 - access(:Z>=:Z AND :Z<=:Z) | Note | ----- | - Degree of Parallelism is 10 because of table property |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test4_pk| COUNT(FLAG) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 2t2zbg8kfxu13, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test4_pk | Plan hash value: 3300041866 | --------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | --------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 29 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 29 | | | 2 | PX COORDINATOR | | 1 | | 1 |00:00:00.01 | 29 | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 1 | 1 |00:00:00.01 | 24 | | | 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 24 | | | 5 | PX BLOCK ITERATOR | | 1 | 10000 | 10000 |00:00:00.01 | 24 | | |* 6 | TABLE ACCESS FULL| TEST4_PK | 1 | 10000 | 10000 |00:00:00.01 | 24 | | --------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 6 - access(:Z>=:Z AND :Z<=:Z) | Note | ----- | - Degree of Parallelism is 10 because of table property |
|---|
Using a procedure "crt_tab_test4_bi" of PL/SQL to dynamically create 'TEST4_BI' table containing 1e4 lines data
CREATE OR REPLACE PROCEDURE crt_tab_test4_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 test4_bi PURGE';
v_sql_2 := q'[CREATE TABLE test4_bi (
id NUMBER NOT NULL
, flag VARCHAR2(7) NOT NULL
)
SEGMENT CREATION IMMEDIATE
NOLOGGING
PARALLEL 10
]';
v_sql_3 := 'CREATE BITMAP INDEX test4_only_bi ON test4_bi (flag)';
v_sql_4 := q'[INSERT /*+ append */ INTO test4_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 test4_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 test4_bi COMPUTE STATISTICS';
END crt_tab_test4_bi; Procedure created.
EXECUTE crt_tab_test4_biStatement processed.
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test4_bi| COUNT(*) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 010fd6zzxz93t, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test4_bi | Plan hash value: 1510060295 | --------------------------------------------------------------------------------------------------------- | | 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| TEST4_ONLY_BI | 1 | | 4 |00:00:00.01 | 4 | | --------------------------------------------------------------------------------------------------------- |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test4_bi| COUNT(1) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 1xnhgcgabnzbb, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test4_bi | Plan hash value: 1510060295 | --------------------------------------------------------------------------------------------------------- | | 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| TEST4_ONLY_BI | 1 | | 4 |00:00:00.01 | 4 | | --------------------------------------------------------------------------------------------------------- |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test4_bi| COUNT(ID) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID cp6pfagsk48df, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test4_bi | Plan hash value: 1510060295 | --------------------------------------------------------------------------------------------------------- | | 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| TEST4_ONLY_BI | 1 | | 4 |00:00:00.01 | 4 | | --------------------------------------------------------------------------------------------------------- |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test4_bi| COUNT(FLAG) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 25xx9fmfcv4mx, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test4_bi | Plan hash value: 1510060295 | --------------------------------------------------------------------------------------------------------- | | 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| TEST4_ONLY_BI | 1 | | 4 |00:00:00.01 | 4 | | --------------------------------------------------------------------------------------------------------- |
|---|
Using a procedure "crt_tab_test4_pk_bi" of PL/SQL to dynamically create 'TEST4_PK_BI' table containing 1e4 lines data
CREATE OR REPLACE PROCEDURE crt_tab_test4_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 test4_pk_bi PURGE';
v_sql_2 := q'[CREATE TABLE test4_pk_bi (
id NUMBER NOT NULL CONSTRAINT test4_pk PRIMARY KEY
, flag VARCHAR2(7) NOT NULL
)
SEGMENT CREATION IMMEDIATE
NOLOGGING
PARALLEL 10
]';
v_sql_3 := 'CREATE BITMAP INDEX test4_bi ON test4_pk_bi (flag)';
v_sql_4 := q'[INSERT /*+ append */ INTO test4_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 test4_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 test4_pk_bi COMPUTE STATISTICS';
END crt_tab_test4_pk_bi; Procedure created.
EXECUTE crt_tab_test4_pk_biStatement processed.
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test4_pk_bi| COUNT(*) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID cn9wb2gz9csw9, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test4_pk_bi | Plan hash value: 1466733283 | ---------------------------------------------------------------------------------------------------- | | 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| TEST4_BI | 1 | | 4 |00:00:00.01 | 4 | | ---------------------------------------------------------------------------------------------------- |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test4_pk_bi| COUNT(1) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID gghu5jba7mxv9, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test4_pk_bi | Plan hash value: 1466733283 | ---------------------------------------------------------------------------------------------------- | | 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| TEST4_BI | 1 | | 4 |00:00:00.01 | 4 | | ---------------------------------------------------------------------------------------------------- |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test4_pk_bi| COUNT(ID) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID dngh5k7u34hcg, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test4_pk_bi | Plan hash value: 1466733283 | ---------------------------------------------------------------------------------------------------- | | 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| TEST4_BI | 1 | | 4 |00:00:00.01 | 4 | | ---------------------------------------------------------------------------------------------------- |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test4_pk_bi| COUNT(FLAG) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID fvg9jhpaf4yw1, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test4_pk_bi | Plan hash value: 1466733283 | ---------------------------------------------------------------------------------------------------- | | 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| TEST4_BI | 1 | | 4 |00:00:00.01 | 4 | | ---------------------------------------------------------------------------------------------------- |
|---|