About Me

Working as Oracle professional over 7 years now. Specialized in - advanced PL/SQL, advanced SQL queries, Performance Tuning & Designing. Have completed Oracle Advanced PLSQL certification (OCP)

Saturday, February 6, 2010

TO FIND LAST N ROWS AND DISPLAY ROWNUMBER IN A TABLE

To find the last N and display the row number.

With the usual way to pick last set of records from any table minimum 3 joins are required. Using analytical functions the same can be reduced to single table hit to retrieve the last N rows and display the row numbers for the same. This is particularly useful when something like this needs to be achieved in a big query that has multiple joins and multiple column fetch.

NON ANALYTICAL WAY

select * from scott.emp minus select * from scott.emp where rownum <
(select count(*) – 10 from scott.emp);


SQL> select * from scott.emp minus select * from scott.emp where rownum <
2 (select count(*) - 10 from scott.emp);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2564595189

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1036 | 9 (67)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 14 | 518 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 14 | 518 | 5 (20)| 00:00:01 |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | | | |
| 8 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - filter( (SELECT COUNT(*)-10 FROM "SCOTT"."EMP" "EMP")>ROWNUM)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
1250 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
11 rows processed

SQL>

ANALYTICAL WAY

SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.comm,qry.deptno,qry.row_desc
from
(SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC) ROW_ASC, COUNT(1) OVER (ORDER BY ROWNUM DESC) ROW_DESC FROM SCOTT.EMP A) QRY where qry.row_Asc between 1 and 11


SQL> SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.com
m,qry.deptno,qry.row_desc
2 from (SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC) ROW_ASC, COUNT(1) OVE
R (ORDER BY ROWNUM DESC) ROW_DESC FROM SCOTT.EMP A) QRY where qry.row_Asc betwee
n 1 and 11;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO ROW_DESC
---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1100
20 4
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30 5
7839 KING PRESIDENT 17-NOV-81 5000
10 6
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 7
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 8
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 9
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 10
7566 JONES MANAGER 7839 02-APR-81 2975
20 11
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 12
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 13
7369 SMITH CLERK 7902 17-DEC-80 800
20 14

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2175649969

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1582 | 5 (40)| 00:00:01 |
|* 1 | VIEW | | 14 | 1582 | 5 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 518 | 5 (40)| 00:00:01 |
| 3 | WINDOW SORT | | 14 | 518 | 5 (40)| 00:00:01 |
| 4 | COUNT | | | | | |
| 5 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("QRY"."ROW_ASC">=1 AND "QRY"."ROW_ASC"<=11)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
 0 physical reads
0 redo size
1360 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
11 rows processed
Posted by at 4 comments:

TO FIND RUNNING TOTAL

To find the running total using the non-analytical way minimum the same table needs to be joined twice. Using Analytical functions the same can be achieved with single table hit. In a single query running total can be computed in no time.

NON ANALYTICAL WAY -

Select emp1.empno, emp1.sal, sum(emp2.sal) from scott.emp emp1, scott.emp emp2
where emp2.empno <= emp1.empno group by emp1.empno, emp1.sal
order by emp1.empno

EMPNO SAL SUM(EMP2.SAL)
---------- ---------- -------------
7369 800 800
7499 1600 2400
7521 1250 3650
7566 2975 6625
7654 1250 7875
7698 2850 10725
7782 2450 13175
7788 3000 16175
7839 5000 21175
7844 1500 22675
7876 1100 23775
7900 950 24725
7902 3000 27725
7934 1300 29025

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1016356015
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 160 | 7 (29)| 0
0:00:01 |
| 1 | SORT GROUP BY | | 10 | 160 | 7 (29)| 0
0:00:01 |
| 2 | MERGE JOIN | | 10 | 160 | 6 (17)| 0
0:00:01 |
| 3 | SORT JOIN | | 14 | 112 | 2 (0)| 0
0:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 112 | 2 (0)| 0
0:00:01 |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 0
0:00:01 |
|* 6 | SORT JOIN | | 14 | 112 | 4 (25)| 0
0:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 112 | 3 (0)| 0
0:00:01 |


Predicate Information (identified by operation id):
---------------------------------------------------

6 - access(INTERNAL_FUNCTION("EMP2"."EMPNO")<=INTERNAL_FUNCTION("EMP1"."EMPNO
" )) filter(INTERNAL_FUNCTION("EMP2"."EMPNO")<=INTERNAL_FUNCTION("EMP1"."EMPNO
" ))

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
790 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>

ANALYTICAL WAY

SQL> set autotrace on
SQL> Select e1.empno, e1.sal, sum(e1.sal) OVER (ORDER BY rownum) from scott.emp
e1 order by e1.empno;

EMPNO SAL SUM(E1.SAL)OVER(ORDERBYROWNUM)
---------- ---------- ------------------------------
7369 800 800
7499 1600 2400
7521 1250 3650
7566 2975 6625
7654 1250 7875
7698 2850 10725
7782 2450 13175
7788 3000 16175
7839 5000 21175
7844 1500 22675
7876 1100 23775
7900 950 24725
7902 3000 27725
7934 1300 29025

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3990564813

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 112 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 112 | 5 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 112 | 5 (40)| 00:00:01 |
| 3 | COUNT | | | | | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
 0 physical reads
0 redo size
807 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>
Posted by at No comments:

TOP N rows from a table

Non Analytical way

SELECT * FROM scott.emp inside WHERE 5 >= (SELECT COUNT (DISTINCT outside.sal) FROM scott.emp outside
WHERE outside.sal >= inside.sal) ORDER BY inside.sal DESC;


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -------
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1721702769

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 25 (4)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 37 | 25 (4)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 4 | SORT GROUP BY | | 1 | 4 | | |
|* 5 | TABLE ACCESS FULL| EMP | 1 | 4 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter( (SELECT COUNT(DISTINCT "OUTSIDE"."SAL") FROM
"SCOTT"."EMP" "OUTSIDE" WHERE "OUTSIDE"."SAL">=:B1)<=5)
5 - filter("OUTSIDE"."SAL">=:B1)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
91 consistent gets
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>

Analytical way
SQL> SELECT a.* FROM
2 (select e.*,row_number() OVER (order by rownum) rnm FROM scott.emp e) a
3 WHERE rnm BETWEEN 1 AND 6;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
20 1
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 2
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 3
7566 JONES MANAGER 7839 02-APR-81 2975
20 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 6


6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1602237660

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | COUNT | | | | ||
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------

-


Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RNM">=1 AND "RNM"<=6)
2 - filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=6)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1140 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
Posted by at No comments:

Retrieve say 5 to 8 rows from table

Non-Analytical way

SELECT a.* FROM
(select e.*,ROWNUM rnm FROM scott.emp e) a
WHERE rnm BETWEEN 3 AND 8;


SQL> SELECT a.* FROM
2 (select e.*,ROWNUM rnm FROM scott.emp e) a
3 WHERE rnm BETWEEN 3 AND 8;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 3

7566 JONES MANAGER 7839 02-APR-81 2975
20 4

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 6

7782 CLARK MANAGER 7839 09-JUN-81 2450
10 7

7788 SCOTT ANALYST 7566 19-APR-87 3000
20 8


6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2077119879

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 14 | 1400 | 3 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RNM"<=8 AND "RNM">=3)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1142 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>


OR

SELECT * FROM scott.emp WHERE rowid in (SELECT rowid FROM scott.emp WHERE rownum <= 8
MINUS
SELECT rowid FROM scott.emp WHERE rownum < 3);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 986843382
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 8 (38)| 00:00:01|
|* 1 | HASH JOIN | | 1 | 49 | 8 (38)| 00:00:01|
| 2 | VIEW | VW_NSO_1 | 8 | 96 | 4 (50)| 00:00:01|
| 3 | MINUS | | | | ||
| 4 | SORT UNIQUE | | 8 | 96 | ||
|* 5 | COUNT STOPKEY | | | | ||
| 6 | INDEX FULL SCAN| PK_EMP | 14 | 168 | 1 (0)| 00:00:01|
| 7 | SORT UNIQUE | | 2 | 24 | ||
|* 8 | COUNT STOPKEY | | | | ||
| 9 | INDEX FULL SCAN| PK_EMP | 14 | 168 | 1 (0)| 00:00:01|
| 10 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access(ROWID="ROWID")
5 - filter(ROWNUM<=8)
8 - filter(ROWNUM<3)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1069 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6 rows processed


Using Analytical functions


SQL> SELECT a.* FROM
(select e.*,row_number() OVER (order by rownum) rnm FROM scott.emp e) a
WHERE rnm BETWEEN 3 AND 8;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 3
7566 JONES MANAGER 7839 02-APR-81 2975
20 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 6
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 7
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 8

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1602237660

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | COUNT | | | | ||
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RNM">=3 AND "RNM"<=8)
2 - filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=8)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1142 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
Posted by at No comments:

Retrieve the Nth row from a table

To retrieve Nth row from a table using the usual way it might require to query the same table thrice (can write it in different way also). But, with Analytical function the same can be made to hit only once. Using this kind of analytical functions is particularly useful when the query needs to be part of a huge query - helps to avoid group by - so the need to query the same table multiple times is reduced.

Non Analytical way

One way to do this - Usual - three table hits.

select sal FROM scott.emp WHERE rowid = (SELECT rowid FROM scott.emp WHERE rownum <= 5 MINUS SELECT rowid FROM scott.emp WHERE rownum < 5);

SAL
----------
1250

Execution Plan
----------------------------------------------------------
Plan hash value: 791824717

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 5 (40)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 16 | 1 (0)| 00:00:01 |
| 2 | MINUS | | | | | |
| 3 | SORT UNIQUE | | 5 | 60 | 2 (50)| 00:00:01 |
|* 4 | COUNT STOPKEY | | | | | |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | 168 | 1 (0)| 00:00:01 |
| 6 | SORT UNIQUE | | 4 | 48 | 2 (50)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | INDEX FULL SCAN | PK_EMP | 14 | 168 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter(ROWNUM<=5)
7 - filter(ROWNUM<5)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed


Using Analytical functions


SQL> SELECT a.* FROM
(select e.*,row_number() OVER (order by rownum) rnm FROM scott.emp e) a
WHERE rnm = 5;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5

Execution Plan
----------------------------------------------------------
Plan hash value: 1602237660

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | COUNT | | | | ||
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RNM"=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=5)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
 0 physical reads
0 redo size
892 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Posted by at 3 comments:

To find Nth maximum value from a table

To find NTH maximum value from a table, we may need to query the same table twice to get the desired output. This will lead to table scans / index scans for the same table twice. The same can be achieved via analytical functions by querying the table once. Have enclosed the execution plan and have highlighted the difference.

Non analytical way

SQL> SELECT DISTINCT SAL FROM SCOTT.EMP A WHERE &N=(SELECT COUNT (DISTINCT B.SAL
)
2 FROM SCOTT.EMP B WHERE A.SAL<=B.SAL);
Enter value for n: 5
old 1: SELECT DISTINCT SAL FROM SCOTT.EMP A WHERE &N=(SELECT COUNT (DISTINCT B
.SAL)
new 1: SELECT DISTINCT SAL FROM SCOTT.EMP A WHERE 5=(SELECT COUNT (DISTINCT B.
SAL)

SAL
----------
2450

Execution Plan
----------------------------------------------------------
Plan hash value: 3556451907

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 25 (4)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 4 | 25 (4)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 14 | 56 | 3 (0)| 00:00:01 |
| 4 | SORT GROUP BY | | 1 | 4 | | |
|* 5 | TABLE ACCESS FULL| EMP | 1 | 4 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter( (SELECT COUNT(DISTINCT "B"."SAL") FROM "SCOTT"."EMP" "B" WHERE "B"."SAL">=:B1)=5)
5 - filter("B"."SAL">=:B1)


Statistics
----------------------------------------------------------
187 recursive calls
0 db block gets
127 consistent gets
4 physical reads
0 redo size
414 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
1 rows processed

USING Analytical functions -


SELECT * FROM
(SELECT e.*, RANK() OVER ( ORDER BY e.SAL DESC) rnk FROM scott.emp e) qry
WHERE qry.rnk = 6;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNK
---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 6


Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("QRY"."RNK"=6)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("E"."SAL") DESC)<=6)


Statistics
----------------------------------------------------------
144 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
888 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> SELECT * FROM
2 (SELECT e.*, RANK() OVER ( ORDER BY e.SAL DESC) rnk FROM scott.emp e) qry
3 WHERE qry.rnk = 6;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNK
---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 6



Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077

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

-

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("QRY"."RNK"=6)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("E"."SAL") DESC
)<=6)


Statistics
----------------------------------------------------------
144 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
888 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Posted by at No comments:

Friday, February 5, 2010

Read Hexa-decimal file via External table

External table was introduced in Oracle 9i which can be used to read file from ORACLE DIRECTORY. Multiple files can be read at the same time. It can also read hexa-decimal files. The following example demonstrates the use of external table to read from hexa-decimal file.

TEST : External table to read from hexa-decimal delimited with different hexa-decimal values.

FILE

Emp Name David Deptid Team1
Emp Name Imran Deptid Team1
Emp Name Ikia Deptid Team1
Emp Name Courts Deptid Team2
Emp Name John Deptid Team2
Emp Name Julie Deptid Team3
Emp Name Josaphine Deptid Team3

create or replace directory ext_dir as 'C:\'

drop table tp_emp_ext;

create table tp_emp_ext
(Emp_name_field varchar2(200),
Emp_name_value varchar2(200),
Dept_id_field varchar2(200),
Dept_id_value varchar2(200))
organization external (
type oracle_loader
default directory ext_dir
access parameters ( records delimited by newline
nodiscardfile
nologfile
fields terminated by 0X'11'
missing field values are null
REJECT ROWS WITH ALL NULL
FIELDS
(Emp_name_field char(200) terminated by 0X'02',
Emp_name_value char(200) terminated by 0X'03',
Dept_id_field char(200) terminated by 0X'02',
Dept_id_value char(200) terminated by 0X'03')
)
location ('samp_text.dat')
)
reject limit unlimited


select * from tp_emp_ext

EMP_NAME_FIELD EMP_NAME_VALUE DEPT_ID_FIELD DEPT_ID_VALUE

Emp Name David Deptid Team1
Emp Name Imran Deptid Team1
Emp Name Ikia Deptid Team1
Emp Name Courts Deptid Team2
Emp Name John Deptid Team2
Emp Name Julie Deptid Team3
Emp Name Josaphine Deptid Team3
Posted by at 6 comments:

FOR LOOP EXECUTE IMMEDIATE VS FORALL EXECUTE IMMEDIATE

Usually FORALL is possible only with DML operations (Insert / Update / Delete / Merge). From Oracle 9i it is possible with EXECUTE IMMEDIATE.

This is just for example purpose only - FORALL is really useful for bulk processing. Though in the below example a direct FORALL - INSERT is possible, have used EXECUTE IMMEDATE to demonstrate its use.

TABLE CREATION SCRIPTS

drop table coll_temp_source

CREATE TABLE coll_temp_source AS SELECT
status, object_id, owner
FROM ALL_OBJECTS where rownum < 201

drop table coll_temp_target

CREATE TABLE coll_temp_target AS SELECT
status, object_id, owner
FROM ALL_OBJECTS where rownum = 5

STEP 1 - USING FORALL

DECLARE
TYPE obj_list IS TABLE OF coll_temp_source%ROWTYPE;
obj_tab obj_list;
TYPE status_list IS TABLE OF coll_temp_source.status%TYPE;
status_tab status_list;
TYPE object_id_list IS TABLE OF coll_temp_source.object_id%TYPE;
object_id_tab object_id_list;
TYPE owner_list IS TABLE OF coll_temp_source.owner%TYPE;
owner_tab owner_list;
BEGIN
SELECT status, object_id, owner BULK COLLECT INTO status_tab, object_id_tab, owner_tab
FROM coll_temp_source WHERE rownum < 11;

FORALL i IN 1 .. status_tab.count
EXECUTE IMMEDIATE
'INSERT INTO coll_temp_target VALUES (:1, :2 ,:3)'
USING status_tab(i), object_id_tab(i) , owner_tab(i);

END;
/

STEP 2 - USING FOR.. LOOP

DECLARE
TYPE obj_list IS TABLE OF coll_temp_source%ROWTYPE;
obj_tab obj_list;
TYPE status_list IS TABLE OF coll_temp_source.status%TYPE;
status_tab status_list;
TYPE object_id_list IS TABLE OF coll_temp_source.object_id%TYPE;
object_id_tab object_id_list;
TYPE owner_list IS TABLE OF coll_temp_source.owner%TYPE;
owner_tab owner_list;
BEGIN
SELECT status, object_id, owner BULK COLLECT INTO status_tab, object_id_tab, owner_tab
FROM coll_temp_source WHERE rownum < 11;

FOR i IN 1 .. status_tab.count LOOP
EXECUTE IMMEDIATE
'INSERT INTO coll_temp_target VALUES (:1, :2 ,:3)'
USING status_tab(i), object_id_tab(i) , owner_tab(i);
END LOOP;

END;
/

SQL> @exec.sql

PL/SQL procedure successfully completed.

SQL> select * from coll_temp_target;

STATUS OBJECT_ID OWNER
------- ---------- ------------------------------
VALID 79400 SYS
VALID 69022 SYS
VALID 84837 SYS
VALID 75686 SYS
VALID 84402 SYS
VALID 71636 SYS
VALID 85043 SYS
VALID 85098 SYS
VALID 71312 SYS
VALID 73222 SYS

10 rows selected.
Posted by at 1 comment:

Collections Vs. Pipelined Table Function

PIPELINED Table functions enables to return sets of data when the next set of data is being processed. This is obviously better than normal TABLE functions. Because, normal TABLE functions picks and processes all the data at one shot then returns it - so its particularly useful for ETL (Extraction - Transformation - Load).

My opinion - For not too complex operations its better to use COLLECTIONS / GLOBAL TEMPORARY Tables rather than Pipelined Table functions. As the extraction / transformation and load is faster than via pipelined table functions. Following is a quick demonstration.

TABLE CREATION SCRIPTS

SQL> drop table coll_temp_source;

Table dropped.

SQL> CREATE TABLE coll_temp_source AS SELECT ALL_OBJECTS.* FROM ALL_OBJECTS,
(select * from dual connect by level <= 25); 2

drop table coll_temp_target5;
CREATE TABLE COLL_TEMP_TARGET5 AS SELECT * FROM ALL_OBJECTS WHERE 1 = 2;


Table created.

SQL> SQL>
Table dropped.

SQL>
Table created.

SQL> SQL>
SQL> drop sequence dummy_seq1;

Sequence dropped.

SQL> create sequence dummy_seq1 start with 1
2 /

Sequence created.


PIPELINED TABLE FUNCTION CREATION SCRIPT

SET TIMING ON

CREATE OR REPLACE FUNCTION pipe_fun (p_cursor IN SYS_REFCURSOR )
RETURN t_expected PIPELINED
PARALLEL_ENABLE (PARTITION p_cursor BY ANY)
AS
TYPE g_object_list IS TABLE OF coll_temp_source%ROWTYPE INDEX BY PLS_INTEGER;
g_object_tab g_object_list;
g_error_level NUMBER;
v_object_id NUMBER;
l_row t_validate_row := t_validate_row(NULL, NULL, NULL,NULL, NULL, NULL,NULL, NULL, NULL,NULL, NULL, NULL,NULL);

BEGIN

LOOP
g_error_level := 2;
FETCH p_cursor BULK COLLECT INTO g_object_tab LIMIT 300;


FOR i IN g_object_tab.FIRST .. g_object_tab.LAST LOOP

g_error_level := 3;
l_row.OWNER := g_object_tab(i).OWNER ;
l_row.OBJECT_NAME := g_object_tab(i).OBJECT_NAME ;
l_row.SUBOBJECT_NAME := g_object_tab(i).SUBOBJECT_NAME ;
l_row.DATA_OBJECT_ID := g_object_tab(i).DATA_OBJECT_ID ;
l_row.OBJECT_TYPE := g_object_tab(i).OBJECT_TYPE ;
l_row.CREATED := g_object_tab(i).CREATED ;
l_row.LAST_DDL_TIME := g_object_tab(i).LAST_DDL_TIME ;
l_row.TIMESTAMP := g_object_tab(i).TIMESTAMP ;
l_row.STATUS := g_object_tab(i).STATUS ;
l_row.TEMPORARY := g_object_tab(i).TEMPORARY ;
l_row.GENERATED := g_object_tab(i).GENERATED ;
l_row.SECONDARY := g_object_tab(i).SECONDARY ;
SELECT dummy_seq1.nextval INTO v_object_id FROM dual;
l_row.object_id := v_object_id;

IF l_row.owner = 'SYS' THEN
l_row.secondary := 'Y';
END IF;
IF l_row.object_type = 'JAVA CLASS' THEN
l_row.temporary := 'Y';
END IF;
PIPE ROW (l_row);
END LOOP;
EXIT WHEN p_cursor%NOTFOUND;
END LOOP;
CLOSE p_cursor;
RETURN;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error level is - ' || g_error_level );
dbms_output.put_line('Error message is - ' || SUBSTR(SQLERRM,1,255));
END;
/


SQL> @pipe_func.sql

Function created.

Elapsed: 00:00:00.10
SQL>
SQL> INSERT INTO COLL_TEMP_TARGET5
SELECT *
FROM TABLE(
pipe_fun (
cursor(SELECT * FROM COLL_TEMP_SOURCE))) 2 3 4 5 ;

761125 rows created.

Elapsed: 00:01:38.91
SQL>


COLLECTIONS

SET TIMING ON
SET SERVEROUTPUT ON SIZE 10000

DECLARE
TYPE g_object_list IS TABLE OF coll_temp_source%ROWTYPE INDEX BY PLS_INTEGER;
g_object_tab g_object_list;
g_error_level NUMBER;
v_object_id NUMBER;

CURSOR cur_coll_temp IS
SELECT *
FROM coll_temp_source;

BEGIN
g_error_level := 1;
OPEN cur_coll_temp;

LOOP
g_error_level := 2;
FETCH cur_coll_temp BULK COLLECT INTO g_object_tab LIMIT 300;

g_error_level := 3;
EXIT WHEN cur_coll_temp%NOTFOUND;

FOR i IN g_object_tab.FIRST .. g_object_tab.LAST LOOP
SELECT dummy_seq1.nextval INTO v_object_id FROM dual;
g_object_tab(i).object_id := v_object_id;
IF g_object_tab(i).owner = 'SYS' THEN
g_object_tab(i).secondary := 'Y';
END IF;

IF g_object_tab(i).object_type = 'JAVA CLASS' THEN
g_object_tab(i).temporary := 'Y';
END IF;
END LOOP;

FORALL i IN g_object_tab.FIRST .. g_object_tab.LAST
INSERT INTO coll_temp_target5 VALUES g_object_tab(i);

COMMIT;

END LOOP;

g_error_level := 10;
CLOSE cur_coll_temp;


EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error level is - ' || g_error_level );
dbms_output.put_line('Error message is - ' || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @collections.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:58.99
SQL>
Posted by at No comments:

Wednesday, February 3, 2010

MERGE BASED ON TEMPORARY TABLES VS MERGE BASED ON COLLECTIONS

Merge - It is always better than separate INSERT / UPDATE and DELETE operations (Delete operation is added in 11g). FORALL Merge is pretty much possible while using COLLECTIONS. FORALL Merge is obviously faster than FOR LOOP Merge, as it enables bulk processing.

My opinion - If there is a choice between FORALL Merge and Merge using Global Temporary tables, its better to use the later. Following is a simple demonstration.

STEP 1 - TABLE CREATE SCRIPTS:

QUICK LOOK AT RESULTS -

Merge based on Temporary table : Elapsed: 00:00:35.68
Merge based on Collections : Elapsed: 00:11:20.27
Merge based on actual table : Elapsed: 00:00:37.30

drop table coll_temp_source

CREATE TABLE coll_temp_source AS SELECT ALL_OBJECTS.* FROM ALL_OBJECTS,
(select * from dual connect by level <= 25)

drop table gtt_stage

CREATE GLOBAL TEMPORARY TABLE gtt_stage on commit preserve rows
as select a.rowid gtt_rid, a.* from coll_temp_source a where 1 = 2

create sequence dummy_seq1 start with 1

drop table coll_temp_target5

CREATE TABLE COLL_TEMP_TARGET5 as
select
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
rownum object_id,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
FROM coll_temp_source where rownum < 20000.


STEP 2 - MERGE + TEMP TABLE SCRIPT

SET TIMING ON
SET SERVEROUTPUT ON SIZE 100000

DECLARE
TYPE rowid_list IS TABLE OF UROWID INDEX BY PLS_INTEGER;
min_rid_tab rowid_list;
max_rid_tab rowid_list;
g_error_level NUMBER;

BEGIN
g_error_level := 0;
INSERT INTO gtt_stage
SELECT /*+ ALL_ROWS */ ctgs.rowid rid
, ctgs.OWNER
, ctgs.OBJECT_NAME
, ctgs.SUBOBJECT_NAME
, rownum
, ctgs.data_object_id
, ctgs.OBJECT_TYPE
, ctgs.CREATED
, ctgs.LAST_DDL_TIME
, ctgs.TIMESTAMP
, ctgs.STATUS
, ctgs.TEMPORARY
, ctgs.GENERATED
, ctgs.SECONDARY
FROM coll_temp_source ctgs ORDER BY ROWID;

g_error_level := 1;
SELECT /*+ ALL_ROWS */ qry.rid BULK COLLECT INTO min_rid_tab
FROM (SELECT MOD (ROWNUM, 10000) rnm,
ROWID rid
FROM coll_temp_source
ORDER BY ROWID) qry
WHERE qry.rnm = 1
ORDER BY 1;

g_error_level := 1;
SELECT /*+ ALL_ROWS */ qry.rid BULK COLLECT INTO max_rid_tab
FROM (SELECT MOD (ROWNUM, 10000) rnm, ROWID rid
FROM coll_temp_source
ORDER BY ROWID) qry
WHERE qry.rnm = 0
UNION
SELECT /*+ ALL_ROWS */ MAX (ROWID)
FROM coll_temp_source;

g_error_level := 2;
FORALL i IN min_rid_tab.FIRST .. min_rid_tab.LAST
MERGE INTO coll_temp_target5 tgt
USING
(SELECT /*+ INDEX (ctgs, gtt_stage_idx) */
ctgs.OWNER OWNER
, ctgs.OBJECT_NAME OBJECT_NAME
, ctgs.SUBOBJECT_NAME SUBOBJECT_NAME
, ctgs.object_id object_id
, ctgs.data_object_id data_object_id
, ctgs.OBJECT_TYPE OBJECT_TYPE
, ctgs.CREATED CREATED
, ctgs.LAST_DDL_TIME LAST_DDL_TIME
, ctgs.TIMESTAMP TIMESTAMP
, ctgs.STATUS STATUS
, DECODE(object_type , 'JAVA CLASS', 'Y', ctgs.TEMPORARY) TEMPORARY
, ctgs.GENERATED GENERATED
, DECODE(ctgs.OWNER, 'SYS', 'Y', ctgs.SECONDARY) SECONDARY
FROM gtt_stage ctgs WHERE gtt_rid BETWEEN min_rid_tab(i) AND max_rid_tab(i)) src
ON (tgt.object_id = src.object_id)
WHEN MATCHED THEN
UPDATE SET
tgt.OWNER = src.OWNER
,tgt.OBJECT_NAME = src.OBJECT_NAME
,tgt.SUBOBJECT_NAME = src.SUBOBJECT_NAME
,tgt.data_object_id = src.data_object_id
,tgt.OBJECT_TYPE = src.OBJECT_TYPE
,tgt.CREATED = src.CREATED
,tgt.LAST_DDL_TIME = src.LAST_DDL_TIME
,tgt.TIMESTAMP = src.TIMESTAMP
,tgt.STATUS = src.STATUS
,tgt.TEMPORARY = src.TEMPORARY
,tgt.GENERATED = src.GENERATED
,tgt.SECONDARY = src.SECONDARY
WHEN NOT MATCHED THEN
INSERT
( tgt.OWNER
,tgt.OBJECT_NAME
,tgt.SUBOBJECT_NAME
,tgt.object_id
,tgt.data_object_id
,tgt.OBJECT_TYPE
,tgt.CREATED
,tgt.LAST_DDL_TIME
,tgt.TIMESTAMP
,tgt.STATUS
,tgt.TEMPORARY
,tgt.GENERATED
,tgt.SECONDARY )
VALUES
(
src.OWNER
,src.OBJECT_NAME
,src.SUBOBJECT_NAME
,src.object_id
,src.data_object_id
,src.OBJECT_TYPE
,src.CREATED
,src.LAST_DDL_TIME
,src.TIMESTAMP
,src.STATUS
,src.TEMPORARY
,src.GENERATED
,src.SECONDARY
);


g_error_level := 4;
COMMIT;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error level is - ' || g_error_level );
dbms_output.put_line('Error message is - ' || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @merge_temp.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:35.68
SQL>



STEP 3 - MERGE + COLLECTIONS Scripts

drop table coll_temp_target5

CREATE TABLE COLL_TEMP_TARGET5 as
select
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
rownum object_id,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
FROM coll_temp_source where rownum < 20000


SET TIMING ON
SET SERVEROUTPUT ON SIZE 10000

DECLARE
TYPE g_OWNER IS TABLE OF COLL_TEMP_SOURCE.OWNER%type;
TYPE g_OBJECT_NAME IS TABLE OF COLL_TEMP_SOURCE.OBJECT_NAME%type;
TYPE g_SUBOBJECT_NAME IS TABLE OF COLL_TEMP_SOURCE.SUBOBJECT_NAME%type;
TYPE g_OBJECT_ID IS TABLE OF COLL_TEMP_SOURCE.OBJECT_ID%type;
TYPE g_DATA_OBJECT_ID IS TABLE OF COLL_TEMP_SOURCE.DATA_OBJECT_ID%type;
TYPE g_OBJECT_TYPE IS TABLE OF COLL_TEMP_SOURCE.OBJECT_TYPE%type;
TYPE g_CREATED IS TABLE OF COLL_TEMP_SOURCE.CREATED%type;
TYPE g_LAST_DDL_TIME IS TABLE OF COLL_TEMP_SOURCE.LAST_DDL_TIME%type;
TYPE g_TIMESTAMP IS TABLE OF COLL_TEMP_SOURCE.TIMESTAMP%type;
TYPE g_STATUS IS TABLE OF COLL_TEMP_SOURCE.STATUS%type;
TYPE g_TEMPORARY IS TABLE OF COLL_TEMP_SOURCE.TEMPORARY%type;
TYPE g_GENERATED IS TABLE OF COLL_TEMP_SOURCE.GENERATED%type;
TYPE g_SECONDARY IS TABLE OF COLL_TEMP_SOURCE.SECONDARY%type;

g_OWNER_t g_OWNER;
g_OBJECT_NAME_t g_OBJECT_NAME;
g_SUBOBJECT_NAME_t g_SUBOBJECT_NAME;
g_OBJECT_ID_t g_OBJECT_ID;
g_DATA_OBJECT_ID_t g_DATA_OBJECT_ID;
g_OBJECT_TYPE_t g_OBJECT_TYPE;
g_CREATED_t g_CREATED;
g_LAST_DDL_TIME_t g_LAST_DDL_TIME;
g_TIMESTAMP_t g_TIMESTAMP;
g_STATUS_t g_STATUS;
g_TEMPORARY_t g_TEMPORARY;
g_GENERATED_t g_GENERATED;
g_SECONDARY_t g_SECONDARY;


g_error_level NUMBER;
v_object_id NUMBER;

CURSOR cur_coll_temp IS
SELECT
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
rownum object_id,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
FROM coll_temp_source;

BEGIN
g_error_level := 1;
OPEN cur_coll_temp;

LOOP
g_error_level := 2;
FETCH cur_coll_temp BULK COLLECT INTO g_OWNER_t
,g_OBJECT_NAME_t
,g_SUBOBJECT_NAME_t
,g_OBJECT_ID_t
,g_DATA_OBJECT_ID_t
,g_OBJECT_TYPE_t
,g_CREATED_t
,g_LAST_DDL_TIME_t
,g_TIMESTAMP_t
,g_STATUS_t
,g_TEMPORARY_t
,g_GENERATED_t
,g_SECONDARY_t LIMIT 300;

g_error_level := 3;
EXIT WHEN cur_coll_temp%NOTFOUND;

FORALL i IN g_owner_t.FIRST .. g_owner_t.LAST
MERGE INTO coll_temp_target5 tgt
USING
(SELECT /*+ ALL_ROWS */
g_OWNER_t(i) owner
,g_OBJECT_NAME_t(i) object_name
,g_SUBOBJECT_NAME_t(i) subobject_name
,g_OBJECT_ID_t(i) object_id
,g_DATA_OBJECT_ID_t(i) data_object_id
,g_OBJECT_TYPE_t(i) object_type
,g_CREATED_t(i) created
,g_LAST_DDL_TIME_t(i) last_ddl_time
,g_TIMESTAMP_t(i) timestamp
,g_STATUS_t(i) status
, DECODE(g_OBJECT_TYPE_t(i) , 'JAVA CLASS', 'Y', g_TEMPORARY_t(i)) TEMPORARY
,g_GENERATED_t(i) generated
,DECODE(g_OWNER_t(i), 'SYS', 'Y', g_SECONDARY_t(i)) secondary
FROM dual ) src
ON (tgt.object_id = src.object_id)
WHEN MATCHED THEN
UPDATE SET
tgt.OWNER = src.OWNER
,tgt.OBJECT_NAME = src.OBJECT_NAME
,tgt.SUBOBJECT_NAME = src.SUBOBJECT_NAME
,tgt.data_object_id = src.data_object_id
,tgt.OBJECT_TYPE = src.OBJECT_TYPE
,tgt.CREATED = src.CREATED
,tgt.LAST_DDL_TIME = src.LAST_DDL_TIME
,tgt.TIMESTAMP = src.TIMESTAMP
,tgt.STATUS = src.STATUS
,tgt.TEMPORARY = src.TEMPORARY
,tgt.GENERATED = src.GENERATED
,tgt.SECONDARY = src.SECONDARY
WHEN NOT MATCHED THEN
INSERT
(tgt.OWNER
,tgt.OBJECT_NAME
,tgt.SUBOBJECT_NAME
,tgt.object_id
,tgt.data_object_id
,tgt.OBJECT_TYPE
,tgt.CREATED
,tgt.LAST_DDL_TIME
,tgt.TIMESTAMP
,tgt.STATUS
,tgt.TEMPORARY
,tgt.GENERATED
,tgt.SECONDARY )
VALUES
(
src.OWNER
,src.OBJECT_NAME
,src.SUBOBJECT_NAME
,src.object_id
,src.data_object_id
,src.OBJECT_TYPE
,src.CREATED
,src.LAST_DDL_TIME
,src.TIMESTAMP
,src.STATUS
,src.TEMPORARY
,src.GENERATED
,src.SECONDARY
);

COMMIT;

END LOOP;

g_error_level := 10;
CLOSE cur_coll_temp;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error level is - ' || g_error_level );
dbms_output.put_line('Error message is - ' || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @merge_coll.sql;


Elapsed: 00:11:20.27

Cancelled the run -
Stopped after processing 58800 760475 records.

STEP 4 - MERGE + TABLE Script

drop table coll_temp_target5

CREATE TABLE COLL_TEMP_TARGET5 as
select
OWNER ,
OBJECT_NAME ,
SUBOBJECT_NAME ,
rownum object_id,
DATA_OBJECT_ID ,
OBJECT_TYPE ,
CREATED ,
LAST_DDL_TIME ,
TIMESTAMP ,
STATUS ,
TEMPORARY ,
GENERATED ,
SECONDARY
FROM coll_temp_source where rownum < 20000


SET TIMING ON
SET SERVEROUTPUT ON SIZE 100000

DECLARE
TYPE rowid_list IS TABLE OF UROWID INDEX BY PLS_INTEGER;
min_rid_tab rowid_list;
max_rid_tab rowid_list;
g_error_level NUMBER;

BEGIN
g_error_level := 1;
SELECT /*+ ALL_ROWS */ qry.rid BULK COLLECT INTO min_rid_tab
FROM (SELECT MOD (ROWNUM, 10000) rnm,
ROWID rid
FROM coll_temp_source
ORDER BY ROWID) qry
WHERE qry.rnm = 1
ORDER BY 1;

g_error_level := 1;
SELECT /*+ ALL_ROWS */ qry.rid BULK COLLECT INTO max_rid_tab
FROM (SELECT MOD (ROWNUM, 10000) rnm, ROWID rid
FROM coll_temp_source
ORDER BY ROWID) qry
WHERE qry.rnm = 0
UNION
SELECT /*+ ALL_ROWS */ MAX (ROWID)
FROM coll_temp_source;

g_error_level := 2;
FORALL i IN min_rid_tab.FIRST .. min_rid_tab.LAST
MERGE INTO coll_temp_target5 tgt
USING
(SELECT /*+ ALL_ROWS */
ctgs.OWNER OWNER
, ctgs.OBJECT_NAME OBJECT_NAME
, ctgs.SUBOBJECT_NAME SUBOBJECT_NAME
, ctgs.object_id object_id
, ctgs.data_object_id data_object_id
, ctgs.OBJECT_TYPE OBJECT_TYPE
, ctgs.CREATED CREATED
, ctgs.LAST_DDL_TIME LAST_DDL_TIME
, ctgs.TIMESTAMP TIMESTAMP
, ctgs.STATUS STATUS
, DECODE(object_type , 'JAVA CLASS', 'Y', ctgs.TEMPORARY) TEMPORARY
, ctgs.GENERATED GENERATED
, DECODE(ctgs.OWNER, 'SYS', 'Y', ctgs.SECONDARY) SECONDARY
FROM coll_temp_source ctgs WHERE rowid BETWEEN min_rid_tab(i) AND max_rid_tab(i)) src
ON (tgt.object_id = src.object_id)
WHEN MATCHED THEN
UPDATE SET
tgt.OWNER = src.OWNER
,tgt.OBJECT_NAME = src.OBJECT_NAME
,tgt.SUBOBJECT_NAME = src.SUBOBJECT_NAME
,tgt.data_object_id = src.data_object_id
,tgt.OBJECT_TYPE = src.OBJECT_TYPE
,tgt.CREATED = src.CREATED
,tgt.LAST_DDL_TIME = src.LAST_DDL_TIME
,tgt.TIMESTAMP = src.TIMESTAMP
,tgt.STATUS = src.STATUS
,tgt.TEMPORARY = src.TEMPORARY
,tgt.GENERATED = src.GENERATED
,tgt.SECONDARY = src.SECONDARY
WHEN NOT MATCHED THEN
INSERT
( tgt.OWNER
,tgt.OBJECT_NAME
,tgt.SUBOBJECT_NAME
,tgt.object_id
,tgt.data_object_id
,tgt.OBJECT_TYPE
,tgt.CREATED
,tgt.LAST_DDL_TIME
,tgt.TIMESTAMP
,tgt.STATUS
,tgt.TEMPORARY
,tgt.GENERATED
,tgt.SECONDARY )
VALUES
(
src.OWNER
,src.OBJECT_NAME
,src.SUBOBJECT_NAME
,src.object_id
,src.data_object_id
,src.OBJECT_TYPE
,src.CREATED
,src.LAST_DDL_TIME
,src.TIMESTAMP
,src.STATUS
,src.TEMPORARY
,src.GENERATED
,src.SECONDARY
);


g_error_level := 4;
COMMIT;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error level is - ' || g_error_level );
dbms_output.put_line('Error message is - ' || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @merge_table.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:37.30
SQL>



Notes -
1. Merge based on TEMP table is way faster for huge data set !!
2. FORALL is possible for MERGE statement
3. Collections can be used in MERGE - its ok for small sets of data. Use global temporary for large sets of data.

The above example for temporary table picks small chunks of data and processes the same. This is achieved by referencing ROWID.
Posted by at No comments:

TEMPORARY TABLE VS COLLECTION

STEP 1 - TABLE CREATE SCRIPTS:

QUICK LOOK AT THE RESULTS :

Temporary table : Elapsed: 00:00:17.84
Collections : Elapsed: 00:00:41.21

drop table coll_temp_source

CREATE TABLE coll_temp_source AS SELECT ALL_OBJECTS.* FROM ALL_OBJECTS,
(select * from dual connect by level <= 25)

drop table gtt_stage

CREATE GLOBAL TEMPORARY TABLE gtt_stage on commit preserve rows
as select a.rowid gtt_rid, a.* from coll_temp_source a where 1 = 2

create sequence dummy_seq1 start with 1

drop table coll_temp_target5

CREATE TABLE COLL_TEMP_TARGET5 AS SELECT * FROM ALL_OBJECTS WHERE 1 = 2

STEP 2 - RUN COLLECTIONS SCRIPT

SET TIMING ON
SET SERVEROUTPUT ON SIZE 10000

DECLARE
TYPE g_object_list IS TABLE OF coll_temp_source%ROWTYPE INDEX BY PLS_INTEGER;
g_object_tab g_object_list;
g_error_level NUMBER;
v_object_id NUMBER;

CURSOR cur_coll_temp IS
SELECT *
FROM coll_temp_source;

BEGIN
g_error_level := 1;
OPEN cur_coll_temp;

LOOP
g_error_level := 2;
FETCH cur_coll_temp BULK COLLECT INTO g_object_tab LIMIT 300;

g_error_level := 3;
EXIT WHEN cur_coll_temp%NOTFOUND;

FOR i IN g_object_tab.FIRST .. g_object_tab.LAST LOOP
IF g_object_tab(i).owner = 'SYS' THEN
g_object_tab(i).secondary := 'Y';
SELECT dummy_seq1.nextval INTO v_object_id FROM dual;
g_object_tab(i).object_id := v_object_id;
END IF;

IF g_object_tab(i).object_type = 'JAVA CLASS' THEN
g_object_tab(i).temporary := 'Y';
SELECT dummy_seq1.nextval INTO v_object_id FROM dual;
g_object_tab(i).object_id := v_object_id;
END IF;
END LOOP;

FORALL i IN g_object_tab.FIRST .. g_object_tab.LAST
INSERT INTO coll_temp_target5 VALUES g_object_tab(i);

COMMIT;

END LOOP;


g_error_level := 10;
CLOSE cur_coll_temp;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error level is - ' || g_error_level );
dbms_output.put_line('Error message is - ' || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @1_coll.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:41.21
SQL>


STEP 3 - RUN TEMPORARY TABLE BASED SCRIPT

SET TIMING ON
SET SERVEROUTPUT ON SIZE 100000

DECLARE
TYPE rowid_list IS TABLE OF UROWID INDEX BY PLS_INTEGER;
min_rid_tab rowid_list;
max_rid_tab rowid_list;
g_error_level NUMBER;

BEGIN
g_error_level := 0;
INSERT INTO gtt_stage
SELECT /*+ ALL_ROWS */ ctgs.rowid rid
, ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, dummy_seq1.nextval obj_id
, ctgs.data_object_id data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM coll_temp_source ctgs ;

g_error_level := 2;
INSERT
INTO coll_temp_target5
SELECT /*+ ALL_ROWS */
ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, ctgs.object_id obj_id
, ctgs.data_object_id data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, DECODE(object_type , 'JAVA CLASS', 'Y', ctgs.TEMPORARY) temp
, ctgs.GENERATED gen
, DECODE(ctgs.OWNER, 'SYS', 'Y', ctgs.SECONDARY) sec
FROM gtt_stage ctgs ;

g_error_level := 4;
COMMIT;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error level is - ' || g_error_level );
dbms_output.put_line('Error message is - ' || SUBSTR(SQLERRM,1,255));
END;
/


SQL> @l_temp.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.84
SQL>

*************************************************************************************
Posted by at No comments:

NORMAL INSERT VS MULTI-TABLE INSERT

Multi-table insert is introduced in ORACLE 9i. The main advantage is, it is simple to code and easy to maintain; just using a single INSERT script multiple tables and multiple data can be inserted into single / multiple tables.

1. Multi-table inserts is not possible across db links.
2. if you want to access sequence then it should be in the INSERT INTO clause and not in Select clause
3. FORALL can be used with Multi-table inserts
4. COLLECTIONS can be used in multi-table inserts
5. Insert-FIRST and Pivot table insert are also in the same lines.

My opinion - I prefer to use Multi-table inserts if possible in place of single table inserts.

STEP 1 - TABLE CREATE SCRIPTS:

QUICK LOOK AT THE RESULTS -

Multi-table Insert : Elapsed: 00:00:22.28
Normal Insert : Elapsed: 00:00:38.08

drop table coll_temp_source

CREATE TABLE coll_temp_source AS SELECT ALL_OBJECTS.* FROM ALL_OBJECTS,
(select * from dual connect by level <= 25)

drop table gtt_stage

CREATE GLOBAL TEMPORARY TABLE gtt_stage on commit preserve rows
as select a.rowid gtt_rid, a.* from coll_temp_source a where 1 = 2

create sequence dummy_seq1 start with 1

drop table coll_temp_target;
drop table coll_temp_target2;
drop table coll_temp_target3;
drop table coll_temp_target4;

CREATE TABLE COLL_TEMP_TARGET
(OWNER VARCHAR2(30)
,OBJECT_NAME VARCHAR2(30)
,SUBOBJECT_NAME VARCHAR2(30)
,OBJECT_ID VARCHAR2(30));

CREATE TABLE COLL_TEMP_TARGET2
(OBJECT_ID NUMBER
,DATA_OBJECT_ID NUMBER
,OBJECT_TYPE VARCHAR2(18)
,CREATED DATE
,LAST_DDL_TIME DATE);

CREATE TABLE COLL_TEMP_TARGET3
(OBJECT_ID NUMBER
,TIMESTAMP VARCHAR2(19)
,STATUS VARCHAR2(7)
,TEMPORARY VARCHAR2(1));


CREATE TABLE COLL_TEMP_TARGET4
(OBJECT_ID NUMBER
,GENERATED VARCHAR2(1)
,SECONDARY VARCHAR2(1));

STEP 2 - MULTI-TABLE INSERT SCRIPT

SET TIMING ON
SET SERVEROUTPUT ON SIZE 100000

DECLARE
g_error_level NUMBER;

BEGIN
g_error_level := 0;
INSERT INTO gtt_stage
SELECT /*+ ALL_ROWS */ ctgs.rowid rid
, ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, dummy_seq1.nextval obj_id
, ctgs.DATA_OBJECT_ID data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM coll_temp_source ctgs ;

g_error_level := 2;
INSERT ALL
WHEN 1 = 1 THEN
INTO coll_temp_target VALUES
(ownr, obj_nm, sub_obj, obj_id)
WHEN 1 = 1 THEN
INTO coll_temp_target2 VALUES
(obj_id, data_obj, obj_type, crtd, last_ddl)
WHEN 1 = 1 THEN
INTO coll_temp_target3 VALUES
(obj_id, time_stmp, stus, temp)
WHEN 1 = 1 THEN
INTO coll_temp_target4 VALUES
(obj_id, gen, sec)
SELECT
ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, ctgs.OBJECT_ID obj_id
, ctgs.DATA_OBJECT_ID data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM coll_temp_source ctgs ;

g_error_level := 2;
INSERT ALL
WHEN 1 = 1 THEN
INTO coll_temp_target VALUES
(ownr, obj_nm, sub_obj, obj_id)
WHEN 1 = 1 THEN
INTO coll_temp_target2 VALUES
(obj_id, data_obj, obj_type, crtd, last_ddl)
WHEN 1 = 1 THEN
INTO coll_temp_target3 VALUES
(obj_id, time_stmp, stus, temp)
WHEN 1 = 1 THEN
INTO coll_temp_target4 VALUES
(obj_id, gen, sec)
SELECT
ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, ctgs.OBJECT_ID obj_id
, ctgs.DATA_OBJECT_ID data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM gtt_stage ctgs ;

g_error_level := 4;
COMMIT;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error level is - ' || g_error_level );
dbms_output.put_line('Error message is - ' || SUBSTR(SQLERRM,1,255));
END;
/


SQL> @1_temptab_multi.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:22.28

ONT TABLE INSERT SCRIPT

NOTES: -

STEP 3a - Single table Insert

truncate table coll_temp_target;
truncate table coll_temp_target2;
truncate table coll_temp_target3;
truncate table coll_temp_target4;

drop table coll_temp_source

CREATE TABLE coll_temp_source AS SELECT ALL_OBJECTS.* FROM ALL_OBJECTS,
(select * from dual connect by level <= 25)

SCRIPT -

SET TIMING ON
SET SERVEROUTPUT ON SIZE 100000

DECLARE
TYPE rowid_list IS TABLE OF UROWID INDEX BY PLS_INTEGER;
min_rid_tab rowid_list;
max_rid_tab rowid_list;
g_error_level NUMBER;

BEGIN
g_error_level := 0;
INSERT INTO gtt_stage
SELECT /*+ ALL_ROWS */ ctgs.rowid rid
, ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, ctgs.OBJECT_ID obj_id
, dummy_seq1.nextval data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM coll_temp_source ctgs ;

g_error_level := 2;
INSERT
INTO coll_temp_target
SELECT
ctgs.OWNER ownr
, ctgs.OBJECT_NAME obj_nm
, ctgs.SUBOBJECT_NAME sub_obj
, ctgs.OBJECT_ID obj_id
FROM gtt_stage ctgs ;


INSERT
INTO coll_temp_target2
SELECT
ctgs.OBJECT_ID obj_id
, ctgs.DATA_OBJECT_ID data_obj
, ctgs.OBJECT_TYPE obj_type
, ctgs.CREATED crtd
, ctgs.LAST_DDL_TIME last_ddl
FROM gtt_stage ctgs ;


INSERT
INTO coll_temp_target3
SELECT
ctgs.OBJECT_ID obj_id
, ctgs.TIMESTAMP time_stmp
, ctgs.STATUS stus
, ctgs.TEMPORARY temp
FROM gtt_stage ctgs ;

INSERT
INTO coll_temp_target4
SELECT
ctgs.OBJECT_ID obj_id
, ctgs.GENERATED gen
, ctgs.SECONDARY sec
FROM gtt_stage ctgs ;

g_error_level := 4;
COMMIT;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error level is - ' || g_error_level );
dbms_output.put_line('Error message is - ' || SUBSTR(SQLERRM,1,255));
END;
/

SQL> @1_temptab_onetable.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:38.08
Posted by at No comments:

Few more examples - Analytical Functions

FINDING MISSING SEQUENCE / NUMBER from a table

SELECT /*+ ALL_ROWS */
pre_gap+1 start_gap, max_seq_gap
FROM
(SELECT /*+ ALL_ROWS */ seq_column post_gap, LAG(seq_column,1,0) OVER (ORDER BY seq_column) pre_gap ,
(SELECT /*+ ALL_ROWS */ MAX(seq_column) FROM table_name) max_seq_gap
FROM table_name)
WHERE pre_gap != 0
AND post_gap - pre_gap > 1

Delete duplicates

DELETE FROM table_name tnm WHERE tnm.rowid IN
(SELECT rowid FROM (SELECT ROWID, ROW_NUMBER () OVER (PARTITION BY ORDER BY ) duplicate FROM table_name ) qry
WHERE qry.duplicate > 1);
Posted by at 1 comment:

Not Exists vs. Analytical functions

Analytical Functions - Introduced in Oracle 8i. There are different families of functions available and with each release new functions are introduced to the family tree. The below is a quick demonstration of using Analytical functions in place of NOT EXISTS.

Note: Similar logic can be applied to EXISTS also.

This is particularly useful for huge sets of data joined across multiple tables.
Following is a simple demonstration.

Case 1 - NOT EXIST vs USE OF ANALYTICAL FUNCTION

Requirement - Pick only those objects which are referenced in - PACKAGE, PACKAGE BODY, INDEX, TABLE, FUNCTION, PROCEDURE.
and not part of the rest.


ie., say,

TABLE_NAME OBJECT_TYPE
TAB1 PACKAGE
TAB1 PACKAGE BODY
TAB1 [ TRIGGER ]
TAB1 INDEX

Say, in the above example TRIGGER is not part of the expected in-list [ PACKAGE, PACKAGE BODY, INDEX, TABLE, FUNCTION, PROCEDURE ]. So, this should not be taken for consideration.

TABLE_NAME OBJECT_TYPE
TAB2 PACKAGE
TAB2 PACKAGE BODY
TAB2 INDEX

Above is part of the in-list, it should be included to the select.

Step 1 - Create table


Drop table KEY_TABLE

CREATE TABLE KEY_TABLE (table_name varchar2(50), referenced_in varchar2(50), object_id number)

Step 2 - Populate data

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB1', object_type
from all_objects) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB2', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE')
) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB3', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE', 'PACKAGE BODY''PROCEDURE')
) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB4', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE', 'PACKAGE BODY''PROCEDURE', 'TRIGGER')
) a

INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct 'TAB5', object_type
from all_objects where
OBJECT_TYPE IN (
'PACKAGE BODY', 'PACKAGE', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE')
) a


INSERT INTO key_table
select a.*, dummy_seq1.nextval from
(SELECT distinct
'TAB'||rownum * 20
, object_type
from all_objects) a

Step 3 - QUERY

-- USING NOT EXISTS

SELECT /*+ ALL_ROWS */ * from key_table ktab
WHERE ktab.REFERENCEd_IN in ('PACKAGE', 'PACKAGE BODY', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE')
AND NOT EXISTS
(SELECT 1 FROM key_Table elim
WHERE elim.object_id = ktab.object_id
AND elim.REFERENCEd_IN not in ('PACKAGE', 'PACKAGE BODY', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE'))


-- USING ANALYTICAL FUNCTIONS

SELECT /*+ ALL_ROWS */ ktab.*,
COUNT(1) OVER (PARTITION BY referenced_in, table_name order by referenced_in, table_name) COUNT_TOTAL,
(CASE WHEN ktab.referenced_in in ('PACKAGE', 'PACKAGE BODY', 'INDEX', 'TABLE', 'FUNCTION', 'PROCEDURE') THEN
1
else
0
END) COUNT_ELIM
FROM key_table_1 ktab
Posted by at No comments:

SPLIT ONE COLUMN WITH DELIMITER TO MULTIPLE COLUMNS

If a delimiter is available on a column and if that needs to be split to multiple columns, then we usually use INSTR and SUBSTR to split into multiple columns and insert into table. Following is a simple demonstration without using multiple INSTR's and SUBSTR's and insert one column with a specified delimiter into multiple columns.

Step 1 : Create table script

drop table test_tab

create table test_tab
(col1 varchar2(20),
col2 varchar2(20),
col3 varchar2(20),
col4 varchar2(20),
col5 varchar2(20),
col6 varchar2(20),
col7 varchar2(20),
col8 varchar2(20),
col9 varchar2(20),
col10 varchar2(20));

Step 2. PL/SQL script

set serveroutput on size 100000
declare
v_data VARCHAR2(200);
begin
SELECT 'INSERT INTO TEST_TAB VALUES ('
||''''
||replace(qry.dat, '$', ''',''')
|| REPLACE(RPAD(chr(10),LENGTH2, chr(14)),chr(14), ''',''')
||''''
||')'
INTO v_data
from
(SELECT
10 - (length(a.dat) - length(translate( a.dat, chr(0)||'$', chr(0)))) LENGTH2, a.dat
FROM
(select 'abcd$efgh$hijk%lmn$uvp' dat from dual) a) qry;

dbms_output.put_line('Value of v_data is' || v_data);

EXECUTE IMMEDIATE v_Data;
END;

SQL> @col_tocols.sql
21 /
Value of v_data isINSERT INTO TEST_TAB VALUES
('abcd','efgh','hijk%lmn','uvp
','','','','','','')

PL/SQL procedure successfully completed.

OUTPUT -

SQL> select * from test_Tab;

COL1 COL2 COL3
-------------------- -------------------- --------------------
COL4 COL5 COL6
-------------------- -------------------- --------------------
COL7 COL8 COL9
-------------------- -------------------- --------------------
COL10
--------------------
abcd efgh hijk%lmn
uvp


Notes

Line 1 - SELECT 'INSERT INTO TEST_TAB VALUES ('
Line 2 - ||''''
Line 3 - ||replace(qry.dat, '$', ''',''')
Line 4 - || REPLACE(RPAD(chr(10),LENGTH2, chr(14)),chr(14), ''',''')
Line 5 - ||''''
Line 6 - ||')'
Line 7 - INTO v_data
Line 8 - from
Line 9 - (SELECT
Line 10 - 10 - (length(a.dat) - length(translate( a.dat, chr(0)||'$', chr(0)))) Line 11 - LENGTH2, a.dat
Line 12 - FROM
Line 13 - (select 'abcd$efgh$hijk%lmn$uvp' dat from dual) a) qry;

1. Line 3 - replace(qry.dat, '$', ''',''') -> Search character $ would be replaced with ','.

2. Line 4 - REPLACE(RPAD(chr(10),LENGTH2, chr(14)),chr(14), ''',''') -> In the above example there are 3 "$" symbols - these will be replaced with ','. For 3 search strings "$" this will be split to 3 columns only. But, in the table there are 10 columns. So, to fill rest of the columns use RPAD function.

3. Line 10 & 11 - Translate function will replace one to one character. chr(0) is NULL. chr(0)||'$' would be trranslated to chr(0) ignoring the '$' which would give the LENGTH of the string excluding the search character.

Now, formula is -
10 MINUS (Total length of the string MINUS Length of the string excluding search character) -> This will give the length of left out columns in the table.

10 - (22 - 19) => 7.
Posted by at No comments:
Subscribe to: Posts (Atom)

Followers