Posts

Showing posts from August, 2015

Optimizing PL/SQL Panel at Oracle Open World 2015 - Questions welcome!

At Oracle Open World 2014, I was a member of a panel on SQL and PL/SQL Performance. It was an awful lot of fun, but as I had expected, most of the questions focused on SQL performance, and most of them were answered by Tom Kyte (no complaints from me!).

I came away with a strong feeling that we should offer separate panels on SQL and PL/SQL, so that each could get the proper amount of attention (or as "proper" as you can get in 45 minutes. Sigh...).

Well, my wish has come true!

There will be a panel on SQL for Functionality, Performance and Maintainability, and its future, and another on PL/SQL: Optimizing PL/SQL for Performance and Maintainability (CON8416) Moscone South - 306, Wednesday, October 28th, 13:45 - 14:30 Notice that we are suggesting that when it comes to "optimization," we should all be thinking about more than performance. Performance is, justifiably, a primary concern for any application developer (and DBA), but that doesn't mean it should be the

Table Functions, Part 5c: Another use case for Pipelined Table Functions (and simple example)

From Oracle Help Center (a.k.a., documentation), we read:
Data is said to be pipelined if it is consumed by a consumer (transformation) as soon as the producer (transformation) produces it, without being staged in tables or a cache before being input to the next transformation.  Pipelining enables a table function to return rows faster and can reduce the memory required to cache a table function's results.  A pipelined table function can return the table function's result collection in subsets. The returned collection behaves like a stream that can be fetched from on demand. This makes it possible to use a table function like a virtual table.In a nutshell, this means that the calling query can put to use the rows returned by the pipelined table function (PTF) before the function has returns all rows.

A simple way to demonstrate this (and highlight another nice use case for PTFs) is with the SQL IN operator.

IN is used in the WHERE clause to determine if a column or expression is…

Table Functions, Part 5b: Table Functions vs Pipelined Table Functions

Last week, I published my first post in the Table Function series on pipelined table functions. I included this advice at the top of the post:

Here's my first piece of advice regarding this interesting and in many ways unique (within PL/SQL) feature:
You are unlikely to ever need a pipelined table function, but when you do, it is going to be amazing.I bring this up right at the start because I have found over the years that many developers talk in the most glowing terms about pipelined table functions. Yet when I look at what they are doing with those functions, it becomes clear that they are not pumped up about the pipelining. They are not, in fact, benefiting at all from the pipelining feature.

Instead, they are excited simply about table functions. And that's cool, that's fine. Table functions are, indeed, quite awesome. Read my series! :-)

Part of the reason I gave this advice is that I'd run some performance tests on TFs and PTFs and noticed that PTFs seemed to run a…

Developer Choice Awards Update: Let the judging begin!

Image
After a one week extension for the nominations period, we have now closed nominations for the first-ever round of Oracle Database Developer Choice Awards.

We have a total of 75 nominations across five categories (SQL, PL/SQL, Application Express, Database Design and ORDS). That's quite a healthy number and we look forward to the judges now winnowing that list down to a set of finalists in each group.

Judges? What judges? Isn't Oracle simply going to decide who the finalists - and then winners - will be?

NO WAY.

These are developer choice awards. By this we mean that our users decide the finalists and then vote among the finalists to determine the winners of the awards.

We've set up a panel of judges for each category. Each panel consists of four ACEs and/or ACE Directors, plus one Oracle employee. For example, I am on the PL/SQL panel (surprise!).

And now you are probably wondering: so who are the other PL/SQL judges? And the judges on the other panels?

Ha. You expect us to…

Table Functions, Part 5a: An introduction to pipelined table functions

Image
[Gee, that was embarrassing. I start a series on table functions, and then it falters and stops. My apologies; I got so busy eating and drinking and breathing and sleeping and....anyway, back to the series!]

In this post I introduce PL/SQL developers to the pipelined table function.

Here's my first piece of advice regarding this interesting and in many ways unique (within PL/SQL) feature:
You are unlikely to ever need a pipelined table function, but when you do, it is going to be amazing.I bring this up right at the start because I have found over the years that many developers talk in the most glowing terms about pipelined table functions. Yet when I look at what they are doing with those functions, it becomes clear that they are not pumped up about the pipelining. They are not, in fact, benefiting at all from the pipelining feature.

Instead, they are excited simply about table functions.

And that's cool, that's fine. Table functions are, indeed, quite awesome. Read my series…