What's New

This section lists new features for Release 11.2.2 that are documented in this reference and provides cross-references to additional information.

New features in Release 11.2.2.4.0

• For the INSERT...SELECT statement, you can reference the target table in the FROM clause or in a subquery.

• You can ALTER a table to add a NOT NULL column with a default value. The DEFAULT clause is required.

New features in Release 11.2.2.1.0

• 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.

New features in Release 11.2.2.0.0

• 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 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".

• Support for analytic specific functions, "DENSE_RANK", "FIRST_VALUE", "LAST_VALUE", "RANK", and "ROW_NUMBER".

• 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.

• Additional support for implicit data type conversion. See "Implicit data type conversion".

• 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.

• Support for the MONTHS_BETWEEN function was added, which is described in "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.

• Null values have been expanded. See "Null values" for full details.

• Support for the NULLIF function was added, which compares two expressions. See "NULLIF" for full details.

• 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.

• Support for SOUNDEX function. See "SOUNDEX".

• Support for TIMESTAMPADD and TIMESTAMPDIFF functions. See "TIMESTAMPADD" and "TIMESTAMPDIFF".