Hello my ODC friends ,
Today I'm writng several Oracle SQL and PL/SQL code snippets to observe time spending by query count(*), count(1), count(id) and count(flag) of my TEST table.
The following is my total 3 number of code.
CREATE TABLE cnt_way (
mark VARCHAR2(5) PRIMARY KEY
);
INSERT INTO cnt_way (mark) VALUES ('*');
INSERT INTO cnt_way (mark) VALUES ('1');
INSERT INTO cnt_way (mark) VALUES ('id');
INSERT INTO cnt_way (mark) VALUES ('flag');
COMMIT;
CREATE OR REPLACE PROCEDURE crt_tab_test (l_num IN NUMBER)
AS
v_sql_1 VARCHAR2(2000);
v_sql_2 VARCHAR2(2000);
BEGIN
v_sql_1 := 'DROP TABLE test PURGE';
v_sql_2 := 'CREATE TABLE test '
|| 'NOLOGGING '
|| 'AS SELECT ROWNUM id '
|| ' , CASE WHEN ROWNUM BETWEEN 1 AND 1/5*' || l_num || ' THEN ''low'' '
|| ' WHEN ROWNUM BETWEEN 2/5*' || l_num || ' AND 3/5*' || l_num || ' THEN ''mid'' '
|| ' WHEN ROWNUM BETWEEN 4/5*' || l_num || ' AND ' || l_num || ' THEN ''high'' '
|| ' ELSE ''unknown'' '
|| ' END flag '
|| ' , DBMS_RANDOM.string (''p'', 20) pwd '
|| ' FROM dual '
|| ' CONNECT BY LEVEL <= ' || l_num;
EXECUTE IMMEDIATE v_sql_1;
EXECUTE IMMEDIATE v_sql_2;
DBMS_STATS.gather_table_stats(
OWNNAME => user,
TABNAME => 'TEST'
);
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE v_sql_2;
DBMS_STATS.gather_table_stats(
OWNNAME => user,
TABNAME => 'TEST'
);
END crt_tab_test;
/
CREATE OR REPLACE PROCEDURE qry_tab_cnt_no_pk_and_bitmap
AS
start_time DATE;
end_time DATE;
TYPE type_mark IS TABLE OF cnt_way.mark%TYPE;
var_mark type_mark := type_mark();
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS''';
SELECT mark BULK COLLECT INTO var_mark FROM cnt_way;
FOR i IN var_mark.first .. var_mark.last LOOP
start_time := SYSDATE;
-- DBMS_OUTPUT.put_line (var_mark(i));
EXECUTE IMMEDIATE 'SELECT COUNT(' || var_mark(i) || ') FROM test';
end_time := SYSDATE;
DBMS_OUTPUT.put_line ('Elapsed Time: ' || end_time - start_time);
END LOOP;
END qry_tab_cnt_no_pk_and_bitmap;
/
Now let me execute them respectively.
SQL> CREATE TABLE cnt_way (
2 mark VARCHAR2(5) PRIMARY KEY
3 );
INSERT INTO cnt_way (mark) VALUES ('*');
Table created.
SQL> SQL> INSERT INTO cnt_way (mark) VALUES ('1');
1 row created.
SQL>
1 row created.
SQL> INSERT INTO cnt_way (mark) VALUES ('id');
1 row created.
SQL> INSERT INTO cnt_way (mark) VALUES ('flag');
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL> CREATE OR REPLACE PROCEDURE crt_tab_test (l_num IN NUMBER)
2 AS
3 v_sql_1 VARCHAR2(2000);
4 v_sql_2 VARCHAR2(2000);
5 BEGIN
6 v_sql_1 := 'DROP TABLE test PURGE';
7 v_sql_2 := 'CREATE TABLE test '
8 || 'NOLOGGING '
9 || 'AS SELECT ROWNUM id '
10 || ' , CASE WHEN ROWNUM BETWEEN 1 AND 1/5*' || l_num || ' THEN ''low'' '
11 || ' WHEN ROWNUM BETWEEN 2/5*' || l_num || ' AND 3/5*' || l_num || ' THEN ''mid'' '
12 || ' WHEN ROWNUM BETWEEN 4/5*' || l_num || ' AND ' || l_num || ' THEN ''high'' '
13 || ' ELSE ''unknown'' '
14 || ' END flag '
15 || ' , DBMS_RANDOM.string (''p'', 20) pwd '
16 || ' FROM dual '
17 || ' CONNECT BY LEVEL <= ' || l_num;
18 EXECUTE IMMEDIATE v_sql_1;
19 EXECUTE IMMEDIATE v_sql_2;
20 DBMS_STATS.gather_table_stats(
21 OWNNAME => user,
22 TABNAME => 'TEST'
23 );
24 EXCEPTION
25 WHEN OTHERS THEN
26 EXECUTE IMMEDIATE v_sql_2;
27 DBMS_STATS.gather_table_stats(
28 OWNNAME => user,
29 TABNAME => 'TEST'
30 );
31 END crt_tab_test;
32 /
Procedure created.
SQL> execute crt_tab_test(1000);
PL/SQL procedure successfully completed.
By the way running the previous 1st and 2nd step my code are both all Okay, but I create 3rd PL/SQL code and execute it has shown an error of "ORA-06502" below.
SQL> CREATE OR REPLACE PROCEDURE qry_tab_cnt_no_pk_and_bitmap
2 AS
3 start_time DATE;
4 end_time DATE;
5 TYPE type_mark IS TABLE OF cnt_way.mark%TYPE;
6 var_mark type_mark := type_mark();
7 BEGIN
8 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS''';
9 SELECT mark BULK COLLECT INTO var_mark FROM cnt_way;
10 FOR i IN var_mark.first .. var_mark.last LOOP
11 start_time := SYSDATE;
12 -- DBMS_OUTPUT.put_line (var_mark(i));
13 EXECUTE IMMEDIATE 'SELECT COUNT(' || var_mark(i) || ') FROM test';
14 end_time := SYSDATE;
15 DBMS_OUTPUT.put_line ('Elapsed Time: ' || end_time - start_time);
16 END LOOP;
17 END qry_tab_cnt_no_pk_and_bitmap;
18 /
Procedure created.
SQL> exec qry_tab_cnt_no_pk_and_bitmap;
BEGIN qry_tab_cnt_no_pk_and_bitmap; END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYS.QRY_TAB_CNT_NO_PK_AND_BITMAP", line 15
ORA-06512: at line 1
Could you help me troubleshooting it? Thanks beforehand!
Best Regards
Quanwen Zhao