Jump to content
skywalker

Storing the reference of TABLE function

Recommended Posts

Hi,

I am using VARRAY in my stored procedure. To use it in select statement I have to TABLE function as shown below.

SELECT * FROM TABLE(bulk_coll_arr) 

I am using this TABLE function with bulk_coll_arr varray at two places. So this TABLE function is being used invoked two times.

Is there any way to store the output of this TABLE(bulk_coll_arr) in some pl/sql variable so that I can using pl/sql variable in select statement and TABLE function will invoked only once?

I am looking for something like this...

temp_tab = TABLE(bulk_coll_arr);

select * form temp_tab;

 

Can you please explain what TABLE function does internally to convert varray to virtual or in memmory table?

Will it cause a performance issue if used with an array of size 100k?

 

Share this post


Link to post
Share on other sites

Hi,

As I understand it, the TABLE function simply bulk reads the table rows and places the output into a RAM VARRAY.

probided that you have the memory, it should work for larger arrays.

I’m not a PL/SQL guru, but I have asked one to pop in and advise you!

Also, not how parallelism can help you:

http://www.dba-oracle.com/plsql/t_plsql_pipelining.htm

And here:

http://www.dba-oracle.com/t_plsql_associative_array_example.htm

Stand by for more . . . 

Share this post


Link to post
Share on other sites

Hello Skywalker,

You use TABLE function only when you want to join a TYPE to a TABLE for manipulation. I don't see any problem in using it twice if that's what your business demands.

If you still want to use a common PL/SQL form of storage instead of using the TABLE function, you can very well assign it to a cursor like below and use it in your PL/SQL code.

open cur for select * from table(your type);

But, As your type BULK_COLL_ARRAY is in your memory already, you wont find any difference in TABLE(bulk_coll_arr) being called twice or more. I recommend you to use it the same way as it is unless you have any other performance issues.

Quote

 

I am looking for something like this...

temp_tab = TABLE(bulk_coll_arr);

select * form temp_tab;

 

You cannot do the above! You cannot assign a table into a variable, but you can assign it to a TYPE, but still you cannot call a type in the SELECT statement without using the TABLE function.

When you use the TABLE function, Oracle internally converts it into a GTT kind of table for you to use it only in your current session. 

And for your question regarding the size of the array, you are good if you have enough space in your PGA memory as COLLECTIONs use PGA memory and not SGA. But if its more than 100k, its sensible to put it

in a table rather than in a TYPE just so to not to over-do your PGA. TYPES are most commonly used when you work with little to medium amount of data.

For more information on Collections and other PL/SQL topics, please refer to my best selling PL/SQL book from my signature.

 

Thank you,

Boobal Ganesan 

Author of the Advanced PL/SQL Programming book

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×