Hi, The reason for the large increase might be that the subquery creates a temporary table.
I wonder if the query could be simplified so that no temporary table is necessary? Regards, Thomas On Sat, Dec 29, 2012 at 1:49 PM, Philipp Cornelius < [email protected]> wrote: > So, I need two things now: > > - How can I fix the database size (the content should make the database > only around 46GB large)? > - How can I prevent this from happening again (i.e., what has happened)? > > > On Saturday, 29 December 2012 11:20:28 UTC+1, Philipp Cornelius wrote: >> >> Hi >> >> I have a H2 database which saves around 760MB/day. Yesterday, database >> size was around 45,542,743.00 KB (approximated). >> Then, while performing the following query, the database literally >> exploded: >> >> "SELECT a, b FROM t1 WHERE a NOT IN ((SELECT a FROM t2) UNION (SELECT a >> FROM t3 WHERE c = ?)) ORDER BY b ASC LIMIT ?;" >> >> (There are unique and non-unique inidces on a in every table and an index >> on c in t3. I am pretty sure this is not the most efficient query, but it >> was good enough for our purposes ... until now). >> >> While performing the above query, the database increased in size with >> approx. 20MB/s until the disks were full. Currently, that is 89GB and 130GB >> respectively for the main and the backup server (the same thing happened on >> the redundant backup server at the same time). >> >> I have analyzed the database content, but I cannot see where the 20MB/s >> of data came from? >> > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/h2-database/-/O4oXTPShYcIJ. > > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
