Explain plan for struct and array in Oracle

The previous article introduced how to explain the struct and array in Oracle,

1. Explain plan
2. Select * from table (dept_array (department_type (
3. 1, -- DNO number (10 ),
4. 'Name', -- name varchar2 (50 ),
5. 'Location' -- location varchar2 (50)
6. ) T
7. Join Table2 T2 on T. DNO = t2.dno;
8. Select * from table (dbms_xplan.display );

The running result contains the following data:

1. Plan_table_output
2. 1 plan hash value: 1748000095
3. 2
4. 3 Cores ----------------------------------------------------------------------------------------------
5. 4 | ID | operation | Name | rows | bytes | cost (% CPU) | time |
6. 5 Cores ----------------------------------------------------------------------------------------------
7. 6 | 0 | SELECT statement |8168| 16336 | 24 (0) | 00:00:01 |
8. 7 | 1 | collection iterator constructor fetch |
9. 8 bytes ----------------------------------------------------------------------------------------------

By default, the number of data rows in array is 8168. However, the number of rows in array varies depending on different scenarios. Use cardinality hint to let optimizer know the number of rows in the array.

1. Explain plan
2. Select/* + cardinality (T10) */* From table (dept_array (department_type (
3. 1, -- DNO number (10 ),
4. 'Name', -- name varchar2 (50 ),
5. 'Location' -- location varchar2 (50)
6. ) T
7. Join Table2 T2 on T. DNO = t2.dno;
8. Select * from table (dbms_xplan.display );

Running result:

1. Plan_table_output
2. 1 plan hash value: 1748000095
3. 2
4. 3 Cores ----------------------------------------------------------------------------------------------
5. 4 | ID | operation | Name | rows | bytes | cost (% CPU) | time |
6. 5 Cores ----------------------------------------------------------------------------------------------
7. 6 | 0 | SELECT statement |10| 20 | 24 (0) | 00:00:01 |
8. 7 | 1 | collection iterator constructor fetch |
9. 8 bytes ----------------------------------------------------------------------------------------------

The number 10 is the maximum number of rows in array.

 

Refer:

The cardinality hint when you are using PL/SQL collections in SQL