Re: HOWTO: STORED PROCEDURE RETURNING MANY ROWS
Date: Mon, 20 Jun 2011 15:45:10 +0200
Message-ID: <1308577510.61_at_user.newsoffice.de>
Tim X schrieb am 18.06.2011 in <87y610c5ws.fsf_at_puma.rapttech.com.au>:
> If I understand you correctly, I think your approach is flawed. If you
> are going to select data simply as input to use in a join with data from
> another query, your better off just doing it all as 1 sql statement.
No, because I had within every statement the complex logic of the
procedure above. I did not mention but in fact the result set of the
stored procedure is to be combined many times with different other
tables/views from users, that do not have to understand the logic behind
the scene.
> The problem with your approach is that you are working hard to
> circumvent all of Oracle's efforts to make things as optimized as
> possible.
I have to decide whether I prefer some seconds more answering time and a
minute to develop the query or a very short answering time and a many
hour develop of query for each user.
I know, I could solve my problem by creating a table/temp table as
result of my stored procedure. But that is not my question. There are
about 10000 records to be processed, this can not so much time that I
want to create/drop tables with 5 rows each time I use the procedure.
> Consider two simple scenarios. In the first one, you use various SQL
> statements to extract the final set of data you want. The statements are
> placed inside a procedure that allows you to both pass various values
> used to define/control the result set (via bind variables) and set a ref
> cursor to pass back which you can then query and further process or
> display the data. Oracle will e able to use any relevant indexes,
> exploit caching of SQL statements and take full advantage of the
> optimiser.
> Now consider your (as I understand it) approach. You want to define
> procedures that will return a result set whih you then want to use in
> further queries involving joins and other operations. In this case,
> you want to effectively generate a pseudo table via a procedure call
> which you then use in joins and additonal SQL to extract the final
> result. However, there wil be no indexes, optimiser statistics and
> little caching opportunities that the DB can use to make things as
> efficient as possible.
I am not absolutely sure, because the result set of the procedure is
very small. For this I do not need an index. I know, that oracle likes
corresponding indexes in two combined tables but nevertheless I can tell
it to use an index for the corresponding table.
Something like
select /*+ index(B) */ ...
from MyResultSet A
join AnyOtherTable B on A.B_ID=B.A_ID
So I suppose this wouldn't be my problem.
There are many ways to solve my problem but there sure is no simple sql I could use. I definitely need program code and definitely have to combine it with other tables. But I would prefer to do it on the server and not on the client. And I prefer to do it without temporary tables. And I do not want to write the same procedure 1000 times for each scenario a user could need.
> I also suspect there are other pitfalls with the approach you are
> adopting. At a guess, I would think you have hit upon this idea because
> you observed that many of your SQL queries had a common element, making
> it fairly logical to want to isolate that bit of common SQL in one place
> - less typing, potentially more maintainable, cleaner code etc. All good
> objectives to strive for. However, you also need to consider how Oracle
> processes queries and strive for a balance.
This I try. But I can not pass the pitfalls before I know how to return
some rows by a PL/SQL stored procedure and use this in a select.
> If you are looking at ways to minimise SQl and ensure consistency or
> make things easier to maintain, maybe look at other Oracle features. For
> example, rather that your suggestion of a procedure that returns a
> result set, perhaps a better solution would be to define a materialised
> view and join with that view to get your final result? Perhaps use
> functions/procedures that will return a different ref cursor depending
> on some argument or maybe the whole performance can be improved by using
> a PL/SQL collection type, which is passed around to different procedures
> for additional processing so that there is only 1 initial query etc.
I think I exactly look for the answer of my question. Materialized views
do not work because I probably need 1 View for each row of my table.
With collection types I still did not work but I suppose I could not
combine it with other tables by select statement.
> Tim
Thank you
Andreas
By the way:
function MyProc(Param1 in char,Param2 in char...) return
MyPackage.MyRefTable;
can be compiled but if I use it like
Select MyProc('P1','P2') from dual
I get the error ora-00902,if
Select * from MyProc('P1','P2')
I get ora-00933
-- wenn email, dann AndreasMosmann <bei> web <punkt> deReceived on Mon Jun 20 2011 - 08:45:10 CDT