Removing duplicates

From asktom one can find an example removing duplicates. Here is another.

create table duplicates (n int);

insert into duplicates select level from dual connect by level < 100;

insert into duplicates select level from dual connect by level < 50;

delete from duplicates where rowid in (
select rid from (
select rowid rid, first_value(rowid)over(partition by n) frid, dup.*
from duplicates dup
) where frid != rid
)
;

Deprecation

During last two weeks I have noticed twice the use of oracle.jdbc.driver.OracleDriver with 11g version. It has been almost five years ago when the Oracle 9i jdbc driver package "oracle.jdbc.driver." deprecated.

With 11g please use oracle.jdbc.OracleDriver instead of the deprecated oracle.jdbc.driver.OracleDriver.

This might be found in your application server connection pool settings, project jdbc.properties or hard coded something like java.lang.Class.forName("oracle.jdbc.driver.OracleDriver");
as it should be
java.lang.Class.forName("oracle.jdbc.OracleDriver");

NOCOUG Second SQL Challenge

Working with the second NoCOUg SQL Challenge. In the magazine there can be found also "advice for an Oracle Beginner" articles - worth reading.




Here is my five cents to towards the problem. Another answers may be found from Iggy Fernandez blog comments.

When I found the challenge, there were already some published answers to the riddle. So I started with minimizing the starting set. Got rid of nulls in the first place. And after a while ended up with a hierarchical query. On a way I draw a Graphviz picture of the riddle data. Maybe that visualizes some paths I was trying to follow trying to figure out alternative solutions. SQL commands for creating the required data.

with aa as (
select word1, word2, word3, word2 gr
  from riddle
 where word1 is not null
), bb as (
select gr,pre,word
  from aa
unpivot (word for pre in (word1 as 1, word2 as 2, word3 as 3))
), cc as (
select gr,pre,word
     , first_value(case when pre = 3 and word != gr then gr end ignore nulls)over(partition by word) bg
     , first_value(case when pre = 1 and word != gr then gr end ignore nulls)over(partition by word) ag
     , min(pre)over(partition by word) mi
     , max(pre)over(partition by word) ma
  from bb
), dd (gr,mi,ma,pre,word,ord)as (
select gr,mi,ma,pre,word,cast(2 as varchar2(10))
  from cc
 where cc.pre=2 and cc.bg is null and cc.ag is null
 union all
select cc.gr,cc.mi,cc.ma,cc.pre,cc.word
     , dd.ord||case when cc.pre = 1 then cc.mi else cc.ma end
from dd inner join cc on cc.pre in (1,3) and dd.word = cc.gr
)
select listagg(dd.word,' ')within group(order by rpad(dd.ord,10,'2'))
  from dd
;

Update 8.3.2011
Ordering with rpad seems like so borrowed from the riddle_tree. So here is another solution that maintains the ordering number while browsing the tree.

with aa as (
select word1, word2, word3, word2 gr
  from riddle
 where word1 is not null
), bb as (
select gr,pre,word
  from aa
unpivot (word for pre in (word1 as 1, word2 as 2, word3 as 3))
), cc as (
select gr,pre,word
     , first_value(case when pre = 3 and word != gr then gr end ignore nulls)over(partition by word) bg
     , first_value(case when pre = 1 and word != gr then gr end ignore nulls)over(partition by word) ag
     , min(pre)over(partition by word) mi
     , max(pre)over(partition by word) ma
  from bb
), dd (gr,mi,ma,pre,word,nord,lv)as (
select gr,mi,ma,pre,word,2222222,1
  from cc
 where cc.pre=2 and cc.bg is null and cc.ag is null
 union all
select cc.gr,cc.mi,cc.ma,cc.pre,cc.word
     , dd.nord+(cc.pre-2)*power(10,6-dd.lv)
     , dd.lv+1
from dd inner join cc on cc.pre in (1,3) and dd.word = cc.gr
)
select listagg(dd.word,' ')within group(order by dd.nord)
  from dd 
;

revoke through a db link

Why would someone do such a thing? Following will end up an active a user session stuck waiting "SQL*Net message from dblink".

sqlplus /nolog

connect / as sysdba


drop user a cascade;

drop user b cascade;


create user a identified by a;

grant create session to a;

grant create procedure to a;

grant create database link to a;


create user b identified by b;

grant create session to b;


connect a/a

declare 
 comm varchar2(200);
begin 
 select 'create database link b connect to b identified by b using '''||
        sys_context('userenv','db_unique_name')||'''' into comm 
   from dual;
 execute immediate comm;
end;
/


create or replace procedure p as
begin
execute immediate 'revoke execute on p from b';
end;
/


grant execute on p to b;

exec a.p@b

not exists null

I wrote earlier about not in and null. Be careful also when using not exists predicate together with a sub query resulting nulls.

SQL> select 1 from dual where not exists (select 1 from dual where 1=0);

         1
----------
         1

SQL> select 1 from dual where 1=0;

no rows selected



SQL> select 1 from dual where not exists (select max(1) from dual where 1=1);

no rows selected

SQL> select max(1) from dual where 1=1;

    MAX(1)
----------
         1



SQL> select 1 from dual where not exists (select max(1) from dual where 1=0);

no rows selected

SQL> select max(1) from dual where 1=0;

    MAX(1)
----------


SQL> select 1 from dual where not exists (select null from dual);

no rows selected

SQL> select null from dual;

N
-

In the sub query there is a row but it is null, unknown. So not exists null evaluates to false. More about the issue in responses to a oracle-l mailing list post.

ORDER SIBLINGS BY CONNECT_BY_ROOT

In this post I am dealing with a sorting problem of a recursive query and giving a guideline how to implement such ordering.

We are patching an Oracle database to 11.2.0.2 and with one of our test case hit an error

ORA-30007: CONNECT BY ROOT operator is not supported in the START WITH or in the CONNECT BY condition

The problem query does not have connect_by_root in START WITH or CONNECT BY. But it is in order by "order siblings by connect_by_root". So the reported error is somewhat misleading.

What does this order siblings by connect_by_root is trying to accomplish. The hierarchical result is ordered first by some column from a root node of the hierarchy and after that with some columns at the same level of the hierarchy.

In a thread in www.sql.ru there may be found discussion about the same problem. With 10.2.0 ORA-00600: internal error code, arguments: [qkacon:FJswrwo] is reported. It is mentioned that giving a hint /*+ NO_CONNECT_BY_COST_BASED */ bypasses the ORA-00600 problem, but a new one is described. connect_by_root is returning nulls if the same query has siblings word in order by. So our query has problem and the newly introduced error in 11.2.0.2 is actually revealing that to us.

11.2 introduced an alternative way to write hierarchical queries. Here I introduce the problematic queries with a data set having two roots. And in the end a way to implement the requirement using recursive common table expression.

drop table emp purge;

CREATE TABLE EMP
(
EMPNO     NUMBER(4),
ENAME     VARCHAR2(10 BYTE),
MGR       NUMBER(4)
)
;

insert into emp(empno,mgr,ename) values (11,23,'SMITH');
insert into emp(empno,mgr,ename) values (12,16,'ALLEN');
insert into emp(empno,mgr,ename) values (13,16,'WARD');
insert into emp(empno,mgr,ename) values (14,19,'JONES');
insert into emp(empno,mgr,ename) values (15,16,'MARTIN');
insert into emp(empno,mgr,ename) values (16,19,'BLAKE');
insert into emp(empno,mgr,ename) values (17,19,'CLARK');
insert into emp(empno,mgr,ename) values (18,null,'SCOTT');
insert into emp(empno,mgr,ename) values (19,null,'KING');
insert into emp(empno,mgr,ename) values (20,16,'TURNER');
insert into emp(empno,mgr,ename) values (21,18,'ADAMS');
insert into emp(empno,mgr,ename) values (22,16,'JAMES');
insert into emp(empno,mgr,ename) values (23,14,'FORD');
insert into emp(empno,mgr,ename) values (24,17,'MILLER');

update emp set mgr = null where ename = 'SCOTT';

commit;


select em.*, rpad('-',level,'-')||empno , level
from emp em
start with em.mgr is null
connect by prior em.empno = em.mgr
;
18 SCOTT  -18 1
21 ADAMS 18 --21 2
19 KING  -19 1
14 JONES 19 --14 2
23 FORD 14 ---23 3
11 SMITH 23 ----11 4
16 BLAKE 19 --16 2
12 ALLEN 16 ---12 3
13 WARD 16 ---13 3
15 MARTIN 16 ---15 3
20 TURNER 16 ---20 3
22 JAMES 16 ---22 3
17 CLARK 19 --17 2
24 MILLER 17 ---24 3

Trying to add the described ordering:

select /*+ NO_CONNECT_BY_COST_BASED */em.*, level, connect_by_root ename cbr, rpad('-',level,'-')||empno
from emp em
start with em.mgr is null
connect by prior em.empno = em.mgr
order siblings by connect_by_root ename, empno
;

18 SCOTT  1 SCOTT -18
21 ADAMS 18 2 SCOTT --21
19 KING  1 KING -19
14 JONES 19 2 KING --14
23 FORD 14 3 KING ---23
11 SMITH 23 4 KING ----11
16 BLAKE 19 2 KING --16
12 ALLEN 16 3 KING ---12
13 WARD 16 3 KING ---13
15 MARTIN 16 3 KING ---15
20 TURNER 16 3 KING ---20
22 JAMES 16 3 KING ---22
17 CLARK 19 2 KING --17
24 MILLER 17 3 KING ---24

Rows from KING root should be ordered before SCOTT. So using the 11.2.0.2 database the problem is noticed and the ORA-30007: CONNECT BY ROOT operator is not supported in the START WITH or in the CONNECT BY condition is thrown.

How to bypass the problem with a recursive common table query:



with cte (empno,mgr,ename,cbr,l) as (
select empno,mgr,ename,ename cbr,1 from emp where mgr is null
union all
select em.empno,em.mgr,em.ename,ct.cbr,ct.l+1 from emp em inner join cte ct on em.mgr = ct.empno
)
SEARCH DEPTH FIRST BY cbr,empno SET rn
select te.*, rpad('-',l,'-')||empno
from cte te
;

19 KING KING 1 1 -19
14 19 JONES KING 2 2 --14
23 14 FORD KING 3 3 ---23
11 23 SMITH KING 4 4 ----11
16 19 BLAKE KING 2 5 --16
12 16 ALLEN KING 3 6 ---12
13 16 WARD KING 3 7 ---13
15 16 MARTIN KING 3 8 ---15
20 16 TURNER KING 3 9 ---20
22 16 JAMES KING 3 10 ---22
17 19 CLARK KING 2 11 --17
24 17 MILLER KING 3 12 ---24
18 SCOTT SCOTT 1 13 -18
21 18 ADAMS SCOTT 2 14 --21


Problem solved, nice feeling. Also a good taste in my mouth. Thanks to Ilkka and H. and The Yamazaki Single Malt Whisky aged 12 years Japanese whisky. Now to have some cake and buy tickets to Hakametsä Tappara ice hockey game.

Partitioned Outer Join

Today was the day I had to fill some sparse data. I actually used partition by right outer join. The documentation example describes the problem well. Nothing much else to say about that.