Recursive WITH, part III: IS_LEAF

The CONNECT BY syntax provides a useful pseudocolumn, CONNECT_BY_ISLEAF, which identifies leaf nodes in the data: it’s 1 when a row has no further children, 0 otherwise. In this post, I’ll look at emulating this pseudocolumn using recursive WITH. Let’s continue with the example from my previous posts about hierarchical data: the skeleton from the old song “Dem Dry Bones”. UPDATE skeleton SET connected_to_the=NULL WHERE bone='head'; SELECT * FROM skeleton; BONE CONNECTED_TO_THE ---------------------------------------- ---------------------------------------- shoulder neck back shoulder hip back thigh hip knee thigh leg knee foot heel head neck head toe foot arm shoulder wrist arm ankle leg heel ankle finger wrist a rib back b rib back c rib back With CONNECT BY, we can use the CONNECT_BY_ISLEAF pseudocolumn to identify leaf nodes: SELECT bone, level, ltrim(sys_connect_by_path(bone,' -> '),' -> ') AS path FROM skeleton WHERE connect_by_isleaf=1 START WITH connected_to_the IS NULL CONNECT BY prior bone=connected_to_the ORDER siblings BY 1; BONE LEVEL PATH --------- ----- ----------------------------------------------------------------------------------------------- finger 6 head -> neck -> shoulder -> arm -> wrist -> finger a rib 5 head -> neck -> shoulder -> back -> a rib b rib 5 head -> neck -> shoulder -> back -> b rib c rib 5 head -> neck -> shoulder -> back -> c rib toe 12 head -> neck -> shoulder -> back -> hip -> thigh -> knee -> leg -> ankle -> heel -> foot -> toe This pseudocolumn takes a little more thought to replicate using recursive WITH than the LEVEL pseudocolumn and the SYS_CONNECT_BY_PATH, which, as we saw in my last post, fall naturally out of the recursion. We can imitate CONNECT_BY_ISLEAF by searching DEPTH FIRST and using the LEAD function to peek at the next row’s the_level value. If the next row’s level is higher than the current row, then it’s a child of the current row; otherwise, it’s not a child. Since, with DEPTH FIRST, all the children of a row come out before any siblings, if the next row isn’t a child, then the current row is a leaf. WITH skellarchy (bone, parent, the_level) AS ( SELECT bone, connected_to_the, 0 FROM skeleton WHERE bone = 'head' UNION ALL SELECT s.bone, s.connected_to_the , r.the_level + 1 FROM skeleton s, skellarchy r WHERE r.bone = s.connected_to_the ) SEARCH DEPTH FIRST BY bone SET bone_order CYCLE bone SET is_a_cycle TO 'Y' DEFAULT 'N' SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree , the_level, lead(the_level) OVER (ORDER BY bone_order) AS next_level, CASE WHEN the_level < lead(the_level) OVER (ORDER BY bone_order) THEN NULL ELSE 'LEAF' END is_leaf FROM skellarchy ORDER BY bone_order; BONE_TREE THE_LEVEL NEXT_LEVEL IS_L --------------------------------------------- ---------- ---------- ---- head 0 1 neck 1 2 shoulder 2 3 arm 3 4 wrist 4 5 finger 5 3 LEAF back 3 4 a rib 4 4 LEAF b rib 4 4 LEAF c rib 4 4 LEAF hip 4 5 thigh 5 6 knee 6 7 leg 7 8 ankle 8 9 heel 9 10 foot 10 11 toe 11 LEAF Watch out for Cycles The first point of caution about this solution concerns cycles. In my last post, I had created a cycle by making the ‘head’ node’s parent the ‘toe’ node. If I’d left the cycle in the data, the toe node wouldn’t be a leaf any more, but this query would falsely identify the head as a leaf: UPDATE skeleton SET connected_to_the='toe' WHERE bone='head'; BONE_TREE THE_LEVEL NEXT_LEVEL IS_L --------------------------------------------- ---------- ---------- ---- head 0 1 neck 1 2 shoulder 2 3 arm 3 4 wrist 4 5 finger 5 3 LEAF back 3 4 a rib 4 4 LEAF b rib 4 4 LEAF c rib 4 4 LEAF hip 4 5 thigh 5 6 knee 6 7 leg 7 8 ankle 8 9 heel 9 10 foot 10 11 toe 11 12 head 12 LEAF 19 rows selected. This can be corrected for by adding WHERE IS_A_CYCLE=’N’ to the query. Respect the order of evaluation… A second point of caution: if I add a WHERE clause to the query that limits the number of levels, the last line of the resultset will always be identified as a leaf. WITH skellarchy (bone, parent, the_level) AS ( SELECT bone, connected_to_the, 0 FROM skeleton WHERE bone = 'head' UNION ALL SELECT s.bone, s.connected_to_the , r.the_level + 1 FROM skeleton s, skellarchy r WHERE r.bone = s.connected_to_the ) SEARCH DEPTH FIRST BY bone SET bone_order CYCLE bone SET is_a_cycle TO 'Y' DEFAULT 'N' SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree , the_level, lead(the_level) OVER (ORDER BY bone_order) AS next_level, CASE WHEN the_level < lead(the_level) OVER (ORDER BY bone_order) THEN NULL ELSE 'LEAF' END is_leaf FROM skellarchy WHERE the_level < 8 ORDER BY bone_order; BONE_TREE THE_LEVEL NEXT_LEVEL IS_L ------------------------------------------------------------ ---------- ---------- ---- head 0 1 neck 1 2 shoulder 2 3 arm 3 4 wrist 4 5 finger 5 3 LEAF back 3 4 a rib 4 4 LEAF b rib 4 4 LEAF c rib 4 4 LEAF hip 4 5 thigh 5 6 knee 6 7 leg 7 LEAF <<<===== The leg is falsely identified as a leaf, and NEXT_LEVEL comes out as NULL, even though the ‘leg’ row has a child row. Why is that? It’s because this solution uses the LEAD analytic function. With analytic functions, WHERE clauses are evaluated before the analytic functions. Highlighting the relevant bits from the query: WITH skellarchy AS ...[recursive WITH subquery]... SELECT ... LEAD(the_level) OVER (ORDER BY bone_order) AS next_level ... --analytic function FROM skellarchy WHERE the_level < 8 ... --where clause To quote the documentation: Analytic functions compute an aggregate value based on a group of rows…. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row…. Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. In the query above, “where the_level < 8" will be evaluated before LEAD(the_level). The EXPLAIN PLAN shows this very clearly: ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 76 | 8 (25)| 00:00:01 | | 1 | WINDOW BUFFER | | 2 | 76 | 8 (25)| 00:00:01 | <<=== LEAD |* 2 | VIEW | | 2 | 76 | 8 (25)| 00:00:01 | <<=== filter("THE_LEVEL"<8) | 3 | UNION ALL (RECURSIVE WITH) DEPTH FIRST| | | | | | |* 4 | TABLE ACCESS FULL | SKELETON | 1 | 24 | 2 (0)| 00:00:01 | |* 5 | HASH JOIN | | 1 | 49 | 5 (20)| 00:00:01 | | 6 | RECURSIVE WITH PUMP | | | | | | | 7 | TABLE ACCESS FULL | SKELETON | 18 | 432 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("THE_LEVEL"<8) 4 - filter("BONE"='head') 5 - access("R"."BONE"="S"."CONNECTED_TO_THE") The WINDOW BUFFER (analytic window) is evaluated after the VIEW which filters on “THE_LEVEL”

Posted by on 13 June 2016 | 2:25 pm

Recursive WITH, part II: Hierarchical queries

In my last post, I looked at using recursive WITH to implement simple recursive algorithms in SQL. One very common use of recursion is to traverse hierarchical data. I recently wrote a series of posts on hierarchical data, using Oracle’s CONNECT BY syntax and a fun example. In this post, I’ll be revisiting the same data using recursive WITH. There are dozens of examples of hierarchical data, from the EMP table to the Windows Registry to binary trees, but I went with something more fun: the skeleton from the old song “Dem Dry Bones”. Quote:Toe bone connected to the foot bone Foot bone connected to the heel bone Heel bone connected to the ankle bone Ankle bone connected to the shin bone Shin bone connected to the knee bone Knee bone connected to the thigh bone Thigh bone connected to the hip bone Hip bone connected to the back bone Back bone connected to the shoulder bone Shoulder bone connected to the neck bone Neck bone connected to the head bone Since every bone has only one ancestor, and there is a root bone with no ancestor, this is hierarchical data and we can stick it in a table and query it. SELECT * FROM skeleton; BONE CONNECTED_TO_THE ---------------------------------------- ---------------------------------------- shoulder neck back shoulder hip back thigh hip knee thigh leg knee foot heel head neck head toe foot arm shoulder wrist arm ankle leg heel ankle finger wrist a rib back b rib back c rib back You can see that I added some ribs and an arm to make the skeleton more complete! Using Oracle’s CONNECT BY syntax: SQL> col bone FOR a10 SQL> col connected_to_the FOR a9 SQL> col level FOR 99 SQL> col bone_tree FOR a27 SQL> col path FOR a65 SELECT bone, connected_to_the, level, lpad(' ',2*level, ' ') || bone AS bone_tree , ltrim(sys_connect_by_path(bone,'>'),'>') AS path FROM skeleton START WITH connected_to_the IS NULL CONNECT BY prior bone=connected_to_the ORDER siblings BY 1 BONE CONNECTED LEVEL BONE_TREE PATH ---------- --------- ----- --------------------------- ----------------------------------------------------------------- head 1 head head neck head 2 neck head>neck shoulder neck 3 shoulder head>neck>shoulder arm shoulder 4 arm head>neck>shoulder>arm wrist arm 5 wrist head>neck>shoulder>arm>wrist finger wrist 6 finger head>neck>shoulder>arm>wrist>finger back shoulder 4 back head>neck>shoulder>back a rib back 5 a rib head>neck>shoulder>back>a rib b rib back 5 b rib head>neck>shoulder>back>b rib c rib back 5 c rib head>neck>shoulder>back>c rib hip back 5 hip head>neck>shoulder>back>hip thigh hip 6 thigh head>neck>shoulder>back>hip>thigh knee thigh 7 knee head>neck>shoulder>back>hip>thigh>knee leg knee 8 leg head>neck>shoulder>back>hip>thigh>knee>leg ankle leg 9 ankle head>neck>shoulder>back>hip>thigh>knee>leg>ankle heel ankle 10 heel head>neck>shoulder>back>hip>thigh>knee>leg>ankle>heel foot heel 11 foot head>neck>shoulder>back>hip>thigh>knee>leg>ankle>heel>foot toe foot 12 toe head>neck>shoulder>back>hip>thigh>knee>leg>ankle>heel>foot>toe The above CONNECT BY query uses the LEVEL pseudocolumn and the SYS_CONNECT_BY_PATH function. With recursive WITH, there’s no need for these built-ins because these values fall naturally out of the recursion. Let’s start with the basic hierarchical query rewritten in recursive WITH. The hierarchical relationship in our table is: Parent(row.bone) = row.connected_to_the WITH skellarchy (bone, parent) AS ( SELECT bone, connected_to_the FROM skeleton WHERE bone = 'head' -- Start with the root UNION ALL SELECT s.bone, s.connected_to_the FROM skeleton s, skellarchy r WHERE r.bone = s.connected_to_the -- Parent(row.bone) = row.connected_to_the ) SELECT * FROM skellarchy; BONE PARENT ---------- ---------------------------------------- head neck head shoulder neck back shoulder arm shoulder hip back wrist arm a rib back b rib back c rib back thigh hip finger wrist knee thigh leg knee ankle leg heel ankle foot heel toe foot Because we built up the SKELLARCHY table recursively, it’s easy to make an equivalent to the LEVEL pseudocolumn; it falls right out of the recursion: WITH skellarchy (bone, parent, the_level) AS ( SELECT bone, connected_to_the, 0 FROM skeleton WHERE bone = 'head' UNION ALL SELECT s.bone, s.connected_to_the , r.the_level + 1 FROM skeleton s, skellarchy r WHERE r.bone = s.connected_to_the ) SELECT * FROM skellarchy; BONE PARENT THE_LEVEL ---------- ---------- ---------- head 0 neck head 1 shoulder neck 2 back shoulder 3 arm shoulder 3 hip back 4 wrist arm 4 a rib back 4 b rib back 4 c rib back 4 thigh hip 5 finger wrist 5 knee thigh 6 leg knee 7 ankle leg 8 heel ankle 9 foot heel 10 toe foot 11 and it’s also easy to build up a path from root to the current node like the “SYS_CONNECT_BY_PATH” function does for CONNECT BY queries: WITH skellarchy (bone, parent, the_level, the_path) AS ( SELECT bone, connected_to_the, 0, CAST(bone AS varchar2(4000)) FROM skeleton WHERE bone = 'head' UNION ALL SELECT s.bone, s.connected_to_the , r.the_level + 1, r.the_path || '->' || s.bone FROM skeleton s, skellarchy r WHERE r.bone = s.connected_to_the ) SELECT * FROM skellarchy; BONE PARENT THE_LEVEL THE_PATH ---------- ---------- --------- -------------------------------------------------------------------------------- head 0 head neck head 1 head->neck shoulder neck 2 head->neck->shoulder back shoulder 3 head->neck->shoulder->back arm shoulder 3 head->neck->shoulder->arm hip back 4 head->neck->shoulder->back->hip wrist arm 4 head->neck->shoulder->arm->wrist a rib back 4 head->neck->shoulder->back->a rib b rib back 4 head->neck->shoulder->back->b rib c rib back 4 head->neck->shoulder->back->c rib thigh hip 5 head->neck->shoulder->back->hip->thigh finger wrist 5 head->neck->shoulder->arm->wrist->finger knee thigh 6 head->neck->shoulder->back->hip->thigh->knee leg knee 7 head->neck->shoulder->back->hip->thigh->knee->leg ankle leg 8 head->neck->shoulder->back->hip->thigh->knee->leg->ankle heel ankle 9 head->neck->shoulder->back->hip->thigh->knee->leg->ankle->heel foot heel 10 head->neck->shoulder->back->hip->thigh->knee->leg->ankle->heel->foot toe foot 11 head->neck->shoulder->back->hip->thigh->knee->leg->ankle->heel->foot->toe and we can use our generated the_level column to make a nice display just as we used the level pseudocolumn with CONNECT BY: WITH skellarchy (bone, parent, the_level) AS ( SELECT bone, connected_to_the, 0 FROM skeleton WHERE bone = 'head' UNION ALL SELECT s.bone, s.connected_to_the , r.the_level + 1 FROM skeleton s, skellarchy r WHERE r.bone = s.connected_to_the ) SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree FROM skellarchy; BONE_TREE --------------------------- head neck shoulder back arm hip wrist a rib b rib c rib thigh finger knee leg ankle heel foot toe Now, the bones are coming out in a bit of a funny order for a skeleton. Instead of this: shoulder back arm hip wrist a rib b rib c rib thigh finger I want to see this: shoulder arm wrist finger back a rib b rib c rib hip thigh The rows are coming out in BREADTH FIRST ordering – meaning all siblings of ‘shoulder’ are printed before any children of ‘shoulder’. But I want to see them in DEPTH FIRST: going from shoulder to finger before we start on the backbone. WITH skellarchy (bone, parent, the_level) AS ( SELECT bone, connected_to_the, 0 FROM skeleton WHERE bone = 'head' UNION ALL SELECT s.bone, s.connected_to_the , r.the_level + 1 FROM skeleton s, skellarchy r WHERE r.bone = s.connected_to_the ) SEARCH DEPTH FIRST BY bone SET bone_order SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree FROM skellarchy ORDER BY bone_order; BONE_TREE --------------------------- head neck shoulder arm wrist finger back a rib b rib c rib hip thigh knee leg ankle heel foot toe And now the result looks more like a proper skeleton. Now on to cycles. A cycle is a loop in the hierarchical data: a row is its own ancestor. To put a cycle in the example data, I made the skeleton bend over and connect the head to the toe: UPDATE skeleton SET connected_to_the='toe' WHERE bone='head'; And now if we try to run the query: ERROR at line 2: ORA-32044: cycle detected while executing recursive WITH query With the CONNECT BY syntax, we can use CONNECT BY NOCYCLE to run a query even when cycles exist, and the pseudocolumn CONNECT_BY_IS_CYCLE to help detect cycles. For recursive WITH, Oracle provides a CYCLE clause, which is a bit more powerful as it allows us to name the column which is cycling. WITH skellarchy (bone, parent, the_level) AS ( SELECT bone, connected_to_the, 0 FROM skeleton WHERE bone = 'head' UNION ALL SELECT s.bone, s.connected_to_the , r.the_level + 1 FROM skeleton s, skellarchy r WHERE r.bone = s.connected_to_the ) SEARCH DEPTH FIRST BY bone SET bone_order CYCLE bone SET is_a_cycle TO 'Y' DEFAULT 'N' SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree, is_a_cycle FROM skellarchy --where is_a_cycle='N' ORDER BY bone_order; BONE_TREE I ------------------------------------------------------------ - head N neck N shoulder N arm N wrist N finger N back N a rib N b rib N c rib N hip N thigh N knee N leg N ankle N heel N foot N toe N head Y The query runs until the first cycle is detected, then stops. The CONNECT BY syntax does provide a nice pseudocolumn, CONNECT_BY_ISLEAF, which is 1 when a row has no further children, 0 otherwise. In my next post, I’ll look at emulating this pseudocolumn with recursive WITH. Republished with permission. Original URL: http://rdbms-insight.com/wp/?p=103articles: SQL & PL/SQL

Posted by on 3 June 2016 | 4:38 am

Recursion with recursive WITH

I recently had the opportunity to talk with Tom Kyte (!), and in the course of our conversation, he really made me face up to the fact that the SQL syntax I use every day is frozen in time: I’m not making much use of the analytic functions and other syntax that Oracle has introduced since 8i. Here’s a brief history of these additions to Oracle SQL, from Keith Laker, Oracle’s Product Manager for Analytical SQL: 8i Window functions 9i Rollup, grouping sets, cube, enhanced window functions 10g SQL Model clause, statistical functions, partition outer join 11g SQL Pivot clause, Recursive WITH, Listagg, Nth value 12c Pattern matching, Top N Not only do these make complex queries much, much simpler and easier, they are also much faster for the same result than non-analytic SQL, as Tom Kyte has shown repeatedly on his blog and in his books. So, I was sold and I wanted to jump in with Recursive WITH. The WITH clause lets you define inline views to use across an entire query, and the coolest thing about this is that you can define the subquery recursively – so that the inline view calls itself. Recursive WITH basic syntax: WITH Tablename (col1, col2, ...) AS (SELECT A, B, C... FROM dual --anchor member UNION ALL SELECT A', B', C'... from Tablename where... --recursive member ) select ... from Tablename where ... Refactoring the Factorial One fun thing about recursive WITH, aka recursive subquery refactoring, is the ease with which we can implement a recursive algorithm in SQL. Let’s warm up with a classic example of recursion: finding the factorial of a number. Factorial(n) = n! = 1*2*3*…*n . It’s a classic example because Factorial(n) can be defined recursively as: Factorial(0) = 1 Factorial(n) = Factorial(n-1) * n Here’s a first pass at implementing that directly in SQL to find the factorial of 5, using a recursive WITH subquery: WITH Factorial (operand,total_so_far) AS (SELECT 5 operand, 5 total_so_far FROM dual -- Using anchor member to pass in "5" UNION ALL SELECT operand-1, total_so_far * (operand-1) FROM Factorial WHERE operand > 1) SELECT * FROM Factorial; OPERAND TOTAL_SO_F ---------- ---------- 5 5 4 20 3 60 2 120 1 120 and to display just the result, we select it from Factorial: WITH Factorial (operand,total_so_far) AS (SELECT 5 operand, 5 total_so_far FROM dual -- Find the factorial of 5 UNION ALL SELECT operand-1, total_so_far * (operand-1) FROM Factorial WHERE operand > 1) SELECT MAX(operand) || '! = ' || MAX(total_so_far) AS RESULT FROM Factorial; RESULT ----------------- 5! = 120 Ahem! I have cheated a little for simplicity here. The query doesn’t take into account that Factorial(0) = 1: WITH Factorial (operand,total_so_far) AS (SELECT 0 operand, 0 total_so_far FROM dual -- Find the factorial of 0 UNION ALL SELECT operand-1, total_so_far * (operand-1) FROM Factorial WHERE operand > 1) -- This is going to get me nowhere fast... SELECT * FROM Factorial; OPERAND TOTAL_SO_F ---------- ---------- 0 0 To do it properly, we need to include Factorial(0) = 1 in the recursive subquery: WITH Factorial (operand,total_so_far) AS (SELECT 0 operand, 0 total_so_far FROM dual -- Find the factorial of 0 UNION ALL SELECT operand-1, CASE -- Factorial (0) = 1 WHEN operand=0 THEN 1 ELSE (total_so_far * (operand-1)) END FROM Factorial WHERE operand >= 0) SELECT MAX(operand) || '! = ' || MAX(total_so_far) AS RESULT FROM Factorial; RESULT ------------------------------------------------------------------------------------ 0! = 1 We can also reverse direction and recursively build a table of factorials, multiplying as we go. That’s the approach Lucas Jellema takes in his excellent blog post on factorials in SQL. WITH Factorial (operand,output) AS (SELECT 0 operand, 1 output FROM dual UNION ALL SELECT operand+1, output * (operand+1) FROM Factorial WHERE operand < 5) SELECT * FROM Factorial; OPERAND OUTPUT ---------- ---------- 0 1 1 1 2 2 3 6 4 24 5 120 There are two nice things about this approach: first, every row of the subquery result contains n and n! , and second, the rule that 0! = 1 is elegantly captured in the anchor member. denrael ev’ew tahw gniylppA Now let’s do something more interesting – reversing a string. Here’s some sample code in C from the CS 211 course at Cornell: public String reverseString(String word) { if(word == null || word.equals("")) return word; else return reverseString(word.substring(1, word.length())) + word.substring(0,1); } Let’s run through an example word to see how it works. For simplicity I’ll write reverseString(“word”) as r(word). Using “cat” as the word, stepping through the algorithm gives: r(cat) = r(r(at))+c = r(r(r(t))+a+c = r(r(r(r())+t+a+c = ''+t+a+c = tac Now to rewrite the same function in SQL. Using the same example string, “cat,” I want my recursively defined table to look like this: in out -------- cat at c t ac tac In C, the initial letter in the word is the 0th letter, and in SQL, it’s the 1st letter. So the C expression word.substring(1,N) corresponds to SQL expression substr(word,2,N-1) . With that in mind, it’s easy to rewrite the C algorithm in SQL: WITH WordReverse (INPUT, output) AS (SELECT 'CAT' INPUT, NULL output FROM dual UNION ALL SELECT substr(INPUT,2,LENGTH(INPUT)-1), substr(INPUT,1,1) || output FROM wordReverse WHERE LENGTH(INPUT) > 0 ) SELECT * FROM wordReverse; INPUT OUTP -------- ---- CAT AT C T AC TAC NOTE: if using 11.2.0.3 or earlier, you might get “ORA-01489: result of string concatenation is too long” when reversing anything longer than a few letters. This is due to Bug 13876895: False ora-01489 on recursive WITH clause when concatenating columns. The bug is fixed in 11.2.0.4 and 12.1.0.1, and there’s an easy workaround: Cast one of the inputs to the concatenation as a varchar2(4000). We could make this query user-friendlier by using a sql*plus variable to hold the input string. Another approach is to add an additional subquery to the with block to “pass in” parameters. I picked this up from Lucas Jellema’s post mentioned above, and wanted to give it a try, so I’ll add it in to my WordReverse query here. Let’s use this to reverse a word that’s really quite atrocious: WITH params AS (SELECT 'supercalifragilisticexpialidocious' phrase FROM dual), WordReverse (inpt, outpt) AS (SELECT phrase inpt, CAST(NULL AS varchar2(4000)) outpt FROM params UNION ALL SELECT substr(inpt,2,LENGTH(inpt)-1), substr(inpt,1,1) || outpt FROM wordReverse WHERE LENGTH(inpt) > 0 ) SELECT phrase,outpt AS reversed FROM wordReverse, params WHERE LENGTH(outpt) = LENGTH(phrase) ; PHRASE REVERSED ---------------------------------- ---------------------------------------- supercalifragilisticexpialidocious suoicodilaipxecitsiligarfilacrepus Now you might not have needed to know how to spell “supercalifragilisticexpialidocious” backwards, but one recursive requirement that does come up often is querying hierarchical data. I wrote a series of posts on hierarchical data recently, using Oracle’s CONNECT BY syntax. But recursive WITH can also be used to query hierarchical data. That’ll be the subject of my next post. Republished with permission. Original URL: http://rdbms-insight.com/wp/?p=94articles: SQL & PL/SQL

Posted by on 24 May 2016 | 4:03 am

SQL*Plus error logging – New feature release 11.1

One of the most important things that a developer does apart from just code development is, debugging. Isn’t it? Yes, debugging the code to fix the errors that are raised. But, in order to actually debug, we need to first capture them somewhere. As of now, any application has it’s own user defined error logging table(s). Imagine, if the tool is rich enough to automatically capture the errors. It is very much possible now with the new SQL*PLus release 11.1 A lot of times developers complain that they do not have privilege to create tables and thus they cannot log the errors in a user defined error logging table. In such cases, it’s a really helpful feature, at least during the unit testing of the code. I made a small demonstration in SCOTT schema using the default error log table SPERRORLOG, hope this step by step demo helps to understand easily : NOTE : SQL*Plus error logging is set OFF by default. So, you need to “set errorlogging on” to use the SPERRORLOG table. SP2 Error Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> desc sperrorlog; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(256) TIMESTAMP TIMESTAMP(6) SCRIPT VARCHAR2(1024) IDENTIFIER VARCHAR2(256) MESSAGE CLOB STATEMENT CLOB SQL> truncate table sperrorlog; Table truncated. SQL> set errorlogging on; SQL> selct * from dual; SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored. SQL> select timestamp, username, script, statement, message from sperrorlog; TIMESTAMP --------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- 11-SEP-13 01.27.29.000000 AM SCOTT TIMESTAMP --------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- selct * from dual; SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored. ORA Error SQL> truncate table sperrorlog; Table truncated. SQL> select * from dula; select * from dula * ERROR at line 1: ORA-00942: table or view does not exist SQL> select timestamp, username, script, statement, message from sperrorlog; TIMESTAMP --------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- 11-SEP-13 01.36.08.000000 AM SCOTT TIMESTAMP --------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- select * from dula ORA-00942: table or view does not exist Like shown above, you can capture PLS errors too. If you want to execute it through scripts, you can do it like this, and later spool the errors into a file. I kept these three lines in the sperrorlog_test.sql file - truncate table sperrorlog; selct * from dual; select * from dula; SQL> @D:\sperrorlog_test.sql; Table truncated. SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored. select * from dula * ERROR at line 1: ORA-00942: table or view does not exist SQL> select TIMESTAMP, SCRIPT, STATEMENT, MESSAGE from sperrorlog; TIMESTAMP --------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- 11-SEP-13 01.50.17.000000 AM D:\sperrorlog_test.sql; SP2-0734: unknown command beginning "D:\sperror..." - rest of line ignored. TIMESTAMP --------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- 11-SEP-13 01.50.27.000000 AM D:\sperrorlog_test.sql selct * from dual; SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored. TIMESTAMP --------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- 11-SEP-13 01.50.27.000000 AM D:\sperrorlog_test.sql select * from dula ORA-00942: table or view does not exist SQL> Check Oracle documentation on SPERRORLOG. In addition to above, if you want to be particularly specific about each session’s error to be spooled into a file you could do this - SQL> set errorlogging on identifier my_session_identifier Above mentioned IDENTIFIER keyword becomes a column in SPERRORLOG table. It would get populated with the string value “my_session_identifier”. Now you just need to do this - SQL> select timestamp, username, script, statement, message 2 from sperrorlog 3 where identifier = 'my_session_identifier'; To spool the session specific errors into a file, just do this - SQL> spool error.log SQL> select timestamp, username, script, statement, message 2 from sperrorlog 3 where identifier = 'my_session_identifier'; SQL> spool off articles: SQL & PL/SQL

Posted by on 2 May 2014 | 9:39 am

Finding gaps with analytic functions

Finding gaps is classic problem in PL/SQL. The basic concept is that you have some sort of numbers (like these: 1, 2, 3, 5, 6, 8, 9, 10, 15, 20, 21, 22, 23, 25, 26), where there’s supposed to be a fixed interval between the entries, but some entries could be missing. The gaps problem involves identifying the ranges of missing values in the sequence. For these numbers, the solution will be as follows: START_GAP END_GAP 4 4 7 7 11 14 16 19 24 24 First, run the following code, to create tab1 table: CREATE TABLE tab1 ( col1 INTEGER ); Then, insert a few rows: INSERT INTO tab1 VALUES (1); INSERT INTO tab1 VALUES (2); INSERT INTO tab1 VALUES (3); INSERT INTO tab1 VALUES (5); INSERT INTO tab1 VALUES (6); INSERT INTO tab1 VALUES (8); INSERT INTO tab1 VALUES (9); INSERT INTO tab1 VALUES (10); INSERT INTO tab1 VALUES (15); INSERT INTO tab1 VALUES (20); INSERT INTO tab1 VALUES (21); INSERT INTO tab1 VALUES (22); INSERT INTO tab1 VALUES (23); INSERT INTO tab1 VALUES (25); INSERT INTO tab1 VALUES (26); COMMIT; With data, you can take care of solving the gaps problem… One of the most efficient solutions to the gaps problem involves using analytic functions (also known as window functions) WITH aa AS (SELECT col1 AS cur_value, LEAD (col1) OVER (ORDER BY col1) AS next_value FROM tab1) SELECT cur_value + 1 AS start_gap, next_value - 1 AS end_gap FROM aa WHERE next_value - cur_value > 1 ORDER BY start_gap Using the LEAD function, you can return for each current col1 value (call it cur_value) the next value in the sequence (call it next_value). Then you can filter only pairs where the difference between the two is greater than the one. articles: SQL & PL/SQL

Posted by on 12 January 2014 | 6:20 am

Inverted tables: an alternative to relational structures

The inverted table format can deliver fast and flexible query capabilities, but is not widely used. ADABAS is probably the most successful implementation, but how often do you see that nowadays? Following is a description of how to implement inverted structures within a relational database. All code run on Oracle Database 12c, release 12.1.0.1. Consider this table and a few rows, that describe the contents of my larder: create table food(id number,capacity varchar2(10),container varchar2(10),item varchar2(10)); insert into food values(1,'large','bag','potatoes'); insert into food values(2,'small','box','carrots'); insert into food values(3,'medium','tin','peas'); insert into food values(4,'large','box','potatoes'); insert into food values(5,'small','tin','carrots'); insert into food values(6,'medium','bag','peas'); insert into food values(7,'large','tin','potatoes'); insert into food values(8,'small','bag','carrots'); insert into food values(9,'medium','box','peas'); The queries I run against the table might be "how many large boxes have I?" or "give me all the potatoes, I don't care about how they are packed". The idea is that I do not know in advance what columns I will be using in my predicate: it could be any combination. This is a common issue in a data warehouse. So how do I index the table to satisfy any possible query? Two obvious possibilities: First, build an index on each column, and the optimizer can perform an index_combine operation on whatever columns happen to be listed in the predicate. But that means indexing every column - and the table might have hundreds of columns. No way can I do that. Second, build a concatenated index across all the columns: in effect, use an IOT. That will give me range scan access if any of the predicated columns are in the leading edge of the index key followed by filtering on the rest of the predicate. Or if the predicate does not include the leading column(s), I can get skip scan access and filter. But this is pretty useless, too: there will be wildly divergent performance depending on the predicate. The answer is to invert the table: create table inverted(colname varchar2(10),colvalue varchar2(10),id number); insert into inverted select 'capacity',capacity,id from food; insert into inverted select 'container',container,id from food; insert into inverted select 'item',item,id from food; Now just one index on each table can satisfy all queries: create index food_i on food(id); create index inverted_i on inverted(colname,colvalue); To retrieve all the large boxes: orclz> set autotrace on explain orclz> select * from food where id in 2 (select id from inverted where colname='capacity' and colvalue='large' 3 intersect 4 select id from inverted where colname='container' and colvalue='box'); ID CAPACITY CONTAINER ITEM ---------- ---------- ---------- ---------- 4 large box potatoes Execution Plan ---------------------------------------------------------- Plan hash value: 1945359172 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | C --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 141 | | 1 | MERGE JOIN | | 3 | 141 | | 2 | TABLE ACCESS BY INDEX ROWID | FOOD | 9 | 306 | | 3 | INDEX FULL SCAN | FOOD_I | 9 | | |* 4 | SORT JOIN | | 3 | 39 | | 5 | VIEW | VW_NSO_1 | 3 | 39 | | 6 | INTERSECTION | | | | | 7 | SORT UNIQUE | | 3 | 81 | | 8 | TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED | 3 | 81 | |* 9 | INDEX RANGE SCAN | INVERTED_I | 3 | | | 10 | SORT UNIQUE | | 3 | 81 | | 11 | TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED | 3 | 81 | |* 12 | INDEX RANGE SCAN | INVERTED_I | 3 | | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("ID"="ID") filter("ID"="ID") 9 - access("COLNAME"='capacity' AND "COLVALUE"='large') 12 - access("COLNAME"='container' AND "COLVALUE"='box') Note ----- - dynamic statistics used: dynamic sampling (level=2) orclz> Or all the potatoes: orclz> select * from food where id in 2 (select id from inverted where colname='item' and colvalue='potatoes'); ID CAPACITY CONTAINER ITEM ---------- ---------- ---------- ---------- 1 large bag potatoes 4 large box potatoes 7 large tin potatoes Execution Plan ---------------------------------------------------------- Plan hash value: 762525239 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cos --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 183 | | 1 | NESTED LOOPS | | | | | 2 | NESTED LOOPS | | 3 | 183 | | 3 | SORT UNIQUE | | 3 | 81 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED | 3 | 81 | |* 5 | INDEX RANGE SCAN | INVERTED_I | 3 | | |* 6 | INDEX RANGE SCAN | FOOD_I | 1 | | | 7 | TABLE ACCESS BY INDEX ROWID | FOOD | 1 | 34 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("COLNAME"='item' AND "COLVALUE"='potatoes') 6 - access("ID"="ID") Note ----- - dynamic statistics used: dynamic sampling (level=2) - this is an adaptive plan orclz> Of course, consideration needs to be given to handling more complex boolean expressions; maintaining the inversion is going to take resources; and a query generator has to construct the inversion code and re-write the queries. But In principle, this structure can deliver indexed access for unpredictable predicates of any number of any columns, with no separate filtering operation. Can you do that with a normalized star schema? I don't think so. I hope this little thought experiment has stimulated the little grey cells, and made the point that relational structures are not always optimal for all problems. -- John Watson Oracle Certified Master DBA http://skillbuilders.comarticles: Warehousing

Posted by on 8 September 2013 | 3:52 am

Three impossibilities with partitioned indexes

There are three restrictions on indexing and partitioning: a unique index cannot be local non-prefixed; a global non-prefixed index is not possible; a bitmap index cannot be global. Why these limitations? I suspect that they are there to prevent us from doing something idiotic. This is the table used for all examples that follow: CREATE TABLE EMP (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) ) PARTITION BY HASH (EMPNO) PARTITIONS 4; the usual EMP table, with a partitioning clause appended. It is of course a contrived example. Perhaps I am recruiting so many employees concurrently that a non-partitioned table has problems with buffer contention that can be solved only with hash partitioning. Why can't I have a local non-prefixed unique index? A local non-unique index is no problem, but unique is not possible: orclz> create index enamei on emp(ename) local; Index created. orclz> drop index enamei; Index dropped. orclz> create unique index enamei on emp(ename) local; create unique index enamei on emp(ename) local * ERROR at line 1: ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index You cannot get a around the problem by separating the index from the constraint (which is always good practice): orclz> create index enamei on emp(ename) local; Index created. orclz> alter table emp add constraint euk unique (ename); alter table emp add constraint euk unique (ename) * ERROR at line 1: ORA-01408: such column list already indexed orclz> So what is the issue? Clearly it is not a technical limitation. But if it were possible, consder the implications for performance. When inserting a row, a unique index (or a non-unique index enforcing a unique constraint) must be searched to see if the key value already exists. For my little four partition table, that would mean four index searches: one of each local index partition. Well, OK. But what if the table were range partitioned into a thousand partitions? Then every insert would have to make a thousand index lookups. This would be unbelievably slow. By restricting unique indexes to global or local prefixed, Uncle Oracle is ensuring that we cannot create such an awful situation. Why can't I have a global non-prefixed index? Well, why would you want one? In my example, perhaps you want a global index on deptno, partitioned by mgr. But you can't do it: orclz> create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4; create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4 * ERROR at line 1: ORA-14038: GLOBAL partitioned index must be prefixed orclz>This index, if it were possible, might assist a query with an equality predicate on mgr and a range predicate on deptno: prune off all the non-relevant mgr partitions, then a range scan. But exactly the same effect would be achieved by using global nonpartitioned concatenated index on mgr and deptno. If the query had only deptno in the predicate, it woud have to search each partition of the putative global partitioned index, a process which would be just about identical to a skip scan of the nonpartitioned index. And of course the concatenated index could be globally partitioned - on mgr. So there you have it: a global non-prefixed index would give you nothing that is not available in other ways. Why can't I have a global partitioned bitmap index? This came up on the Oracle forums recently, https://forums.oracle.com/thread/2575623 Global indexes must be prefixed. Bearing that in mind, the question needs to be re-phrased: why would anyone ever want a prefixed partitioned bitmap index? Something like this: orclz> orclz> create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4; create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4 * ERROR at line 1: ORA-25113: GLOBAL may not be used with a bitmap index orclz> If this were possible, what would it give you? Nothing. You would not get the usual benefit of reducing contention for concurrent inserts, because of the need to lock entire blocks of a bitmap index (and therefore ranges of rows) when doing DML. Range partitioning a bitmap index would be ludicrous, because of the need to use equality predicates to get real value from bitmaps. Even with hash partitions, you would not get any benefit from partition pruning, because using equality predicates on a bitmap index in effect prunes the index already: that is what a bitmap index is for. So it seems to me that a globally partitioned bitmap index would deliver no benefit, while adding complexity and problems of index maintenance. So I suspect that, once again, Uncle Oracle is protecting us from ourselves. Is there a technology limitation? I am of course open to correction, but I cannot see a technology limitation that enforces any of these three impossibilities. I'm sure they are all technically possible. But Oracle has decided that, for our own good, they will never be implemented. -- John Watson Oracle Certified Master DBA http://skillbuilders.comarticles: RDBMS Server

Posted by on 1 September 2013 | 11:22 am

Are older releases of the database really unsupported?

I see posts on Oracle related forums about various releases (anything that isn't 11.x or 12.x) being "unsupported". This is wrong. Of course you should upgrade any 9i or 10g databases, but you don't have to. Oracle Corporation's lifetime support policy is documented here, Lifetime Support Policy take a look, and you'll see that release 10.2 was in premier support until end July 2010 when it went into extended support. At end July 2013, it goes into sustaining support. Sustaining support will continue indefinitely. Even release 8.1.7 will have sustaining support indefinitely. So what is sustaining support? That is documented here, Lifetime support benefits To summarize, extended support gives you everything you are likely to need. What you do not get is certification against new Oracle products or new third party products (principally, operating systems). But does that matter? I don't think so. For example, release 11.2.0.3 (still in premier support) is not certified against Windows 8, but it works fine. Sustaining support has a more significant problem: no more patches. Not even patches for security holes, or changes in regulatory requirements. The security patch issue may of course be serious, but regulatory issues are unlikely to matter (this is a database, not a tax management system.) Think about it: 10g has been around for many years. It is pretty well de-bugged by now. If you hit a problem with no work around, you are pretty unlucky. Sustaining support gives you access to technical support, available patches, software, and documentation. That is all most sites will ever need. Right now, I am working on a 9.2.0.8 database. It cannot be upgraded because the application software is written by a company that does not permit a database upgrade. Why not? Well, the reason may be commercial: they have a replacement product that is supported on newer databases. But that is nothing to do with me. The database works, the software works. Making it work better is a challenge - but that is what a DBA is paid to do. Don't just write it off as "unsupported". Of course I am not suggesting that users should not upgrade to current releases - but upgrades are a huge project, and can have major implications. Running out dated software is silly, unless you have an irrefutable reason for so doing. The lack of security patches make you vulnerable to data loss. The lack of regulatory patches may make it illegal. The lack of newer facilities will be restricting the utility of the system. You may be losing money by not taking of advantage of changes of newer technology that can better exploit your hardware. If anyone is looking for consulting support to upgrade their database - my boss will be happy to give you a quote. But I won't refuse to support you in the meantime. -- John Watson Oracle Certified Master DBA http://skillbuilders.comarticles: RDBMS Server

Posted by on 28 July 2013 | 5:19 am

12C: IN DATABASE ARCHIVING

In this post, I will demonstrate a new feature introduced in 12c : In database archiving. It enables you to archive rows within a table by marking them as invisible. This is accomplshed  by means of a hidden column ORA_ARCHIVE_STATE. These invisible rows are not visible to the queries but if needed, can be viewed , by setting a session parameter ROW ARCHIVAL VISIBILITY. Overview: -- Create test user uilm, tablespace ilmtbs -- Connect as user uilm -- create and populate test table (5 rows) ilmtab with row archival clause -- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created   and has the default value of 0 (indicates that row is active) -- Note that this column is not visible when we describe the table or simply issue select * from ... -- We need to access data dictionary to view the column -- Make two  rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value. -- Check that inactive rows are not visible to query -- Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also -- Set the parameter ROW ARCHIVAL VISIBILITY  = active to hide inactive rows -- Issue an insert into ... select * and check that only 3 visible rows are inserted -- Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also -- Issue an insert into ... select * and check that all the rows are inserted but ORA_ARCHIVE_STATE    is not propagated in inserted rows -- Disable row archiving in the table and check that column ORA_ARCHIVE_STATE is automatically dropped -- drop tablespace ilmtbs and user uilm Implementation : -- Create test user, tablespace and test table SQL> conn sys/oracle@em12c:1523/pdb1 as sysdba sho con_name CON_NAME ------------------------------ PDB1 SQL> set sqlprompt PDB1> PDB1>create tablespace ilmtbs datafile '/u02/app/oracle/oradata/cdb1/pdb1/ilmtbs01.dbf' size 1m; grant connect, resource, dba  to uilm identified by oracle; alter user uilm default tablespace ilmtbs; conn uilm/oracle@em12c:1523/pdb1 sho con_name CON_NAME ------------------------------ PDB1-- create table with "row archival clause" PDB1>drop table ilmtab purge; create table ilmtab (id number, txt char(15)) row archival; insert into ilmtab values (1, 'one'); insert into ilmtab values (2, 'two'); insert into ilmtab values (3, 'three'); insert into ilmtab values (4, 'four'); insert into ilmtab values (5, 'five'); commit;-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created    and has the default value of 0 (indicates that row is active) PDB1>col ora_archive_state for a20 select id, txt, ora_archive_state from ilmtab; ID TXT             ORA_ARCHIVE_STATE ---------- --------------- -------------------- 1 one             0 2 two             0 3 three           0 4 four            0 5 five            0-- Note that this column is not visible when we describe the table or simply issue select * from ... PDB1>desc ilmtab Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- ID                                                 NUMBER TXT                                                CHAR(15) PDB1>select * from ilmtab; ID TXT ---------- --------------- 1 one 2 two 3 three 4 four 5 five -- Since the column is invisible, let me try and make it visible-- Note that Since the column is maintained by oracle itself, user can't modify its attributes PDB1>alter table ilmtab modify (ora_archive_state visible); alter table ilmtab modify (ora_archive_state visible) *ERROR at line 1:ORA-38398: DDL not allowed on the system ILM column -- We need to access data dictionary to view the column-- Note that this column is shown as hidden and has not been generated by user PDB1>col hidden for a7 col USER_GENERATED for 20 col USER_GENERATED for a20 select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED from user_tab_cols where table_name='ILMTAB'; TABLE_NAME  COLUMN_NAME          HID USER_GENERATED ----------- -------------------- --- -------------------- ILMTAB      ORA_ARCHIVE_STATE    YES NO ILMTAB      ID                   NO  YES ILMTAB      TXT                  NO  YES-- We can make selected rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value. This can be accomplished using update table... set ORA_ACRHIVE_STATE = . <non-zero value> . dbms_ilm.archivestatename(1) -- Let's update row with id =1 with ORA_ARCHIVE_STATE=2     and update row with id =2 with dbms_ilm.archivestatename(2) PDB1>update ilmtab set ora_archive_state=2 where id=1; update ilmtab set ora_archive_state= dbms_ilm.archivestatename(2) where id=2;-- Let's check whether updates have been successful and hidden rows are not visible PDB1>select id, txt, ORA_ARCHIVE_STATE from ilmtab; ID TXT             ORA_ARCHIVE_STATE ---------- --------------- -------------------- 3 three           0 4 four            0 5 five            0 -- The updated rows are not visible!! -- Quite logical since we have made the rows active and by default only active rows are visible -- To see inactive rows also, we need to set the parameter ROW ARCHIVAL VISIBILITY  = all at session level -- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using dbms_ilm.archivestatename(2) PDB1>alter session set ROW ARCHIVAL VISIBILITY  = all; select id, txt, ORA_ARCHIVE_STATE from ilmtab; ID TXT             ORA_ARCHIVE_STATE ---------- --------------- -------------------- 1 one             2 2 two             1 3 three           0 4 four            0 5 five            0-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using    dbms_ilm.archivestatename(2) -- Let's find out why-- Note that The function dbms_ilm.archivestatename(n) returns only two values    0 for n=0 and 1 for  n <> 0 PDB1>col state0 for a8 col state1 for a8 col state2 for a8 col state3 for a8 select dbms_ilm.archivestatename(0) state0 ,dbms_ilm.archivestatename(1) state1, dbms_ilm.archivestatename(2) state2,dbms_ilm.archivestatename(3) state3  from dual; STATE0   STATE1   STATE2   STATE3 -------- -------- -------- -------- 0        1        1        1-- In order to make the inactive rows (id=1,2) hidden again, we need to set the parameter ROW ARCHIVAL VISIBILITY  = Active PDB1>alter session set row archival visibility = active; select id, txt, ORA_ARCHIVE_STATE from ilmtab; ID TXT             ORA_ARCHIVE_STATE ---------- --------------- -------------------- 3 three           0 4 four            0 5 five            0-- Let's issue an insert into ... select * -- Note that only 3 new rows are visible PDB1>insert into ilmtab select * from ilmtab; select id, txt, ora_archive_state from ilmtab; ID TXT             ORA_ARCHIVE_STATE ---------- --------------- -------------------- 3 three           0 4 four            0 5 five            0 3 three           0 4 four            0 5 five            0 6 rows selected. -- I want to check if hidden rows were also inserted -- Let's check by making  hidden rows visible again-- Note that only visible rows(id=3,4,5) were inserted PDB1>alter session set row archival visibility=all; select id, txt, ora_archive_state from ilmtab; ID TXT             ORA_ARCHIVE_STATE ---------- --------------- -------------------- 1 one             2 2 two             1 3 three           0 4 four            0 5 five            0 3 three           0 4 four            0 5 five            0 8 rows selected.-- Let's set row archival visibility = all and then again insert rows from ilmtab-- Note that all the 8 rows are inserted but ORA_ARCHIVE_STATE ha not been copied    ORA_ARCHIVE_STATE <> 0 in only 2 records (id = 1,2) even now. PDB1>alter session set row archival visibility=all; insert into ilmtab select * from ilmtab; select id, txt, ora_archive_state from ilmtab order by id; ID TXT             ORA_ARCHIVE_STATE ---------- --------------- -------------------- 1 one             0 1 one             2 2 two             0 2 two             1 3 three           0 3 three           0 3 three           0 3 three           0 4 four            0 4 four            0 4 four            0 4 four            0 5 five            0 5 five            0 5 five            0 5 five            0 16 rows selected.-- Disable row level archiving for the table -- Note that as soon as row archiving is disabled, pseudo column ora_archive_state is dropped automatically PDB1>alter table ilmtab no row archival; select id, txt, ORA_ARCHIVE_STATE from ilmtab; ERROR at line 1:ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier PDB1>col hidden for a7 col USER_GENERATED for 20 col USER_GENERATED for a20 select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED from user_tab_cols where table_name='ILMTAB'; TABLE_NAME  COLUMN_NAME          HID USER_GENERATED ----------- -------------------- --- -------------------- ILMTAB      ID                   NO  YES ILMTAB      TXT                  NO  YESNote : Had we created this table using sys, we could not have disabled row archiving . -- cleanup -- PDB1>conn sys/oracle@em12c:1523/pdb1 as sysdba drop tablespace ilmtbs including contents and datafiles; drop user uilm cascade;References: http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#VLDBG14154 ---------------------------------------------------------------------------------------------------- Oracle 12c Index ----------------------------------------------------------------------------------------------   articles: Technical Articles

Posted by on 28 July 2013 | 3:42 am

12c: CONNECTING TO PDB'S WITH SAME NAME

When you create a PDB, the database automatically creates and starts a service inside the CDB.The service has the same name as the PDB. It is possible that the name of the service will collide with an existing service name which is registered with the same listener. For example if two or more CDBs on the same computer system use the same listener, and the newly established PDB has the same service name as another PDB in these CDBs, then a collision occurs. You must not attempt to operate a PDB that causes a collision with an existing service name. I will demonstrate that a connection that specifies the default service name of a PDB can connect randomly to any of the PDBs with the same service name. To avoid incorrect connections, we should configure a separate listener for each CDB on a computer system. Current scenario: I have two CDB's (CDB1 and CDB2) on the same computer system. Pluggable database PDB1 exists in both the CDB's CDB1 and CDB2 There are two listeners running in database home listener1 on port 1523 listener2 on port 1524 Overview: -- Register both the CDB's (and hence PDB's) with listener1 running on port 1523. -- Verify that if we repeatedly connect to service PDB1, we are randomly connected to different pdb's (PDB1@CDB1 and PDB1@CDB2). -- Register PDB1@CDB2 with listener2 on port 1524. -- Verify that now we can connect to the right pdb Implementation: -- Register both the CDB's with listener1 running on port 1523 CDB1>alter system set local_listener='em12c.oracle.com:1523'; sho parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string em12c.oracle.com:1523 CDB2>alter system set local_listener='em12c.oracle.com:1523'; sho parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string em12c.oracle.com:1523 -- check that both the CDB's and PDB1 in both the CDB's are registered with listener1 (port 1523) [oracle@em12c ~]$ lsnrctl stat listener1 (output trimmed) Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/em12c/listener1/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1523))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "cdb2" has 1 instance(s). Instance "cdb2", status READY, has 1 handler(s) for this service... Service "cdb2XDB" has 1 instance(s). Instance "cdb2", status READY, has 1 handler(s) for this service... Service "pdb1" has 2 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Instance "cdb2", status READY, has 1 handler(s) for this service... The command completed successfully It can be seen that PDB PDB1 has same name in both the CDB's (CDB1 and CDB2) and default service for both the PDB's (PDB1@CDB1 and PDB1@CDB2) are registered with the listener on the same port (1523). -- Verify that if we repeatedly connect to service PDB1, we are randomly connected to different pdb's (PDB1@CDB1 and PDB1@CDB2) CDB1>conn system/oracle@em12c:1523/pdb1 sho parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string cdb2 CDB1>conn system/oracle@em12c:1523/pdb1 sho parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string cdb1 CDB1>conn system/oracle@em12c:1523/pdb1 sho parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string cdb2 CDB1>conn system/oracle@em12c:1523/pdb1 sho parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string cdb1 Hence, to connect to the right PDB, we should register different PDB's with listeners running on different ports. -- Let's register PDB1@CDB2 with listener2 on port 1524 CDB2>alter system set local_listener='em12c.oracle.com:1524'; sho parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string em12c.oracle.com:1524 -- check that CDB1 and pdb1@CDB1 are registered with listener1 (port 1523) [oracle@em12c ~]$ lsnrctl stat listener1 (output trimmed) Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/em12c/listener1/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1523))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... The command completed successfully -- check that CDB2 and PDB1@CDB2 are registered with listener2 (port 1524) [oracle@em12c ~]$ lsnrctl stat listener2 (output trimmed) Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/em12c/listener2/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em12c.oracle.com)(PORT=1524))) Services Summary... Service "cdb2" has 1 instance(s). Instance "cdb2", status READY, has 1 handler(s) for this service... Service "cdb2XDB" has 1 instance(s). Instance "cdb2", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "cdb2", status READY, has 1 handler(s) for this service... The command completed successfully -- Verify that now we can connect to the right pdb -- connect to PDB1@CDB1 (listener1, port 1523) SQL> conn system/oracle@em12c:1523/pdb1 sho parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string cdb1 SQL> conn system/oracle@em12c:1523/pdb1 sho parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string cdb1 -- connect to PDB1@CDB2 (listener2, port 1524) SQL> conn system/oracle@em12c:1524/pdb1 sho parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string cdb2 SQL> conn system/oracle@em12c:1524/pdb1 sho parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string cdb2 Hence, it can be concluded that to avoid incorrect connections, we should configure a separate listener for each CDB on a computer system. References: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6009.htm http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdblogic.htm#CIHDEDCC ------------------------------------------------------------------------------------- articles: Technical Articles

Posted by on 28 July 2013 | 3:37 am