Netezza User rowset Limit: Limiting Query Result set

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:3 mins read

If you execute the query which returns a large result set, then system may crash or may not respond properly. You can set the Netezza user rowset limit during query run. You can set a limit on the number of rows a query can return and thus restrict resources for large result sets.

Read:

Specifying a Netezza user rowset limit when you create a user or a group automatically limits the rows that are returned so that users do not have to append a limit clause to their SQL queries every time they run the query.

Netezza User rowset Limit Syntax

You can specify a Netezza user rowset limit when you create a user or group in Netezza performance server. ALTER command can also be used to limit the rowset of a user or a group which are already available in the Netezza server.

You can specify any number up to 2,147,483,647 or zero. If you specify the zero, then it is unlimited i.e. query can return as many rows as available in the table or matching conditions if any.

The Create User or Group with Netezza rowset Limit

CREATE USER username WITH ROWSETLIMIT [number | UNLIMITED];
CREATE GROUP name WITH ROWSETLIMIT [number | UNLIMITED];

The Alter User or Group to specify Netezza user rowset Limit

ALTER USER username WITH ROWSETLIMIT [number | UNLIMITED];
ALTER GROUP name WITH ROWSETLIMIT [number | UNLIMITED];

Overriding Netezza User rowset Limit

If your task is to perform INSERT TO … SELECT FROM or CREATE TABLE AS … SELECT operations, the Netezza user rowset limit can affect the results by limiting the number of rows that are inserted to the final resulting table.

If you are using above commands to create user tables, you can override the Netezza user rowset limit within your current user session to ensure that those queries complete with all the matching rows.

TRAINING.ADMIN(ADMIN)=> SET ROWSETLIMIT_LEVEL=0;
SET VARIABLE

TRAINING.ADMIN(ADMIN)=> SHOW ROWSETLIMIT_LEVEL;
NOTICE: ROWSETLIMIT_LEVEL is off

This override does not change the limit for other SELECT queries, or for INSERT TO … SELECT FROM or CTAS queries that write to external table destinations.

Tags: