12c launch webcast - key data warehousing features explained

By -

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: , , , , ,

Comments

  1. Stew Ashton8 October 2013 at 22:31

    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
    MATCH_RECOGNIZE (
    PARTITION BY symbol ORDER BY tstamp
    MEASURES FIRST(x.tstamp) AS first_x,
    LAST(z.tstamp) AS last_z
    ONE ROW PER MATCH
    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

    ReplyDelete

Post a Comment

Popular posts from this blog

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

By -
Image
Image courtesy of wikipedia This post covers one of the new SQL performance enhancements that we incorporated into Database 12c Release 2 . All of these enhancements are completely automatic, i.e. transparent to the calling app/developer code/query. These features are enabled by default because who doesn’t want their queries running faster with zero code changes? So in this post I am going to focus on the new In-Memory “cursor duration” temporary table  feature. Let’s start by looking at cursor duration temp tables… Above image courtesy of wikimedia.org What is a cursor duration temp table? This is a feature that has been around for quite a long time. Cursor duration temporary tables (CDTs) are used to materialize intermediate results of a query to improve performance or to support complex multi step query execution. The following types of queries commonly use cursor duration temp tables: WITH Clause and parallel recursive WITH Grouping Sets Star Trans
Keep reading

SQL Pattern Matching Deep Dive - Part 1

By -
Image
There has been quite a lot of interest in the new 12c MATCH_RECOGNIZE feature for SQL pattern matching. Therefore, I thought now would be the perfect time to start a series of quick deep dive posts that explain how SQL pattern matching works. Over the coming weeks I will cover the following topics in a series of posts. This is the start of a series of posts based on a presentation that I put together for the recent annual  BIWA conferenc e at Oracle HQ. The Oracle BI, DW and Analytics user community always puts on a great conference and this year was the best yet. You can download any or all of the presentations from this year’s conference by following this link . My pattern matching deep dive presentation started life about a year ago as a post covering some of the new keywords in the explain plan that are linked to pattern matching, see here . It has now expanded to cover a much wider range of topics. Firstly, a huge vote of thanks to my development team (Lei Sheng and Chun-Chie
Keep reading

SQL Pattern Matching Deep Dive - Part 6, state machines

By -
Image
The obvious way to start this particular post is to pose a couple of simple questions: what is a state machine and why should you care? In general I would say that you don't need to know about or care about state machines. That's the beauty of using SQL for pattern matching. The MATCH_RECOGNIZE clause encapsulates all the deep technical modelling and processing that has to be performed to run pattern matching on a data set. However, there are times when it is useful, probably vital, that you understand what is going on behind the scenes and one of the most obvious situations is when backtracking happens. Therefore, the content covered in this post is a going to be a gently lead-in into my next post where I am going to discuss the concept of “backtracking”  and the dreaded  ORA-30009 error. Let’s start our voyage of discovery…when you attempt to run a SQL statement containing a MATCH_RECOGNIZE clause during the compilation phase we generate a finite state machine ba
Keep reading