Observing Spending Time of Separately Querying COUNT(*|1|id|flag) on some Test Tables

View All Scripts Login to Run Script

Script Name Observing Spending Time of Separately Querying COUNT(*|1|id|flag) on some Test Tables
Description This demo usually uses to observe the spending time of separately querying COUNT(*), COUNT(1), COUNT(id) and COUNT(flag) on some 'Test' tables. Using 3 tables saving the spending time querying COUNT test table, some procedures dynamically creating test table, a special function and another procedure to build this pretty interesting demo. Exactly speaking, this demo should have been splitted into 4 sections (in other words, 4 small pieces of demo): (1) Demo1 used to build test tables (using a very simple approach CTAS) with 'NO PARALLEL'; (2) Demo2 is basically similar to 'Demo1' and its only difference is at a table property 'PARALLEL 10' when building a mode of test tables; (3) Demo3 usually builds test tables (creating some table structures firstly with 'NO PARALLEL' and then adding the appropriate check or index to those tables, at last inserting several test data using hint 'APPEND'); (4) Demo4 is also basically similar to 'Demo3' and its only difference is also at a table property 'PARALLEL 10'; Of course, all Demos previously mentioned use the similar (3 tables) ER Diagram. (1) The ER Diagram of 'Demo1' consists of three tables: 'cnt_method', 'tab_stru' and 'cnt_spd_time'; (2) The ER Diagram of 'Demo2' includes this three tables: 'cnt_method_2', 'tab_stru_2' and 'cnt_spd_time_2'; (3) The ER Diagram of 'Demo3' contains 3 tables: 'cnt_method_3', 'tab_stru_3' and 'cnt_spd_time_3'; (4) The ER Diagram of 'Demo4' has these 3 tables: 'cnt_method_4', 'tab_stru_4' and 'cnt_spd_time_4'; By the way all these test tables are dynamically generated by some Oracle PL/SQL procedures. 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'; The test tables in 'Demo1' have 'TEST', 'TEST_PK', 'TEST_BI' and 'TEST_PK_BI', table 'TEST' has neither PRIMARY KEY nor BITMAP INDEX, table 'TEST_PK' has only PRIMARY KEY, table 'TEST_BI' has only BITMAP INDEX, and table 'TEST_PK_BI' has both PRIMARY KEY and BITMAP INDEX. The test tables in 'Demo2' have 'TEST2', 'TEST2_PK', 'TEST2_BI' and 'TEST2_PK_BI', table 'TEST2' has neither PRIMARY KEY nor BITMAP INDEX, table 'TEST2_PK' has only PRIMARY KEY, table 'TEST2_BI' has only BITMAP INDEX, and table 'TEST2_PK_BI' has both PRIMARY KEY and BITMAP INDEX. The test tables in 'Demo3' have 'TEST3', 'TEST3_PK', 'TEST3_BI' and 'TEST3_PK_BI', 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. The test tables in 'Demo4' have 'TEST4', 'TEST4_PK', 'TEST4_BI' and 'TEST4_PK_BI', table 'TEST4' has neither PRIMARY KEY nor BITMAP INDEX, table 'TEST4_PK' has only PRIMARY KEY, table 'TEST4_BI' has only BITMAP INDEX, and table 'TEST4_PK_BI' has both PRIMARY KEY and BITMAP INDEX. There has another one more important point being emphasized here, my Demo(s) has all tested on Oracle 18.3 and 11.2 (https://github.com/guestart/A-Demo-Spending-Time-of-Querying-COUNT-table). So I am coming here to do some test.
Area SQL General / SQL Query
Contributor Quanwen Zhao
Created Monday December 30, 2019

Statement 1

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

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

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

Procedure created.

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

Statement 3

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

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

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

Procedure created.

Statement 4
Running procedure "crt_tab_test_pk".
```
EXECUTE crt_tab_test_pk
```
```
Statement processed.
```

Statement 5

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

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

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

Procedure created.

Statement 6
Running procedure "crt_tab_test_bi".
```
EXECUTE crt_tab_test_bi
```
```
Statement processed.
```

Statement 7

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

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

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

Procedure created.

Statement 8
Running procedure "crt_tab_test_pk_bi".
```
EXECUTE crt_tab_test_pk_bi
```
```
Statement processed.
```

Statement 9

Using the table 'cnt_method' to specially mark the method of COUNT.

This Demo uses an ER Diagram, amongst which has a parent table 'cnt_method'

CREATE TABLE cnt_method (  
   mark VARCHAR2(4) CONSTRAINT cnt_method_pk PRIMARY KEY,  
   CONSTRAINT cnt_method_ck CHECK (mark IN ('*', '1', 'id', 'flag'))  
)

Table created.

Statement 10

INSERT INTO cnt_method (mark) VALUES ('*')

1 row(s) inserted.

Statement 11

INSERT INTO cnt_method (mark) VALUES ('1')

1 row(s) inserted.

Statement 12

INSERT INTO cnt_method (mark) VALUES ('id')

1 row(s) inserted.

Statement 13

INSERT INTO cnt_method (mark) VALUES ('flag')

1 row(s) inserted.

Statement 14
```
COMMIT
```
```
Statement processed.
```

Statement 15

Using the table 'tab_stru' to specially store the table name and table structure.

This Demo uses an ER Diagram, amongst which has another parent table 'tab_stru'

CREATE TABLE tab_stru (  
   name VARCHAR2(10) NOT NULL,  
   mark VARCHAR2(10) CONSTRAINT tab_stru_pk PRIMARY KEY,  
   CONSTRAINT tab_stru_ck_1 CHECK (name IN ('test', 'test_pk', 'test_bi', 'test_pk_bi')),  
   CONSTRAINT tab_stru_ck_2 CHECK (mark IN ('no_pk_bi', 'only_pk', 'only_bi', 'both_pk_bi'))  
)

Table created.

Statement 16

INSERT INTO tab_stru (name, mark) VALUES ('test', 'no_pk_bi')

1 row(s) inserted.

Statement 17

INSERT INTO tab_stru (name, mark) VALUES ('test_pk', 'only_pk')

1 row(s) inserted.

Statement 18

INSERT INTO tab_stru (name, mark) VALUES ('test_bi', 'only_bi')

1 row(s) inserted.

Statement 19

INSERT INTO tab_stru (name, mark) VALUES ('test_pk_bi', 'both_pk_bi')

1 row(s) inserted.

Statement 20
```
COMMIT
```
```
Statement processed.
```

Statement 21

Using the table 'cnt_spd_time' to specially save the spending time querying COUNT all those test tables.

This Demo uses an ER Diagram, amongst which has a child table 'cnt_spd_time'

CREATE TABLE cnt_spd_time (  
   tab_num   NUMBER NOT NULL,  
   tab_mark  VARCHAR2(10) NOT NULL,  
   cnt_mark  VARCHAR2(4) NOT NULL,  
   spd_time  NUMBER NOT NULL,  
   time_unit VARCHAR2(1) DEFAULT 's',  
   CONSTRAINT cnt_spd_time_pk PRIMARY KEY (tab_num, tab_mark, cnt_mark),  
   CONSTRAINT cnt_spd_time_fk_1 FOREIGN KEY (tab_mark) REFERENCES tab_stru (mark),  
   CONSTRAINT cnt_spd_time_fk_2 FOREIGN KEY (cnt_mark) REFERENCES cnt_method (mark)  
)

Table created.

Statement 22

Using a pretty useful Function 'two_timestamp_interval' to get an interval value of two different timestamp.

CREATE OR REPLACE FUNCTION two_timestamp_interval (endtime IN TIMESTAMP, starttime IN TIMESTAMP)  
RETURN NUMBER  
AS  
   str VARCHAR2(50);  
   seconds  NUMBER;  
   minutes  NUMBER;  
   hours    NUMBER;  
   days     NUMBER;  
BEGIN  
   str := TO_CHAR(endtime - starttime);  
   seconds := TO_NUMBER(SUBSTR(str, INSTR(str, ' ')+7));  
   minutes := TO_NUMBER(SUBSTR(str, INSTR(str, ' ')+4, 2));  
   hours := TO_NUMBER(SUBSTR(str, INSTR(str, ' ')+1, 2));  
   days := TO_NUMBER(SUBSTR(str, 1, INSTR(str, ' ')));  
   RETURN (days*24*60*60 + hours*60*60 + minutes*60 + seconds);  
END two_timestamp_interval;

Function created.

Statement 23

Using a procedure 'cnt_tab_spd_time' of PL/SQL to dynamically save the spending time of querying COUNT all those test tables into the table 'cnt_spd_time'.

CREATE OR REPLACE PROCEDURE cnt_tab_spd_time  
AS  
   line_nums       NUMBER;  
   start_time      TIMESTAMP;  
   end_time        TIMESTAMP;  
   consume_seconds NUMBER;  
   v_name   tab_stru.name%TYPE;  
   v_mark_1 tab_stru.mark%TYPE;  
   v_mark_2 cnt_method.mark%TYPE;  
BEGIN  
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF''';  
   FOR cur1 IN (SELECT * FROM tab_stru ORDER BY ROWID) LOOP  
      FOR cur2 IN (SELECT * FROM cnt_method ORDER BY ROWID) LOOP  
         v_name := cur1.name;  
         v_mark_1 := cur1.mark;  
         v_mark_2 := cur2.mark;  
         start_time := SYSTIMESTAMP;  
         EXECUTE IMMEDIATE 'SELECT COUNT(' || v_mark_2 || ') FROM ' || v_name INTO line_nums;  
         end_time := SYSTIMESTAMP;  
         consume_seconds := two_timestamp_interval(end_time, start_time);  
         INSERT INTO cnt_spd_time (tab_num, tab_mark, cnt_mark, spd_time, time_unit) VALUES (line_nums, v_mark_1, v_mark_2, consume_seconds, DEFAULT);  
         COMMIT;  
      END LOOP;  
   END LOOP;  
END cnt_tab_spd_time;

Procedure created.

Statement 24
Running procedure "cnt_tab_spd_time".
```
EXECUTE cnt_tab_spd_time
```
```
Statement processed.
```

Statement 25

Querying all the values of table 'cnt_spd_time'.

SELECT * FROM cnt_spd_time


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi * .001212 s
10000 no_pk_bi 1 .001209 s
10000 no_pk_bi id .001598 s
10000 no_pk_bi flag .001572 s
10000 only_pk * .00179 s
10000 only_pk 1 .001215 s
10000 only_pk id .00146 s
10000 only_pk flag .001455 s
10000 only_bi * .001091 s
10000 only_bi 1 .001093 s
10000 only_bi id .001448 s
10000 only_bi flag .001324 s
10000 both_pk_bi * .001001 s
10000 both_pk_bi 1 .000992 s
10000 both_pk_bi id .001128 s
10000 both_pk_bi flag .001235 s

16 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	*	.001212	s
10000	no_pk_bi	1	.001209	s
10000	no_pk_bi	id	.001598	s
10000	no_pk_bi	flag	.001572	s
10000	only_pk	*	.00179	s
10000	only_pk	1	.001215	s
10000	only_pk	id	.00146	s
10000	only_pk	flag	.001455	s
10000	only_bi	*	.001091	s
10000	only_bi	1	.001093	s
10000	only_bi	id	.001448	s
10000	only_bi	flag	.001324	s
10000	both_pk_bi	*	.001001	s
10000	both_pk_bi	1	.000992	s
10000	both_pk_bi	id	.001128	s
10000	both_pk_bi	flag	.001235	s

Statement 26

Querying some values of table 'cnt_spd_time' and filtering by using "WHERE tab_mark = 'no_pk_bi' ORDER BY ROWID".

SELECT * FROM cnt_spd_time WHERE tab_mark = 'no_pk_bi' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi * .001212 s
10000 no_pk_bi 1 .001209 s
10000 no_pk_bi id .001598 s
10000 no_pk_bi flag .001572 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	*	.001212	s
10000	no_pk_bi	1	.001209	s
10000	no_pk_bi	id	.001598	s
10000	no_pk_bi	flag	.001572	s

Statement 27

Querying some values of table 'cnt_spd_time' and filtering by using "WHERE tab_mark = 'only_pk' ORDER BY ROWID".

SELECT * FROM cnt_spd_time WHERE tab_mark = 'only_pk' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 only_pk * .00179 s
10000 only_pk 1 .001215 s
10000 only_pk id .00146 s
10000 only_pk flag .001455 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	only_pk	*	.00179	s
10000	only_pk	1	.001215	s
10000	only_pk	id	.00146	s
10000	only_pk	flag	.001455	s

Statement 28

Querying some values of table 'cnt_spd_time' and filtering by using "WHERE tab_mark = 'only_bi' ORDER BY ROWID".

SELECT * FROM cnt_spd_time WHERE tab_mark = 'only_bi' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 only_bi * .001091 s
10000 only_bi 1 .001093 s
10000 only_bi id .001448 s
10000 only_bi flag .001324 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	only_bi	*	.001091	s
10000	only_bi	1	.001093	s
10000	only_bi	id	.001448	s
10000	only_bi	flag	.001324	s

Statement 29

Querying some values of table 'cnt_spd_time' and filtering by using "WHERE tab_mark = 'both_pk_bi' ORDER BY ROWID".

SELECT * FROM cnt_spd_time WHERE tab_mark = 'both_pk_bi' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 both_pk_bi * .001001 s
10000 both_pk_bi 1 .000992 s
10000 both_pk_bi id .001128 s
10000 both_pk_bi flag .001235 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	both_pk_bi	*	.001001	s
10000	both_pk_bi	1	.000992	s
10000	both_pk_bi	id	.001128	s
10000	both_pk_bi	flag	.001235	s

Statement 30

Querying some values of table 'cnt_spd_time' and filtering by using "WHERE cnt_mark = '*' ORDER BY ROWID".

SELECT * FROM cnt_spd_time WHERE cnt_mark = '*' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi * .001212 s
10000 only_pk * .00179 s
10000 only_bi * .001091 s
10000 both_pk_bi * .001001 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	*	.001212	s
10000	only_pk	*	.00179	s
10000	only_bi	*	.001091	s
10000	both_pk_bi	*	.001001	s

Statement 31

Querying some values of table 'cnt_spd_time' and filtering by using "WHERE cnt_mark = '1' ORDER BY ROWID".

SELECT * FROM cnt_spd_time WHERE cnt_mark = '1' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi 1 .001209 s
10000 only_pk 1 .001215 s
10000 only_bi 1 .001093 s
10000 both_pk_bi 1 .000992 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	1	.001209	s
10000	only_pk	1	.001215	s
10000	only_bi	1	.001093	s
10000	both_pk_bi	1	.000992	s

Statement 32

Querying some values of table 'cnt_spd_time' and filtering by using "WHERE cnt_mark = 'id' ORDER BY ROWID".

SELECT * FROM cnt_spd_time WHERE cnt_mark = 'id' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi id .001598 s
10000 only_pk id .00146 s
10000 only_bi id .001448 s
10000 both_pk_bi id .001128 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	id	.001598	s
10000	only_pk	id	.00146	s
10000	only_bi	id	.001448	s
10000	both_pk_bi	id	.001128	s

Statement 33

Querying some values of table 'cnt_spd_time' and filtering by using "WHERE cnt_mark = 'flag' ORDER BY ROWID".

The End Section of 'Demo1':

SELECT * FROM cnt_spd_time WHERE cnt_mark = 'flag' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi flag .001572 s
10000 only_pk flag .001455 s
10000 only_bi flag .001324 s
10000 both_pk_bi flag .001235 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	flag	.001572	s
10000	only_pk	flag	.001455	s
10000	only_bi	flag	.001324	s
10000	both_pk_bi	flag	.001235	s

Statement 34

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

The Start Section of 'Demo2': 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;

Procedure created.

Statement 35
Running procedure "crt_tab_test2".
```
EXECUTE crt_tab_test2
```
```
Statement processed.
```

Statement 36

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST2_PK' table, meanwhile after finishing creating 'TEST2_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_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;

Procedure created.

Statement 37
Running procedure "crt_tab_test2_pk".
```
EXECUTE crt_tab_test2_pk
```
```
Statement processed.
```

Statement 38

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST2_BI' table, meanwhile after finishing creating 'TEST2_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_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;

Procedure created.

Statement 39
Running procedure "crt_tab_test2_bi".
```
EXECUTE crt_tab_test2_bi
```
```
Statement processed.
```

Statement 40

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST2_PK_BI' table, meanwhile after finishing creating 'TEST2_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_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;

Procedure created.

Statement 41
Running procedure "crt_tab_test2_pk_bi".
```
EXECUTE crt_tab_test2_pk_bi
```
```
Statement processed.
```

Statement 42

Using the table 'cnt_method_2' to specially mark the method of COUNT.

This Demo uses an ER Diagram, amongst which has a parent table 'cnt_method_2'

CREATE TABLE cnt_method_2 ( 
   mark VARCHAR2(4) CONSTRAINT cnt_method_2_pk PRIMARY KEY, 
   CONSTRAINT cnt_method_2_ck CHECK (mark IN ('*', '1', 'id', 'flag')) 
)

Table created.

Statement 43

INSERT INTO cnt_method_2 (mark) VALUES ('*')

1 row(s) inserted.

Statement 44

INSERT INTO cnt_method_2 (mark) VALUES ('1')

1 row(s) inserted.

Statement 45

INSERT INTO cnt_method_2 (mark) VALUES ('id')

1 row(s) inserted.

Statement 46

INSERT INTO cnt_method_2 (mark) VALUES ('flag')

1 row(s) inserted.

Statement 47
```
COMMIT
```
```
Statement processed.
```

Statement 48

Using the table 'tab_stru_2' to specially store the table name and table structure.

This Demo uses an ER Diagram, amongst which has another parent table 'tab_stru_2'

CREATE TABLE tab_stru_2 ( 
   name VARCHAR2(12) NOT NULL, 
   mark VARCHAR2(10) CONSTRAINT tab_stru_2_pk PRIMARY KEY, 
   CONSTRAINT tab_stru_2_ck_1 CHECK (name IN ('test2', 'test2_pk', 'test2_bi', 'test2_pk_bi')), 
   CONSTRAINT tab_stru_2_ck_2 CHECK (mark IN ('no_pk_bi', 'only_pk', 'only_bi', 'both_pk_bi')) 
)

Table created.

Statement 49

INSERT INTO tab_stru_2 (name, mark) VALUES ('test2', 'no_pk_bi')

1 row(s) inserted.

Statement 50

INSERT INTO tab_stru_2 (name, mark) VALUES ('test2_pk', 'only_pk')

1 row(s) inserted.

Statement 51

INSERT INTO tab_stru_2 (name, mark) VALUES ('test2_bi', 'only_bi')

1 row(s) inserted.

Statement 52

INSERT INTO tab_stru_2 (name, mark) VALUES ('test2_pk_bi', 'both_pk_bi')

1 row(s) inserted.

Statement 53
```
COMMIT
```
```
Statement processed.
```

Statement 54

Using the table 'cnt_spd_time_2' to specially save the spending time querying COUNT all those test tables.

This Demo uses an ER Diagram, amongst which has a child table 'cnt_spd_time_2'

CREATE TABLE cnt_spd_time_2 ( 
   tab_num   NUMBER NOT NULL, 
   tab_mark  VARCHAR2(10) NOT NULL, 
   cnt_mark  VARCHAR2(4) NOT NULL, 
   spd_time  NUMBER NOT NULL, 
   time_unit VARCHAR2(1) DEFAULT 's', 
   CONSTRAINT cnt_spd_time_2_pk PRIMARY KEY (tab_num, tab_mark, cnt_mark), 
   CONSTRAINT cnt_spd_time_2_fk_1 FOREIGN KEY (tab_mark) REFERENCES tab_stru_2 (mark), 
   CONSTRAINT cnt_spd_time_2_fk_2 FOREIGN KEY (cnt_mark) REFERENCES cnt_method_2 (mark) 
)

Table created.

Statement 55

Using a pretty useful Function 'two_timestamp_interval' to get an interval value of two different timestamp.

CREATE OR REPLACE FUNCTION two_timestamp_interval (endtime IN TIMESTAMP, starttime IN TIMESTAMP) 
RETURN NUMBER 
AS 
   str VARCHAR2(50); 
   seconds  NUMBER; 
   minutes  NUMBER; 
   hours    NUMBER; 
   days     NUMBER; 
BEGIN 
   str := TO_CHAR(endtime - starttime); 
   seconds := TO_NUMBER(SUBSTR(str, INSTR(str, ' ')+7)); 
   minutes := TO_NUMBER(SUBSTR(str, INSTR(str, ' ')+4, 2)); 
   hours := TO_NUMBER(SUBSTR(str, INSTR(str, ' ')+1, 2)); 
   days := TO_NUMBER(SUBSTR(str, 1, INSTR(str, ' '))); 
   RETURN (days*24*60*60 + hours*60*60 + minutes*60 + seconds); 
END two_timestamp_interval;

Function created.

Statement 56

Using a procedure 'cnt_tab_spd_time_2' of PL/SQL to dynamically save the spending time of querying COUNT all those test tables into the table 'cnt_spd_time_2'.

CREATE OR REPLACE PROCEDURE cnt_tab_spd_time_2 
AS 
   line_nums       NUMBER; 
   start_time      TIMESTAMP; 
   end_time        TIMESTAMP; 
   consume_seconds NUMBER; 
   v_name   tab_stru_2.name%TYPE; 
   v_mark_1 tab_stru_2.mark%TYPE; 
   v_mark_2 cnt_method_2.mark%TYPE; 
BEGIN 
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF'''; 
   FOR cur1 IN (SELECT * FROM tab_stru_2 ORDER BY ROWID) LOOP 
      FOR cur2 IN (SELECT * FROM cnt_method_2 ORDER BY ROWID) LOOP 
         v_name := cur1.name; 
         v_mark_1 := cur1.mark; 
         v_mark_2 := cur2.mark; 
         start_time := SYSTIMESTAMP; 
         EXECUTE IMMEDIATE 'SELECT COUNT(' || v_mark_2 || ') FROM ' || v_name INTO line_nums; 
         end_time := SYSTIMESTAMP; 
         consume_seconds := two_timestamp_interval(end_time, start_time); 
         INSERT INTO cnt_spd_time_2 (tab_num, tab_mark, cnt_mark, spd_time, time_unit) VALUES (line_nums, v_mark_1, v_mark_2, consume_seconds, DEFAULT); 
         COMMIT; 
      END LOOP; 
   END LOOP; 
END cnt_tab_spd_time_2;

Procedure created.

Statement 57
Running procedure "cnt_tab_spd_time_2".
```
EXECUTE cnt_tab_spd_time_2
```
```
Statement processed.
```

Statement 58

Querying all the values of table 'cnt_spd_time_2'.

SELECT * FROM cnt_spd_time_2


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi * .001463 s
10000 no_pk_bi 1 .00145 s
10000 no_pk_bi id .001442 s
10000 no_pk_bi flag .001458 s
10000 only_pk * .00139 s
10000 only_pk 1 .001296 s
10000 only_pk id .001301 s
10000 only_pk flag .001394 s
10000 only_bi * .001031 s
10000 only_bi 1 .000986 s
10000 only_bi id .001445 s
10000 only_bi flag .00117 s
10000 both_pk_bi * .000983 s
10000 both_pk_bi 1 .000962 s
10000 both_pk_bi id .001025 s
10000 both_pk_bi flag .001322 s

16 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	*	.001463	s
10000	no_pk_bi	1	.00145	s
10000	no_pk_bi	id	.001442	s
10000	no_pk_bi	flag	.001458	s
10000	only_pk	*	.00139	s
10000	only_pk	1	.001296	s
10000	only_pk	id	.001301	s
10000	only_pk	flag	.001394	s
10000	only_bi	*	.001031	s
10000	only_bi	1	.000986	s
10000	only_bi	id	.001445	s
10000	only_bi	flag	.00117	s
10000	both_pk_bi	*	.000983	s
10000	both_pk_bi	1	.000962	s
10000	both_pk_bi	id	.001025	s
10000	both_pk_bi	flag	.001322	s

Statement 59

Querying some values of table 'cnt_spd_time_2' and filtering by using "WHERE tab_mark = 'no_pk_bi' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_2 WHERE tab_mark = 'no_pk_bi' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi * .001463 s
10000 no_pk_bi 1 .00145 s
10000 no_pk_bi id .001442 s
10000 no_pk_bi flag .001458 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	*	.001463	s
10000	no_pk_bi	1	.00145	s
10000	no_pk_bi	id	.001442	s
10000	no_pk_bi	flag	.001458	s

Statement 60

Querying some values of table 'cnt_spd_time_2' and filtering by using "WHERE tab_mark = 'only_pk' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_2 WHERE tab_mark = 'only_pk' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 only_pk * .00139 s
10000 only_pk 1 .001296 s
10000 only_pk id .001301 s
10000 only_pk flag .001394 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	only_pk	*	.00139	s
10000	only_pk	1	.001296	s
10000	only_pk	id	.001301	s
10000	only_pk	flag	.001394	s

Statement 61

Querying some values of table 'cnt_spd_time_2' and filtering by using "WHERE tab_mark = 'only_bi' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_2 WHERE tab_mark = 'only_bi' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 only_bi * .001031 s
10000 only_bi 1 .000986 s
10000 only_bi id .001445 s
10000 only_bi flag .00117 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	only_bi	*	.001031	s
10000	only_bi	1	.000986	s
10000	only_bi	id	.001445	s
10000	only_bi	flag	.00117	s

Statement 62

Querying some values of table 'cnt_spd_time_2' and filtering by using "WHERE tab_mark = 'both_pk_bi' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_2 WHERE tab_mark = 'both_pk_bi' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 both_pk_bi * .000983 s
10000 both_pk_bi 1 .000962 s
10000 both_pk_bi id .001025 s
10000 both_pk_bi flag .001322 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	both_pk_bi	*	.000983	s
10000	both_pk_bi	1	.000962	s
10000	both_pk_bi	id	.001025	s
10000	both_pk_bi	flag	.001322	s

Statement 63

Querying some values of table 'cnt_spd_time_2' and filtering by using "WHERE cnt_mark = '*' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_2 WHERE cnt_mark = '*' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi * .001463 s
10000 only_pk * .00139 s
10000 only_bi * .001031 s
10000 both_pk_bi * .000983 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	*	.001463	s
10000	only_pk	*	.00139	s
10000	only_bi	*	.001031	s
10000	both_pk_bi	*	.000983	s

Statement 64

Querying some values of table 'cnt_spd_time_2' and filtering by using "WHERE cnt_mark = '1' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_2 WHERE cnt_mark = '1' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi 1 .00145 s
10000 only_pk 1 .001296 s
10000 only_bi 1 .000986 s
10000 both_pk_bi 1 .000962 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	1	.00145	s
10000	only_pk	1	.001296	s
10000	only_bi	1	.000986	s
10000	both_pk_bi	1	.000962	s

Statement 65

Querying some values of table 'cnt_spd_time_2' and filtering by using "WHERE cnt_mark = 'id' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_2 WHERE cnt_mark = 'id' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi id .001442 s
10000 only_pk id .001301 s
10000 only_bi id .001445 s
10000 both_pk_bi id .001025 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	id	.001442	s
10000	only_pk	id	.001301	s
10000	only_bi	id	.001445	s
10000	both_pk_bi	id	.001025	s

Statement 66

Querying some values of table 'cnt_spd_time_2' and filtering by using "WHERE cnt_mark = 'flag' ORDER BY ROWID".

The End Section of 'Demo2':

SELECT * FROM cnt_spd_time_2 WHERE cnt_mark = 'flag' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi flag .001458 s
10000 only_pk flag .001394 s
10000 only_bi flag .00117 s
10000 both_pk_bi flag .001322 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	flag	.001458	s
10000	only_pk	flag	.001394	s
10000	only_bi	flag	.00117	s
10000	both_pk_bi	flag	.001322	s

Statement 67

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.

The Start Section of 'Demo3': 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 68
Running procedure "crt_tab_test3".
```
EXECUTE crt_tab_test3
```
```
Statement processed.
```

Statement 69

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 70
Running procedure "crt_tab_test3_pk".
```
EXECUTE crt_tab_test3_pk
```
```
Statement processed.
```

Statement 71

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 72
Running procedure "crt_tab_test3_bi".
```
EXECUTE crt_tab_test3_bi
```
```
Statement processed.
```

Statement 73

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 74
Running procedure "crt_tab_test3_pk_bi".
```
EXECUTE crt_tab_test3_pk_bi
```
```
Statement processed.
```

Statement 75

Using the table 'cnt_method_3' to specially mark the method of COUNT.

This Demo uses an ER Diagram, amongst which has a parent table 'cnt_method_3'

CREATE TABLE cnt_method_3 ( 
   mark VARCHAR2(4) CONSTRAINT cnt_method_3_pk PRIMARY KEY, 
   CONSTRAINT cnt_method_3_ck CHECK (mark IN ('*', '1', 'id', 'flag')) 
)

Table created.

Statement 76

INSERT INTO cnt_method_3 (mark) VALUES ('*')

1 row(s) inserted.

Statement 77

INSERT INTO cnt_method_3 (mark) VALUES ('1')

1 row(s) inserted.

Statement 78

INSERT INTO cnt_method_3 (mark) VALUES ('id')

1 row(s) inserted.

Statement 79

INSERT INTO cnt_method_3 (mark) VALUES ('flag')

1 row(s) inserted.

Statement 80
```
COMMIT
```
```
Statement processed.
```

Statement 81

Using the table 'tab_stru_3' to specially store the table name and table structure.

This Demo uses an ER Diagram, amongst which has another parent table 'tab_stru_3'

CREATE TABLE tab_stru_3 ( 
   name VARCHAR2(12) NOT NULL, 
   mark VARCHAR2(10) CONSTRAINT tab_stru_3_pk PRIMARY KEY, 
   CONSTRAINT tab_stru_3_ck_1 CHECK (name IN ('test3', 'test3_pk', 'test3_bi', 'test3_pk_bi')), 
   CONSTRAINT tab_stru_3_ck_2 CHECK (mark IN ('no_pk_bi', 'only_pk', 'only_bi', 'both_pk_bi')) 
)

Table created.

Statement 82

INSERT INTO tab_stru_3 (name, mark) VALUES ('test3', 'no_pk_bi')

1 row(s) inserted.

Statement 83

INSERT INTO tab_stru_3 (name, mark) VALUES ('test3_pk', 'only_pk')

1 row(s) inserted.

Statement 84

INSERT INTO tab_stru_3 (name, mark) VALUES ('test3_bi', 'only_bi')

1 row(s) inserted.

Statement 85

INSERT INTO tab_stru_3 (name, mark) VALUES ('test3_pk_bi', 'both_pk_bi')

1 row(s) inserted.

Statement 86
```
COMMIT
```
```
Statement processed.
```

Statement 87

Using the table 'cnt_spd_time_3' to specially save the spending time querying COUNT all those test tables.

This Demo uses an ER Diagram, amongst which has a child table 'cnt_spd_time_3'

CREATE TABLE cnt_spd_time_3 ( 
   tab_num   NUMBER NOT NULL, 
   tab_mark  VARCHAR2(10) NOT NULL, 
   cnt_mark  VARCHAR2(4) NOT NULL, 
   spd_time  NUMBER NOT NULL, 
   time_unit VARCHAR2(1) DEFAULT 's', 
   CONSTRAINT cnt_spd_time_3_pk PRIMARY KEY (tab_num, tab_mark, cnt_mark), 
   CONSTRAINT cnt_spd_time_3_fk_1 FOREIGN KEY (tab_mark) REFERENCES tab_stru_3 (mark), 
   CONSTRAINT cnt_spd_time_3_fk_2 FOREIGN KEY (cnt_mark) REFERENCES cnt_method_3 (mark) 
)

Table created.

Statement 88

Using a pretty useful Function 'two_timestamp_interval' to get an interval value of two different timestamp.

CREATE OR REPLACE FUNCTION two_timestamp_interval (endtime IN TIMESTAMP, starttime IN TIMESTAMP) 
RETURN NUMBER 
AS 
   str VARCHAR2(50); 
   seconds  NUMBER; 
   minutes  NUMBER; 
   hours    NUMBER; 
   days     NUMBER; 
BEGIN 
   str := TO_CHAR(endtime - starttime); 
   seconds := TO_NUMBER(SUBSTR(str, INSTR(str, ' ')+7)); 
   minutes := TO_NUMBER(SUBSTR(str, INSTR(str, ' ')+4, 2)); 
   hours := TO_NUMBER(SUBSTR(str, INSTR(str, ' ')+1, 2)); 
   days := TO_NUMBER(SUBSTR(str, 1, INSTR(str, ' '))); 
   RETURN (days*24*60*60 + hours*60*60 + minutes*60 + seconds); 
END two_timestamp_interval;

Function created.

Statement 89

Using a procedure 'cnt_tab_spd_time_3' of PL/SQL to dynamically save the spending time of querying COUNT all those test tables into the table 'cnt_spd_time_3'.

CREATE OR REPLACE PROCEDURE cnt_tab_spd_time_3 
AS 
   line_nums       NUMBER; 
   start_time      TIMESTAMP; 
   end_time        TIMESTAMP; 
   consume_seconds NUMBER; 
   v_name   tab_stru_3.name%TYPE; 
   v_mark_1 tab_stru_3.mark%TYPE; 
   v_mark_2 cnt_method_3.mark%TYPE; 
BEGIN 
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF'''; 
   FOR cur1 IN (SELECT * FROM tab_stru_3 ORDER BY ROWID) LOOP 
      FOR cur2 IN (SELECT * FROM cnt_method_3 ORDER BY ROWID) LOOP 
         v_name := cur1.name; 
         v_mark_1 := cur1.mark; 
         v_mark_2 := cur2.mark; 
         start_time := SYSTIMESTAMP; 
         EXECUTE IMMEDIATE 'SELECT COUNT(' || v_mark_2 || ') FROM ' || v_name INTO line_nums; 
         end_time := SYSTIMESTAMP; 
         consume_seconds := two_timestamp_interval(end_time, start_time); 
         INSERT INTO cnt_spd_time_3 (tab_num, tab_mark, cnt_mark, spd_time, time_unit) VALUES (line_nums, v_mark_1, v_mark_2, consume_seconds, DEFAULT); 
         COMMIT; 
      END LOOP; 
   END LOOP; 
END cnt_tab_spd_time_3;

Procedure created.

Statement 90
Running procedure "cnt_tab_spd_time_3".
```
EXECUTE cnt_tab_spd_time_3
```
```
Statement processed.
```

Statement 91

Querying all the values of table 'cnt_spd_time_3'.

SELECT * FROM cnt_spd_time_3


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi * .001094 s
10000 no_pk_bi 1 .001109 s
10000 no_pk_bi id .001008 s
10000 no_pk_bi flag .001307 s
10000 only_pk * .001263 s
10000 only_pk 1 .001285 s
10000 only_pk id .001237 s
10000 only_pk flag .001329 s
10000 only_bi * .001018 s
10000 only_bi 1 .000986 s
10000 only_bi id .001023 s
10000 only_bi flag .000996 s
10000 both_pk_bi * .00097 s
10000 both_pk_bi 1 .001036 s
10000 both_pk_bi id .001009 s
10000 both_pk_bi flag .001026 s

16 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	*	.001094	s
10000	no_pk_bi	1	.001109	s
10000	no_pk_bi	id	.001008	s
10000	no_pk_bi	flag	.001307	s
10000	only_pk	*	.001263	s
10000	only_pk	1	.001285	s
10000	only_pk	id	.001237	s
10000	only_pk	flag	.001329	s
10000	only_bi	*	.001018	s
10000	only_bi	1	.000986	s
10000	only_bi	id	.001023	s
10000	only_bi	flag	.000996	s
10000	both_pk_bi	*	.00097	s
10000	both_pk_bi	1	.001036	s
10000	both_pk_bi	id	.001009	s
10000	both_pk_bi	flag	.001026	s

Statement 92

Querying some values of table 'cnt_spd_time_3' and filtering by using "WHERE tab_mark = 'no_pk_bi' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_3 WHERE tab_mark = 'no_pk_bi' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi * .001094 s
10000 no_pk_bi 1 .001109 s
10000 no_pk_bi id .001008 s
10000 no_pk_bi flag .001307 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	*	.001094	s
10000	no_pk_bi	1	.001109	s
10000	no_pk_bi	id	.001008	s
10000	no_pk_bi	flag	.001307	s

Statement 93

Querying some values of table 'cnt_spd_time_3' and filtering by using "WHERE tab_mark = 'only_pk' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_3 WHERE tab_mark = 'only_pk' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 only_pk * .001263 s
10000 only_pk 1 .001285 s
10000 only_pk id .001237 s
10000 only_pk flag .001329 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	only_pk	*	.001263	s
10000	only_pk	1	.001285	s
10000	only_pk	id	.001237	s
10000	only_pk	flag	.001329	s

Statement 94

Querying some values of table 'cnt_spd_time_3' and filtering by using "WHERE tab_mark = 'only_bi' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_3 WHERE tab_mark = 'only_bi' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 only_bi * .001018 s
10000 only_bi 1 .000986 s
10000 only_bi id .001023 s
10000 only_bi flag .000996 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	only_bi	*	.001018	s
10000	only_bi	1	.000986	s
10000	only_bi	id	.001023	s
10000	only_bi	flag	.000996	s

Statement 95

Querying some values of table 'cnt_spd_time_3' and filtering by using "WHERE tab_mark = 'both_pk_bi' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_3 WHERE tab_mark = 'both_pk_bi' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 both_pk_bi * .00097 s
10000 both_pk_bi 1 .001036 s
10000 both_pk_bi id .001009 s
10000 both_pk_bi flag .001026 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	both_pk_bi	*	.00097	s
10000	both_pk_bi	1	.001036	s
10000	both_pk_bi	id	.001009	s
10000	both_pk_bi	flag	.001026	s

Statement 96

Querying some values of table 'cnt_spd_time_3' and filtering by using "WHERE cnt_mark = '*' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_3 WHERE cnt_mark = '*' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi * .001094 s
10000 only_pk * .001263 s
10000 only_bi * .001018 s
10000 both_pk_bi * .00097 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	*	.001094	s
10000	only_pk	*	.001263	s
10000	only_bi	*	.001018	s
10000	both_pk_bi	*	.00097	s

Statement 97

Querying some values of table 'cnt_spd_time_3' and filtering by using "WHERE cnt_mark = '1' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_3 WHERE cnt_mark = '1' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi 1 .001109 s
10000 only_pk 1 .001285 s
10000 only_bi 1 .000986 s
10000 both_pk_bi 1 .001036 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	1	.001109	s
10000	only_pk	1	.001285	s
10000	only_bi	1	.000986	s
10000	both_pk_bi	1	.001036	s

Statement 98

Querying some values of table 'cnt_spd_time_3' and filtering by using "WHERE cnt_mark = 'id' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_3 WHERE cnt_mark = 'id' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi id .001008 s
10000 only_pk id .001237 s
10000 only_bi id .001023 s
10000 both_pk_bi id .001009 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	id	.001008	s
10000	only_pk	id	.001237	s
10000	only_bi	id	.001023	s
10000	both_pk_bi	id	.001009	s

Statement 99

Querying some values of table 'cnt_spd_time_3' and filtering by using "WHERE cnt_mark = 'flag' ORDER BY ROWID".

The End Section of 'Demo3':

SELECT * FROM cnt_spd_time_3 WHERE cnt_mark = 'flag' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi flag .001307 s
10000 only_pk flag .001329 s
10000 only_bi flag .000996 s
10000 both_pk_bi flag .001026 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	flag	.001307	s
10000	only_pk	flag	.001329	s
10000	only_bi	flag	.000996	s
10000	both_pk_bi	flag	.001026	s

Statement 100

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

The Start Section of 'Demo4': 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.

Statement 101
Running procedure "crt_tab_test4".
```
EXECUTE crt_tab_test4
```
```
Statement processed.
```

Statement 102

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST4_PK' table, meanwhile after finishing creating 'TEST4_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_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.

Statement 103
Running procedure "crt_tab_test4_pk".
```
EXECUTE crt_tab_test4_pk
```
```
Statement processed.
```

Statement 104

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST4_BI' table, meanwhile after finishing creating 'TEST4_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_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.

Statement 105
Running procedure "crt_tab_test4_bi".
```
EXECUTE crt_tab_test4_bi
```
```
Statement processed.
```

Statement 106

In this procedure I use 'q quoting' to concatenate the VARCHAR2 variable creating 'TEST4_PK_BI' table, meanwhile after finishing creating 'TEST4_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_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.

Statement 107
Running procedure "crt_tab_test4_pk_bi".
```
EXECUTE crt_tab_test4_pk_bi
```
```
Statement processed.
```

Statement 108

Using the table 'cnt_method_4' to specially mark the method of COUNT.

This Demo uses an ER Diagram, amongst which has a parent table 'cnt_method_4'

CREATE TABLE cnt_method_4 ( 
   mark VARCHAR2(4) CONSTRAINT cnt_method_4_pk PRIMARY KEY, 
   CONSTRAINT cnt_method_4_ck CHECK (mark IN ('*', '1', 'id', 'flag')) 
)

Table created.

Statement 109

INSERT INTO cnt_method_4 (mark) VALUES ('*')

1 row(s) inserted.

Statement 110

INSERT INTO cnt_method_4 (mark) VALUES ('1')

1 row(s) inserted.

Statement 111

INSERT INTO cnt_method_4 (mark) VALUES ('id')

1 row(s) inserted.

Statement 112

INSERT INTO cnt_method_4 (mark) VALUES ('flag')

1 row(s) inserted.

Statement 113
```
COMMIT
```
```
Statement processed.
```

Statement 114

Using the table 'tab_stru_4' to specially store the table name and table structure.

This Demo uses an ER Diagram, amongst which has another parent table 'tab_stru_4'

CREATE TABLE tab_stru_4 ( 
   name VARCHAR2(12) NOT NULL, 
   mark VARCHAR2(10) CONSTRAINT tab_stru_4_pk PRIMARY KEY, 
   CONSTRAINT tab_stru_4_ck_1 CHECK (name IN ('test4', 'test4_pk', 'test4_bi', 'test4_pk_bi')), 
   CONSTRAINT tab_stru_4_ck_2 CHECK (mark IN ('no_pk_bi', 'only_pk', 'only_bi', 'both_pk_bi')) 
)

Table created.

Statement 115

INSERT INTO tab_stru_4 (name, mark) VALUES ('test4', 'no_pk_bi')

1 row(s) inserted.

Statement 116

INSERT INTO tab_stru_4 (name, mark) VALUES ('test4_pk', 'only_pk')

1 row(s) inserted.

Statement 117

INSERT INTO tab_stru_4 (name, mark) VALUES ('test4_bi', 'only_bi')

1 row(s) inserted.

Statement 118

INSERT INTO tab_stru_4 (name, mark) VALUES ('test4_pk_bi', 'both_pk_bi')

1 row(s) inserted.

Statement 119
```
COMMIT
```
```
Statement processed.
```

Statement 120

Using the table 'cnt_spd_time_4' to specially save the spending time querying COUNT all those test tables.

This Demo uses an ER Diagram, amongst which has a child table 'cnt_spd_time_4'

CREATE TABLE cnt_spd_time_4 ( 
   tab_num   NUMBER NOT NULL, 
   tab_mark  VARCHAR2(10) NOT NULL, 
   cnt_mark  VARCHAR2(4) NOT NULL, 
   spd_time  NUMBER NOT NULL, 
   time_unit VARCHAR2(1) DEFAULT 's', 
   CONSTRAINT cnt_spd_time_4_pk PRIMARY KEY (tab_num, tab_mark, cnt_mark), 
   CONSTRAINT cnt_spd_time_4_fk_1 FOREIGN KEY (tab_mark) REFERENCES tab_stru_4 (mark), 
   CONSTRAINT cnt_spd_time_4_fk_2 FOREIGN KEY (cnt_mark) REFERENCES cnt_method_4 (mark) 
)

Table created.

Statement 121

Using a pretty useful Function 'two_timestamp_interval' to get an interval value of two different timestamp.

CREATE OR REPLACE FUNCTION two_timestamp_interval (endtime IN TIMESTAMP, starttime IN TIMESTAMP) 
RETURN NUMBER 
AS 
   str VARCHAR2(50); 
   seconds  NUMBER; 
   minutes  NUMBER; 
   hours    NUMBER; 
   days     NUMBER; 
BEGIN 
   str := TO_CHAR(endtime - starttime); 
   seconds := TO_NUMBER(SUBSTR(str, INSTR(str, ' ')+7)); 
   minutes := TO_NUMBER(SUBSTR(str, INSTR(str, ' ')+4, 2)); 
   hours := TO_NUMBER(SUBSTR(str, INSTR(str, ' ')+1, 2)); 
   days := TO_NUMBER(SUBSTR(str, 1, INSTR(str, ' '))); 
   RETURN (days*24*60*60 + hours*60*60 + minutes*60 + seconds); 
END two_timestamp_interval;

Function created.

Statement 122

Using a procedure 'cnt_tab_spd_time_4' of PL/SQL to dynamically save the spending time of querying COUNT all those test tables into the table 'cnt_spd_time_4'.

CREATE OR REPLACE PROCEDURE cnt_tab_spd_time_4 
AS 
   line_nums       NUMBER; 
   start_time      TIMESTAMP; 
   end_time        TIMESTAMP; 
   consume_seconds NUMBER; 
   v_name   tab_stru_4.name%TYPE; 
   v_mark_1 tab_stru_4.mark%TYPE; 
   v_mark_2 cnt_method_4.mark%TYPE; 
BEGIN 
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF'''; 
   FOR cur1 IN (SELECT * FROM tab_stru_4 ORDER BY ROWID) LOOP 
      FOR cur2 IN (SELECT * FROM cnt_method_4 ORDER BY ROWID) LOOP 
         v_name := cur1.name; 
         v_mark_1 := cur1.mark; 
         v_mark_2 := cur2.mark; 
         start_time := SYSTIMESTAMP; 
         EXECUTE IMMEDIATE 'SELECT COUNT(' || v_mark_2 || ') FROM ' || v_name INTO line_nums; 
         end_time := SYSTIMESTAMP; 
         consume_seconds := two_timestamp_interval(end_time, start_time); 
         INSERT INTO cnt_spd_time_4 (tab_num, tab_mark, cnt_mark, spd_time, time_unit) VALUES (line_nums, v_mark_1, v_mark_2, consume_seconds, DEFAULT); 
         COMMIT; 
      END LOOP; 
   END LOOP; 
END cnt_tab_spd_time_4;

Procedure created.

Statement 123
Running procedure "cnt_tab_spd_time_4".
```
EXECUTE cnt_tab_spd_time_4
```
```
Statement processed.
```

Statement 124

Querying all the values of table 'cnt_spd_time_4'.

SELECT * FROM cnt_spd_time_4


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi * .001391 s
10000 no_pk_bi 1 .001428 s
10000 no_pk_bi id .00125 s
10000 no_pk_bi flag .001189 s
10000 only_pk * .001339 s
10000 only_pk 1 .001289 s
10000 only_pk id .00122 s
10000 only_pk flag .001235 s
10000 only_bi * .000985 s
10000 only_bi 1 .001039 s
10000 only_bi id .000944 s
10000 only_bi flag .000974 s
10000 both_pk_bi * .000962 s
10000 both_pk_bi 1 .001067 s
10000 both_pk_bi id .001062 s
10000 both_pk_bi flag .000964 s

16 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	*	.001391	s
10000	no_pk_bi	1	.001428	s
10000	no_pk_bi	id	.00125	s
10000	no_pk_bi	flag	.001189	s
10000	only_pk	*	.001339	s
10000	only_pk	1	.001289	s
10000	only_pk	id	.00122	s
10000	only_pk	flag	.001235	s
10000	only_bi	*	.000985	s
10000	only_bi	1	.001039	s
10000	only_bi	id	.000944	s
10000	only_bi	flag	.000974	s
10000	both_pk_bi	*	.000962	s
10000	both_pk_bi	1	.001067	s
10000	both_pk_bi	id	.001062	s
10000	both_pk_bi	flag	.000964	s

Statement 125

Querying some values of table 'cnt_spd_time_4' and filtering by using "WHERE tab_mark = 'no_pk_bi' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_4 WHERE tab_mark = 'no_pk_bi' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi * .001391 s
10000 no_pk_bi 1 .001428 s
10000 no_pk_bi id .00125 s
10000 no_pk_bi flag .001189 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	*	.001391	s
10000	no_pk_bi	1	.001428	s
10000	no_pk_bi	id	.00125	s
10000	no_pk_bi	flag	.001189	s

Statement 126

Querying some values of table 'cnt_spd_time_4' and filtering by using "WHERE tab_mark = 'only_pk' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_4 WHERE tab_mark = 'only_pk' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 only_pk * .001339 s
10000 only_pk 1 .001289 s
10000 only_pk id .00122 s
10000 only_pk flag .001235 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	only_pk	*	.001339	s
10000	only_pk	1	.001289	s
10000	only_pk	id	.00122	s
10000	only_pk	flag	.001235	s

Statement 127

Querying some values of table 'cnt_spd_time_4' and filtering by using "WHERE tab_mark = 'only_bi' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_4 WHERE tab_mark = 'only_bi' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 only_bi * .000985 s
10000 only_bi 1 .001039 s
10000 only_bi id .000944 s
10000 only_bi flag .000974 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	only_bi	*	.000985	s
10000	only_bi	1	.001039	s
10000	only_bi	id	.000944	s
10000	only_bi	flag	.000974	s

Statement 128

Querying some values of table 'cnt_spd_time_4' and filtering by using "WHERE tab_mark = 'both_pk_bi' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_4 WHERE tab_mark = 'both_pk_bi' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 both_pk_bi * .000962 s
10000 both_pk_bi 1 .001067 s
10000 both_pk_bi id .001062 s
10000 both_pk_bi flag .000964 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	both_pk_bi	*	.000962	s
10000	both_pk_bi	1	.001067	s
10000	both_pk_bi	id	.001062	s
10000	both_pk_bi	flag	.000964	s

Statement 129

Querying some values of table 'cnt_spd_time_4' and filtering by using "WHERE cnt_mark = '*' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_4 WHERE cnt_mark = '*' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi * .001391 s
10000 only_pk * .001339 s
10000 only_bi * .000985 s
10000 both_pk_bi * .000962 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	*	.001391	s
10000	only_pk	*	.001339	s
10000	only_bi	*	.000985	s
10000	both_pk_bi	*	.000962	s

Statement 130

Querying some values of table 'cnt_spd_time_4' and filtering by using "WHERE cnt_mark = '1' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_4 WHERE cnt_mark = '1' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi 1 .001428 s
10000 only_pk 1 .001289 s
10000 only_bi 1 .001039 s
10000 both_pk_bi 1 .001067 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	1	.001428	s
10000	only_pk	1	.001289	s
10000	only_bi	1	.001039	s
10000	both_pk_bi	1	.001067	s

Statement 131

Querying some values of table 'cnt_spd_time_4' and filtering by using "WHERE cnt_mark = 'id' ORDER BY ROWID".

SELECT * FROM cnt_spd_time_4 WHERE cnt_mark = 'id' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi id .00125 s
10000 only_pk id .00122 s
10000 only_bi id .000944 s
10000 both_pk_bi id .001062 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	id	.00125	s
10000	only_pk	id	.00122	s
10000	only_bi	id	.000944	s
10000	both_pk_bi	id	.001062	s

Statement 132

Querying some values of table 'cnt_spd_time_4' and filtering by using "WHERE cnt_mark = 'flag' ORDER BY ROWID".

The End Section of 'Demo4':

SELECT * FROM cnt_spd_time_4 WHERE cnt_mark = 'flag' ORDER BY ROWID


TAB_NUM TAB_MARK CNT_MARK SPD_TIME TIME_UNIT
10000 no_pk_bi flag .001189 s
10000 only_pk flag .001235 s
10000 only_bi flag .000974 s
10000 both_pk_bi flag .000964 s

4 rows selected.

TAB_NUM	TAB_MARK	CNT_MARK	SPD_TIME	TIME_UNIT
10000	no_pk_bi	flag	.001189	s
10000	only_pk	flag	.001235	s
10000	only_bi	flag	.000974	s
10000	both_pk_bi	flag	.000964	s

Additional Information

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