Hybrid Transaction and Analytics Processing
Ingres and X100
Ingres is a hybrid online transaction processing and analytics processing database solution.
Ingres lets you create (traditional) Ingres tables (with BTREE, HEAP, HASH, or ISAM storage structure) and X100 tables (with X100 or X100_ROW storage structure).
Traditional Ingres is targeted at online transaction processing applications, while X100 is targeted at analytical database applications.
The difference between traditional Ingres and X100 is that the data stored in X100 tables is processed using the specialized X100 engine, allowing much higher performance for analytical database tasks. X100 is optimized to work with both memory- and disk-resident datasets, allowing it to efficiently process large amounts of data (hundreds of gigabytes). Its vectorised processing, compression, and columnar data layout allow analytical queries to be run fast on a single server.
X100 tables in Ingres can be used for reporting and analysis of your OLTP databases.
While a database can contain a mixture of both traditional Ingres and X100 tables, Ingres does not support mixing both types of tables in the same query. You can, however, easily move data between Ingres and X100 tables directly and create tables using CREATE TABLE AS SELECT or INSERT...SELECT statements.
The following illustrates the hybrid transaction and analytics processing capabilities of Ingres.
X100 Compatibility with Ingres
X100 is integrated into the Ingres framework. X100 tables, however, do not necessarily support all the functionality supported by traditional Ingres tables.
X100 SQL Elements
On the SELECT statement, the following clauses and features are supported:
• WITH common_table_expression
• FIRST rowcount
• ALL, DISTINCT
• GROUP BY
• HAVING
• UNION
• UNION ALL
• INTERSECT
• EXCEPT
• ORDER BY
• Derived tables in FROM clause
• Subqueries (with limitations)
• Joins between X100 tables
• Joins between Ingres tables
• ANSI joins
Note: The CREATE INDEX statement is supported on X100 tables and can be issued only after the table has been created. One clustered index and multiple secondary indexes can be created.
Some SQL elements have restrictions when used in X100. For more information, see X100 Incompatibilities with Ingres.
X100 Unsupported Features
X100 does not support the following features:
• Collation sequences, other than the UCS_BASIC column collation in tables stored with a X100 structure
• User-defined data types and functions
• The legacy language QUEL
• SQL procedures, rules, and some DDL statements
Note: An unsupported function will cause the query to fail only if the function is not resolved (converted to a constant result) during query parsing or optimization.
X100 Incompatibilities with Ingres
The following sections describe some of the major restrictions and incompatibilities between X100 and traditional Ingres.
SQL Cursors
X100 supports read-only scrollable cursors. Cursors on X100 queries cannot be used for update. Only one cursor can be open per connection.
Some Subqueries Do Not Work
Some specific complex forms of subqueries that are supported in traditional Ingres are not supported. Subqueries that do not successfully flatten and compile to a valid X100 syntax produce the E_OP08C2 error. Try to rewrite these queries.
Scalar Subquery Cardinality Check
X100 does not perform a cardinality check on scalar subqueries. Make sure the scalar subquery returns no more than one row.
Cross-system Queries
SELECT queries that access both X100 and traditional Ingres tables cannot execute. To cross join data in traditional Ingres and X100 you have to move the data either into traditional Ingres or into X100 using INSERT…SELECT or CREATE TABLE AS SELECT.
SQL Data Type Incompatibilities
The following incompatibilities between X100 and traditional Ingres are known:
• X100 does not check for overflow on operations on floating point types. (Operations on integer types and decimal types are checked for overflow.)
• TIMESTAMP and INTERVAL DAY TO SECOND can only store 6 decimal places (fractions of seconds), compared to 9 in traditional Ingres.
• X100 supports only the YYYY-MM-DD date format and the YYYY-MM-DD hh:mm:ss.[fffff] timestamp format in SQL. (The vwload utility, however, supports all date formats supported by traditional Ingres.) Use DATE_FORMAT() or TO_CHAR() to change the display of date and time values and STR_TO_DATE() or TO_DATE() or /TO_TIMESTAMP() to convert strings into date and time values.
• Conversion from floating point types to character types can yield slightly different results than in traditional Ingres.
• The MONEY currency symbol is fixed to '$'.
• The ASCII null character ('\0\') is not supported in char and varchar data types.
SQL Function Incompatibilities
The following restrictions or incompatibilities between X100 and traditional Ingres are known:
• X100 supports constant LIKE patterns only. The LIKE predicate does not support a range of characters (separated by a dash within escaped brackets).
• The BEGINNING, CONTAINING, and ENDING predicates do not support the ESCAPE clause.
• The HASH function returns different results in X100 than in traditional Ingres due to a different hash computation algorithm.
• Using the RANDOM function after issuing SET RANDOM_SEED will not return the same results for X100 and Ingres tables. Furthermore, it may not return the same results for every X100 query.
Differences in Constraint Checking
Constraint checking in X100 differs from traditional Ingres in the following ways:
• When loading data with COPY, if a constraint violation is detected, the entire dataset is rejected.
• When loading data with COPY, X100 detects foreign key (referential) constraint violations, unlike traditional Ingres.
• X100 does not handle cyclic constraints.
• Because of the optimistic concurrency control model (see Transaction Isolation Model on page 22) used by X100, it is possible that a constraint violation is only detected at commit time.
Last modified date: 01/30/2023