Confusion with Oracle Connect By

Confusion with Oracle CONNECT BY

How a CONNECT BY query is executed and evaluated - step by step (by example).

Say we have the following table and a connect by query:

select * from mytable;

         X
----------
         1 
         2 
         3 
         4 

SELECT level, m.* 
FROM mytable m
START with x = 1
CONNECT BY PRIOR x +1 = x  OR  PRIOR x + 2 = x 
ORDER BY level;

Step 1:

Select rows from table mytable that meet a START WITH condition, assign LEVEL = 1 to the returned result set:

 CREATE TABLE step1 AS
 SELECT 1 "LEVEL", X from mytable
 WHERE x = 1;

 SELECT * FROM step1;

         LEVEL          X
    ---------- ----------
             1          1

Step 2

Increase level by 1:

LEVEL = LEVEL + 1

Join the result set returned in previous step with mytable using CONNECT BY conditions as the join conditions.

In this clause PRIOR column-name refers to the resultset returned by previous step, and simple column-name refers to the mytable table:

CREATE TABLE step2 AS
SELECT 2 "LEVEL", mytable.X from mytable
JOIN step1 "PRIOR"
ON "PRIOR".x +1 = mytable.x or  "PRIOR".x + 2 = mytable.x;

select * from step2;

     LEVEL          X
---------- ----------
         2          2 
         2          3

STEP x+1

Repeat #2 until last operation returns an empty result set.

Step 3

CREATE TABLE step3 AS
SELECT 3 "LEVEL", mytable.X from mytable
JOIN step2 "PRIOR"
ON "PRIOR".x +1 = mytable.x or  "PRIOR".x + 2 = mytable.x;

select * from step3;

     LEVEL          X
---------- ----------
         3          3 
         3          4 
         3          4

Step 4

CREATE TABLE step4 AS
SELECT 4 "LEVEL", mytable.X from mytable
JOIN step3 "PRIOR"
ON "PRIOR".x +1 = mytable.x or  "PRIOR".x + 2 = mytable.x;

select * from step4;

     LEVEL          X
---------- ----------
         4          4 

Step 5

CREATE TABLE step5 AS
SELECT 5 "LEVEL", mytable.X from mytable
JOIN step4 "PRIOR"
ON "PRIOR".x +1 = mytable.x or  "PRIOR".x + 2 = mytable.x;

select * from step5;

no rows selected

Step 5 returned no rows, so now we finalize the query

Last step

UNION ALL results of all steps and return it as the final result:

SELECT * FROM step1
UNION ALL
SELECT * FROM step2
UNION ALL
SELECT * FROM step3
UNION ALL
SELECT * FROM step4
UNION ALL

SELECT * FROM step5;

     LEVEL          X
---------- ----------
         1          1 
         2          2 
         2          3 
         3          3 
         3          4 
         3          4 
         4          4 

Now let's apply the above procedure to your query:

SELECT * FROM dual;

DUMMY
-----
X 

SELECT LEVEL FROM DUAL CONNECT BY rownum>5;

Step 1

Since the query does not contain the START WITH clause, Oracle selects all records from the source table:

CREATE TABLE step1 AS
SELECT 1 "LEVEL" FROM dual;

select * from step1;

     LEVEL
----------
         1 

Step 2

CREATE TABLE step2 AS
SELECT 2 "LEVEL" from dual
JOIN step1 "PRIOR"
ON rownum > 5

select * from step2;

no rows selected

Since the last step returned no rows, we are going to finalize our query.

Last step

SELECT * FROM step1
UNION ALL

SELECT * FROM step2;

     LEVEL
----------
         1

The analyze of the last query:

select level from dual connect by rownum<10;

I leave to you as a homework assignment.

Confusion in flow of sql output in oracle

Suppose I have two rows and I want to expand both of them to get one row for every integer in the range. For example, take this table:

drop table U purge;

create table U as
select 1 range_id, 2 range_end from dual
union all
select 2, 3 from dual;

select * from u;
ID  END
1   2
2   3

If I try something like your attempt:

select range_id, range_end, level
from u
connect by level <= range_end;

ID  END  LEVEL
1   2   1
1   2   2
2   3   3
2   3   2
2   3   3
2   3   1
1   2   2
2   3   3
2   3   2
2   3   3

What is this mess? It looks like I’m starting with each row and connecting to the other row – which makes sense because I’m not saying to stay on the same row. Let’s try again:

select range_id, range_end, level
from u
connect by level <= range_end
and range_id = prior range_id

Error report - SQL Error: ORA-01436: CONNECT BY loop in user data

Now I made a reference to something PRIOR – the range_id. Oracle sees that the same range_id is accessed twice in a row, so it assumes there is an infinite loop and aborts the execution.

There is a way to avoid that error, using the NOCYCLE keyword:

select range_id, range_end, level
from u
connect by nocycle level <= range_end
and range_id = prior range_id;

ID  END LEVEL
1   2   1
2   3   1

Well, I didn’t get the error, but Oracle still considers that doing the same range_id twice would be a loop, so it stops first.

What we need is to add something to the prior row that will make Oracle think it is different. SYS_GUID() is a very low-cost function that returns a nonrepeating value. If we refer to PRIOR SYS-GUID() in a condition, that is enough to make the prior row unique and to prevent the perception of an infinite loop.

select range_id, range_end, level
from u
connect by level <= range_end
and range_id = prior range_id
and prior sys_guid() is not null;

ID  END LEVEL
1   2   1
1   2   2
2   3   1
2   3   2
2   3   3

Applying this technique to your data:

with data(start_range, end_range) as (
select 1, 3 from dual
union all select 5, 7 from dual
)
SELECT start_range, end_range,
  start_range + level - 1 num
FROM data
CONNECT BY start_range + level - 1 <= END_RANGE
and start_range = prior start_range
and prior sys_guid() is not null;

The other answer works too! I am just trying to explain how CONNECT BY works.

Best regards, Stew Ashton

Connect by clause confusion

Assuming that you want 3-level relationships between employees and managers. Try this query:

SELECT e1.empno, e2.mgr
FROM emp AS e1 INNER JOIN emp AS e2
WHERE e1.mgr = e2.empno

Pay attention that this sql query would not return the top manager of each employee.

So if 'B' is manager of 'A' and also 'C' is manager of 'B', the query would return 'A' and 'C'.

SQL select statement confusion

You don't have to select columns. You can select expressions, which can by columns, constant values, function calls, calculations.... So you could select this from an employee table

select 
  e.nr,  -- column
  upper(e.name) as name, -- Function call to change the name to uppercase.
  e.age + 5 as age, -- expression with calculation with column value. 
  1  -- Just a value. Contains 1  for each row that the query returns.
from 
  employees e;

Note as name and as age. This is because the column name is lost once an expression contains more that just a column name. In that case, you can specify the name that you want the column to have in the query results. A similar trick is done for the table name itself. The alias e is just a shorthand. This is especially useful when using multiple tables (in a join). In this simple query, the table alias can be omitted altogether.

dual is just a special table that has 1 row exactly, and one column named dummy. It has many different uses which have been discussed before. From Oracle 23c onwards, from dual is optional, as it has been in MySQL for a while longer. You can simply write:

select
  1;

Oracle, Connect By rownum

The explanation in Krokodilko's answer is simply wrong. You may disregard the "Correct Answer" mark and the numerous upvotes, it's still wrong. It is interesting that he left as an exercise exactly the case that proves the explanation is wrong.

A CONNECT BY query doesn't work "as if" new tables (or new output rowsets of SELECT statements, anyway) are generated at each step. This is the mistake in the argument.

Rather, there is only one rowset generated overall (across all steps). It is true that new rows are added based on the rows generated at the previous step; but the rowset itself is one, and growing, not separate rowsets.

This is particularly relevant with regard to ROWNUM. ROWNUM is assigned to rows in a single "result" rowset, starting with 1. In a CONNECT BY query, there is only one rowset, and ROWNUM goes from 1 to n in an increasing sequence.

If Krokodilko's answer were correct, then ROWNUM would restart at 1 at each step. This is clearly not the case: let's try it on a "standard" hierarchical query.

select     empno, ename, mgr, level, rownum
from       scott.emp
start with mgr is null
connect by prior empno = mgr
;

     EMPNO ENAME             MGR      LEVEL     ROWNUM
---------- ---------- ---------- ---------- ----------
      7839 KING                           1          1
      7566 JONES            7839          2          2
      7788 SCOTT            7566          3          3
      7876 ADAMS            7788          4          4
      7902 FORD             7566          3          5
      7369 SMITH            7902          4          6
      7698 BLAKE            7839          2          7
      7499 ALLEN            7698          3          8
      7521 WARD             7698          3          9
      7654 MARTIN           7698          3         10
      7844 TURNER           7698          3         11
      7900 JAMES            7698          3         12
      7782 CLARK            7839          2         13
      7934 MILLER           7782          3         14

Confusion Deciphering Error Code Python Module To Connect To Oracle (cx_Oracle)

Apologies for the confusion. There was a bug in the error message. This only occurs with very old versions of the Oracle Client libraries. I've just corrected this here: https://github.com/oracle/odpi/commit/d2fea3801286d054e18b0102e60a69907b7faa9a and that will be released as part of cx_Oracle 7.1.1 soon.

So what the error message is really trying to tell you is that you need to have 11.2 or higher Oracle Client libraries and you have a version old enough that it doesn't even know how to tell you what version it is! So likely 8i or 9i or early versions of 10g. With those older versions they were frequently stored in c:\Windows\system32 and thus take precedence over other libraries that you may have installed. You can force the issue by setting your PATH environment variable to include the C:\app\client\corporateDrone\product\12.1.0\client_1\bin at the beginning. If that doesn't help, you may have to find and remove the older version of OCI.dll -- keeping in mind that doing so will affect any software that depends on it!

The official documentation can be found here: https://cx-oracle.readthedocs.io/en/latest/index.html. There is an enhancement request to include these in the builtin help that you noted doesn't have anything useful. :-) You can see the enhancement request here: https://github.com/oracle/python-cx_Oracle/issues/175.

Hopefully that alleviates your confusion!

Confusion in Oracle naming

A SID is the unique identifier for a database, or if it's a RAC system for an instance of a database.

A Service is an identifier for a service offered by the database, and the database should be configured for services such as "BILLING_APP" or "CUST_WEBSITE". http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams217.htm

Don't confuse a database and an instance, by the way. A database is the set of data and control (etc) files, the instance is the memory areas and processes that access it. RAC has multiple instances per database, each of which can register with the listener to offer all or a subset of the total services offered by the database.

A schema is effectively the username that owns the objects, and there can be multiple schemas in the database.

So an application should be referencing a SERVICE to connect to, through a listener on a certain host and port. The listener resolves this to a database (possibly one of many that register to offer that service). The connection is made to the database as a user who might or might not be the owner of the schema. Typically the connection is not made as the schema owner for security reasons -- in fact the best level of security is to connect as User_A, calling code in User_B's schema, whicxh references tables in other users' schemas.

---

Related Topics