I have a query which returns a large number of rows (presumably a total 37454 rows).
I present three cases:
Select * from invitj_rec; -- this runs for more than 30 mns after which I terminated it.
Select * from invitj_rec limit 37454; -- this returns in a few seconds with 37454 rows.
Select * from invitj_rec limit 37455; -- does not return for 30 mns at least (same as case 1)
In case 2, I can use any number less than 37454 and it returns immediately with the specified number of rows. In case 3, any limit of more than 37454 hangs.
So, apparently the query produces a total of 37454 rows. But I do not know this number in advance. I need to run this query without a "limit" and produce the 37454 rows. Apparently all the rows can be produced in a few seconds (as the use of "limit" proves).
It should be noted that invitj_rec is actually a view which consists of a UNION ALL of about 8 different SQL statements on various tables in the database.