SQL Treasures.

The reason that I call this topic as treasure is that this feature was introduced in Oracle 8.1.6 ( a whiiiiiile back ) . Not many people are using ( to its fullest extent ) .

You would have guessed it by now . Yeah . It is analytics.

Analytics is one of the greatest additon to the SQL family . Though ,it is documented only in Datawarehousing guide . It is equally important in OLTP application .

It has ranking , reporting , statisticial  functions to name a few.

As an example , let us go to scott schema .

In this post , I will cover few basic analytical SQLs . Some of the analytical constructs has been introduced in SQL Server 2005.

Rest of the analytical functions in the subsequent posts.

Example 1: 

Usuage of ROW_NUMBER function .

This is to return  a running sequence number.

SQL> SELECT empno,
  2         ename,
  3         job,
  4         sal,
  5         deptno,
  6         row_number() over(ORDER BY sal DESC) rn
  7  FROM emp;

EMPNO ENAME      JOB             SAL DEPTNO         RN
----- ---------- --------- --------- ------ ----------
 7839 KING       PRESIDENT   5000.00     10          1
 7902 FORD       ANALYST     3000.00     20          2
 7788 SCOTT      ANALYST     3000.00     20          3
 7566 JONES      MANAGER     2975.00     20          4
 7698 BLAKE      MANAGER     2850.00     30          5
 7782 CLARK      MANAGER     2450.00     10          6
 7499 ALLEN      SALESMAN    1600.00     30          7
 7844 TURNER     SALESMAN    1500.00     30          8
 7934 MILLER     CLERK       1300.00     10          9
 7521 WARD       SALESMAN    1250.00     30         10
 7654 MARTIN     SALESMAN    1250.00     30         11
 7876 ADAMS      CLERK       1100.00     20         12
 7900 JAMES      CLERK        950.00     30         13
 7369 SMITH      CLERK        800.00     20         14

Example 2:

The following example has lot of functions .

1. LEAD and LAG will let us to look at the previous and next records's value .

2. RANK and DENSE_RANK let us to rank the particular record based on the column ( we specify ) . The difference between RANK and DENSE_RANK comes when there is a tie in the column value . DENSE_RANK does not skip the rank , where as the RANK does.

SQL> SELECT empno,
  2         ename,
  3         job,
  4         sal,
  5         deptno,
  6         row_number() over(PARTITION BY deptno ORDER BY sal ASC) rn,
  7         rank() over(PARTITION BY deptno ORDER BY sal ASC) rank,
  8         dense_rank() over(PARTITION BY deptno ORDER BY sal ASC) dense_rank,
  9         lag(sal) over(PARTITION BY deptno ORDER BY sal ASC) previous_sal,
 10         lead(sal) over(PARTITION BY deptno ORDER BY sal ASC) next_sal
 11  FROM emp
 12  ORDER BY deptno, sal
 13  /

EMPNO ENAME      JOB             SAL DEPTNO         RN       RANK DENSE_RANK PREVIOUS_SAL   NEXT_SAL
----- ---------- --------- --------- ------ ---------- ---------- ---------- ------------ ----------
 7934 MILLER     CLERK       1300.00     10          1          1          1                    2450
 7782 CLARK      MANAGER     2450.00     10          2          2          2         1300       5000
 7839 KING       PRESIDENT   5000.00     10          3          3          3         2450
 7369 SMITH      CLERK        800.00     20          1          1          1                    1100
 7876 ADAMS      CLERK       1100.00     20          2          2          2          800       2975
 7566 JONES      MANAGER     2975.00     20          3          3          3         1100       3000
 7788 SCOTT      ANALYST     3000.00     20          4          4          4         2975       3000
 7902 FORD       ANALYST     3000.00     20          5          4          4         3000
 7900 JAMES      CLERK        950.00     30          1          1          1                    1250
 7654 MARTIN     SALESMAN    1250.00     30          2          2          2          950       1250
 7521 WARD       SALESMAN    1250.00     30          3          2          2         1250       1500
 7844 TURNER     SALESMAN    1500.00     30          4          4          3         1250       1600
 7499 ALLEN      SALESMAN    1600.00     30          5          5          4         1500       2850
 7698 BLAKE      MANAGER     2850.00     30          6          6          5         1600

Example 3:

NTILE let us to divide the results in equal height . 

In the following example , the result set is divided into three equal parts . 

I find  NTILE  very useful  in do it yourself (DIY) parallel-zing   jobs . In DIY jobs , you could divvy up the result set and send it to parallel jobs either using DBMS_JOB / DBMS_SCHDULER.

SQL> SELECT empno, ename, job, sal, deptno, ntile(3) over(ORDER BY sal) ntile
  2  FROM emp
  3  ORDER BY sal
  4  /

EMPNO ENAME      JOB             SAL DEPTNO      NTILE
----- ---------- --------- --------- ------ ----------
 7369 SMITH      CLERK        800.00     20          1
 7900 JAMES      CLERK        950.00     30          1
 7876 ADAMS      CLERK       1100.00     20          1
 7521 WARD       SALESMAN    1250.00     30          1
 7654 MARTIN     SALESMAN    1250.00     30          1
 7934 MILLER     CLERK       1300.00     10          2
 7844 TURNER     SALESMAN    1500.00     30          2
 7499 ALLEN      SALESMAN    1600.00     30          2
 7782 CLARK      MANAGER     2450.00     10          2
 7698 BLAKE      MANAGER     2850.00     30          2
 7566 JONES      MANAGER     2975.00     20          3
 7788 SCOTT      ANALYST     3000.00     20          3
 7902 FORD       ANALYST     3000.00     20          3
 7839 KING       PRESIDENT   5000.00     10          3

Example 4: 

The first look at the following example may be little bit intimidating . If you look at the second example , it may be clear .

In the latter example , we get the ratio of the salaries in DEPT 10 . The total of sal is 8750 , out of which CLARK's salary is 2450 ( 28% of the department's total salary ).

It helped us in one scenario , where the requirement was to save the report into an excel spreadsheet format .
This report was developed using PowerBuilder . In PowerBuilder , we can save the contents of the datawindow into an excel spreadsheet . The developer has done in the ratio calculations in the front end ( computed column in PB lingo ) . When the data window was saved as an excel , the calculation did not make into an excel ( as only  the result set of  the SQL was saved ) . In this case , the following function came in handy.

SQL> SELECT empno,
  2         ename,
  3         job,
  4         deptno,
  5         sal,
  6         ratio_to_report(sal) over() rr_whole,
  7         ratio_to_report(sal) over(PARTITION BY deptno) rr_deptno
  8  FROM emp
  9  /

EMPNO ENAME      JOB       DEPTNO       SAL   RR_WHOLE  RR_DEPTNO
----- ---------- --------- ------ --------- ---------- ----------
 7782 CLARK      MANAGER       10   2450.00 0.08440999       0.28
 7839 KING       PRESIDENT     10   5000.00 0.17226528 0.57142857
 7934 MILLER     CLERK         10   1300.00 0.04478897 0.14857142
 7566 JONES      MANAGER       20   2975.00 0.10249784 0.27356321
 7902 FORD       ANALYST       20   3000.00 0.10335917 0.27586206
 7876 ADAMS      CLERK         20   1100.00 0.03789836 0.10114942
 7369 SMITH      CLERK         20    800.00 0.02756244 0.07356321
 7788 SCOTT      ANALYST       20   3000.00 0.10335917 0.27586206
 7521 WARD       SALESMAN      30   1250.00 0.04306632 0.13297872
 7844 TURNER     SALESMAN      30   1500.00 0.05167958 0.15957446
 7499 ALLEN      SALESMAN      30   1600.00 0.05512489 0.17021276
 7900 JAMES      CLERK         30    950.00 0.03273040 0.10106382
 7698 BLAKE      MANAGER       30   2850.00 0.09819121 0.30319148
 7654 MARTIN     SALESMAN      30   1250.00 0.04306632 0.13297872



SQL> SELECT empno,
  2         ename,
  3         job,
  4         deptno,
  5         sal,
  6         ratio_to_report(sal) over(PARTITION BY deptno) rr_deptno
  7  FROM emp
  8  Where deptno = 10
  9  /

EMPNO ENAME      JOB       DEPTNO       SAL  RR_DEPTNO
----- ---------- --------- ------ --------- ----------
 7782 CLARK      MANAGER       10   2450.00       0.28
 7839 KING       PRESIDENT     10   5000.00 0.57142857
 7934 MILLER     CLERK         10   1300.00 0.14857142

Enhanced TRUNCATE in Oracle 11g Release 2 ( 11.2.0.2)

Oracle 11g Release 2 patch 2 ( 11.2.0.2) introduced "Enhanced Truncate " Functionality , by which you can release all 
the storage when you truncate the table .

Please see below for example.

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> create table t as select object_id , object_name from all_objects;

Table created.

SQL> select sum(bytes)/1024/1024 from user_segments where segment_name ='T';

SUM(BYTES)/1024/1024
--------------------
                   3

SQL> truncate table t;

Table truncated.

SQL>  select sum(bytes)/1024/1024 from user_segments where segment_name ='T';

SUM(BYTES)/1024/1024
--------------------
               .0625

SQL> drop table t purge;

Table dropped.

SQL> create table t as select object_id , object_name from all_objects;

Table created.

SQL> truncate table t drop storage ; 
Table truncated.

SQL> select sum(bytes)/1024/1024 from user_segments where segment_name ='T';

SUM(BYTES)/1024/1024
--------------------
               .0625


SQL> drop table t purge;

Table dropped.

SQL> create table t as select object_id , object_name from all_objects;

Table created.






Let us use 'drop all storage '  clause with the TRUNCATE .

SQL>  truncate table t drop all storage ;

Table truncated.

SQL>  select sum(bytes)/1024/1024 from user_segments where segment_name ='T';

SUM(BYTES)/1024/1024
--------------------

SQL> Select count(*) from user_segments where  segment_name ='T';

  COUNT(*)
----------
         0
As you can see , there is no storage associated with the table 'T' .

Comments Welcome.

Interval Partitioning

Oracle 11g has a new feature that will make life easier for the DBAs. Prior to this release , the DBAs must make sure they create a new partition to accommodate the new incoming dataset .

 For example , to facilitate year end processing , they should create additional partitions to move the new year’s data into the database . Failing to do so will create “abend” jobs . They have to reactively create new partitions and re-run the job.


Let me see with an example.

Let us create a table , partition by year .

SQL> CREATE TABLE t_partTable created.

2 (
3 salyear INTEGER ,
4 salmount NUMBER(5,2) ,
5 saldesc VARCHAR2(40)
6 )
7 partition BY range
8 (
9 salyear
10 )
11 (
12 partition p_2001 VALUES less than (2001),
13 partition p_2002 VALUES less than (2002),
14 partition p_2003 VALUES less than (2003),
15 partition p_2004 VALUES less than (2004)
16 );



Let us insert few records.

SQL> insert into t_part values ( 2000 , 15.00 , 'Tamil VHS') ;Let us add a record for the year 2004. As you can see , there is an error as we have not created a partition for the year 2004.

1 row created.

SQL> insert into t_part values ( 2001 , 25.00 , 'Tamil VCD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 30.00 , 'Tamil SVCD') ;
1 row created.


SQL> insert into t_part values ( 2003 , 500.00 , 'Tamil DVD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 40.00 , 'English SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'English DVD') ;
1 row created.


SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');

PL/SQL procedure successfully completed.


SQL> SELECT table_name,
2 partition_name,
3 num_rows
4 FROM user_tab_partitions
5 WHERE table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
---------- -------------- -------
T_PART      P_2001         1
T_PART      P_2002         1
T_PART      P_2003         2
T_PART      P_2004         2


 
SQL> insert into t_part values ( 2004 , 550.00 , 'French DVD') ;

insert into t_part values ( 2004 , 550.00 , 'French DVD')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

We can mitigate the above issue by creating a default partition .
Here is the second version of the table.

SQL> drop table t_part purge ;

Table dropped.


SQL> create table t_part
2 (
3 salyear integer ,
4 salmount number(5,2) ,
5 saldesc varchar2(40)
6 )
7 partition by range(salyear)
8 (
9 partition p_2001 values less than (2001),
10 partition p_2002 values less than (2002),
11 partition p_2003 values less than (2003),
12 partition p_2004 values less than (2004),
13 partition p_max values less than (MAXVALUE)
14 );


Table created.

SQL> insert into t_part values ( 2000 , 15.00 , 'Tamil VHS') ;
1 row created.

SQL> insert into t_part values ( 2001 , 25.00 , 'Tamil VCD') ;
1 row created.


SQL> insert into t_part values ( 2002 , 30.00 , 'Tamil SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'Tamil DVD') ;
1 row created.


SQL> insert into t_part values ( 2002 , 40.00 , 'English SVCD') ;
1 row created.


SQL> insert into t_part values ( 2003 , 500.00 , 'English DVD') ;
1 row created.

SQL> insert into t_part values ( 2004 , 550.00 , 'French DVD') ;
1 row created.


SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');
PL/SQL procedure successfully completed.


SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';


TABLE_NAME PARTITION_NAME NUM_ROWS


---------   -----------  ----------
T_PART       P_2001           1
T_PART       P_2002           1
T_PART       P_2003           2
T_PART       P_2004           2
T_PART       P_MAX            1



As you can see the issue is mitigated ...
Let us insert a insert for another year .

SQL> insert into t_part values ( 2010 , 950.00 , 'Tamil Blu Ray ') ; SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');

PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
--------- ----------- ----------
T_PART      P_2001           1
T_PART      P_2002           1
T_PART      P_2003           2
T_PART      P_2004           2
T_PART      P_MAX            1


The issue is mitigated , but all other data goes into one partition , which lead into other issues ...

Here is where interval partition comes handy.

Let us create third version of the table .

SQL> drop table t_part purge ;

Table dropped.

SQL> create table t_part
2 (
3 salyear integer ,
4 salmount number(5,2) ,
5 saldesc varchar2(40)
6 )
7 partition by range(salyear)
8 interval(1)
9 (
10 partition p_2001 values less than (2001),
11 partition p_2002 values less than (2002),
12 partition p_2003 values less than (2003),
13 partition p_2004 values less than (2004)
14 );

Table created.
SQL> insert into t_part values ( 2000 , 15.00 , 'Tamil VHS') ;

1 row created.

SQL> insert into t_part values ( 2001 , 25.00 , 'Tamil VCD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 30.00 , 'Tamil SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'Tamil DVD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 40.00 , 'English SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'English DVD') ;
1 row created.

SQL> insert into t_part values ( 2004 , 550.00 , 'French DVD') ;
1 row created.

SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');
PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
--------- ------------- ----------
T_PART       P_2001          1
T_PART       P_2002          1
T_PART       P_2003          2
T_PART       P_2004          2
T_PART       SYS_P62         1

As you can see from the above example , Oracle gracefully accomodated new record and assigned it to a new partition ( system defined ) . Later on , you can rename the partition to user defined one.

SQL> alter table t_part rename partition SYS_P62 to p_2005;

Table altered.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
---------- ------------- ----------
T_PART       P_2001           1
T_PART       P_2002           1
T_PART       P_2003           2
T_PART       P_2004           2
T_PART       P_2005           1
Let us create another record for the year 2010
 SQL> insert into t_part values ( 2010 , 950.00 , 'Tamil Blu Ray ') ;

1 row created.

SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');
PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
---------  -------------- ----------
T_PART      P_2001           1
T_PART      P_2002           1
T_PART      P_2003           2
T_PART      P_2004           2
T_PART      P_2005           1
T_PART      SYS_P63          1


Now , Oracle has created another parition for the different  year's data.

Hope you enjoyed this post.



1 row created.

Health Check

Oracle 11g provides a new tool to check the Health of the database .
The package is DBMS_HM. The database detects the issues , when it encounters them .
This can also be invoked manually . However , not all checks can be executed .

The following is the structure of the view v$hm_check


SQL> desc v$hm_check

ID               NUMBER
NAME             VARCHAR2(64)
NAME_NLS         VARCHAR2(1024)
CLSID            NUMBER
CLS_NAME         VARCHAR2(15)
FLAGS            NUMBER
INTERNAL_CHECK   VARCHAR2(1)
OFFLINE_CAPABLE  VARCHAR2(1)
DESCRIPTION      VARCHAR2(1024)
Only the checks with Internal Check <> 'Y' can be executed manually .
The results can be viewed thru ADRCI tool
These are the checks in v$hm_check .


SQL> col name format a35 wrapped
SQL> col description format a55 wrapped
SQL> Select name , description , internal_check from v$hm_check;


HM Test Check                       Check for health monitor functionality Y
DB Structure Integrity Check        Checks integrity of all database files N
CF Block Integrity Check            Checks integrity of a control file block N

Data Block Integrity Check          Checks integrity of a data file block N

Redo Integrity Check                Checks integrity of redo log content N

Logical Block Check                 Checks logical content of a block Y
Transaction Integrity Check         Checks a transaction for corruptions N
Undo Segment Integrity Check        Checks integrity of an undo segment N
No Mount CF Check                   Checks control file in NOMOUNT mode Y
Mount CF Check                      Checks control file in mount mode Y
CF Member Check                     Checks a multiplexed copy of the control Y
file
All Datafiles Check                 Checks all datafiles in the database Y
Single Datafile Check               Checks a data file Y
Tablespace Check Check              Checks a tablespace Y
Log Group Check                     Checks all members of a log group Y
Log Group Member Check              Checks a particular member of a log grou Y
p


.....
.....27 rows selected.


SQL> exec dbms_hm.run_check('Dictionary Integrity Check' , 'HM run');
PL/SQL procedure successfully completed.

View the results of the check in ADRCI.

D:\test>adrci
ADRCI: Release 11.2.0.1.0 - Production on Wed May 12 13:15:03 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "c:\app\oracle"
adrci>






**********************************************************


HM RUN RECORD 9


**********************************************************


RUN_ID                           21

RUN_NAME                         HM run

CHECK_NAME                       Dictionary Integrity Check

NAME_ID                          24

MODE                             0

START_TIME                       2010-05-12 13:09:22.671000 -04:00
RESUME_TIME                     
END_TIME                         2010-05-12 13:09:37.781000 -04:00
MODIFIED_TIME                    2010-05-12 13:09:37.781000 -04:00
TIMEOUT                          0
FLAGS                            0
STATUS                           5
SRC_INCIDENT_ID                  0
NUM_INCIDENTS                    0
ERR_NUMBER                       0
REPORT_FILE                      



As mentioned above , only the non-internal checks can be invoked manually .
As you can see , the following gives the error message , as this is a internal check .

SQL> exec dbms_hm.run_check('CF Member Check' , 'HM run');
BEGIN dbms_hm.run_check('CF Member Check' , 'HM run'); END;
*
ERROR at line 1:
ORA-51001: check [CF Member Check] not found in HM catalog
ORA-06512: at "SYS.DBMS_HM", line 191
ORA-06512: at line 1


Also , some of the checks are only for ASM instance .

SQL> exec dbms_hm.run_check('ASM Disk Visibility Check' , 'HM check asm run');
BEGIN dbms_hm.run_check('ASM Disk Visibility Check' , 'HM check asm run'); END;
*
ERROR at line 1:
ORA-51037: check [ASM Disk Visibility Check] can only be executed in ASM
instance
ORA-06512: at "SYS.DBMS_HM", line 191
ORA-06512: at line 1

Welcome any feedback.

Native Compilation

11g Release 1 and above introduced  the real "native" compilation for PL/SQL  objects . Prior to this release ,  when we compile the objects in "native" format  , it was converted into "c" code . Now additional layer is gone .

By default , the objects are in INTERPRETED format . This is good for most of the applications .
But , if  your PL/ SQL objects are arithmetic intensive , native compilation may give better performance .

This native compilation applies to PL/SQL only ... does not have any impact on SQL.

In my test , the performance was 12% better than it was in INTERPRETED format.

( your mileage may vary )


I tried this exercise in Windows version of 11g R1 . Though  I compiled in native format  , not all PL/SQL were converted into NATIVE . Some package bodies / functions were still in the INTERPRETED format.

In RHEL , it worked perfectly fine .

Here are the steps .


SQL> alter system set plsql_code_type=native;

System altered.

SQL> alter system set plsql_optimize_level=3;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

You will be need to start the instance in upgrade mode.

SQL> startup upgrade;

....

SQL> @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
.....
.....

Upon completion of the above script , you will see the following message .

DOC>#######################################################################
DOC>   dbmsupgnv.sql completed successfully. All PL/SQL procedures,
DOC>   functions, type bodies, triggers, and type bodies objects in the
DOC>   database have been invalidated and their settings set to native.
DOC>
DOC>   Shut down and restart the database in normal mode and
DOC>   run utlrp.sql to recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 3240239104 bytes
Fixed Size                  2148760 bytes
Variable Size            1577059944 bytes
Database Buffers         1644167168 bytes
Redo Buffers               16863232 bytes
Database mounted.
Database opened.
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

.....

Upon completion of the above script , you will see the following message .

SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error

PL/SQL procedure successfully completed.

SQL> SET serveroutput off.

SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>

You can check the compilation mode for all the objects in the database using the following sql . 

  1     SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*)
  2     FROM DBA_PLSQL_OBJECT_SETTINGS
  3     WHERE PLSQL_CODE_TYPE IS NOT NULL
  4     GROUP BY TYPE, PLSQL_CODE_TYPE
  5*    ORDER BY TYPE, PLSQL_CODE_TYPE
SQL> /

FUNCTION        NATIVE                 386
PACKAGE         NATIVE                1288
PACKAGE BODY    NATIVE                1225
PROCEDURE       NATIVE                 655
TRIGGER         NATIVE                 488
TYPE            INTERPRETED           2288
TYPE            NATIVE                 321
TYPE BODY       NATIVE                 224

8 rows selected.

Bear in mind , TYPES will not in NATIVE mode , as it does not have a executable code.

Good luck.

Analytics 2.0

Jumping ahead from basic analytics to analytics 2.0 ( new feature in 11g R2) 

One simple yet  neat , nicer addition to analytics family in 11g R2.
There is a new analytic function called LISTAGG  , where I could concatenate the list of the data values in a
particular group .

Prior to this release , we would have used hierarchical  query ... SYS_PATH to a achieve this result set.


Let us proceed with a simple example


SQL> Create table  t as select * from all_objects;

Table created

SQL>
SQL>         Select owner , object_type , listagg(object_name , '~')
  2           within group
  3           (order by object_name )
  4           from t
  5           where owner in ('SCOTT' , 'OUTLN')
  6           group by owner , object_type
  7           order by object_type
  8  ;

OWNER                          OBJECT_TYPE         LISTAGG(OBJECT_NAME,'~')WITHIN
------------------------------ ------------------- --------------------------------------------------------------------------------
OUTLN                          INDEX               OL$HNT_NUM~OL$NAME~OL$NODE_OL_NAME~OL$SIGNATURE
SCOTT                          INDEX               PK_DEPT~PK_EMP
OUTLN                          PROCEDURE           ORA$GRANT_SYS_SELECT
OUTLN                          TABLE               OL$~OL$HINTS~OL$NODES
SCOTT                          TABLE               BONUS~DEPT~EMP~SALGRADE


Note : ~ is the delimiter

Bear in mind , the result set is of datatype varchar2( unless otherwise the columns defined in the group are RAW) , so you may be hit the limitation of varchar2(4000).

SQL>        Select owner , object_type , listagg(object_name , '~')
  2           within group
  3           (order by object_name )
  4           from t
  5           where owner in ('SCOTT' , 'SYS')
  6           group by owner , object_type
  7           order by object_type
  8  ;

Select owner , object_type , listagg(object_name , '~')
         within group
         (order by object_name )
         from t
         where owner in ('SCOTT' , 'SYS')
         group by owner , object_type
         order by object_type

ORA-01489: result of string concatenation is too long

Have a good time with analytcs.
Please refer to http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm for additional informaiton .