hi,
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
/
The query gets all the data, so RETURN AS LOCATOR will have no effect. You've asked for the actual values, so the database can't return just a locator.
To spot the difference, query the base table without expanding out the nested tables into rows. Then look at the plan:
set serveroutput off
alter session set statistics_level = all;
select *
from tab1 t0;
select * from dbms_xplan.display_cursor ( format => 'ALLSTATS LAST');
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 7 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CVAL1_NTAB | 2 | 1 | 4 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | SYS_FK0000179052N00002$ | 2 | 1 | 4 |00:00:00.01 | 2 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| COL2_NTAB | 1 | 1 | 2 |00:00:00.01 | 2 |
|* 4 | INDEX RANGE SCAN | SYS_FK0000179051N00002$ | 1 | 1 | 2 |00:00:00.01 | 1 |
| 5 | TABLE ACCESS FULL | TAB1 | 1 | 1 | 1 |00:00:00.01 | 7 |
-------------------------------------------------------------------------------------------------------------------------
Notice the table accesses for CVAL1_NTAB and COL2_NTAB.
There's no reference to COL3_NTAB because you've set this to RETURN AS LOCATOR.
If you add the hint, the query returns all nested tables as locators, so the plan just shows TAB1:
select /*+ NESTED_TABLE_GET_REFS */ *
from tab1 t0;
select * from dbms_xplan.display_cursor ( format => 'ALLSTATS LAST');
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 7 |
| 1 | TABLE ACCESS FULL| TAB1 | 1 | 1 | 1 |00:00:00.01 | 7 |
--------------------------------------------------------------------------
As you fiddle with different locator settings in the table, check the plan to see which nested tables are accessed.