google7ee7f750db33e29f.html

By -
google-site-verification: google7ee7f750db33e29f.html
Location: San Francisco, CA, USA

Popular posts from this blog

Exploring the interfaces for User Defined Aggregates

By -
Image
  image courtesy of wikipedia Whilst I was working on the functional specification for the LISTAGG extensions that we implemented in 12c Release 2, I came across Tom Kyte’s stragg function  which uses the User Defined Aggregate API introduced in database 9i. Tom’s comprehensive answer covers the two important areas that need to considered when using the data cartridge API: 1) a UDA can run as serial process and 2) a UDA can run as a parallel process. Therefore, you need to code for both these eventualities. Dealing with both scenarios can be a little challenging - as I have discovered over the last few weeks. having looked at a number of posts there is a common theme for explaining how the various interfaces for user defined aggregate actually work. One the clearest examples is on Tim Hall’s blog:  String Aggregation Techniques . This got me thinking…would it be possible to take the new  extensions we made to LISTAGG and incorporate them into custom string manipulation func...
Keep reading

Managing overflows in LISTAGG

By -
Image
This is an interesting problem that has come up a few times in discussions (and I think it has been mentioned on the SQL forums as well).  When using LISTAGG  on very large data sets you can sometimes create a list that is too long and consequently get an ORA-01489: result of string concatenation is too long   error . Obviously, it is possible to determine in advance if the error is going to occur using some bespoke PL/SQL code and then take appropriate action within your application to manage the rows that contain stings that exceed the VARCHAR2 limit. Many customers have implemented workarounds to overcome the  ORA-01489  error, however, this has mostly involved the use of complex code which has impacted performance. Wouldn’t it be great if there was a simple yet elegant way to resolve this issue? Actually there is and we can use a few of the most recent analytical SQL functions. If you are using Database 12c you can make use of the  MATCH_RECOGNIZE  function to effecti...
Keep reading

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 S...
Keep reading