Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL DWH features (
PL/SQL DWH features [message #431557] Wed, 18 November 2009 02:19 Go to next message
Messages: 585
Registered: November 2007
Location: NE
Senior Member


If you have used any of the following PL/SQL features and have general examples and advantages to share, request you to share it with me.

1)Parallel DML
2)Table Function
3)Pipelined Table Function
4)Asynchronous PL/SQL

Thank you.
Re: PL/SQL DWH features [message #431558 is a reply to message #431557] Wed, 18 November 2009 02:52 Go to previous messageGo to next message
Messages: 2818
Registered: May 2007
Location: Scotland
Senior Member
Well, it has been a while. You must have forgotten how this forum works.
Re: PL/SQL DWH features [message #431603 is a reply to message #431557] Wed, 18 November 2009 07:56 Go to previous messageGo to next message
Messages: 4621
Registered: February 2005
Location: East Coast USA
Senior Member
The babbling of a madman never ceases to amaze.
Re: PL/SQL DWH features [message #431615 is a reply to message #431557] Wed, 18 November 2009 09:31 Go to previous messageGo to next message
Messages: 24960
Registered: January 2009
Location: SoCal
Senior Member
Your answers are just a few clicks beyond the URL below.
Let us know when you have found your answer.
Re: PL/SQL DWH features [message #431646 is a reply to message #431615] Wed, 18 November 2009 21:53 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Nice documentation page BlackSwan.

Let me add some opinions as well since I think the opinion of experienced Oracle people is also valuable. Of course these are my opinions and there are surely others who have just the opposite opinion.

1) parallel dml: overrated. Most systems do not need it and infact would suffer from it. The idea of parallel dml is to make one sql statement or one job go faster at the expense of all other pieces of code. It is not a CONCURRENCY FRIENDLY FEATURE. Indeed, most hardware systems are not set up to actually do parallel dml well. Unless you really know how it works and why it will benefit you and how it will affect your overall system performance, don't bother.

2) table function: highly valuable. One of the top 20 features of oracle. You can take any data from anywhere and present it through a view as just another rowsource. You won't use it every day but when you use it well it is magic. But it has at least one limit, it is only practical to use these as rowsources when they return <1000 rows (more or less) (OK, this number may be different for different systems but the point is you won't be writing a table function that buffers 1 million rows in memory.

3) pipelined table function: same as table function with two big differences: a) it has a small memory footprint so can be very efficient when compared to the normal table function if the normal table function is manipulating lots of rows. This is a fix to the problem of table functions noted above, but b) it is only callable from sql. You cannot take any arbitrary table function and turn it into a pipelined table function because if that table function is being called from plsql it won't work. At least that is how I remember it.

4) Aynchronous PL/SQL: IS THERE SUCH A THING? Not natively to PLSQL at least I do not recall it. However, you can have a look at the various mechanisms available to Oracle that facilitate this kind of processing: a) dbms_job, b) dbms_alert, c) dbms_pipe, Oracle Advanced Queueing (dbms_aq, dbms_aqadm). Read up on polling in Oracle and the packages above. There is also a related feature SKIP LOCKED when reading cursors. Don't use cursors unless you have to but this feature is kind of cool in the right situation. That said I only used it once and I am sure there was a better way, I just could not think of it.

Good luck, Kevin
Previous Topic: dbms_lock.sleep problems
Next Topic: optimisation for view as it is time consuming
Goto Forum:

Current Time: Mon Oct 24 09:13:01 CDT 2016

Total time taken to generate the page: 0.09255 seconds