SQL and PL-SQL New Features in Oracle11g

> Fine Grained Dependency Tracking: In 11g we track dependencies at the level of element within unit. so that these changes haveno consequence
• Transparent performance improvement
• Unnecessary recompilation certainly consumes CPU
create table t(a number)
create view v as select a from t
alter table t add(Unheard_Of number)
select status from User_Objectswhere Object_Name = 'V'- -----VALID
No recompilation of dependent objects when Columns added to tables OR Proceduresadded to packages

> Named and Mixed Notation from SQL:
select fun(P4=>10) from DUAL; In 10g not possible to call function in select statment by passing 4th parameter, but in 11g it is possible

> PL/SQL "continue" keyword - It is same as we read in c/c++ loop

> Support for “super”: It is same "super" in Java.

> Powerfull Regular Expression:
Now we can access data between TAGS like data between tags ......... The new built-in REGEXP_COUNT returns the number of times the pattern is matched in theinput string.

> New table Data Type "simple_integer"

> SQL Performance Analyzer(SPA) :
It is same as Database replay except it not capture all transaction.The SQL PerformanceAnalyzer (SPA) leverages existing Oracle Database 10g SQL tuning components. The SPAprovides the ability to capture a specific SQL workload in a SQL Tuning Set, take aperformance baseline before a major database or system change, make the desired changeto the system, and then replay the SQL workload against the modified database orconfiguration. The before and after performance of the SQL workload can then be comparedwith just a few clicks of the mouse. The DBA only needs to isolate any SQL statements thatare now performing poorly and tune them via the SQL Tuning Advisor

> Caching The Results with /*+ result_cache */ :
select /*+ result_cache */ * from my_table, New for Oracle 11g, the result_cache hint cachesthe result set of a select statement. This is similar to alter table table_name cache,but as youcan adding predicates makes /*+ result_cache */ considerably more powerful by caching asubset of larger tables and common queries.
select /*+ result_cache */ col1, col2, col3 from my_table where colA = :B1

> The compound trigger : A compound trigger lets you implement actions for each of the table DML timing points in asingle trigger

> PL/SQL unit source can exceeds 32k characters

> Easier to execute table DDL operations online: Option to wait for active DML operations instead of aborting

> Fast add column with default value: Does not need to updateall rows to default value.