Oracle 10g New Features in SQL

Oracle 10g is release targetted on two goals : Manageability and performance

I have provided the highlights on SQL Enhancements here. And planning to post more related items here.

New features with SQL in 10g

• 10g Release provides support for case-insensitive queries and sorts, which facilitates to search and sort their data, Regardless of the casing and accent of the characters.
• Regular expressions are also supported in SQL and PL/SQL, allowing developers to write one-line queries that previously would have taken multiple lines of SQL code.
• Now SQL is POSIX-compliant and also supports multilingual queries and is locale sensitive.
• A new capability, Expression Filtering, is also supported in this release, that allows application developers to manage and evaluate conditional expressions that describe users' interests in data.
• Other SQL improvements include new CONNECT BY processing that supports ancestor-descendant pairs, and new collection performance and type evolution enhancements.

Case-Insensitive and Accent-Insensitive Query and Sort

10g has provided linguistic/accent based sorts and. Also you can write a sort or query on the base letters only (accent insensitive) or on the base letter and the accents (case insensitive).

New Data types - BINARY_DOUBLE, BINARY_FLOAT

These new types are single- and double-precision floating point number datatypes. With this types arithmetic calculations are faster and uses less storage. Also, you can implement algorithms with IEEE behavior for arithmetic operations. Intense number crunching computations (where scale and precision requests accommodated by the IEEE types) can run substantially faster using these types than using regular type NUMBER. Integration with XML and Java environments is an added merit(because it is now similar with Java/XML datatypes).

Collection Enhancements

Now collections are much morebetter in performance and functionality.

Now, You can use

• use varrays in temporary tables.
• Size of a VARRAY type object can be modified.
• Specify different tablespaces for different columns of nested table type.
• You can use comparison conditions (Equal, Not Equal) and ANSI Multiset operations with collection/nested variables.

CONNECT BY - Enhancements

Connect by has also added a bunch of new and useful features.

• All ancestor-dependent pairs can be returned (not just parent-child pairs) .
• A new pseudocolumn added to find whether a given node is a leaf or not.
• A cycle in a hierarchy triggers an error with details on rows afeected in the cycle Supports simple subqueries.

Expression Filter Enhancements

Expression Filter provides an Expression datatype, SQL EVALUATE operator, and indexing. This allows conditional expressions to be stored in a column of a table and searched. The EVALUATE operator checks incoming data with the expressions to find affected rows. You can sort Incoming data in a table and compare it with expressions of another table by using a join. Yo can easily make very complex relationships between tables.

Extensible Indexing

Ability to alter user-defined domain operators, is possible with Extensible Indexing. You can maintain global and local domain indexes during partition maintenance. This enables parallel creation of local domain indexes.

Regular SQL Expressions

This release supports POSIX-compliant regular expressions. This incerases search and replace capability in programming environments like Java. In SQL,this is implemented by using new functions in equivalent to existing functions (For instance LIKE and REPLACE).Also, supports multilingual queries and locale sensitive expressions.

SCN or Row Timestamp

This is a new pseudocolumn consisting of the date/time stamp (also called SCN - System change number) for last commit. You can efficiently implement optimistic locking by using this. Before 10g, we have to compare all columns with identical values of old row. Using SCN, this is simplified, just compare the row SCN to verify that the row has not changed after it is selected for a update.