The following steps are an example of how to use server-side cursors and fetch-count together:
-
Enable server cursors by issuing the following command inside ACT (a setting of 1 turns cursors “on” and a setting of 0 means “off”):
beehive=> \set use-server-cursors 1
-
Set a fetch-count of 100:
beehive=> \set fetch-count 100
- Issue the following SQL query:
SELECT A,B,COUNT(*) FROM lineitem GROUP BY a, b;
This will actually cause the following statements to be run:
BEGIN; DECLARE x CURSOR FOR SELECT A,B,COUNT(*) FROM LINEITEM GROUP BY A,B; FETCH 100 FROM x; // 100 FETCH 100 FROM x; // 200 ... ... FETCH 100 FROM x; // until all the rows have been fetched CLOSE x; END;
Aster Database reports all the fetched rows as the row count for this query.