Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Advanced Oracle SQL #2

Hierarchical queries with CONNECT BY clause
by

Rodion Mironov

on 12 December 2012

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Advanced Oracle SQL #2

Problem CONNECT BY basics CONNECT BY features Links
and practice How to store hierarchical data? Table per level divisions departments groups Pro:
hmm...hard question :) Contra:
fixed number of levels;
only tree-like structures;
non-flexible (can't assign group to division);
queries are different for each level. Field per level departments Pro:
unified queries (the same for all levels);
typical queries can be done in "simple" SQL; Contra:
fixed number of levels;
only tree-like structures; dep_id number
p1_id number --self-referensing FK
p2_id number --self-referensing FK
...
pN_id number --self-referensing FK Edges + aux. data departments Pro:
any number of levels;
unified queries (the same for all levels);
typical queries can be done in "simple" SQL; Contra:
only tree-like structures;
expensive tree rebuild is needed for some operations. dep_id number
parent_id number -- self-referensing FK
hierarchy varchar2 -- path from root ('/1/8/22...') Nested sets model by Joe Celko Just edges departments Pro:
any number of levels;
any hierarchy or graph type;
no tree rebuild needed; Contra:
cannot be processed with simple SQL. dep_id number
parent_id number -- self-referensing FK How to process hierarchical data? Processing options one self-join per level? - bad.
cycles or recursion? - not available in SQL. Special SQL extensions
for hierarchical queries! Data model create table cities(
city varchar2(50 char)
,region_code varchar2(10 char)
); create table trains(
train_code varchar2(10 char) not null
,origin varchar2(50 char) not null
,destination varchar2(50 char) not null
,duration_min number(5) not null); SELECT distinct destination
FROM trains
START WITH origin='Kyiv'
CONNECT BY PRIOR destination = origin; What cities can we reach from Kyiv? START WITH
works like WHERE condition for first level of hierarchy; CONNECT BY
works like JOIN condition from previous to next level; PRIOR
references columns from previous level of hierarchy; base_dataset := <SELECT * FROM ...>;
result := empty;
current_level := select from base_dataset where <START WITH condition>;
while (current_level not empty) {
result := result UNION ALL current_level;
current_level := current_level inner join base_dataset on <CONNECT BY condition>;
}; Pseudo code SELECT distinct origin
FROM trains
START WITH destination='Ivano-Frankivsk'
CONNECT BY destination = PRIOR origin; What cities can we reach
Ivano-Frankivsk from? Order of execution
joins;
start with/connect by;
where;
group by and having;
order by. SELECT distinct origin
FROM trains t
inner join cities c on t.origin = c.city and c.region_code <> 'West'
START WITH destination='Ivano-Frankivsk'
CONNECT BY destination = PRIOR origin; -- Joins are executed before Connect By
-- Join filters out Lviv and we see no valid origins.
-- We just can't build other routes without Lviv -- Joins and Connect By.
-- Question: what non-west cities can we reach Ivano-Frankivsk from? SELECT distinct origin
FROM trains t
inner join cities c on t.origin = c.city
WHERE c.region_code <> 'West'
START WITH destination='Ivano-Frankivsk'
CONNECT BY destination = PRIOR origin; -- Where is executed after Connect By
-- So result is valid this time. -- Where and Connect By.
-- Question: what non-west cities can we reach Ivano-Frankivsk from? SELECT destination, count(*) as "Routes number"
FROM trains
START WITH origin='Kyiv'
CONNECT BY PRIOR destination = origin
GROUP BY destination; -- Group By is executed after Connect By
-- We get expected result -- Connect By and Group By.
-- Question: how many routes exist from Kyiv to any other city? PRIOR keyword can be used
more than ones SELECT distinct destination
FROM trains
START WITH origin='Kyiv'
CONNECT BY PRIOR destination = origin
and PRIOR destination <> 'Lviv'; -- Use PRIOR several times.
-- Question: What cities can we reach from Kyiv
-- if Lviv is too beautiful and nobody leaves it. /*
Good idea to remember:
START WITH condition filters root nodes;
CONNECT BY condition filters branches;
WHERE condition filter leafs.
*/ LEVEL pseudo column -- LEVEL preudo column SELECT destination, level
FROM trains
START WITH origin='Kyiv' -- level = 1 for records in START WITH
CONNECT BY PRIOR destination = origin; -- increment level for each step SYS_CONNECT_BY_PATH SELECT
sys_connect_by_path(origin,'/') || '/' || destination as route
,level as num_trains
FROM trains
WHERE destination = 'Simferopol'
START WITH origin='Kyiv'
CONNECT BY PRIOR destination = origin; -- SYS_CONNECT_BY_PATH
-- Question: show all routes from Kyiv to Simferopol function that forms text string concatenating values at each hierarchy level;
useful to collect info about route; Ordering Connect By output default order:
tree structure preserved;
no custom ordering available;
Order By clause:
custom order available;
broken tree structure;
Order Siblings By clause:
tree structure preserved;
custom order at each level. SELECT
LPAD(' ',(level-1)*3,'-') || destination
,train_code
FROM trains
START WITH origin='Kyiv'
CONNECT BY PRIOR destination = origin; -- default CONNECT BY ordering SELECT
LPAD(' ',(level-1)*3,'-') || destination
,train_code
FROM trains
START WITH origin='Kyiv'
CONNECT BY PRIOR destination = origin
ORDER BY train_code; -- Simple ORDER BY SELECT
LPAD(' ',(level-1)*3,'-') || destination
,train_code
FROM trains
START WITH origin='Kyiv'
CONNECT BY PRIOR destination = origin
ORDER SIBLINGS BY train_code; -- ORDER SIBLINGS BY Cycles in Connect By 1 2 3 4 5 6 Cycle_Test table select id
,sys_connect_by_path(id,'/') as route
from cycle_test
start with id = 1
connect by prior id = parent_id; -- Works fine until 6->1 edge added -- Create cycle update cycle_test
set parent_id = 6
where id = 1; -- Fails with "connect by loop..." error -- NOCYCLE keyword means
-- "don't follow cycles, don't return error" select id
,sys_connect_by_path(id,'/') as route
,Connect_By_IsCycle
from cycle_test
start with id = 1
connect by NOCYCLE prior id = parent_id; -- Connect_By_IsCycle returns 1 if the current row has a child which is also its ancestor. Returns 0 otherwise. -- Oracle detects cycle only if PRIOR keyword
-- is used in CONNECT BY condition -- It is often used for rows generation from DUAL select level
from dual
connect by level <= 10; Select all subordinates of Steven King;
Select all managers of employee with phone_number = '650.505.2876'. Don't show that employee himself.
For each 'Programmer' show list of his managers from top to bottom (like '/King/..../TheProgrammer').
List all second-level subordinates of Steven King (direct subordinates are first-level subordinates).
For each employee show his salary and total salary of all his managers. Preserve tree structure in output.
Generate list of dates from sysdate to last day of the year. HR schema Hierarchical Queries Hierarchical Query Pseudocolumns Trees in SQL by Joe Celko http://docs.oracle.com/cd/E11882_01/server.112/e26088/queries003.htm#i2053935 http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns001.htm#SQLRF50939 http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html
Full transcript