Features Introduced in Actian X 11.1
DBMS Server Enhancements
• Hybrid joins – Standard query syntax can be used to join Ingres and X100 tables in a SELECT, UPDATE, or DELETE statement. For information on restrictions, see the SQL Reference Guide.
• Support for X100 tables in database procedures – The following SQL statements, valid for Ingres tables, are now also supported for X100 tables:
– CREATE PROCEDURE
– DROP PROCEDURE
– DECLARE
– EXECUTE PROCEDURE
– FOR – ENDFOR
– IF-THEN-ELSE
– MESSAGE
– RAISE ERROR
– RETURN
– RETURN ROW
– WHILE – ENDWHILE
• 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 for Ingres tables – The LISTAGG function concatenates expressions within a group. This function can be used for both Ingres and X100 tables.
• 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.
• (Beta Version) 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 setting on the opf_autostats DBMS configuration parameter controls this feature. It can be enabled for Ingres tables, X100 tables, or both. The current default is X100.
Enhancements for X100
• Alterable min-max index for X100 tables – You can create or drop a min-max index for an X100 table with the ALTER TABLE...ADD|DROP MINMAX statement and add or drop columns to and from a min-max index for a table with the ALTER TABLE…ALTER MINMAX ADD|DROP COLUMN statement. In addition, you can use the WITH [NO]MINMAX_SAMPLES option on the CREATE TABLE [AS SELECT], DECLARE GLOBAL TEMPORARY TABLE, and ALTER TABLE…ADD MINMAX statements. A sampled min-max index is used by the optimizer when generating automatic histograms and allows the optimizer to produce better query plans faster.
• Nullable unique keys for X100 tables – Columns that you specify as unique or that you use as part of a table-level unique constraint can be nullable. There can be multiple rows with NULL. For multi-column keys (table-level unique constraint), uniqueness is enforced only on keys columns with non-null entries. Likewise, referential constraints are enforced only on non-null entries.
• vwload enhancements – Support for directory names and limited pattern matching in file names when specifying data files to load with vwload. Also, vwload now allows leading spaces before a null value in the data file and has a new option to keep empty strings for character columns that are NOT NULL (--notnull_empty).
• Performance improvements – Query performance against X100 tables is improved through internal features such as update propagation improvements, parallel build of shared hash tables, and unique string area.
Last modified date: 08/14/2024