Oracle 18c: Data Warehousing New Features.. What’s NEWS..

ByJoel Pérez ( Oracle ACE Director )

Oracle 18c: Data Warehousing New Features.. What’s NEWS..

Oracle 18c: Data Warehousing New Features.. What’s NEWS..

Oracle 18c: Oracle Data Warehousing新功能一览

By Joel Peréz & Skant Gupta

This article is brought by en.enmotech.com

Data Warehousing will be the focus of the first Autonomous Database Service in Oracle 18c, so, this implies.. many new features will exist for this area.

Many people.. including me.. we are so eager to see how the autonomous conception will work on Data Warehouses since it was announced that a new Autonomous Database for Datawarehouse will be just a thing of load tables.. running queries and that’s it.. that’s what Oracle Corp. said and really we are so eager to see how close could be what we imagine in comparison to reality.

Meanwhile, let’s see some technical Oracle 18c New Features for Data Warehousing and you will start to realize how all this will be working..

Key 18c Features to Enhance Data Warehouses

DATABASE INFRASTRUCTURE

• Autonomous health framework
• Standby support for nologging ops

DATA MANAGEMENT

• Online partition merge
• Partitioning scheme online conversion of a table
• Private temporary tables

DATA PROCESSING

• SQL language
  • Enhanced approximate query processing
  • Enhanced analytic views, incl. MDX interface
• In-Memory
  • In-memory external tables
  • In-memory for NVRAM
  • Automatic in-memory management
• Optimizer and SQL processing
  • Inline external tables
  • Polymorphic table functions
  • Better fine-grained cursor Invalidation – Enhanced SQL Plan Management
• And much more …

In-Memory Key Features

• Automatic In-Memory Management
• In-Memory For External Tables
  • Support for all storage tiers
• In-Memory for Extreme Capacity NVRAM Memory
  • Dramatic increase of in-memory capacity
• Warehouse-specific performance improvements:
  • In-Memory Dynamic Scans
  • In-Memory Optimized Arithmetic

In-Memory For External Tables

Fast Analytics on External Data

• External Tables allow transparent access to data outside the DB

• In-Memory For External Tables builds in-memory column cache of data outside the DB for ultra-fast analytics on external data
• All In-Memory Optimizations apply
  • Vector processing, JSON expressions extend transparently to external data
• Up to 100X faster

Approximate Query Processing

Delivers significantly faster analysis for interactive and highly iterative data exploration

• Approximations for expensive aggregate calculations:

1. APPROX_COUNT_DISTINCT
2. APPROX_PERCENTILE
3. APPROX_MEDIAN

• • 6-13X faster, accuracy typically within < 1%
• Use with ZERO code changes
  • approx_for_aggregation = TRUE
• Accuracy and error rate provided

Top-N approximate aggregation

• Approximate results for common top n queries
  • Approximately how many page views did the top five blog posts get last week?
  • What were the top 50 customers in each region and their approximate spending?
• Order of magnitudes faster processing with high accuracy (error rate < 0.5%)
• New approximate functions APPROX_COUNT(), APPROX_SUM(), APPROX_RANK()

Oracle Advanced Analytics in 18.1

• New Scalable Machine Learning Algorithms (SQL API)
  • Random Forests for Classification
  • Neural Networks for both classification and regression
  • Explicit Semantic Analysis ML algorithm extended to support classification
  • Time Series via Exponential Smoothing
  • CUR decomposition-based algorithm for attribute and row importance

• Ability to export ML models to C and Java for applications deployment

Analytic Views

• Moves business logic (Aggregations, Hierarchies, Calculations) back into database
• Simple SQL for complex analytic queries
  • no joins or GROUP-BY clauses necessary
• Works on top of pre-existing tables or views
  • no persistent storage
• Built-in data visualization via APEX

Analytic Views enhancements in 18.1

• More calculations within Analytic Views:
  • Ranking and statistical functions
    • RANK_*, PERCENTILE_*, STATS_*, COVAR_*
• Hierarchical expressions
  • HIER_DEPTH, HIER_LEVEL, HIER_MEMBER_NAME, etc
• Broader schema support for Analytic Views:
  • Snowflake schemas; flat/denormalized fact tables (in addition to star schemas)
• More powerful SQL over Analytic Views:
  • Dynamic definition of calculations within SQL queries

MDX Query Language with 18.1 Analytic Views

• Support for MDX (Multi-Dimensional Expression) query language
  • Initially certified for use by Microsoft Excel Pivot Tables
    • Support/certification for other applications to follow
• Includes a multi-dimensional query cache
  • Similar to the SQL Result Cache

Polymorphic Tables: Self-Describing, Fully Dynamic SQL

• Part of ANSI 2016
• Encapsulate sophisticated algorithms
  • Hides implementation of algorithms
  • Leverage powerful, dynamic capabilities of SQL
  • Pass in any table-columns for processing
  • Returns SQL rowset (table, JSON, XML doc etc)
    • E.g. return credit score and associated risk level

Inline external tables

• External table definition provided at runtime
  • Similar to inline view
• No need to pre-create external tables that are used one time only
  • Increased developer productivity

and more..

If you want to be updated with all our articles send us the Invitation or Follow us:

Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

or Join our LinkedIn group: Oracle Cloud DBaaS

Author Bios:

Joel Pérez is an Expert DBA ( Oracle ACE Director, Maximum Availability OCM, OCM Cloud Admin. & OCM12c/11g ) with over 17 years of Real World Experience in Oracle Technology, specialised in design and implement solutions of: High Availability, Disaster Recovery, Upgrades, Replication, Cloud and all area related to Oracle Databases. International consultant with duties, conferences & activities in more than 50 countries and countless clients around the world. Habitual and one of leading writers of Technical Oracle articles for: OTN Spanish, Portuguese, English and more. Regular Speaker in worldwide Oracle events like: OTN LAD (Latin America), OTN MENA (Middle East & Africa), OTN APAC ( Asian Pacific), DTCC China, Oracle Code.. . Joel has always been known for being a pioneer in Oracle technology since the early days of his career being the first Latin American awarded as “OTN Expert” at year 2003 by Oracle Corp., one of the first “Oracle ACE” globally in the Oracle ACE Program at year 2004. He was honoured as one of the first “OCM Database Cloud Administrator” & Maximum Availability OCM in the world. Currently Joel works as Senior Cloud Database Architect in “Yunhe Enmo (Beijing) Technology Co.,Ltd”., company located in Beijing, China En.enmotech.com

Skant Gupta is an Oracle Certified Cloud Professional in Oracle Database 12c, an Oracle Certified Expert in Oracle Real Application Clusters (Oracle RAC) in Oracle Database 11g and 12c, and an Oracle Exadata Certified and an Oracle Certified Professional in Oracle Database 10g, 11g, and 12c. He works at Vodafone Technology in the UK and formerly worked as a senior DBA at Etisalat in Dubai. He has six years of experience with various Oracle technologies, focusing mainly on Cloud, database, and high availability solutions, Oracle WebLogic Suite, Oracle Exadata and Oracle GoldenGate. He has presented at several Oracle user groups worldwide, most recently in the US, the United Arab Emirates, and the India. Skant website link: oracle-help.co

This article is brought by Yunhe Enmo (Beijing) Technology Co.,LTD En.enmotech.com

Follow our Company in Linkedin: https://www.linkedin.com/company/1856106/

Subscribe to our Blog and enjoy our Weekly Cloud article..!