12c launch webcast - key data warehousing features explained

In case you missed Wednesday's Oracle Database 12c launch webcast then the good news is that it is now available for on-demand replay. The live session was amazingly well attended and the questions posted by the participants were great. There were quite a few data warehousing questions posted and I think I managed to answer nearly all of them. If I missed your particular question then let me know and I will make sure you get an answer.  

During the webcast Tom Kyte offered his "Top 12 features of Oracle Database 12c" and there were quite a few data warehousing features that made it on to his list:

  • SQL analytics row_limiting/Top-n
  • Pattern matching
  • Partitioning enhancements
  • Adaptive execution plans 
You can watch the webcast right now by clicking on the image below, which will take you to the landing pad for the on-demand replay. 

alternatively you can click here to watch the webcast. FYI - there is a registration process but it is quite painless.

Once you watched the webcast I expect you will want to get more information about each of these features so below I have provided a brief overview of each feature and a link to the feature whitepaper:

Adaptive Query Optimization - The new groundbreaking adaptive query optimization in Oracle Database 12c enables the Optimizer to make runtime adjustments to execution plans and to discover additional information that can lead to better statistics. Learn about this exciting new functionality here. The home page on OTN is here.

SQL Pattern Matching - The new groundbreaking SQL pattern matching capabilities brings the simplicity and efficiency of the most common data analysis language to the process of identifying patterns within a data set. It provides a completely new native SQL syntax that has adopted the regular expression capabilities of Perl by implementing a core set of rules to define patterns in sequences (streams of rows). Learn about this exciting new functionality here. The home page on OTN is here.

Partitioning - Oracle has enhanced the functionality of Oracle Partitioning with every release, by either adding new partitioning techniques, enhancing the scalability, or extending the manageability and maintenance capabilities. Oracle Database 12c is no different by offering enhanced composite partitioning strategies and vastly enhanced partition maintenance operations.  Learn about this exciting new functionality here. The home page on OTN is here.

Heat Map- stores system-generated data usage statistics at the row and segment levels – information that can be used to automate the compression and movement of data in order to reduce storage costs, improve performance and optimize Oracle Database storage. Heat Map, used in conjunction with Automatic Data Optimization can automate compression and storage policies based on the usage of the data. Learn about this exciting new functionality here. The home page on OTN is here.

Hope the above is useful.

Technorati Tags: , , , , ,


  1. Hello Keith,

    I hope this comment will reach you even though the post is a bit old. I watched with great interest your three podcasts and started testing. I noticed that the first example is subject to "catastrophic backtracking". Try this:

    drop table ticker;
    CREATE TABLE Ticker (SYMBOL VARCHAR2(10), tstamp DATE, price NUMBER);
    INSERT INTO ticker
    select 'ACME', trunc(sysdate)+level, 10000-level from dual connect by level <= 10000;

    SELECT first_x, last_z FROM ticker
    PARTITION BY symbol ORDER BY tstamp
    MEASURES FIRST(x.tstamp) AS first_x,
    LAST(z.tstamp) AS last_z
    PATTERN (X+ Y+ W+ Z+)
    DEFINE X AS (price < PREV(price)),
    Y AS (price > PREV(price)),
    W AS (price < PREV(price)),
    Z AS (price > PREV(price))

    It takes me over 90 seconds to find no rows.

    The standard solutions for avoiding catastrophic backtracking are: Friedl's "unrolled loops", possessive quantifiers and atomic groups - all of which are available in Perl since 2007 and which are not yet possible in 12c.

    I would be very interested in any workarounds you can think of, or any (shareable) insight into future directions that might help with this problem. Is there a forum where such things can be discussed? I am thinking of the PL/SQL and SQL forum, but maybe the MATCH_RECOGNIZE experts hang out elsewhere?

    This row pattern matching is great stuff, but any serious study requires us to seek out the gotchas. I hope you understand.

    Best regards, Stew Ashton


Post a Comment

Popular posts from this blog

Dealing with very very long string lists using Database 12.2

My query just got faster - brief introduction to 12.2 in-memory cursor duration temp tables

Ultimate, comprehensive review for big data warehousing for #OOW18.