Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

Tag Archives: Oracle 11gr2

LISTAGG and PIVOT functions in Oracle 11g R2

Analytic functions are first introduced in Oracle 8i. They have got few functions in new release. The first function which I will discuss here will be LISTAGG. It creates a delimited list within a group. Some times you need to toil hard for this. Most of the times, grouping by and converting columns to rows.

With Oracle 11g R2, all you need is the following.

SQL> select deptno,
  2         listagg( ename, '; ' )
  3         within group
  4         (order by ename) enames
  5     from hr.employees
  6    group by deptno
  7    order by deptno
  8   /

---------  --------------------
  10       CLARK; KING; MILLER
  20       ADAMS; FORD; JONES;
           SCOTT; SMITH
  30       ALLEN; BLAKE;
           JAMES; MARTIN;
           TURNER; WARD

You can decide what to append with. The syntax is LISTAGG(column, character). Read more of this post

Automatic Detection of Data Sources of Result-Cached Function

Oracle 11g release 1 gave the option of caching function results. It is done using the optional reference of mentioning RESULT_CACHE keyword. The values of these function calls are stored in system global area (SGA). It will have one result for every unique combination of parameter of passed. These are available for every session. Thus this helps in greatly reducing the execution time of an often repeated complex process inside a function which gets same repeated input values. This will increase performance in high concurrent applications.

In Oracle 11g Release 1

It is mentioned, the best candidate for result caching is function that is invoked frequently and depend on information that changes infrequently or never. Consider the following function.

REPLACE FUNCTION get_sal (p_emp_id in hr.employees.employee_id%type)
lv_sal_rtn   hr.employees.salary%type;
   select salary
      into lv_sal_rtn
     from hr.employees
   where employee_id = p_emp_id;

 RETURN lv_sal_rtn;

  WHEN others
           RETURN 0;

Note: the function execution values are stored in SGA by explicitly mentioning RESULT_CACHE. And the downside of this is, at times you can get wrong return values. This is because the values are already cached in memory and not calculated again. Consider the following example. Read more of this post