CONNECT BY ORACLE SQL - Online Tech Support

There are times when we need to program some crazy things and one of them is pre filling the tables. When you do know in advance that loads of lines will be inserted to your table in a very short time some preparations needs to be done. Among of all other reasons one of them would be re-filling your table to make sure the table has it’s tablespace big enough and the room already reserved. After the re-filling process is done we will truncate the table re-set sequences and analyse the objects. The meaning all of it is to make sure the traffic wouldn’t have delays because of our Oracle database is busy with extending table sizes. You could set the tablespace size value via Oracle DBA commands but then you take a risk of miscalculating the size and the last traffic would still see delays. To play safe we will do it in old ways manually using Oracle Rownum Function and hierarchical query clause Connect By to generate the rows and insert them into the Oracle table. The Oracle Select syntax is following:

   SELECT <columns>
     FROM <table>
  CONNECT BY <the_condition>;

The first thing is to try the select query and we are expecting 10000000000 rows in very short time as it may happen in online marketing where a promotion is open only for 24 hours and all sales are hitting new records. We will write the query and for testing purposes we will do count that will fetch all rows the output is following.

 SELECT COUNT(*)
   FROM DUAL
  CONNECT BY rownum <= 10000000000;

To make sure this “ORA-30009: Not enough memory for CONNECT BY operation” error is not some kind of SQL Developer tool’s restriction we will try the query in the SQL Plus tool.

The “ORA-30009: Not enough memory for CONNECT BY operation” error in output confirms there is not change to write this query in a simple way and use the existing Oracle Connect By clause. Well, this is not end of the world SQL and especially Oracle SQL has loads of ways to write the same query in a different ways and using other functions. We will re-write the query to use Oracle With operator and of course the query looks more complex but it will depend less on memory limits set by Oracle. Take a look at the following query but don’t execute it. The query take around 6 hours to finish.

 WITH my_dual(n) AS
  (SELECT 1 FROM dual
    UNION ALL
   SELECT my_dual.n + 1
     FROM my_dual
    WHERE my_dual.n < 10000000000 )
 SELECT COUNT(*)
   FROM my_dual;

We did manage to write a query that doesn’t crash for the 10000000000 rows but it takes 6 hours to finish and we may need to re-use it for other tables too or another time. One of the option would be to create a table but this doesn’t make much sense to have one row table and somebody can misunderstand the purpose of it and delete or even worse start using it the code. We found the best middle way to create a materialized view and the query will be stored in it. Plus we can make changes in the query directly without having more packages or procedures stored in the database. In other words we got solution “all in one“.

CREATE MATERIALIZED VIEW my_big_dual
   AS 
 WITH my_dual(n) AS
  (SELECT 1 FROM dual
    UNION ALL
   SELECT my_dual.n + 1
     FROM my_dual
    WHERE my_dual.n < 10000000000
   )
 SELECT * 
   FROM my_dual;

It still takes time to get the materialized view been created by Oracle database and it will be around a few hours but using it take now seconds or actually the same Oracle Count function took 40 seconds to read all 10000000000 rows as in the following picture.

SELECT COUNT(*) 
  FROM my_big_dual;

Once again creating a materialized view isn’t the only option you could use still the query and run it with a parallel hint for example. As a mater of fact we did try it and it does finish the query many times quicker. The down side is that rest of your code and processes will slow down and I do mean very slow down mode. For us the best option was to wait until the materialized view got created and re-use it as many times we need to.