New Features in Ingres 11.2
DBMS Server Enhancements
• Partitioned table management – New syntax on the MODIFY statement lets you split, merge, or drop partitions of a table. The ability to redefine partitions can be needed for large data sets to improve performance and availability. The new MODIFY actions are:
– MODIFY...PARTITION...TO SPLIT splits a partition into two or more partitions
– MODIFY...PARTITION...TO MERGE merges two or more partitions into fewer partitions
– MODIFY...PARTITION...TO DROP drops a partition and deletes its data
For more information, see the SQL Reference Guide.
• Column masking – The MASKED attribute can be assigned to columns so that unprivileged users cannot view the data. The MASKED [AS {BASIC | NULL | 0 | ‘ ‘ }] column attribute can be used on CREATE TABLE and ALTER TABLE. The UNMASK subject privilege, when assigned, lets the user see the data. A user with the UNMASK privilege can use the SQL function MASK_COLUMN(expr AS {BASIC | NULL | 0 | ‘ ‘ | UNMASK}) in views to selectively unmask data, and control how that data can be interacted with and presented, and who can access it.
• JSON support – Users can store, access, and generate JSON data. JSON data must be stored in the database using an existing string column type of char, varchar, long varchar, byte, varbyte, or long byte. The installation must use the UTF8 character set.
• CREATE STATISTICS and DROP STATISTICS – The CREATE STATISTICS statement creates statistics and histograms on specified tables and columns. It has the same effect as optimizedb but allows applications to manipulate statistics from within a transaction.
• LISTAGG function – The LISTAGG function concatenates expressions within a group.
• olfc_notify DBMS configuration parameter – The olfc_notify resource specifies whether notifications should be issued when ON_LOGFULL=COMMIT is triggered. The default is OFF. Setting this resource to ON causes ON_LOGFULL=COMMIT to be treated like ON_LOGFULL=NOTIFY.
• ALTER TABLE DROP CONSTRAINT IF EXISTS - IF EXISTS suppresses error reporting for the specified constraint if it does not exist and the user matches the schema.
• Pivot tables – The PIVOT SQL table reference can be used to create a pivot table, which summarizes and groups data selected from a table.
• Automatic statistics generation – Automatic histogram generation removes the need to generate statistics for proper query execution. Histograms are automatically generated on all columns that appear in WHERE clauses and do not already have a histogram stored in the catalog. This feature gives you more flexibility in managing statistics. The opf_autostats DBMS configuration parameter setting controls this feature.
• Scalar user-defined functions (UDFs) - Custom scalar functions can be created for use in queries to extend database functionality. The CREATE FUNCTION statement defines a function and DROP FUNCTION removes it. Functions can be written in SQL For details, see Scalar User-defined Functions, CREATE FUNCTION, and DROP FUNCTION in the SQL Reference Guide.
Note: SQL UDFs are a development release. Please work with Actian Support before using such UDFs in a production environment.
• Workload management - The following features help to manage workload of the database:
– Control the access mode for the database – You can disallow insert, update, delete, truncate, copy, and DDL operations using the GRANT READONLY statement.
– Limit row count – The database privilege QUERY_ROW_LIMIT n can be granted to a role, user, group, or public to limit the number of rows returned in a query. This can be set at the session level using SET ACTUAL MAXROW n.
– Abort queries based on defined limits – The database privilege QUERY_ROW_STEP_LIMIT n can be granted to a role, user, group, or public to specify the number of rows an individual query step is allowed to return before the query is canceled. This can be set at session level using SET MAXROWSTEP n.
For details, see Database Privileges in the SQL Reference Guide.
• Reverse strings - The REVERSE() function reverses the order of characters in a string. For details, see String Functions in the SQL Reference Guide.
Last modified date: 11/28/2023