Category Archives: Oracle 12c New Features

APPROX_COUNT_DISTINCT in Oracle 12c.

Most of time during performance analysis to understand Stale stats, we usually need to check estimated NDV (Number of Distinct values). If data-set is huge , it take considerable time to project desired output using traditional COUNT(DISTINCT) appraoch. For any … Continue reading

Rate this:

Posted in Oracle 12c New Features, Oracle Internal, Oracle Performance | Tagged , , , | Leave a comment

Lateral Views in Oracle 12c.

#TGIF. In Oracle 12c, we got introduce to LATERAL Views officially(Before same was getting use internally as part of Query Transformation) to join Inline views, Pre 12c we were not able to achieve same of joining inline views. With Lateral … Continue reading

Rate this:

Posted in Oracle 12c New Features, Oracle Internal | Tagged , , | Leave a comment

Interesting New WITH Clause Feature in Oracle 12c.

#TGIF In Oracle 12c, WITH clause is capable to have PL-SQL type Function or procedure inline with underlying SQL. Function or Procedural within WITH clause is scope till SQL Execution lifetime and cease to exist after execution completion. Having Function … Continue reading

Rate this:

Posted in Oracle 12c New Features | Tagged , , , , , , | Leave a comment

Oracle 12c : Default values on NULL.

In Oracle, DEFAULT provide an option to have any default expression when column data is not specified during data manipulations. During Table creation or using Alter we can provide DEFAULT value for column. While data manipulation we can use “default” … Continue reading

Rate this:

Posted in Oracle 12c New Features | Tagged , , | Leave a comment

Oracle 12c TOP N Pagination performance considerations.

In my post on TOP N and Pagination in Oracle 12c highlighted features introduce. In current post will try to compare performance and cardinality estimations for a scenario between INLINE ORDER BY + OUTER ROWNUM and TOP N IN 12C. … Continue reading

Rate this:

Posted in Oracle 12c New Features, Oracle Internal | Tagged , , , , , | 1 Comment

Dynamic Statistics in Oracle 12c

Dynamic statistics allow the optimizer to augment existing statistics to get more accurate cardinality estimates for not only single table accesses but also for joins and group-by predicates. The results of the dynamic sampling queries will be persisted in the … Continue reading

Rate this:

Posted in Oracle 12c New Features | Tagged , , , , | Leave a comment

TOP N and Pagination in Oracle 12c

Other than Oracle, most of OLTP Databases already had set of SQL syntax for fetching Top N. In Oracle Database 12c includes support for the ANSI-standard FETCH FIRST/NEXT and OFFSET clauses—together called the row limiting clause. Syntax for the same … Continue reading

Rate this:

Posted in Oracle 12c New Features | Tagged , , , | 3 Comments

Online Statistics Gathering in Oracle 12c

Online Statistics Gathering, As name implies in oracle we can get Stats automatically Gathered on a object while object is getting created in schema. For online stats , Oracle Carry on Data which were scan\access during Data load in case of … Continue reading

Rate this:

Posted in Oracle 12c New Features | Tagged , , | Leave a comment

Scalar Subquery Unnesting in Oracle 12c.

A Scalar Subquery is a subquery that appear in select clause and that will return only one records having one column.For record return more then one, it is bound be to give error. select co1, (select max(col1) from table2 where … Continue reading

Rate this:

Posted in Oracle 12c New Features | Tagged , , | 3 Comments