PL/SQL vs SQL

FUNCTION getenames(deptno_in IN emp.deptno%type) RETURN VARCHAR2

IS

  TYPE enames_aa IS TABLE OF emp.ename%type INDEX BY pls_integer;

  l_returnvalue VARCHAR2(32767) := '';

  l_enames enames_aa;

BEGIN

  SELECT e.ename

    BULK COLLECT INTO l_enames

    FROM emp e

   WHERE e.deptno = deptno_in

   ORDER BY e.ename ASC NULLS FIRST;

  IF l_enames.count > 0 THEN

    FOR indx IN 1 .. l_enames.count LOOP

      l_returnvalue := l_returnvalue || l_enames(indx) || ',';

    END LOOP;

  END IF;

  l_returnvalue := rtrim(l_returnvalue, ',');

  RETURN l_returnvalue;

END;

 

 

SQL:

 

SELECT d.dname, getenames(d.deptno) enames

  FROM dept d

 

Result:

 

DNAME ENAMES

-------------- ----------------------------------------------------------------------

ACCOUNTING CLARK,KING,MILLER

RESEARCH ADAMS,FORD,JONES,SCOTT,SMITH

SALES ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

OPERATIONS

 

 

New SQL:

SELECT d.dname, (SELECT listagg(e.ename, ',') WITHIN GROUP(ORDER BY e.ename)

                   FROM emp e

                  WHERE 1=1

                    AND e.deptno = d.deptno

                ) enames

  FROM dept d

 

 

 

Jdeveloper Performance

The following settings will increase the performance of your JDeveloper 11g.
Go to your JDeveloper root directory (JDEVELOPER_HOME/jdeveloper)
Open the file ide/bin/ide.conf, and change memory settings to something larger, like so:
AddVMOption -Xmx1024M
AddVMOption -Xms1024M
Open the file jdev/bin/jdev.conf, and increase the value of MaxPermSize:
AddVMOption -XX:MaxPermSize=512M
Add the following settings at the bottom.
# optimize the JVM for strings / text editing
AddVMOption -XX:+UseStringCache
AddVMOption -XX:+OptimizeStringConcat
AddVMOption -XX:+UseCompressedStrings
# if on a 64-bit JVM, but using less than 32 GB RAM, this reduces object pointer memory size
AddVMOption -XX:+UseCompressedOops
# use an aggressive garbage collector (constant small collections)
AddVMOption -XX:+AggressiveOpts
# for multi-core machines, use multiple threads to create objects and reduce pause times
AddVMOption -XX:+UseConcMarkSweepGC
# to activate memory monitor
AddVMOption -DMainWindow.MemoryMonitorOn=true
Open the file jdev/bin/jdev.boot, and remove the # before VFS_ENABLE property.
#
# This will enable a "virtual" file system feature within JDeveloper.
# This can help performance for projects with a lot of files,
# particularly under source control. For non-Windows platforms however,
# any file changes made outside of JDeveloper, or by deployment for
# example, may not be picked by the "virtual" file system feature. Do
# not enable this for example, on a Linux OS if you use an external editor.
#
VFS_ENABLE = true
Go to your user root directory.
On a Linux OS, open the file .jdeveloper/system11.1.1.7.xx.xx.xx/DefaultDomain/bin/setDomainEnv.sh.
On Windows OS, open the file AppData\Roaming\JDeveloper\system11.1.1.7.xx.xx.xx\DefaultDomain\bin\setDomainEnv.cmd.
Change memory settings to something larger, like so:
set XMX_SUN_64BIT=1024
set XMX_SUN_32BIT=1024



Thanks to Waslley Leandro de Souza

http://waslleysouza.com.br/en/2014/04/increase-the-performance-of-jdeveloper-11g/