Home » SQL & PL/SQL » SQL & PL/SQL » Queries regarding Associative Arrays (merged topics)
Queries regarding Associative Arrays (merged topics) [message #234917] Thu, 03 May 2007 08:10 Go to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
I have some queries regarding Associative arrays :--

1)The memory allocation/de-allocation for associative arrays is managed automatically by Oracle or NOT,or we should explicitly manage.
2)How to use EXTEND builtin collection procedure for associative arrays.
3)Is it true that PL/SQL arrays should not be used if there is any data more than a few 1000 rows/records/cells should rather reside in Oracle tables and not PL/SQL arrays.



Regards
cgk1983
Questions regarding associative arrays [message #234932 is a reply to message #234917] Thu, 03 May 2007 08:48 Go to previous messageGo to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
I have some queries regarding Associative arrays :--

1)The memory allocation/de-allocation for associative arrays is managed automatically by Oracle or NOT,or we should explicitly manage.
2)How to use EXTEND builtin collection procedure for associative arrays.
3)Is it true that PL/SQL arrays should not be used if there is any data more than a few 1000 rows/records/cells should rather reside in Oracle tables and not PL/SQL arrays.



Regards
cgk1983
Re: Questions regarding associative arrays [message #234938 is a reply to message #234932] Thu, 03 May 2007 09:07 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
> 1)The memory allocation/de-allocation for associative arrays is managed automatically by Oracle or NOT,or we should explicitly manage.

Memory is automatically managed. If you "destroy" the array by assigning it to NULL, using the DELETE method to delete all entries, or assign it to another hash, the used memory will be deallocated.


> 2)How to use EXTEND builtin collection procedure for associative arrays.

I don't understand your question.


> 3)Is it true that PL/SQL arrays should not be used if there is any data more than a few 1000 rows/records/cells should rather reside in Oracle tables and not PL/SQL arrays.

How you want to process your data is a better determinant of PL/SQL tables vs. Database Tables. PL/SQL tables are really good if you want to iterate though PL/SQL logic. Database tables (including Global Temporary Tables) are really good when you want to apply set-based logic (eg. UPDATE statement) to a large data set. You should never perform SQL logic inside an array loop or cursor loop - that is catastrphically inefficient.

Set-based logic is typically more effiecient, but sometimes impractiacal for conditional/branching/iterative/recusive logic. If you want to use PL/SQL logic over a large data set, use a PL/SQL array, but try to batch it into sets of 1000 rows so that it does not swap to disk.

Ross Leishman

Re: Queries regarding Associative Arrays (merged topics) [message #234946 is a reply to message #234917] Thu, 03 May 2007 09:21 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
1) Memory is allocated from the user's PGA. The total available memory is determined by init.ora parameters such as workarea_size_policy and pga_aggregate_target.

2) You don't. Associative arrays are sparse and an element is automatically created when assign a value to it. You can delete it by using arrayval.delete.

3) I don't think there are any hard and fast rules here. For a start, it depends on how wide the array is as well as how long. If each element in the array is a record type with 500 columns, then that will obviously use more memory than one with a single column.

I've made quite extensive use of these to implement lookup type functionality and have regularly used arrays with 50 or 60 thousand elements on them. I've done tests on them where I've had narrow arrays of over a million elements.

[Updated on: Thu, 03 May 2007 09:22]

Report message to a moderator

Previous Topic: substring problem
Next Topic: Spacing.
Goto Forum:
  


Current Time: Sat Dec 03 10:05:35 CST 2016

Total time taken to generate the page: 0.08984 seconds