Hi,
I'm trying to understand more on dense and sparse in collections.
Here is my understanding:
Dense: If all the rows between the first and last are defined and given a value (including NULL)
Sparse: Not defined or populated sequentially. There are gaps between defined rows.
VARRAYS are always dense as we cannot delete values in between and the values can only be deleted from the end (using TRIM).
Nested Tables start with dense and can become sparse. Below example will illustrate:
DECLARE
TYPE nt_typ IS TABLE OF VARCHAR2(2);
nt1 nt_typ := nt_typ('n1','n2','n3','n4'); --at this point nested table is dense
TYPE va_typ IS VARRAY(5) OF VARCHAR2(2);
va1 va_typ := va_typ('v1','v2','v3','v4','v5'); --varray is dense
nt1_cnt number;
BEGIN
va1(3) := null; --is still dense
va1.trim(); --is still dense
nt1.DELETE(3); -- will delete n3 -- at this point nested table is sparse
nt1_cnt := nt1.COUNT; --will be 3
--But if we try to retrieve all the value of nt1 using a FOR loop
--we get no data found exception as data for index 3 doesn't exist. (works fine with WHILE loop)
--Question: if my understanding is correct based on above, how is sparse data collection useful in real time applications?
END;
/
Any additional suggestions to understand more on collections is greatly appreciated.
Using: Windows 8.1
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
"CORE 12.1.0.1.0 Production"
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production