Pipelined Table Function in Standard Edition

pipedTableFunction_2ways

Pipelined & Parallel Table Function in Standard Edition?

 


Earlier today I received a question from Mr. Lothard Flatz concerning a featured that to my understanding has been available since Oracle9i.

pipelinedTable_function_se_twitter

Thank you Lothard. And without any further information, I assume this question targets the database version 11gR2 or 12cR1.


My answer is … It depends…

To be frank, my first thought was “yes this is ok to use in a Standard Edition Database”, but after reading more about this feature, I started to hesitate and doubt my own answer. Why?

Please keep on reading, and you can judge for your self, am I making the correct assumptions or not.

Hopefully somebody among my peers can either verify or correct my assumptions in the end of this post.

Please also NOTE this:


Oracle licensing is a complicated business.

The notes here are only a guide. You should always discuss your licensing with Oracle License Management Services or any other third party company, who are classified as professionals on this business.


What is this Pipelined Table Function?

From the “Database Data Cartridge Developer’s Guide” 11gR2 document we can read:

tableFunctions_summary

And while you are making your self acquainted with the subject,  why not also read this article from Tim Hall about this feature before continuing.


“parallel execution” – in standard edition? 

As you noticed from the picture above, Oracle documentation mentioned the word “parallel execution”, and that is always generating the question “is this ok in a Standard Edition Database environment, or not“.

First we need to check the  Oracle License Document from release 11.2 for some guidances:

11gr2_features_edition_performance

We can only find something on the PL/SQL Function Result Cache.

I assume this is referring to the RESULT_CACHE clause, since that will to my understanding, boost the performance by reusing function results, instead of recalculating.

From the internet, we can find more information about RESULT_CACHE from either this post by Tim Hall or from the reference guide from 12c.

Note to my self: “So is the License document also referring to the DBMS_RESULT_CACHE package, and therefor we cannot use it in Standard Edition?”


Parallell Table Function

Since the “Table Function”- feature also has the parallel option available, it might be good to analyse it as well. The “Database Data Cartridge Developer’s Guide” 11gR2 document tell us this about parallel table functions:

paralelltable_function

And the 11gR2 License Document will tell us these things about parallelism usage (same in 12c):

    • Parallel backup and recovery                      N N Y
    • Parallel query/DML                                          N N Y
    • Parallel statistics gathering                          N N Y
    • Parallel index build/scans                              N N Y
    • Parallel Data Pump Export/Import          N N Y
    • In-memory Parallel Execution                     N N Y
    • Parallel Statement Queuing                         N N Y
    • Parallel capture and apply via XStream N N Y
    • Parallel spatial index builds                           N N Y

The 12c Enterprise Manager License Document

I couldn’t find any additional information about “Pipelined Table Functions” or “Parallel Table Functions” from this document, so from the information provided by the License documents I would summaries the result:


Summary

Yes, to my understanding one of the features is available in our Standard Edition Database Environment – the Pipelined Table function.
But the other one – the Parallel Table Function I would like to classify as a “forbidden fruit”, and the reasons why is what the License Document tell us about Parallelism and this information from the Database Data Cartridge Developer’s Guide:
  1. For a table function to be executed in parallel, it must have a partitioned input parameter. Parallelism is turned on for a table function if, and only if, both the following conditions are met:
    1. The function has a PARALLEL_ENABLE clause in its declaration
    2. Exactly one REF CURSOR is specified with a PARTITION BY clause- I hope somebody among my peers could verify or correct this assumption.

So what do you think?
As I mentioned earlier. The last resort when unsure, is asking Oracle for assistant.
Confirmation:
lothar_conf
Take care, stay well, and enjoy the weekend!
-Ann
Turku 15th May 2015

Leave a Reply

Your email address will not be published. Required fields are marked *