xvi
■ You can specify the
DISTINCT
qualifier in scalar subqueries. For more information,
see "Subqueries" on page 3-6.
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. For more information, see "INSERT...SELECT" on
page 6-185.
■ 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" on page 6-48.
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" on page 6-146, "CREATE TABLE" on page 6-134, "ALTER TABLE" on
page 6-48, and "CREATE INDEX" on page 6-92 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" on page 1-22.
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" on
page 4-2.
■ 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" on page 6-221. For the new functions,
see "Aggregate functions" on page 4-4, "GROUP_ID" on page 4-34, "GROUPING"
on page 4-35, and "GROUPING_ID" on page 4-37.
■ Support for subquery factoring using the
WITH
clause in the
SELECT
statement. For
more details, see "SELECT" on page 6-201 and "WithClause" on page 6-211.
■ 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" on page 4-5.
■ You can specify the
AnalyticClause
in aggregate functions
AVG
,
COUNT
,
MAX
,
MIN
,
and
SUM
. For more information, see "Aggregate functions" on page 4-4 and
"Analytic functions" on page 4-5. See also the specific aggregate function, "AVG"