Hi Team,
There's a function called SPLIT which was used to split our variable data into multi rows (fields terminated by '|'), for example turn 11|22|33 into 3 rows:11,22 and 33. When we tried to query that table with JOIN, oops, the cost was up to 131K, our customer believed that we should optimize the SQL statement.
HERE is the DEMO:
SQL> create or replace type type_split is table of varchar2(30000); PLZ NOTICE THAT THE LENGTH IS 30000
2 /
Type created.
SQL> create or replace function split
2 (
3 p_list varchar2,
4 p_sep varchar2 := ','
5 ) return type_split pipelined
6 is
7 l_idx pls_integer;
8 v_list varchar2(4000) := p_list;
9 begin
10 loop
11 l_idx := instr(v_list,p_sep);
12 if l_idx > 0 then
13 pipe row(substr(v_list,1,l_idx-1));
14 v_list := substr(v_list,l_idx+length(p_sep));
15 else
16 pipe row(v_list);
17 exit;
18 end if;
19 end loop;
20 return;
21 end split;
22 /
Function created.
SQL> set linesize 160
SQL> SELECT SUM(SUM_QTY), MAX(SUM_QTY)
2 FROM (SELECT A.EC_NAME, B.POSITION_NAME, SUM(C.ECQ_NAME) SUM_QTY
3 FROM (SELECT ROWNUM EC_NUM, A.COLUMN_VALUE EC_NAME
4 FROM TABLE(SPLIT('11|22|33|44', '|')) A) A
5 LEFT JOIN (SELECT ROWNUM POSITION_NUM, B.COLUMN_VALUE POSITION_NAME
6 FROM TABLE(SPLIT('11|22|33|44', '|')) B) B
7 ON A.EC_NUM = B.POSITION_NUM
8 LEFT JOIN (SELECT ROWNUM ECQ_NUM, C.COLUMN_VALUE ECQ_NAME
9 FROM TABLE(SPLIT('11|22|33|44', '|')) C) C
10 ON A.EC_NUM = C.ECQ_NUM
11 GROUP BY A.EC_NAME, B.POSITION_NAME);
Execution Plan
----------------------------------------------------------
Plan hash value: 2535028843
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | | 131K (2)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 54M| 675M| | 131K (2)| 00:00:06 |
| 3 | HASH GROUP BY | | 54M| 306G| | 131K (2)| 00:00:06 |
|* 4 | HASH JOIN RIGHT OUTER | | 54M| 306G| 15M| 130K (1)| 00:00:06 |
| 5 | VIEW | | 8168 | 15M| | 29 (0)| 00:00:01 |
| 6 | COUNT | | | | | | |
| 7 | COLLECTION ITERATOR PICKLER FETCH | SPLIT | 8168 | 16336 | | 29 (0)| 00:00:01 |
|* 8 | HASH JOIN OUTER | | 667K| 2564M| 15M| 1627 (1)| 00:00:01 |
| 9 | VIEW | | 8168 | 15M| | 29 (0)| 00:00:01 |
| 10 | COUNT | | | | | | |
| 11 | COLLECTION ITERATOR PICKLER FETCH| SPLIT | 8168 | 16336 | | 29 (0)| 00:00:01 |
| 12 | VIEW | | 8168 | 15M| | 29 (0)| 00:00:01 |
| 13 | COUNT | | | | | | |
| 14 | COLLECTION ITERATOR PICKLER FETCH| SPLIT | 8168 | 16336 | | 29 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."EC_NUM"="C"."ECQ_NUM"(+))
8 - access("A"."EC_NUM"="B"."POSITION_NUM"(+))
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
161 recursive calls
0 db block gets
377 consistent gets
0 physical reads
0 redo size
624 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedHere's my questions:
1) How does oracle generate the execution plan? the TABLE(SPLIT()) is not a heap table, I guess there was no histogram and the result of TABLE(SPLIT()) was resident in PGA (I had found the event 'PGA memory operation') during executions. By the way, if I reduce the length of type named 'type_split' in this case (say from 30000 to 3000), the COST would reduced somehow.
2) Will Adaptive Cursor Sharing available?
3) What do you guys suggest to optimize our query in this case? The COST seems depressed me.