SYS_CONNECT_BY_PATH Example ( Oracle )

Sys_connect_by_path is a Oracle feature (Introduced in Version 9i) which is used in hierarchical queries to return the position of a child element in a hierarchical structure
vis-a-vis its parent.

For example if one wants to know the position of
an employee with respect to the owner of the company
in a tree-like structure, the sys_connect_by_path
feature makes the task really simple.


Usage :-

SELECT   SYS_CONNECT_BY_PATH( < column > , 
                              < seperator > )
FROM    ...
CONNECT BY ...

Here, < column > denotes the table column name
whose path position we need to see, and < seperator >
denotes the character which SYS_CONNECT_BY_PATH
uses to seperate the each position value from the
other in the hierarchical tree structure.




Let us see an Example :-

Here, we have a Table "parentchild", which shows information about each Child and its parent as below.

select * from parentchild order by child

 
--------------------
CHILD     PARENT
--------- ---------
3
30          3
31          3
32          3
33          3
300         30
301         30
302         30
312         31
313         31
--------------------

Now using the Oracle feature sys_connect_by_path,
let us see how each child element figures in the hierarchical structure :-

select parent, child, sys_connect_by_path(child,'/') path ,level
from parentchild
connect by parent = prior child
order by child

 
----------------------------------------
PARENT    CHILD    PATH            LEVEL
--------- -------- -------------   -----
             3      /3               1
3            30     /3/30            2
3            30     /30              1
3            31     /31              1
3            31     /3/31            2
3            32     /3/32            2
3            32     /32              1
3            33     /3/33            2
3            33     /33              1
30           300    /300             1
30           300    /30/300          2
30           300    /3/30/300        3
30           301    /3/30/301        3
30           301    /30/301          2
30           301    /301             1
30           302    /30/302          2
30           302    /302             1
30           302    /3/30/302        3
31           312    /3/31/312        3
31           312    /312             1
31           312    /31/312          2
31           313    /31/313          2
31           313    /3/31/313        3
31           313    /313             1
----------------------------------------

In this example sys_connect_by_path uses the character '/'
to seperate the elements in the hierarchy and displays the 'Child'
column value in the hierarchy.

The sys_connect_by_path could be used only in a Oracle hierarchical query in conjunction with the connect by clause (which defines the child parent path).

Scalar Sub query

Scalar subquery :-
This oracle feature enables us to add a query in the column list.

Let us see an example of scalar sub-query and check its performance :-




create table tst (x , y)
as select trunc(dbms_random.value(100,200)) x,
trunc(dbms_random.value(100,200))
from user_objects where rownum<=100000;

select * from tst;
X Y Z
------- ---------- ----------
1 2 3
3 4 5
4 2 6
7 1 5
9 2 1


-- Function to find average of x and y columns
CREATE OR REPLACE function tst_func(a in number, b in number)
return number is
j number;
begin
select avg((a+b)/2) into j from dual;
return j;
end;
/


-- Run the function on table tst with 1,00,000 records
SQL> declare
a number;
b number;
c number;
begin
for mrec in (select x,y,tst_func(x,y) t from tst)
loop
a:=mrec.x;
end loop;
end;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:21.05


-- Same example with scalar sub-query on table tst with 1,00,000 records
SQL> declare
a number;
b number;
c number;
begin
for mrec in (select x,y,(select tst_func(x,y) from dual) t from tst)
loop
a:=mrec.x;
end loop;
end ;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:03.09


As the example shows, the performance is quiet good ...

How to find blocking sessions in Oracle

how many times have you encountered a
slam damn jam you man statement playing havoc in
your database system and not knowing who it is.

This query could tell us which sessions are
being blocked, and point out the 'hero' who is doing it too :-

select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2

ORA-00060 (self lock)

ORA-00060: deadlock detected while waiting for resource
When an autonomous transaction is initiated and an object which had uncommitted
changes from the current session is changed, we will encounter the error
mentioned above as illustrated from the example below :-


SQL> create global temporary table gtt ( x int primary key ) on commit preserve rows;
Table created.

SQL> insert into gtt values ( 1 );
1 row created.

SQL> commit;
Commit complete.

SQL> update gtt set x = 1;
1 row updated.
SQL> declare
2 pragma autonomous_transaction;
3 begin
4 delete from gtt where x = 1;
5 end;
6 /declare*ERROR at line 1:ORA-00060: deadlock detected while waiting for resourceORA-06512: at line 4


Solution ?
Before initiating an autonomous transation,
care should be taken to commit changes
on any obect which may be changed by the autonomous transaction.

Oracle - Delete Duplicate rows

-- 1
delete c where rowid not in (select min(rowid) from c group by object_name);

-- 2
delete c where rowid in (select rid from (select rowid rid, row_number() over (partition by object_name order by rowid) rn from c ) where rn <> 1);

Oracle-Columns to Rows

**********************************************************
TABLE T has a column data which is to be converted to rows **

select * from t ;

TXT £first£second£third£fourth£

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



*********Using SQL *********
begin
/*01rowstocol in SQL*/
for mrec in
(select substr(txt,substart+1,subend-substart-1) t
from
( select txt,inst substart,
lead(inst) over (partition by txt order by txt) subend
from (
select txt,instr(txt,'£',1,rw) inst ,rw
from
( select rownum rw,t.txt
from t,user_objects u )
)
where inst > 0
)
order by 1
)
loop
null;
end loop;
end;
/

Oracle PL/SQL Progress check

Often, when we have a PL/SQL process running,
we need to know the progress of the process
(say it has completed 10% or so), so that we could
give an educated answer to the boss's bickering
'what time do you think the process will get over ?'
enuff of my lecture , here is the script :)


##########################
DECLARE
l_nohint number default dbms_application_info.set_session_longops_nohint;
l_rindex number default l_nohint;
l_slno number;
BEGIN
------------------------------------------
-- Actual process to be run starts here --
------------------------------------------
for i in 1 .. 20
loop
-- -- your actual code could be included here --

-- a delibrate delay introduced so that we could check the progress
for v_timepass_var in 1..10000000 loop null; end loop;

-- Actual progress check code
dbms_application_info.set_session_longops
( rindex => l_rindex,
slno => l_slno,
op_name => 'my long running operation',
target => 1234,
target_desc => '"msg description"',
context => 0,
sofar => i,
totalwork => 20,
units => 'loops' );
end loop;
------------------------------------------
-- Actual process to be run ends here --

------------------------------------------
end;
/

CONTENTS OF v$session_longops during the process run :-


OPNAME SOFAR TOTAL MSG
myoperation 676 3 20 myoperation: Mywork 676: 3 out of 20 loops
myoperation 676 13 20 myoperation: Mywork 676: 13 out of 20 loops


# THIS ENABLED PROGRESS STATUS TO BE KNOWN DURING THE LONG-RUNNING PROCESS RUN (SOFAR/TOTAL).


Hope this helps.. :)

##########################