Compare the performance between two functions in Oracle

Sometimes when we can do something in Oracle in more than one way, but at that point we have to use the best one which can serves us better.

PERFORMANCE is the most important criteria we have to put into consideration when have more than one solution. Nice tip in Oracle enables us to measure performance -time spent- between more than one function which is get_cpu_time from dbms_utility package which returns the current CPU time in 100th's of a second and we use it before and after each function with loop of relatively big number of iteration.

Example:

DECLARE
   v_c1     NUMBER;
   v_c2     NUMBER;
   v_date   DATE;
   v_char   VARCHAR2 (10);
BEGIN
   v_c1 := DBMS_UTILITY.get_cpu_time;

   FOR c IN 1 .. 100000
   LOOP
      v_date := TRUNC (SYSDATE);
   END LOOP;

   v_c2 := DBMS_UTILITY.get_cpu_time;
   DBMS_OUTPUT.put_line ('For loop using TRUNC');
   DBMS_OUTPUT.put_line ('CPU seconds used: ' || (v_c2 - v_c1));
   v_c1 := DBMS_UTILITY.get_cpu_time;

   FOR c IN 1 .. 100000
   LOOP
      v_char := TO_CHAR (SYSDATE, 'DD-MM-YYYY');
   END LOOP;

   v_c2 := DBMS_UTILITY.get_cpu_time;
   DBMS_OUTPUT.put_line ('For loop using TO_CHAR');
   DBMS_OUTPUT.put_line ('CPU seconds used: ' || (v_c2 - v_c1));
END;

The output is:

For loop using TRUNC
CPU seconds used: 25
For loop using TO_CHAR
CPU seconds used: 68
PL/SQL procedure successfully completed.

The numbers will change depending on CPU speed and tasks assigned, but anyway you can notice the difference.


Saad,

Oracle UNION, UNION ALL, INTERSECT, MINUS

If you have result sets from two queries or more, and you want to combine those result sets or you want to remove duplicate rows or get only the common rows or not common rows, it's time to learn about UNION, UNION ALL, INTERSECT and MINUS queries:

UNION Query:
Allows you to combine the result sets of two or more queries but it removes the duplicate rows between the queries.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
UNION
SELECT *
  FROM emp
 WHERE deptno = 20;

You can notice that employee with empno = 7566 should come in both select statement but comes once since we used UNION.

UNION ALL Query:
Allows you to combine the result set of two or more queries and returns all rows.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
UNION ALL
SELECT *
  FROM emp
 WHERE deptno = 20;

Since we used UNION ALL, rows from both queries are returned and duplicate rows are not removed.

INTERSECT Query:

It allow you to get only selected rows returned by all queries you are using in INTERSECT Query.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
INTERSECT
SELECT *
  FROM emp
 WHERE deptno = 20;

It returns only one row -returned by both queries-.

MINUS Query:

Retuns rows from first query that are not returned by other queries.

SELECT *
  FROM emp
 WHERE job = 'MANAGER'
MINUS
SELECT *
  FROM emp
 WHERE deptno = 20;

Note:
Each SQL statement within the UNION,UNION ALL,INTERSECT and MINUS query must have the same number of fields in the result sets with similar data types.

Saad,