What's New

Support is added for constant expressions, dynamic parameters, and nulls in the values lists used for IN, ANY, SOME, or ALL. For more information, see "ALL / NOT IN predicate (value list)" and "ANY / IN predicate (value list)".

When using the WITH clause, you can specify the set operators UNION, MINUS, and INTERSECT in the main query. For more information on the WITH clause, see "WithClause".

You can use the CREATE [UNIQUE] HASH INDEX statement to create a hash index. For more information on creating hash indexes, see "CREATE INDEX".

You can use statement level optimizer hints in SQL statements. For more information on statement level optimizer hints, see "Statement level optimizer hints".

For the CREATE TABLE statement, you can specify the keyword ENABLE as part of the NOT NULL constraint in a column definition. For more information, see "Column Definition".

You can specify more than one subquery in the SET clause of the UPDATE statement. You can also specify a SELECT DISTINCT subquery in the SET clause of the UPDATE statement. For more information on the UPDATE statement, see "UPDATE".

You can use the SET COMMIT_BUFFER_SIZE_MAX clause of the ALTER SESSION statement to set the maximum size of the commit buffer when a connection is in progress. For more information, see "ALTER SESSION".

You can specify the COMMIT EVERY n ROWS clause in the UNLOAD CACHE GROUP statement. For more information, see "UNLOAD CACHE GROUP".

You can specify the TABLE DEFINITION CHECKING clause as a StoreAttribute for both the CREATE ACTIVE STANDBY PAIR and the ALTER ACTIVE STANDBY PAIR statements. For more information, see "CREATE ACTIVE STANDBY PAIR".

You can specify the DISTINCT qualifier in scalar subqueries. For more information, see "Subqueries".

For the INSERT...SELECT statement, you can reference the target table in the FROM clause or in a subquery. For more information, see "INSERT...SELECT".

You can ALTER a table to add a NOT NULL column with a default value. The DEFAULT clause is required. For more information on the ALTER TABLE statement, see"ALTER TABLE".

Support for in-memory columnar compression of tables. Compression is defined at the column level, which stores the data more efficiently. Eliminates redundant storage of duplicate values within columns and improves the performance of SQL queries that perform full table scans. See "In-memory columnar compression of tables", "CREATE TABLE", "ALTER TABLE", and "CREATE INDEX" for details on table compression support defined by each of these statements.

Support for LOB (large object) data types. This includes CLOB (character LOB), NCLOB (national character LOB), and BLOB (binary LOB) data types. For more details, see "LOB data types".

Support for LOBs was added to the CREATE TABLE, SELECT, INSERT, and UPDATE SQL statements. LOBs are also supported in the LIKE and IS [NOT] NULL operators and the REPLACE, LOWER, UPPER, TRIM, LTRIM, RTRIM, ASCIISTR, INSTR, INSTRB, INSTR4, SUBSTR, SUBSTRB, SUBSTR4, NLSSORT, LPAD, RPAD, TO_DATE, TO_NUMBER, TO_CHAR, LENGTH, LENGTHB, CONCAT and NVL functions.

Support for the following LOB functions: EMPTY_CLOB, EMPTY_BLOB, TO_LOB, TO_CLOB, TO_NCLOB, and TO_BLOB. For more information, see "LOB functions".

Support for the GROUP BY statement: In this release, support was added for the GROUPING SETS, ROLLUP and CUBE clauses. In addition, the GROUPING, GROUPING_ID, and GROUP_ID functions were also added. For details on the new clauses for the GROUP BY statement, see "GROUP BY clause". For the new functions, see "Aggregate functions", "GROUP_ID", "GROUPING", and "GROUPING_ID".

Support for subquery factoring using the WITH clause in the SELECT statement. For more details, see "SELECT" and "WithClause".

The SQL functions were broken out of the "Expressions" chapter and are now located in alphabetical order in the new Chapter 4, "Functions". Included in this re-organization, all of the functions listed within the Aggregate functions, String functions, and User and session functions were added to the alphabetical list.

Support for analytic functions. See "Analytic functions".

You can specify the AnalyticClause in aggregate functions AVG, COUNT, MAX, MIN, and SUM. For more information, see "Aggregate functions" and "Analytic functions". See also the specific aggregate function, "AVG", "COUNT", "MAX", "MIN", and "SUM".

You can use cache grid functions to determine the location of data in a cache grid and then execute a query for the information from that node. See "Cache grid functions" for details.

TimesTen provides additional support for implicit data type conversion. See "Implicit data type conversion".

TimesTen provides additional support for datetime arithmetic. See "Datetime and interval data types in arithmetic operations".

You can specify DISTINCT in an aggregate function to consider only distinct values of the argument expression. See "Aggregate functions" for details.

You can use character strings, columns, expressions, results from a function, or any combination in either the source or the pattern within the LIKE predicate. See "LIKE predicate" for more details.

TimesTen supports the MONTHS_BETWEEN function. For more information, see "MONTHS_BETWEEN".

You can use NLS character set functions to retrieve the character set name or ID number. See "NLS character set functions" for details.

TimesTen extends support for null values. For more information, see "Null values".

TimesTen supports the NULLIF function. For more information, see "NULLIF".

You can use NULLS FIRST or NULLS LAST in your ORDER BY clause. For more information, see "SELECT".

Range indexes used to be referred to as T-tree indexes. Now all output and commands use range as the identifying terminology. For example, in "NLSSORT", the output shows range indexes, such as "non-unique range index on columns."

Support for the REPLACE function was added, which substitutes a sequence of characters in a given string with another set of characters or removes the string entirely. See "REPLACE" for details.

TimesTen supports the SOUNDEX function. See "SOUNDEX".

TimesTen supports the TIMESTAMPADD and TIMESTAMPDIFF functions. See "TIMESTAMPADD" and "TIMESTAMPDIFF".