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.
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.
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:
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:
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.
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:
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:
- 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:
- The function has a
PARALLEL_ENABLEclause in its declaration
- Exactly one
REF CURSORis specified with a
PARTITION BYclause- I hope somebody among my peers could verify or correct this assumption.
- The function has a