Learning blogger

This seems like a great tool, but I would like to have more control of the code snippets etc. Fx. are indentation removed, something I find annoying.

Forgot the all important str2branch function

This function was inspired by the str2table function, which can be found on asktom.oracle.com.

CREATE OR REPLACE TYPE node_parent_type AS OBJECT ( lvl NUMBER, node VARCHAR2(200), PARENT VARCHAR2(200) )
/

CREATE OR REPLACE TYPE str2Branch_Type AS TABLE OF node_parent_type
/

CREATE OR REPLACE FUNCTION Str2Branch ( p_string IN VARCHAR2 , p_delim IN VARCHAR2 )
RETURN str2Branch_Type
PIPELINED
AS
l_str LONG;
l_piece LONG;
l_parent LONG := NULL;
l_n NUMBER;
l_lvl NUMBER := 0;
BEGIN
l_str := p_string;
LOOP l_n := INSTR(l_str, p_delim);
IF (l_n = 1) --Starts with a delimiter
THEN l_n := INSTR(SUBSTR(l_str,2), p_delim);
END IF;
EXIT WHEN (NVL(l_n,0) = 0);
l_piece := SUBSTR( l_str, 1, l_n ); --First piece
l_str := SUBSTR( l_str, l_n+1 ); --Rest
l_n := INSTR( l_piece, p_delim, -1 );
EXIT
WHEN (NVL(l_n,0) = 0); --Only needed, if we have to end with a delimiter.
pipe ROW( node_parent_type ( l_lvl, LTRIM(RTRIM(SUBSTR(l_piece,l_n+1))), l_parent ) );
l_parent := LTRIM(RTRIM(SUBSTR(l_piece,l_n+1)));
l_lvl := l_lvl + 1;
END LOOP;
RETURN;
END;
/

Creating a hierical tree structure from strings

Recently, I have been helping a company in redesigning their schema, and one issue was that they stored hieracal trees in varchar2(4000) column, one full branch per row.

Data was if this kind:

/Oracle/8/1/5/Microsoft
/Oracle/8/1/5/VMS
/Oracle/8/1/5/0/1/VMS

etc.

Their main problem was that searching in this was really ugly, as they had to full table/index scan a lot, when searching using like '%%'

I came up with this solution to convert this into a small table that can be queried using connect by instead:


CREATE GLOBAL TEMPORARY TABLE XXX_TREE_TEMP
( ID NUMBER NOT NULL
, node_name VARCHAR2(256)
, parent_branch VARCHAR2(4000)
)
ON COMMIT DELETE ROWS;
INSERT INTO XXX_TREE_TEMP
SELECT ROWNUM
, b.node
, b.parent_branch
FROM (
WITH p AS (SELECT the_full_node
FROM big_table
WHERE the_full_node IS NOT NULL
GROUP BY the_full_node)
SELECT DISTINCT
t.node
, SUBSTR(p.the_full_node, 1, INSTR(p.the_full_node,'/',1,t.lvl+1)-1) AS parent_branch
, t.lvl
FROM P, TABLE(Str2branch(p.the_full_node,'/')) t
ORDER BY t.lvl, t.node
) b
;

INSERT INTO XXX_TREE
SELECT t1.ID AS node_id
, MIN(t2.ID) AS parent_id
, t1.node_name
FROM XXX_TREE_TEMP t1, XXX_TREE_TEMP t2
WHERE t1.parent_branch = DECODE(t2.parent_branch,NULL,'',t2.parent_branch)'/'t2.node_name
GROUP BY t1.ID, t1.node_name
UNION ALL
SELECT t1.ID AS node_id
, NULL AS parent_id
, t1.node_name
FROM XXX_TREE_TEMP t1
WHERE t1.parent_branch IS NULL
GROUP BY t1.ID, t1.node_name
ORDER BY 1, 2
;

This can then be queries like this to find the original full braches:

SELECT ID
, SYS_CONNECT_BY_PATH(a.node_name, '/')'/' navn
FROM XXX_TREE a
CONNECT BY PRIOR ID = parent_id
START WITH parent_id IS NULL;


Limiting to branches with a special string is trivial now, as the string can be indexed ( xxx_tree.node_name ).

When it goes into production, I will update about the success.

I would have liked to not use the temporary table, but my first shot at this failed (nested with statements), and this is not som important, as this is a one shot load.

First blog

My first blog.

The reason for this is that I came up with a solution to a problem, which I would like to share. As I found inspiration on Tom Kyte's AskTom, I originally wanted to post it there, but could not find a good thread/question where it matched the question. And since I could not create a new one, I did this, thinking it could happen again.

I hope that You find my postings useful, and that You will not hesitate to comment/correct/improve the code that I post.

Br, Kim