Using a procedure "crt_tab_test2" of PL/SQL to dynamically create 'TEST2' table containing 1e4 lines data
CREATE OR REPLACE PROCEDURE crt_tab_test2
AS
v_num NUMBER;
v_sql_1 VARCHAR2(2000);
v_sql_2 VARCHAR2(2000);
BEGIN
v_num := 1e4;
v_sql_1 := 'DROP TABLE test2 PURGE';
v_sql_2 := q'[CREATE TABLE test2
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 test2 COMPUTE STATISTICS';
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE v_sql_2;
EXECUTE IMMEDIATE 'ANALYZE TABLE test2 COMPUTE STATISTICS';
END crt_tab_test2; EXECUTE crt_tab_test2SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test2| COUNT(*) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 50yskaddddmxj, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test2 | Plan hash value: 2571823673 | --------------------------------------------------------------------------------------------- | | 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| TEST2 | 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 test2| COUNT(1) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID akpkbfdzdvty8, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test2 | Plan hash value: 2571823673 | --------------------------------------------------------------------------------------------- | | 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| TEST2 | 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 test2| COUNT(ID) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 2fhnhnsmc2fh1, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test2 | Plan hash value: 2571823673 | --------------------------------------------------------------------------------------------- | | 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| TEST2 | 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 test2| COUNT(FLAG) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 84tmdmjsjs2gf, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test2 | Plan hash value: 2571823673 | --------------------------------------------------------------------------------------------- | | 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| TEST2 | 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_test2_pk" of PL/SQL to dynamically create 'TEST2_PK' table containing 1e4 lines data
CREATE OR REPLACE PROCEDURE crt_tab_test2_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 test2_pk PURGE';
v_sql_2 := q'[CREATE TABLE test2_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 test2_pk ADD CONSTRAINT test2_only_pk PRIMARY KEY (id)';
EXECUTE IMMEDIATE v_sql_1;
EXECUTE IMMEDIATE v_sql_2;
EXECUTE IMMEDIATE v_sql_3;
EXECUTE IMMEDIATE 'ANALYZE TABLE test2_pk COMPUTE STATISTICS';
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE v_sql_2;
EXECUTE IMMEDIATE v_sql_3;
EXECUTE IMMEDIATE 'ANALYZE TABLE test2_pk COMPUTE STATISTICS';
END crt_tab_test2_pk; EXECUTE crt_tab_test2_pkSELECT /*+ gather_plan_statistics */ COUNT(*) FROM test2_pk| COUNT(*) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 5ccmux2t0w7rb, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test2_pk | Plan hash value: 4037883998 | --------------------------------------------------------------------------------------------- | | 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| TEST2_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(1) FROM test2_pk| COUNT(1) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 3y91zhg1ty0jx, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test2_pk | Plan hash value: 4037883998 | --------------------------------------------------------------------------------------------- | | 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| TEST2_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(id) FROM test2_pk| COUNT(ID) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 57wkx67bys2bj, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test2_pk | Plan hash value: 4037883998 | --------------------------------------------------------------------------------------------- | | 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| TEST2_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 test2_pk| COUNT(FLAG) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 9tq28s7hfw98k, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test2_pk | Plan hash value: 4037883998 | --------------------------------------------------------------------------------------------- | | 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| TEST2_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_test2_bi" of PL/SQL to dynamically create 'TEST2_BI' table containing 1e4 lines data
CREATE OR REPLACE PROCEDURE crt_tab_test2_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 test2_bi PURGE';
v_sql_2 := q'[CREATE TABLE test2_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 test2_only_bi ON test2_bi (flag)';
EXECUTE IMMEDIATE v_sql_1;
EXECUTE IMMEDIATE v_sql_2;
EXECUTE IMMEDIATE v_sql_3;
EXECUTE IMMEDIATE 'ANALYZE TABLE test2_bi COMPUTE STATISTICS';
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE v_sql_2;
EXECUTE IMMEDIATE v_sql_3;
EXECUTE IMMEDIATE 'ANALYZE TABLE test2_bi COMPUTE STATISTICS';
END crt_tab_test2_bi; EXECUTE crt_tab_test2_biSELECT /*+ gather_plan_statistics */ COUNT(*) FROM test2_bi| COUNT(*) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 1mucwqzqfc9g6, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test2_bi | Plan hash value: 738733133 | --------------------------------------------------------------------------------------------------------- | | 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| TEST2_ONLY_BI | 1 | | 4 |00:00:00.01 | 4 | | --------------------------------------------------------------------------------------------------------- |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test2_bi| COUNT(1) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID ff8n1ywzp57y4, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test2_bi | Plan hash value: 738733133 | --------------------------------------------------------------------------------------------------------- | | 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| TEST2_ONLY_BI | 1 | | 4 |00:00:00.01 | 4 | | --------------------------------------------------------------------------------------------------------- |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test2_bi| COUNT(ID) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 7vwjdubs6fzr9, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test2_bi | Plan hash value: 3635498818 | --------------------------------------------------------------------------------------------- | | 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| TEST2_BI | 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 test2_bi| COUNT(FLAG) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID dptsygccvymdd, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test2_bi | Plan hash value: 3885697993 | --------------------------------------------------------------------------------------------------------- | | 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| TEST2_ONLY_BI | 1 | | 4 |00:00:00.01 | 4 | | --------------------------------------------------------------------------------------------------------- |
|---|
Using a procedure "crt_tab_test2_pk_bi" of PL/SQL to dynamically create 'TEST2_PK_BI' table containing 1e4 lines data
CREATE OR REPLACE PROCEDURE crt_tab_test2_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 test2_pk_bi PURGE';
v_sql_2 := q'[CREATE TABLE test2_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 test2_pk_bi ADD CONSTRAINT test2_pk PRIMARY KEY (id)';
v_sql_4 := 'CREATE BITMAP INDEX test2_bi ON test2_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 test2_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 test2_pk_bi COMPUTE STATISTICS';
END crt_tab_test2_pk_bi; EXECUTE crt_tab_test2_pk_biSELECT /*+ gather_plan_statistics */ COUNT(*) FROM test2_pk_bi| COUNT(*) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID cg7p26ztaxnw8, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test2_pk_bi | Plan hash value: 1790107405 | ---------------------------------------------------------------------------------------------------- | | 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| TEST2_BI | 1 | | 4 |00:00:00.01 | 4 | | ---------------------------------------------------------------------------------------------------- |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test2_pk_bi| COUNT(1) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID gajytcztu317h, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test2_pk_bi | Plan hash value: 1790107405 | ---------------------------------------------------------------------------------------------------- | | 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| TEST2_BI | 1 | | 4 |00:00:00.01 | 4 | | ---------------------------------------------------------------------------------------------------- |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test2_pk_bi| COUNT(ID) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 2rdyk4syr5qa7, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test2_pk_bi | Plan hash value: 1790107405 | ---------------------------------------------------------------------------------------------------- | | 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| TEST2_BI | 1 | | 4 |00:00:00.01 | 4 | | ---------------------------------------------------------------------------------------------------- |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test2_pk_bi| COUNT(FLAG) | 10000 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))| PLAN_TABLE_OUTPUT | SQL_ID 373q472fq4brp, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test2_pk_bi | Plan hash value: 3275165930 | ---------------------------------------------------------------------------------------------------- | | 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| TEST2_BI | 1 | | 4 |00:00:00.01 | 4 | | ---------------------------------------------------------------------------------------------------- |
|---|