CODEWITHSUNDEEP
sqloracle-database
Abichellam
Abichellam

Reputation: 509

Limit on nested conditions

Is there any limitation on the level of nesting in a where clause condition in Oracle? For example:

select * from sample where ((((a or b) or c) or d) or e)

Upvotes: 0

Views: 731

Answers (2)

CodeSmith
CodeSmith

Reputation: 3207

I assume by where nesting you mean correlated subquery nesting in Oracledb?

That is 1 level deep for up to version 10, and 255 from version 10 forward.

At least this is how I understand it:

From Oracle Database SQL Reference 10g Release 1 (10.1) Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery

From Oracle9i SQL Reference Release 2 (9.2) Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement.

A subquery in the WHERE clause of a SELECT statement is also called a nested subquery. You can nest up to 255 levels of subqueries in the a nested subquery.

EDIT: I see. What you are doing is not called nesting the WHERE clauses, you are nesting logical conditions. I don't think there is a limit to those, but you might run into too long query if you keep up adding more and more.

That depends on more then 1 factor, some being:

  • db version
  • OS and OS version
  • architecture (64/32)

More information you can find here: database limitations

Upvotes: 0

MT0
MT0

Reputation: 168351

Is there limitation on level of nesting the where condition clause in oracle?

select * from sample where ((((a or b) or c) or d) or e)

Theoretically, no.

Practically, you will be limited by the physical limitations of the system as to the maximum size of an SQL statement (or, alternatively, by the wrath of the next developer who comes along and can't work out which of the thousands of opening and closing brackets match in your statement).

If you really wanted to you could do something like:

DECLARE
  v_sql CLOB;
BEGIN
  v_sql := 'SELECT * FROM DUAL WHERE ';
  FOR i IN 1 .. 100000 LOOP
    v_sql := v_sql || '(';
  END LOOP;
  v_sql := v_sql || '1=1';
  FOR i IN 1 .. 100000 LOOP
    v_sql := v_sql || ')';
  END LOOP;
  EXECUTE IMMEDIATE v_sql;
END;
/

Upvotes: 3

Related Questions