Hi All,
This question is based on the thread:
Here I have created same script with different methods.
1. CURSOR
------------------
DECLARE
CURSOR table_count
IS
SELECT table_name
FROM user_tables
ORDER BY 1;
sqlstr VARCHAR2 (1000);
numrow NUMBER;
BEGIN
DBMS_OUTPUT.put_line ('Start time ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ));
FOR i IN table_count
LOOP
sqlstr := 'SELECT COUNT(*) FROM ' || i.table_name;
EXECUTE IMMEDIATE sqlstr INTO numrow;
if numrow>0 then
DBMS_OUTPUT.put_line (RPAD (i.table_name, 30, '.') || ' = ' || numrow);
end if;
END LOOP;
DBMS_OUTPUT.put_line ('End time ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') );
END;
My understandings:
It will do row by row processing generally slow performance
2. BULK COLLECT
-----------------------------
DECLARE
sqlstr VARCHAR2 (1000);
numrow NUMBER;
TYPE table_name is table of varchar2(30);
tname table_name;
BEGIN
DBMS_OUTPUT.put_line ('Start time ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ));
SELECT table_name
BULK COLLECT INTO tname
FROM user_tables
ORDER BY 1;
FOR i IN tname.FIRST..tname.COUNT
LOOP
sqlstr := 'SELECT COUNT(*) FROM ' || tname(i);
EXECUTE IMMEDIATE sqlstr INTO numrow;
if numrow>0 then
DBMS_OUTPUT.put_line (RPAD (tname(i), 30, '.') || ' = ' || numrow);
end if;
END LOOP;
DBMS_OUTPUT.put_line ('End time ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ));
END;
My understandings:
1. It will avoid context switching
2. Uses more PGA
3. CURSOR AND BULK COLLECT
--------------------------------------------------
DECLARE
CURSOR table_count
IS
SELECT table_name
FROM user_tables
ORDER BY 1;
sqlstr VARCHAR2 (1000);
numrow NUMBER;
TYPE table_name is table of varchar2(30);
tname table_name;
BEGIN
OPEN table_count;
FETCH table_count BULK COLLECT INTO tname;
DBMS_OUTPUT.put_line ('Start time ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ));
FOR i IN tname.FIRST..tname.COUNT
LOOP
sqlstr := 'SELECT COUNT(*) FROM ' || tname(i);
EXECUTE IMMEDIATE sqlstr INTO numrow;
if numrow>0 then
DBMS_OUTPUT.put_line (RPAD (tname(i), 30, '.') || ' = ' || numrow);
end if;
END LOOP;
DBMS_OUTPUT.put_line ('End time ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ));
END;
My understandings:
I really don't understand why some people prefer this as this method is having both CURSOR and BULK COLLECT
4. IMPLICIT CURSOR
----------------------------------
DECLARE
sqlstr VARCHAR2 (1000);
numrow NUMBER;
BEGIN
DBMS_OUTPUT.put_line ('Start time ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ));
FOR i IN ( SELECT table_name
FROM user_tables
ORDER BY 1)
LOOP
sqlstr := 'SELECT COUNT(*) FROM ' || i.table_name;
EXECUTE IMMEDIATE sqlstr INTO numrow;
if numrow>0 then
DBMS_OUTPUT.put_line (RPAD (i.table_name, 30, '.') || ' = ' || numrow);
end if;
END LOOP;
DBMS_OUTPUT.put_line ('End time ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') );
END;
My understandings:
It will also gives better performance compare than CURSOR Loops
Since the above 4 methods are doing same Job, please clarify how to choose the correct methodologies for different scenarios. What things we have to consider before choosing one method.