Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Oracle 11g SQL New Features

By Saurabh Gupta | on May 15, 2011 |
PL/SQL

In continuation to my last tutorial on PL/SQL enhancements, I have compiled the list of enhancement pertaining to SQL. Similar to PL/SQL, Oracle had done considerable enhancements in SQL to raise performance level from instrumental to advisory. In the tutorial, we shall go through the performance oriented features, fresh additions and language enhancements.

Performance improvements

a. Alter table to add columns with default value

In Oracle 11g, we can alter a table to add a column with a default value. Earlier, a developer used to update the value for the new column using UPDATE statement. Refer the example code [1].

Example Code [1]
  1. ALTER TABLE EMPLOYEES
  2. ADD EMP_STATUS VARCHAR2(1) DEFAULT ‘D’ NOT NULL

The new feature surely reduces the overhead of UPDATE statement.

The table to be altered must not be a temporary table, object table or Index organized table and also it must not contain LOB columns.

b. Invisible Indexes

An index can now be created in invisible mode. A new initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES has been introduced to indicate Oracle optimizer to use invisible indexes or not. It can be set at system level as TRUE or FALSE.

Syntax [1a]: Create an Invisible Index
  1. CREATE OR REPLACE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME) INVISIBLE
Syntax [1b]: ALTER INDEX command to toggle the visibility mode
  1. ALTER INDEX INDEX_NAME [VISIBLE | INVISIBLE]

c. SQL Result cache

Oracle 11g server memory can now cache the SQL query results using RESULT_CACHE hint. In the first execution of an SQL statement using result cache hint, Oracle caches the result in server cache. On the subsequent executions of the same query for the same set of input values (if exist), oracle fetches the result from the cache instead of re-executing the query.

RESULT_CACHE_MODE parameter specifies the applicability of result cache feature in SQL queries. It accepts two admissible values namely, MANUAL and FORCE. It can be set at system or session level using ALTER command as in Example Code [2a]

Example Code [2a]
  1. ALTER SYSTEM SET RESULT_CACHE_MODE = MANUAL;
  2. ALTER SESSION SET RESULT_CACHE_MODE = FORCE;

For MANUAL mode, RESULT_CACHE hint must be specified with the SQL statements to use the feature. In FORCE mode, server enables the caching feature with all the SQL statements. Its usage is depicted in the below Example Code [2b]. Apart from this parameter, there are RESULT_CACHE_MAX_SIZE, RESULT_CACHE_MAX_RESULT, and RESULT_CACHE_REMOTE_ORIENTATION are also set for server cache feature implementation.

Example Code [2b]
  1. SELECT /*+ result_cache */ DEPARTMENT_ID, MAX(SALARY), 
  2. FROM EMPLOYEES
  3. GROUP BY DEPARTMENT_ID;

New Language features

a. Fresh Additions

1. New data types: SIMPLE_INTEGER, SIMPLE_FLOAT, SIMPLE_DOUBLE

Oracle 11g adds a new set of PL/SQL data types which are well compatible with real native compilation feature of 11g. Due to their fixed synchronization with the hardware, they provide faster arithmetic operations.

The new set of PL/SQL data type includes SIMPLE_INTEGER, SIMPLE_FLOAT, SIMPLE_DOUBLE to support binary integer and floating point values in applications.

A variable declared as SIMPLE_INTEGER doesn’t pass through the not null and value overflow checks followed by the server. Its value ranges from -2147483648 to 2147483648, same as that of PLS_INTEGER is Oracle 10g.

Example Code [3]: Demonstration of Overflow check in SIMPLE_INTEGER
  1. SET SERVEROUT ON
  2. DECLARE
  3. L_VAR SIMPLE_INTEGER := 2147483646;
  4. BEGIN
  5. WHILE (L_VAR < 10)
  6. LOOP
  7. L_VAR := L_VAR + 1
  8. DBMS_OUTPUT.PUT_LINE(L_VAR);
  9. END LOOP;
  10. END;

2147483647
-2147483648

2. Regular Expressions

REGEXP_COUNT is the new regular expression function, inducted in Oracle 11g release. Basically, the function came as language support enhancements because all major programming languages support functions which count the character or string appearances in a given string.

Example Code [3]: Demonstrate REGEXP_COUNT usage
  1. WITH C AS
  2. (SELECT ‘Sachin Tendulkar’ STARS FROM DUAL UNION ALL
  3. SELECT ‘Virender Sehwag’ STARS FROM DUAL UNION ALL
  4. SELECT ‘Gautam Gambhir’ STARS FROM DUAL UNION ALL
  5. SELECT ‘MS Dhoni’ STARS FROM DUAL UNION ALL
  6. SELECT ‘Yuvraj Singh’ STARS FROM DUAL)
  7. SELECT STARS, REGEXP_COUNT(STARS, ‘s’, 1, ‘i’) S FROM C;
  8.  
  9. STARS     S
  10. --------- --
  11. Sachin Tendulkar 1
  12. Virender Sehwag 1
  13. Gautam Gambhir 0
  14. MS Dhoni 1
  15. Yuvraj Singh 1

3. Read Only tables

In Oracle 11g, a table can be set as READ ONLY. In READ ONLY mode, a table can only be queried; DML and DDL (Truncate and Alter) operations are restricted on such tables. At any point of time in a session, the table mode can be switched from READ WRITE or READ ONLY mode and vice versa. Note that the table can be created in READ WRITE mode only.

Syntax [2]
  1. ALTER TABLE [TABLE NAME] [READ ONLY | READ WRITE]

4. Virtual columns

Oracle 11g allows database developers to create virtual columns in a table, whose value is always derived through an expression. The expression must use the columns of the same table or a deterministic function. Virtual Columns act as normal columns during indexing, and partitioning. As of now, virtual column must belong to primitive data type family and not to LOBs or collection data types.

Syntax [3]
  1. COLUMN [datatype] [GENERATED ALWAYS] AS ( <colu mn_expression> ) [VIRTUAL] [( 
  2. inline_constraint [,...] )]
Example Code [4]:

ST_PERC column in HIST_STUDENT table is a virtual column, which always generates using values of ST_MARKS and ST_MAX_MARKS.

  1. CREATE TABLE HIST_STUDENT(
  2. ST_ROLLNO NUMBER,
  3. ST_MARKS NUMBER,
  4. ST_MAX_MARKS NUMBER,
  5. ST_PERC NUMBER AS ((ST_MARKS/ST_MAX_MARKS)*100)
  6. );

5. Partitioning

Since its induction in Database family, partitioning has been always been an eye catcher for the database professionals. This is because of its comprehensive results in application performance and logical support to data warehousing. Oracle 11g has made considerable additions in Partitioning techniques. The fresh features in Partitioning include the following:

  • Interval partitioning
  • Extended composite partitioning
  • Reference partitioning
  • System partitioning
  • System-managed domain indexes

6. IGNORE_ROW_ON_DUPKEY_INDEX hint

During a direct loading Insert process, unique key conflict in data can result into process failure. To make this check passive during the process, Oracle 11g furnishes a new hint IGNORE_ROW_ON_DUPKEY_INDEX.

7. Analytic functions – NTH_VALUE, LISTAGG

Oracle 11g inducts two new functions in analytic family i.e. NTH_VALUE and LISTAGG.
LISTAGG aggregates a column values in a single row format. NTH_VALUE is an extended format of FIRST_VALUE and LAST_VALUE functions to get a random row from a grouped result set.

Syntax [4]
  1. LISTAGG - LISTAGG (measure_expr [, 'delimiter_expr']) WITHIN GROUP (ORDER 
  2. BY clause) [OVER PARTITION BYclause]
Example:

Aggregate the employee name working in a department

  1. NTH_VALUE - NTH_VALUE (measure_expr,n)[FROM {FIRST | LAST}]
  2. [{RESPECT | IGNORE} NULLS ]OVER (analytic_clause)
Example:

Retrieve 2nd highest Salary in each department

b. Enhancements to improve upon work around solutions and enhance usability

1. Sequence usage

In Oracle 11g, sequence value assignment can be used as a PL/SQL construct. Earlier, sequence value was used to be fetched through a SELECT statement, which added up the overhead of context switch between SQL and PL/SQL engines.

Example Code [5]:

Demonstration of Sequence assignment as a PL/SQL construct.

  1. DECLARE
  2. L_ID NUMBER;
  3. BEGIN
  4. L_ID:= TEST_SEQ.NEXTVAL;
  5. END;

2. Named and Mixed notation

Oracle 11g SQL now supports the function call with both named and positioned parameters. This is known as mixed notation of a function call in an SQL statement.

A function F_PRIMECOUNT in Example Code [6] accepts two parameters to count the numbers prime numbers between the ranges.

Example Code [6]
  1. SQL> SELECT F_PRIMECOUNT (P_A => 10, 20) FROM DUAL;

3. Skip locked

This is a utility enhancement in Oracle 11g, which enables the database users to query the records from a table, which are not part of any running transaction in any of the database session. The records which are locked are skipped in the final result set, retrieved by the SELECT query.

The SQL statement in Example Code [7] queries the unlocked records from EMPLOYEES table

Example Code [7]
  1. SELECT *
  2. FROM EMPLOYEES
  3. FOR UPDATE SKIP LOCKED

4. DATABASE_ROLE constant for SYS_CONTEXT

Oracle 11g added a new context constant to identify the current database role on the running server. The database role can be PRIMARY, PHYSICAL STANDBY, LOGICAL STANDBY, and SNAPSHOT STANDBY.

Example Code [8]
  1. SELECT sys_context('USERENV', 'DATABASE_ROLE') FROM dual;

5. NO_DATA_NEEDED exception

Oracle 11g adopted a generic exception NO_DATA_NEEDED for parallel access and pipelined table functions. Prior to 11g, this was identified by the error number ORA-06528

« « What is Alpha Testing
Different Types of People Skills » »

Author Description

Avatar

Free Training

RSSSubscribe 0 Followers
  • Popular
  • Recent
  • PL/SQL Native Compilation in Oracle 11g

    April 25, 2011 - 0 Comment
  • Download Free Oracle SQL Developer

    October 6, 2006 - 0 Comment
  • Oracle 11g PL/SQL New Features

    April 24, 2011 - 0 Comment
  • Oracle PL/SQL Tutorial

    December 25, 2007 - 0 Comment
  • PL/SQL Native Compilation in Oracle 11g

    April 25, 2011 - 0 Comment
  • Oracle 11g PL/SQL New Features

    April 24, 2011 - 0 Comment
  • Oracle PL/SQL Tutorial

    December 25, 2007 - 0 Comment
  • Download Free Oracle SQL Developer

    October 6, 2006 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • PL/SQL Native Compilation in Oracle 11g
  • Oracle 11g PL/SQL New Features
  • Oracle PL/SQL Tutorial
  • Download Free Oracle SQL Developer

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT