connect by and recursive with (part 2)

According to the doc
The subquery_factoring_clause now supports recursive subquery
factoring (recursive WITH), which lets you query hierarchical data.
This feature is more powerful than CONNECT BY in that it
provides depth-first search and breadth-first search, and supports
multiple recursive branches. A new search_clause and cycle_clause
let you specify an ordering for the rows and mark cycles in the
recursion

As written there and there, recursive with has more capabilities. It is also ANSI and implemented in DB2, MS SQL Server. CONNECT BY is an Oracle oddity. But does recursive with perfoms as well as connect by?

  1. Create a big emp
  2. 
    create table big_emp as
    with t(empno,mgr) as
    (select 1, null from dual
    union all 
    select empno+1,trunc(dbms_random.value(1+empno/10,empno))
    from t 
    where empno<100000)
    select * from t
    

  3. execution plan
  4. CONNECT BY

    
    select empno,mgr
    from big_emp
    connect by mgr = prior empno
    start with mgr is null;
    
    E M 
    - -
    1 -  
    2 1 
    4 2 
    6 2 
    8 6 
    
    Operation                 Object    Rows Time Cost   Bytes
    ------------------------- ------- ------ ---- ---- -------
    SELECT STATEMENT                       3    3  185      78
    CONNECT BY WITH FILTERING
    TABLE ACCESS FULL         BIG_EMP      1    1   61      10
    HASH JOIN                              2    2  122      46
    CONNECT BY PUMP
    TABLE ACCESS FULL         BIG_EMP 100000    1   61 1000000
    

    recursive WITH

    
    with e(empno,mgr) as (
    select empno, mgr 
    from big_emp 
    where mgr is null
    union all
    select f.empno,f.mgr 
    from big_emp f, e 
    where e.empno=f.mgr)
    select empno,mgr
    from e;
    
    E M
    - -
    1 -  
    2 1 
    3 1 
    4 2 
    5 3 
    ...
    
    Operation                 Object    Rows Time Cost   Bytes
    ------------------------- ------- ------ ---- ---- -------
    SELECT STATEMENT                       3    3  183      78
    VIEW                                   3    3  183      78
    UNION ALL (RECURSIVE WITH) BREADTH FIRST
    TABLE ACCESS FULL BIG_EMP              1    1   61      10
    HASH JOIN                              2    2  122      46
    RECURSIVE WITH PUMP
    TABLE ACCESS FULL BIG_EMP         100000    1   61 1000000
    

    In this particular simple case, it seems CONNECT BY have a 1% higher cost.

  5. execution time
  6. CONNECT BY

    
    select sum(mgr)
    from 
    (
    select empno,mgr
    from big_emp
    connect by mgr = prior empno
    start with mgr is null
    )
    
    SUM(MGR) 
    ------------
    2745293877 
    
    1 rows returned in 0.73 seconds 
    

    recursive with

    
    with e(empno,mgr) as (
    select empno, mgr 
    from big_emp 
    where mgr is null
    union all
    select f.empno,f.mgr 
    from big_emp f, e 
    where e.empno=f.mgr)
    select sum(mgr)
    from e;
    
    SUM(MGR) 
    ------------
    2745293877 
    
    1 rows returned in 1.24 seconds 
    

Honestly I am not surprised that CONNECT BY is faster, CONNECT BY has been in the Oracle database forever and has been massively tuned in 8.1.7.4.

And then one day you find, ten years have got behind you.

CONNECT BY and Recursive CTE

11gR2 introduced a new mechanism to build up hierarchies.

I remembered a thread in developpez.net that reveals the dubious implementation of nocycle in 10g.

For the CONNECT BY ISLEAF, I have read the technique on amis.nl.

Ok, here is my graph

The 10g query


with o as
(
SELECT 'A' obj, 'B' link from dual union all
SELECT 'A', 'C' from dual union all
SELECT      'C', 'D' from dual union all
SELECT           'D', 'C' from dual union all
SELECT           'D', 'E' from dual union all
SELECT                'E', 'E' from dual)
select connect_by_root obj root,level,obj,link,
  sys_connect_by_path(obj||
'->'
||link,','),
  connect_by_iscycle,
  connect_by_isleaf
from o 
connect by nocycle obj=prior link
start with obj='A';

ROOT LEVEL O L PATH                 CYCLE  LEAF
---- ----- - - -------------------- ----- -----
A        1 A B ,A->B                    0     1
A        1 A C ,A->C                    0     0
A        2 C D ,A->C,C->D               1     0
A        3 D E ,A->C,C->D,D->E          1     1

Obviously in 10g the connect by nocycle does not work that well with that kind of graphs, D-C and E-E are missing and C-D and D-E are marked as cycling…

Let’s try the 11gR2 equivalency.


with o(obj,link) as
(
SELECT 'A', 'B' from dual union all
SELECT 'A', 'C' from dual union all
SELECT      'C', 'D' from dual union all
SELECT           'D', 'C' from dual union all
SELECT           'D', 'E' from dual union all
SELECT                'E', 'E' from dual),
t(root,lev,obj,link,path) as (
select obj,1,obj,link,cast(obj||'->'||link 
as varchar2(4000))
from o 
where obj='A'  -- START WITH
union all
select 
  t.root,t.lev+1,o.obj,o.link,
  t.path||', '||o.obj||
    '->'
    ||o.link
from t, o 
where t.link=o.obj
)
search depth first by obj set ord
cycle obj set cycle to 1 default 0
select root,lev,obj,link,path,cycle,
    case
    when (lev - lead(lev) over (order by ord)) < 0
    then 0
    else 1
    end is_leaf
 from t;

ROOT LEV  OBJ  LINK PATH                        CYCLE IS_LEAF
---- ---- ---- ---- --------------------------- ----- -------
A    1    A    B    A->B                            0       1
A    1    A    C    A->C                            0       0
A    2    C    D    A->C, C->D                      0       0
A    3    D    C    A->C, C->D, D->C                0       0
A    4    C    D    A->C, C->D, D->C, C->D          1       1
A    3    D    F    A->C, C->D, D->E                0       0
A    4    F    F    A->C, C->D, D->E, E->E          0       0
A    5    F    F    A->C, C->D, D->E, E->E, E->E    1       1

It looks good :)

If you exclude the rows with cycle=1, you get the six rows for the graph.

read without Enter

A small unix tip today.

Do you want to continue ?

If you are expecting “y” or “n” but do not want to enforce the user to type y[Enter] but simply y, you can use the -n option in bash.

Within a ksh script:


yorn=$(bash -c 'read -p "Do you want to continue ? " -n 1 ans;echo "$ans"')

Within bash:


read -n 1 yorn

number series

Patrick Wolf wrote about the newest Apex release, which contains a 11.2 db engine, so I had to play with recursive queries ;)


with t(x) as (select 1 from dual 
union all
select x+1 from t where x<5 )
select x from t;

X
1
2
3
4
5

with t(x,y) as (select 1 x, 1 y from dual 
union all
select x+1,y*(x+1) from t where x<5 )
select x,y "X!" from t;

X  X!
1  1
2  2
3  6
4  24
5  120

with t(r,x,y) as (select 1,1,1 from dual 
union all
select r+1,y,x+y from t where r<5)
select x fib from t

FIB 
1 
1 
2 
3 
5 

with t1(x) as (select 1 from dual 
union all
select x+1 from t1 where x<4),
t2(x,y,z) as (select x, 1,x from t1
union all 
select x,y+1,x*(y+1) from t2 where y<2)
select listagg(z,';') within group (order by x) s from t2 group by y;

S 
1;2;3;4 
2;4;6;8 

where is the TRIGGER ANY TABLE privilege?

You have your table data in one schema and your procedures in another one. But can you have triggers and tables in different schemas?


SYS@lsc01> create user u1 identified by u1;

User created.

SYS@lsc01> create user u2 identified by u2;

User created.

SYS@lsc01> grant create table, unlimited tablespace to u1;

Grant succeeded.

SYS@lsc01> grant create session, create trigger to u2;

Grant succeeded.

SYS@lsc01> create table u1.t(x number);

Table created.

SYS@lsc01> grant select on u1.t to u2;

Grant succeeded.

SYS@lsc01> connect u2/u2
Connected.
U2@lsc01> create trigger u2.tr after insert on u1.t for each row
  2  begin
  3  null;
  4  end;
  5  /
create trigger u2.tr after insert on u1.t for each row
                                        *
ERROR at line 1:
ORA-01031: insufficient privileges

What’s the missing privilege? To create a trigger on another schema, you need the CREATE ANY TRIGGER privilege.

 
U2@lsc01> connect / as sysdba
Connected.
SYS@lsc01> grant CREATE ANY TRIGGER to u2;

Grant succeeded.

SYS@lsc01> 
SYS@lsc01> connect u2/u2
Connected.
U2@lsc01> create trigger u2.tr after insert on u1.t for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.