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.
EXECUTE crt_tab_test3
Statement processed.
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test3
COUNT(*) | 10000 |
---|
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 | | -------------------------------------------------------------------------------------- |
---|
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test3
COUNT(1) | 10000 |
---|
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 | | -------------------------------------------------------------------------------------- |
---|
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test3
COUNT(ID) | 10000 |
---|
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 | | -------------------------------------------------------------------------------------- |
---|
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test3
COUNT(FLAG) | 10000 |
---|
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 | | -------------------------------------------------------------------------------------- |
---|
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.
EXECUTE crt_tab_test3_pk
Statement processed.
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test3_pk
COUNT(*) | 10000 |
---|
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 | | ------------------------------------------------------------------------------------------------- |
---|
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test3_pk
COUNT(1) | 10000 |
---|
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 | | ------------------------------------------------------------------------------------------------- |
---|
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test3_pk
COUNT(ID) | 10000 |
---|
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 | | ------------------------------------------------------------------------------------------------- |
---|
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test3_pk
COUNT(FLAG) | 10000 |
---|
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 | | ------------------------------------------------------------------------------------------------- |
---|
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.
EXECUTE crt_tab_test3_bi
Statement processed.
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test3_bi
COUNT(*) | 10000 |
---|
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 | | --------------------------------------------------------------------------------------------------------- |
---|
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test3_bi
COUNT(1) | 10000 |
---|
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 | | --------------------------------------------------------------------------------------------------------- |
---|
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test3_bi
COUNT(ID) | 10000 |
---|
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 | | --------------------------------------------------------------------------------------------------------- |
---|
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test3_bi
COUNT(FLAG) | 10000 |
---|
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 | | --------------------------------------------------------------------------------------------------------- |
---|
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.
EXECUTE crt_tab_test3_pk_bi
Statement processed.
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM test3_pk_bi
COUNT(*) | 10000 |
---|
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 | | ---------------------------------------------------------------------------------------------------- |
---|
SELECT /*+ gather_plan_statistics */ COUNT(1) FROM test3_pk_bi
COUNT(1) | 10000 |
---|
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 | | ---------------------------------------------------------------------------------------------------- |
---|
SELECT /*+ gather_plan_statistics */ COUNT(id) FROM test3_pk_bi
COUNT(ID) | 10000 |
---|
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 | | ---------------------------------------------------------------------------------------------------- |
---|
SELECT /*+ gather_plan_statistics */ COUNT(flag) FROM test3_pk_bi
COUNT(FLAG) | 10000 |
---|
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 | | ---------------------------------------------------------------------------------------------------- |
---|