| PL/SQL Tables [message #632998] |
Mon, 09 February 2015 21:27  |
Eric Langager
Messages: 40
Registered: April 2004
Location: Beijing, China
|
Member |
|
|
|
This just idle curiosity, you guys, so please don't let me take you away from important work. I am just wondering if anyone can give me a practical example of something you can do with a PL/SQL table that cannot be done with an ordinary cursor. Thanks.
|
|
|
|
|
|
|
|
| Re: PL/SQL Tables [message #633007 is a reply to message #632998] |
Tue, 10 February 2015 02:32  |
John Watson
Messages: 9003
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your table (or "associative array", to use what I believe is the correct term) is created and stored in session's PGA. So you will never get on ora-1555 snapshot too old as you loop through it, which is all too likely with a slow cursor loop. But, of course: Quote:With PL/SQL table I can crash the OS
|
|
|
|
|
|
| Re: PL/SQL Tables [message #633011 is a reply to message #633010] |
Tue, 10 February 2015 02:42 |
John Watson
Messages: 9003
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I wasn't aware of that. I'll have to do some reverse engineering to work out what is going on. Not today, but thankyou for the insight.
In practice, I wouldn't use either solution for a horrendous loop job: I would load the rows into a global temporary table, which shoud avoid both ora-1555 and memory issues. Though possibly not issues with read consistency - it is a very SQL Server type of solution.
|
|
|
|
|
|
| Re: PL/SQL Tables [message #633014 is a reply to message #633013] |
Tue, 10 February 2015 03:26 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 10 February 2015 08:52
To be able to give you the first row Oracle must have gotten all of them to sort them
Not necessarily - if the column(s) in the order by are indexed and oracle is using that index to retrieve the data then it doesn't need to pre-sort the results before returning them and you can still get ora-1555.
|
|
|
|
| Re: PL/SQL Tables [message #633017 is a reply to message #633014] |
Tue, 10 February 2015 04:04 |
John Watson
Messages: 9003
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I would consider this as one of the few circumstances where a hint would be acceptable, to force a sort. Or perhaps one could generate a SQL Patch with that SQL Repair Advisor facility. I have never actually used that outside of the laboratory.
I did (a couple of years back) have to deal with an awful case on these lines: a cursor of a few million rows with lots of awful SQL in the loop (including COMMITs) that sometimes used to ora-1555 after perhaps thirty hours. We finally fixed the problem by tuning the SQLs in the loop with extensive use of stored outlines. That got it down to about six hours, which would usualy run and if it didn't there was time a couple of retries: it was a job scheduled for weekends. Not a solution I liked, but this was Standard Edition and there were strict limits on the changes we were alloed to make.
|
|
|
|
|
|
| Re: PL/SQL Tables [message #633021 is a reply to message #633017] |
Tue, 10 February 2015 04:27 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
John Watson wrote on Tue, 10 February 2015 10:04I did (a couple of years back) have to deal with an awful case on these lines: a cursor of a few million rows with lots of awful SQL in the loop (including COMMITs) that sometimes used to ora-1555 after perhaps thirty hours.
*shudder*
|
|
|
|
| Re: PL/SQL Tables [message #633072 is a reply to message #633003] |
Tue, 10 February 2015 09:59 |
Eric Langager
Messages: 40
Registered: April 2004
Location: Beijing, China
|
Member |
|
|
|
Well, a smiley could be considered practical, I guess, if it is something you need to communicate a message efficiently. But what does that have to do with PL/SQL Tables?
|
|
|
|
|
|
| Re: PL/SQL Tables [message #633142 is a reply to message #633075] |
Wed, 11 February 2015 09:39 |
Eric Langager
Messages: 40
Registered: April 2004
Location: Beijing, China
|
Member |
|
|
>The smiley was a response to Blackswan's reply.
OK, I guess I missed that. But that really goes to my original point. My situation was a bit unusual, because I was a trainer for 12 years, so my environment was not real. Not the software but the circumstance. I never used software simulators as a trainer. I had my students install Oracle9i, and then showed them how to edit the init.ora file to reduce the memory allocations to 5 MB so that it could be run on a laptop with 256 MB of RAM. So the software was always real. But a test table with 5 columns and 10 rows is not exactly an industrial load. Now I use Oracle for my own data--grades, a flash card program for studying Chinese, and my research at the national library of China. So many times I have said, "OK, I see how I can do this with a PL/SQL table, but I can also do it with a cursor that is much simpler." I guess PL/SQL tables may eventually be obsolete.
|
|
|
|
|
|
| Re: PL/SQL Tables [message #633144 is a reply to message #633143] |
Wed, 11 February 2015 09:50 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Or,
Explain your requirement in detail, and you might find the same task could be done much efficiently in plain SQL rather than PL/SQL.
I have seen people (ab)using PL/SQL, while they could do it in plain SQL much more efficiently.
[Updated on: Wed, 11 February 2015 09:52] Report message to a moderator |
|
|
|
| Re: PL/SQL Tables [message #633164 is a reply to message #632998] |
Wed, 11 February 2015 18:42 |
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Eric Langager wrote on Mon, 09 February 2015 22:27I am just wondering if anyone can give me a practical example of something you can do with a PL/SQL table that cannot be done with an ordinary cursor. Thanks.
Nothing, but with PL/SQL table you will need that cursor only once per session. Assume you have status table:
code = 1, name = OPEN
code = 2, name = CLOSED
code = 3, name = REJECTED
code = 4, name = REOPEN
.
.
.
And you have ton of stored procedures and other PL/SQL code that gets status name as parameter but all tables PL/SQL code needs to query obviously have status code. You can:
a) join STATUS table to every SQL and do that extra join every time such SQL is executed in your session
b) select, once per session, STATUS table into package global valiable of PL/SQL associative array type and use it in SQL directly.
SY.
|
|
|
|
| Re: PL/SQL Tables [message #633858 is a reply to message #633144] |
Thu, 26 February 2015 07:07 |
Eric Langager
Messages: 40
Registered: April 2004
Location: Beijing, China
|
Member |
|
|
That's interesting...actually, I did rewrite one of my scripts a couple years because I met a computer scientist who told me that he did not use PL/SQL because it wasn't "optimized." I was able to do it with SQL, but it didn't seem to save me that much time or trouble. And there are some situations where you just need PL/SQL. For example, years ago when I was teaching in the Software College at Beihang University here in Beijing, I had a lab designed to illustrate the difference between a b-tree and bitmap index. The idea of the lab is that a bitmap index is much more efficient on columns with low cardinality. I had the students create a table with 100,000 rows of very low cardinality. Here is the table creation script:
CREATE TABLE numbers ( no NUMBER,
odd_even VARCHAR2(1) );
And here is the script that inserts 100,000 rows in a column with very low cardinality (only two unique values):
BEGIN
FOR i IN 1..100000
LOOP
IF mod(i,2)=1
THEN
INSERT INTO numbers
VALUES(i,'O');
ELSE
INSERT INTO numbers
VALUES(i,'E');
END IF;
IF mod(i,500)=0
THEN
COMMIT;
END IF;
END LOOP;
END;
/
I don't think you can do this with SQL. You need PL/SQL.
|
|
|
|
|
|
| Re: PL/SQL Tables [message #637126 is a reply to message #633861] |
Mon, 11 May 2015 04:05 |
Eric Langager
Messages: 40
Registered: April 2004
Location: Beijing, China
|
Member |
|
|
Pretty impressive. It works.  So I guess what it comes down to is that much of what we use PL/SQL for could be done with SQL (except procedures), but in different cases it might be simpler to use one or the other. Sometimes a simple cursor might be easier. Other times basic SQL could do the job. I just haven't found a case where a PL/SQL table would be a more efficient solution than a cursor. Perhaps it's just a left over invention from the beginning days of Oracle.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
