PL/SQL DWH features [message #431557] |
Wed, 18 November 2009 02:19  |
user71408
Messages: 585 Registered: November 2007 Location: NE
|
Senior Member |
|
|
Hi,
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 #431646 is a reply to message #431615] |
Wed, 18 November 2009 21:53  |
 |
Kevin Meade
Messages: 2103 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
|
|
|