Refer to object-relational-developers-guide/Nested Table Locators
‘For large child sets, the parent row and a locator to the child set can be returned so that the child rows can be accessed on demand; the child sets also can be filtered. Using nested table locators enables you to avoid unnecessarily transporting child rows for every parent.’ I couldn’t find any difference in size column by using ‘return as locator’ , would you please see my examples as follows and let me know how should I check effect of using ‘return as locator’ if its possible to track changes through column size after data insert or some thing like that? thank you.
create type inner_table as table of number;
/
create type middle_table as table of inner_table;
/
create type outer_table as table of middle_table;
/
create table tab1
(
col1 number,
col2 outer_table
)
nested table col2
store as col2_ntab (
nested table column_value
store as cval1_ntab (
nested table column_value
store as cval2_ntab
return as locator));
/
insert into tab1
values (2,
outer_table (middle_table (inner_table (1,
2,
3,
4,
5),
inner_table (10,
20,
30,
40,
50)),
middle_table (inner_table (1000,
2000,
3000,
4000,
5000),
inner_table (10000,
20000,
30000,
40000,
50000))))
/
select /*+ NESTED_TABLE_GET_REFS +*/
col1,
col2,
t1.*,
t2.*,
t3.*
from tab1 t0,
table (t0.col2) t1,
table (t1.column_value) t2,
table (t2.column_value) t3
/