What's New in the SQL Language Reference?

This section describes new features of Oracle Database 11g and provides pointers to additional information.

For information on features that were new in earlier versions of Oracle Database, refer to the documentation for the earlier release.

Oracle Database 11g Release 2 (11.2.0.2) New Features in the SQL Language Reference

New Features in the SQL Language Reference

The following top-level SQL statements are enhanced in this release:

• CREATE TABLE and ALTER TABLE now support the clause deferred_segment_creation for partitions and subpartitions. This lets you postpone creation of a segment until the first row of data is inserted into the partition or subpartition.

• TRUNCATE TABLE has a new clause DROP ALL STORAGE that lets you deallocate all segments for a table. ALTER TABLE has a new clause DROP ALL STORAGE that lets you deallocate all segments for a partition or subpartition.

Oracle Database 11g Release 2 (11.2.0.1) New Features in the SQL Language Reference

Structural Changes in the SQL Language Reference

A number of sections of this book that were make up primarily of PL/SQL were moved to Oracle Database PL/SQL Language Reference in Oracle Database 11g Release 1. Please refer to "Structural Changes in the SQL Language Reference" for details on this migration of material.

New Features in the SQL Language Reference

The following top-level SQL statements are new or enhanced in this release:

• A new top-level SQL statement ALTER DATABASE LINK lets you update the fixed user password in a database link when the password of a connection or authentication user has changed.

• The ALTER DISKGROUP statement has the following changes:

  • A new disk_region_clause lets you determine the Intelligent Data Placement attribute of the disk group file.

  • New diskgroup_volume_clauses let you manipulate logical Oracle Automatic Storage Management (Oracle ASM) volumes corresponding to physical volume devices.

  • Several new clauses let you control access to Oracle ASM files: usergroup_clauses, user_clauses, file_permissions_clause, and file_owner_clause.

• AUDIT and NOAUDIT contain a new clause ALL STATEMENTS that lets you enable and disable auditing of all top-level SQL statements executed. In AUDIT only, a new clause IN SESSION CURRENT allows you to limit auditing to the current session.

• CREATE DISKGROUP and ALTER DISKGROUP have the following changes:

  • A new clause QUORUM | REGULAR let you designate a disk or failure group as a quorum disk or failure group, which can contain the voting file for Cluster Synchronization Services (CSS).

• Two new statements, CREATE EDITION and DROP EDITION, let you use editions. An edition makes it possible to have two or more versions of the same editionable objects in the database.

• CREATE INDEXTYPE and ALTER INDEXTYPE have a new clause WITH SYSTEM MANAGED STORAGE TABLES. This clause makes it possible to create domain indexes in both range- and list-partitioned tables.

• ALTER SESSION contains a new clause SYNC WITH PRIMARY that lets you synchronize the physical standby database with the primary database. A new session parameter STANDBY_MAX_DATA_DELAY lets you specify a session-specific apply lag tolerance for queries to a physical standby database that is in real-time query mode.

• CREATE MATERIALIZED VIEW LOG has a new clause COMMIT SCN that instructs the database to use commit SCN data instead of timestamps to refresh the materialized view, which improves the speed of the refresh.

• CREATE MATERIALIZED VIEW LOG and ALTER MATERIALIZED VIEW LOG have a new clause mv_log_purge_clause that lets you specify the purge time for the materialized view log.

• CREATE TABLE and ALTER TABLE are enhanced in the following ways:

  • A new clause deferred_segment_creation lets you postpone creation of the table segment until the first row of data is inserted into the table. This clause is also applicable to materialized views.

  • The clause table_compression has new syntax and terminology. Use COMPRESS FOR OLTP to specify OLTP table compression. (In earlier releases, the syntax was COMPRESS FOR ALL OPERATIONS.) Use COMPRESS BASIC to specify basic table compression. (In earlier releases, the syntax was COMPRESS FOR DIRECT_LOAD OPERATIONS and this type of compression was called DSS table compression.)

  • A new RESULT_CACHE Clause lets you specify whether the results of queries that name the table are considered for result caching.

  • The nested_table_col_properties provides a LOCAL keyword to equipartition a nested table with partitioned base table. This is the default behavior in this release. The default in earlier releases was not to equipartition the nested table with the partitioned base table. Now you must specify the GLOBAL keyword to store an unpartitioned nested table with a partitioned base table.

• The CREATE VIEW has a new keyword EDITIONING that lets you create an editioning view.

• The statement GRANT has a new EXECUTE object privilege on directory objects. The ORACLE_LOADER access driver for external tables references this privilege when deciding whether to execute a preprocessor program.

The following built-in functions are new or enhanced in this release:

• For a specified measure, the function LISTAGG orders data within each group specified in an ORDER BY clause and then concatenates the values of the measure column.

• A new NTH_VALUE function returns the value of a measure in a specified row of a window of data.

• Three new functions are useful when you are changing the time zone data file for your database: ORA_DST_AFFECTED, ORA_DST_CONVERT, and ORA_DST_ERROR.

The following miscellaneous features are new or enhanced in this release:

• Hints, which were introduced in Oracle7, are now superseded by several Oracle tools, including the SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer. See "Hints" for more information.

• Beginning with Oracle Database 11g Release 2, the PARALLEL, PARALLEL_INDEX, NO_PARALLEL, and NO_PARALLEL_INDEX hints are statement-level hints and supersede the earlier object-level hints. See "Note on Parallel Hints".

• A new APPEND_VALUES Hint lets you use direct-path INSERT with the VALUES clause.

• When specifying a redo log file, you can use the new keyword BLOCKSIZE Clause to override the operating system-dependent sector size.

• The LOB_compression_clause now has a new LOW setting, which results in significantly higher decompression and compression speeds, at the cost of a slightly lower compression ratio.

• The subquery_factoring_clause now supports recursive subquery factoring (recursive WITH), which lets you query hierarchical data. This feature is more powerful than CONNECT BY in that it provides depth-first search and breadth-first search, and supports multiple recursive branches. A new search_clause and cycle_clause let you specify an ordering for the rows and mark cycles in the recursion.

Oracle Database 11g Release 1 New Features in the SQL Language Reference

Structural Changes in the SQL Language Reference

A number of SQL statements are constructed almost entirely of PL/SQL elements. Those statements continue to appear in this reference, but the bulk of their syntax and semantics has been moved to Oracle Database PL/SQL Language Reference. The following table contains links to both the abbreviated SQL syntax and semantics in this book and to the full syntax and semantics in Oracle Database PL/SQL Language Reference.

New Features in the SQL Language Reference

The following top-level SQL statements are new or enhanced in this release:

• ALTER DATABASE has been enhanced as follows:

  • The clause managed_standby_recovery has been greatly simplified. A number of subclauses have been deprecated as the database now handles much of the recovery process automatically.

  • The supplemental_db_logging contains new syntax that lets you enable or disable supplemental logging of PL/SQL calls.

  • The standby_database_clauses have new syntax that lets you convert a physical standby database into a snapshot standby database or convert a snapshot standby database into a physical standby database.

  • The clause managed_standby_recovery has new KEEP IDENTITY syntax that lets you use the rolling upgrade feature provided by a logical standby and also revert to the original configuration of a primary database and a physical standby.

• ALTER DISKGROUP has been enhanced as follows:

  • The check_diskgroup_clause has simplified syntax for checking the consistency of disk groups, disks, and files in an Oracle ASM environment.

  • The clause diskgroup_availability offers new options when mounting a disk group.

  • New clauses disk_offline_clause and disk_online_clause let you take a disk offline for repair and then bring it back online.

• ALTER INDEX has been enhanced as follows:

  • A new MIGRATE parameter lets you migrate a domain index from user-managed storage tables to system-managed storage tables.

  • A new INVISIBLE parameter lets you modify an index so that it is invisible to the optimizer.

  • The "PARAMETERS Clause" now lets you rebuild an XMLIndex index as well as a domain index.

• ALTER SYSTEM has been enhanced as follows:

  • New syntax lets you kill a session on another instance in an Oracle Real Application Clusters (Oracle RAC) environment.

  • New rolling_migration_clauses let you prepare an Oracle ASM cluster for migration and return it to normal operation after all nodes have migrated to the same software version.

• ALTER TABLE has been enhanced as follows:

  • The behavior of the add_column_clause when you specify a DEFAULT value has been enhanced for improved performance.

  • The syntax for READ ONLY | READ WRITE lets you put a table into read-only mode, to prevent DDL or DML changes during table maintenance, and then back into read/write mode.

  • The clause add_table_partition has expanded syntax to let you add a system partition.

  • The flashback_archive_clause lets you enable or disable historical tracking for the table.

  • The add_column_clause now lets you add a virtual column to a table.

  • A new clause alter_interval_partitioning lets you convert a range-partitioned table to an interval_partitioned table.

  • A new dependent_tables_clause lets you instruct the database to cascade various partition maintenance operations on a table to reference-partitioned child tables.

• ALTER TABLESPACE has new syntax that lets you shrink the space taken by a temporary tablespace or an individual temp file.

• ASSOCIATE STATISTICS has syntax that lets you specify that the database should manage storage of statistics collected on a system-managed domain index.

• AUDIT has new syntax that lets you audit various activities on data mining models.

• CALL now permits positional, named, and mixed notation in the argument to the routine being called, if the routine takes any arguments.

• COMMENT has a new MINING MODEL clause lets you provide descriptive comments for a data mining model.

• CREATE DISKGROUP and ALTER DISKGROUP have new syntax that lets you set various attributes of a disk group.

• The new statements CREATE FLASHBACK ARCHIVE, ALTER FLASHBACK ARCHIVE, and DROP FLASHBACK ARCHIVE let you create, modify, and drop flashback data archives, which in turn let you track historical changes to tables.

• CREATE INDEX has been enhanced as follows:

  • A new local_domain_index_clause lets you create a locally partitioned domain index.

  • The index_attributes have been modified to let you create an index that is invisible to the optimizer.

  • A new XMLIndex_clause lets you create an XMLIndex index for XML data.

• CREATE INDEXTYPE and ALTER INDEXTYPE let you specify that domain indexes built on the subject indextypes can be range partitioned, and will have their storage tables and partition maintenance operations managed by the database.

• CREATE PFILE has new syntax that lets you create a parameter file from current system-wide parameter settings.

• CREATE RESTORE POINT has new syntax that lets you create a restore point for a specified datetime or SCN in the past, and to preserve a flashback database.

• CREATE SPFILE has new syntax that lets you create a system parameter file from current system-wide parameter settings.

• CREATE TABLE has been enhanced as follows:

  • The flashback_archive_clause lets you create the table with tracking of historical changes enabled

  • The clause system_partitioning lets you partition the table BY SYSTEM

  • A new virtual_column_definition lets you create a virtual column.

  • New syntax for XML storage lets you store XML data in binary XML format.

  • A new clause reference_partitioning lets you partition a table by reference to another partitioned table.

  • The LOB_parameters now include a SECUREFILE parameter, which lets you specify a new storage for LOBs that is faster, more efficient, and allows for new features such as LOB compression, encryption, and deduplication.

  • A new LOB_compression_clause lets you enable or disable server-side LOB compression for LOBs using SecureFile storage.

  • A new LOB_deduplicate_clause lets you coalesce duplicate data into a single shared repository, reducing storage consumption and simplifying storage management for LOBs using SecureFile storage.

  • The LOB_parameters now include ENCRYPT and DECRYPT clauses to enable and disable encryption of LOB columns for LOBs using SecureFile storage.

• CREATE TABLESPACE has new syntax which, along with a new ENCRYPT keyword in the storage_clause, lets you encrypt an entire tablespace.

• DROP DISKGROUP has a new FORCE keyword that lets you drop a disk group that can no longer be mounted by an Oracle ASM instance.

• GRANT contains several new system and object privileges that enable the grantee to work with data mining models.

• LOCK TABLE has new syntax that lets you specify the maximum number of seconds the statement should wait to obtain a DML lock on the table.

• MERGE now supports operations on tables with domain indexes.

• SELECT has new PIVOT syntax that lets you rotate rows into columns. A new UNPIVOT operation lets you query data to rotate columns into rows.

The following SQL built-in functions have been added or enhanced:

• CUBE_TABLE is a new built-in function that extracts data from a cube or dimension and returns it in the two-dimensional format of a relational table.

• INSERTXMLAFTER let you add one or more nodes of any kind immediately after a target node that is not an attribute node.

• REGEXP_INSTR and REGEXP_SUBSTR now have an optional subexpr parameter that lets you target a particular substring of the regular expression being evaluated.

• REGEXP_COUNT is a new built-in function that counts the number of occurrences of a specified regular expression pattern in a source string.

• PREDICTION, PREDICTION_COST, and PREDICTION_SET have been enhanced. New syntax let you specify that the stored cost matrix should be used only if it is available, or to specify a cost matrix inline.

• PREDICTION_BOUNDS is a new function that returns the lower and upper confidence bounds for a prediction.

• XMLCAST and XMLEXISTS are two new functions that let you cast XML data to SQL scalar data types and determine whether an XQuery expression returns a nonempty XQuery sequence, respectively.

• XMLDIFF and XMLPATCH are two new functions that provide SQL interfaces to the corresponding XMLDiff and XMLPatch C APIs. They let you compare two XMLType documents and use the diff file to patch an XMLType document.

The following miscellaneous changes have been made:

• In earlier releases, one form of expression in Chapter 6, "Expressions" was the variable expression. This form has been renamed to placeholder expression for consistency with other books in the documentation set. See "Placeholder Expressions".

• In earlier releases, the TRUNCATE statement was presented as a single statement with separate syntactic branches for TABLE and CLUSTER. That command has now been divided into TRUNCATE CLUSTER and TRUNCATE TABLE for consistency with other top-level SQL statements. No actual syntax or semantic changes have occurred.

• Two new hints, "RESULT_CACHE Hint" and "NO_RESULT_CACHE Hint", let you override settings of the RESULT_CACHE_MODE initialization parameter.

• "Function Expressions" now permit positional, named, and mixed notation in the argument to a user-defined function being used as an expression.

• The index_partition_description syntax of ALTER TABLE and ALTER INDEX now lets you specify parameters for a partition of a domain index.

• A new object type object type is supported with Oracle Multimedia. See ORDDicom