Included in Oracle Database is a compelling array of analytical features and functions that are accessible through SQL and a new fast and efficient way to organize data using a dimensional model.
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.
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:
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.
The key benefits provided by Oracle's in-database analytical functions and features are:
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:
The key benefits provided by Oracle's new analytic views are: