What's New in Database 18c

There are many new SQL features and enhancements in 18c that extend Oracle's flexible and comprehensive analytical framework:

• Enhancements to ROUND function to support additional financial requirements
• Self-describing, fully dynamic Polymorphic Table Functions - part of ANSI SQL 2016
• Apprximate query processing for TOP-N results
• Additional approximate query processing: APPROX_COUNT, APPROX_SUM, APPROX_RANK
• Analytic Views now support MDX (Multi-Dimensional Expression) query language

Overall, the SQL analytic functions and features in Oracle Database 18c make it the most effective platform for delivering analytical results directly into operational, data warehousing and business intelligence projects. For more information see the following:

Presentation: SQL For Data Warehousing and Analysis: What's New In Oracle Database 18c (PDF)

Also new in 18c for : Analytic Views which organize data using a dimensional model. They provide a fast and efficient way to create analytic queries over data stored in existing database tables and views. They allow you to easily add aggregations and calculations to data sets and to present data in views that can be queried with relatively simple SQL. New in Database 18c is the ability to use the MDX (Multi-Dimensional Expression) query language.

Presentation OpenWorld 2016, Using Analytic Views to Enhance BI Applications and Simplify Development (PPTX)

Try Database 18c for FREE

Try out all of these exciting new features today by using the new free LiveSQL service. It provides you with instant access to a comprehensive collection of community scripts and structured tutorials covering the most important SQL features in Oracle Database 18c.

Overview of SQL for Analysis, Reporting and Modeling

The in-database analytical functions and 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
• Analytical windows
• Reporting aggregates
• LAG/LEAD
• FIRST/LAST
• Statistics library
• Hypothetical Rank and Distribution
• Approximate query processing
• 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 function. Database 18c further provides the ability to write self-describing, reusable, fully dynamic table functions along with extensions to approximate query processing.

Key Benefits of SQL for Analysis, Reporting and Modeling

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.

White Paper: SQL Natural Language For Data Analysis (PDF)

LiveSQL Tutorials: pattern matching

Overview of Analytic Views

Analytic views organize data using a dimensional model. They allow you to easily add aggregations and calculations to data sets and to present data in views that can be queried with relatively simple SQL.

Like standard relational views, analytic views are metadata objects (that is, they do not store data) which can be queried using SQL. They access data from other database objects such as tables, views, and external tables and can join multiple tables into a single view.

Analytic views also:

• Organize data using a rich business model that has dimensional and hierarchical concepts
• Include system-generated columns with hierarchical data
• Automatically aggregate data
• Include embedded measure calculations that are easily defined using syntax based on the business model
• Include presentation metadata
• Hierarchically aware calculation expressions

Key Benefits of Analytic Views

The key benefits provided by Oracle's new analytic views are:

Simplified and faster application development - it is much easier to define calculations within analytic views than it is to write or generate complex SELECT statements.

Calculation rules are stored once - rules are stored in the database which provides end-users with greater freedom of choice in their use of reporting tools.

Caluclation consistentcy - because calculation rules are defined once and stored inside the database, they can be re-used by any number of applications

White Paper:Analytic Views

Presentation: Using Analytic Views to Enhance BI Applications and Simplify Development (PPTX)

LiveSQL Tutorials: Analytic Views

Analytical SQL Documentation

Oracle's analytic SQL features and functions are a core part of Oracle Database. These features provide many ways to improve the performance of your data warehouse. The Data Warehousing Guide groups these features and functions into following areas:

• SQL for Analysis and Reporting
• SQL for Aggregation in Data Warehouses
• SQL for Pattern Matching
• SQL for Modeling
• Advanced Analytical SQL
• Analytic Views
  • Overview of Analytic Views
  • Attribute Dimension and Hierarchy Objects
  • Analytic View Objects

Learn more about the compelling array of analytical features and functions that are accessible through SQL using Oracle Database 18c

Technical Information

Data Sheets

Analytical SQL Data Sheet (PDF)

Whitepapers

• SQL - the natural language for analysis (PDF)
• Patterns Everywhere - Find then fast! (PDF)

iBooks

• Patterns Everywhere - Find then fast! (Apple iBook for OSX, iPad and iPad Mini)

Presentations

• Analytical SQL in Oracle Database 18c (PDF)
• Using Analytical SQL to Intelligently Explore Big Data (PDF)
• SQL - the best development language for Big Data (PDF)
• Analyze This! Analytical Power in SQL, More Than You Ever Dreamed Of (PDF)
• Oracle Database SQL Model Clause (PPT)

Oracle Magazine - Ask Tom

March 2015 SQL, SQL and More SQL Tom Kyte covers using analytical SQL functions and SQL Model clause

Demos

• Using pattern matching and spatial analytics to track fraud (OOW 2013 - Keynote demo)
• Sessionization with SQL Pattern Matching

Videos

Tom Kyte: 12 Features of Oracle Database 12c

George Lumpkin: Big Data and Data Warehousing for the Enterprise

Keep It Simple SQL (KISS):

• Welcome to the KISS (Keep It Simply SQL) Analytics series
• Ranking rows
• Options for ranking
• Mind the gap
• More ranking options
• Dealing with null
• Predicates
• PARTITION BY clause
• PARTITION BY with aggregation
• Execution costs

Checkout the growing library of SQL videos on our YouTube channel by clicking here

Training

Oracle University

• Oracle Database 12c: Analytic SQL for Data Warehousing
• Oracle Database 12c: SQL and PL/SQL New Features

Oracle-by-Example

• SQL Model Clause
• SQL Pattern Matching

Oracle Learning Library

• Pattern Matching
• Sessionization with SQL Pattern Matching
• Introduction to SQL Analytics
• Database X-Series - SQL Pattern Matching

OTN Virtual Developer Day

• SQL Pattern Matching Workshop (Webcast, slides and hands-on lab)
• Download Database 12c VM for workshop

Scripts

• Github library of scripts
• Series of scripts for Analytical SQL
• Pattern Matching use cases and examples
• Scripts and examples for SQL Model

Presentations and Labs from Oracle OpenWorld

• SQL Pattern Matching Big Data Sessionization Hands-on Lab

Related Technology

• Oracle Database 12c
• SQL
• Data Warehousing and Big Data
• Statistical Functions
• Query Optimization