上一篇文章中介紹了如何對Oracle中Struct和Array進行Explain Plan，
- EXPLAIN PLAN FOR
- SELECT * FROM TABLE(DEPT_ARRAY(DEPARTMENT_TYPE(
- 1, --DNO NUMBER (10),
- 'NAME', --NAME VARCHAR2 (50),
- 'LOCATION' --LOCATION VARCHAR2 (50)
- ))) T
- JOIN TABLE2 T2 ON T.DNO = T2.DNO;
- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
運行出來的結果中會有這樣的資料：
- PLAN_TABLE_OUTPUT
- 1 Plan hash value: 1748000095
- 2
- 3 ----------------------------------------------------------------------------------------------
- 4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- 5 ----------------------------------------------------------------------------------------------
- 6 | 0 | SELECT STATEMENT | | 8168 | 16336 | 24 (0)| 00:00:01 |
- 7 | 1 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | | | |
- 8 ----------------------------------------------------------------------------------------------
optimizer預設Array中資料行數為8168， 但是由於情景不同，放在Array中資料的行數不盡相同。使用cardinality hint來讓optimizer知道Array中資料的行數。
- EXPLAIN PLAN FOR
- SELECT /*+ cardinality(t 10 ) */ * FROM TABLE(DEPT_ARRAY(DEPARTMENT_TYPE(
- 1, --DNO NUMBER (10),
- 'NAME', --NAME VARCHAR2 (50),
- 'LOCATION' --LOCATION VARCHAR2 (50)
- ))) T
- JOIN TABLE2 T2 ON T.DNO = T2.DNO;
- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
運行出來的結果：
- PLAN_TABLE_OUTPUT
- 1 Plan hash value: 1748000095
- 2
- 3 ----------------------------------------------------------------------------------------------
- 4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- 5 ----------------------------------------------------------------------------------------------
- 6 | 0 | SELECT STATEMENT | | 10 | 20 | 24 (0)| 00:00:01 |
- 7 | 1 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | | | |
- 8 ----------------------------------------------------------------------------------------------
數字10為Array中資料的最大行數。
參考：
the cardinality hint when you are using PL/SQL collections in SQL
