ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Oracle 10g New Features in SQL

Updated on March 18, 2012

Oracle 10g New Features

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


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.


    0 of 8192 characters used
    Post Comment

    No comments yet.


    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at:

    Show Details
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)