Analytical SQL

Included in Oracle Database 12c is a compelling array of analytical features and functions that are accessible through SQL.

  Key Benefits Overview
  Technical Information Training
  Oracle OpenWorld Keep in Touch
  Related Technologies    

Learn SQL
Analytical SQL
Learning Library
SQL 101
Analytical SQL

White Paper:
Analytical SQL in Database 12c

pattern matching and spatial analytics

Key Benefits of Analytical SQL

The key benefits provided by Oracle's in-database analytical functions and features are:

Enhanced Developer Productivity - perform complex analyses with much clearer and more concise SQL code. Complex tasks can now be expressed using single SQL statement which is quicker to formulate and maintain, resulting in greater productivity.

Improved Query Speed - processing optimizations supported by in-database analytics enable significantly better query performance. Actions which before required self-joins or complex procedural processing may now be performed in native SQL.

Improved Manageability - ability to access a consolidated view of all data types and sources is simplified when applications share a common relational environment rather than a mix of calculation engines with incompatible data structures.

Minimized Learning Effort - SQL analytic functions minimize the need to learn new keywords because the syntax leverages existing well-understood keywords.

Industry standards based syntax - Oracle's features conform to ANSI SQL standard and are supported by a large number of independent software vendors.

Back to top


The in-database analytical functions features that are embedded inside the Oracle Database can be used to answer a wide variety of business problems. Developers and business users can access a wide range of analytic features and combine their results with other SQL queries and analytical pipelines to gain deeper insights.

Oracle's Database includes the following features:
  • Ranking
  • Windowing
  • Reporting Aggregates
  • Inverse Percentile
  • Hypothetical Rank and Distribution
  • Pattern Matching
  • Modeling
  • Advanced aggregations
  • User defined functions
Standards Based SQL
In 1986, SQL became a standard of the American National Standards Institute (ANSI) and since then it has advanced to its current iteration, ANSI 2011. This standardization has two major benefits:
- the standard provides a high degree of application portability across different database systems without major code changes. In the field of data warehousing, BI tools are able to effectively support multiple types of SQL databases in a straightforward manner.

- the SQL standard has ensured continuity in application development. A SQL statement written thirty years ago continues to run today, without any modification to the SQL code.

Continuous Evolution
Oracle has a long history of embedding sophisticated SQL-based analytics within the Oracle Database.

Window functions, which are now a key analytical feature in the analysis of big data, were first introduced in Oracle 8i (1999) and many developers use them to manage complex big data requirements.

Oracle 10g (2003) introduced the SQL Model clause, which provides a spreadsheet-like what-if modeling framework aimed at business users and 12c introduced SQL pattern matching along with the HyperLogLog based approximate count distinct funtion.

Back to top

Technical Information

Data Sheets
Oracle Magazine - Technology: SQL 101 by Melanie Caffrey is a senior development manager at Oracle. Oracle Magazine - Ask Tom

Back to top


Oracle University
Oracle Learning Library
OTN Virtual Developer Day

Back to top

Presentations from Oracle OpenWorld

Back to top