SQL for Data Warehousing and Analytics

    Most of you will already have received an email from the OOW team announcing the call for papers for this year's conference:  http://www.oracle.com/openworld/call-for-papers/index.html . Each year, customers ask me how they can increase their chances of getting their paper accepted? Well, I am going to start by stating that product managers have absolutely no influence over which papers are accepted - even mentioning that a product manager will be co-presenting with you will not increase your chances! So how do you increase you make sure that your presentation title and abstract catches the eye of the selection committee? Here is my top 10 list of guidelines for submitting proposals:   1) Read the "call-for-papers" carefully and follow its instructions - even if you have submitted presentations for lots of Oracle conferences it is always a good idea to carefully read the call for papers and to make sure you follow the instructions. There is an excellent section...

One of the proof points that I often make for using analytical SQL over more sophisticated SQL-based methods is that we have included specific optimizations within the database engine to support our analytical functions. In this blog post I am going to briefly talk about how the database optimizes the number of sorts that occur when using analytical SQL. Sort Optimization 1: Ordering Groups Many of analytical functions include PARTITION BY and/or an ORDER BY clause both of which by definition implies that an ordering process is going to be required. As each function can have its own PARTITION BY-ORDER BY clause this can create situations where lot of different sorts are needed. For example, if we have a SQL statement that included the following: Rank() Over (Partition by (x) Order by (w)) Sum(a) Over (Partition by (w,x) Order by (z)) Ntile() Over (Partition by (x) Order by (y)) Sum(b) Over (Partition by (x,y) Order by (z)) this could involve four different sort processes to tak...