Почему при фильтрации по двум коллекциям, время выполнения увеличивается в разы и запрос очень долго работает? При обычном IN такого не наблюдается

Рейтинг: 0Ответов: 2Опубликовано: 17.03.2023

Дано: Таблица в которой 6 млн записей. Надо собрать некую статистику за квартал. В квартал попадают 650 тысяч строк.
В процедуру приходят параметры фильтрации данных. Некоторые из них передаются в виде "массива".

В полях products и categories, допустим, лежит всего по 30 уникальных значений(в действительности 27 и 22).

Есть тип данных(коллекция):

create or replace type strings is table of varchar2(256);

Процедура:

PROCEDURE getData(
  Filter1 IN VARCHAR2, 
  Filter2 IN VARCHAR2, 
  Filter3 IN strings,
  Filter4 IN strings,
  RES OUT SYS_REFCURSOR
  ) IS
 tSQL: CLOB;
  b1 strings;
  b2 strings;
  bindNum number := 0;
  BEGIN
    tSQL := 'SELECT count(*) FROM MyTable t WHERE to_char(t.dateInsert, 'Q.YYYY') = to_char(sysdate, 'Q.YYYY') ';
    
    IF (Filter3 IS NOT NULL) THEN
      tSQl := tSQL || ' AND nvl(products, 'notProduct') IN (SELECT column_value FROM TABLE(:b1)) ';
      b1 := Filter3;
      bindNum := bindNum + 1;
    END IF;

    IF (Filter4 IS NOT NULL) THEN
      tSQl := tSQL || ' AND nvl(categories, 'notCateg') IN (SELECT column_value FROM TABLE(:b2)) ';
      b2 := Filter4;
      bindNum := bindNum + 1;
    END IF;
    
    IF (bindNum = 1) then
      OPEN res FOR tSQL USING b1;
    elsif (bindNum = 2) then
      OPEN res FOR tSQL USING b1, b2;
    ELSE
      OPEN res FOR tSQL;
    END IF;
    
  END;

Если выполнять процедуру без данных, т.е. с фильтром по текущему кварталу, то запрос выполняется за 2-3 секунды.

Добавим в параметры filter3 с количеством элементов в коллекции 28(из 30 уникальных по всей таблице) - запрос отрабатывает за 2-3 секунды

Уберем filter3 и добавим в параметры filter4 с количеством элементов в коллекции 28(из 30 уникальных по всей таблице) - запрос отрабатывает за 2-3 секунды

Выполним запрос с переданными filter3 и filter4 с наполнением по 28 элементов в каждой коллекции и запрос отрабатывает за 3-5 минут!

Окей, возможно что то с данными и статистикой по таблице, пишем запрос вручную и меняем выборку из коллекции на перечисление входящих значений в in

SELECT count(*) 
FROM MyTable
WHERE to_char(dateInsert, 'Q.YYYY') = to_char(sysdate, 'Q.YYYY')
AND nvl(products, 'notProduct') IN ('prod1', 'prod2', ...) 
AND nvl(categories, 'notCateg') IN ('categ1', 'categ2', ...)

Запрос отрабатывает за 2-3 секунды...

Как так происходит?

Почему выборка по дате и по одному из фильтров по коллекции, выполняется быстро, а по двум коллекциям, выполняется на порядки дольше? И почему такого не происходит, если заменить SELECT COLUMN_VALUE from table(collection) на явное перечисление значений?

Оракловый мониторинг OEM показывает высокое потребление CPU.

Головой понимаю, что проблема кроется где то на поверхности и явно связана с работой коллекций внутри запросов, но понять не могу... Такое ощущение, что с двумя коллекциями наступает либо какая то блокировка данных за объект, либо он как то начинает перечитывать множество раз значения из коллекции для каждой строки...

План выполнения запроса:

20  | Id  | Operation                                    | Name                  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
21  --------------------------------------------------------------------------------------------------------------------------------------------------
22  |   0 | SELECT STATEMENT                             |                       |        |       | 12808 (100)|          |       |       |          |
23  |   1 |  HASH GROUP BY                               |                       |      2 |  3246 | 12808   (1)| 00:00:01 |   686K|   686K|          |
24  |*  2 |   HASH JOIN OUTER                            |                       |      2 |  3246 | 12807   (1)| 00:00:01 |   995K|   995K|  749K (0)|
25  |*  3 |    HASH JOIN OUTER                           |                       |      2 |  2214 | 12793   (1)| 00:00:01 |  1020K|  1020K|  742K (0)|
26  |   4 |     VIEW                                     |                       |      2 |  2154 | 12786   (1)| 00:00:01 |       |       |          |
27  |   5 |      HASH GROUP BY                           |                       |      2 |  2128 | 12786   (1)| 00:00:01 |   867K|   867K|          |
28  |   6 |       VIEW                                   |                       |      2 |  2128 | 12785   (1)| 00:00:01 |       |       |          |
29  |   7 |        UNION-ALL                             |                       |        |       |            |          |       |       |          |
30  |   8 |         NESTED LOOPS                         |                       |      1 |    66 |  1113   (1)| 00:00:01 |       |       |          |
31  |   9 |          MERGE JOIN CARTESIAN                |                       |      1 |     4 |    59   (2)| 00:00:01 |       |       |          |
32  |  10 |           SORT UNIQUE                        |                       |      1 |     2 |    29   (0)| 00:00:01 |  2048 |  2048 |          |
33  |* 11 |            COLLECTION ITERATOR PICKLER FETCH |                       |      1 |     2 |    29   (0)| 00:00:01 |       |       |          |
34  |  12 |           BUFFER SORT                        |                       |      1 |     2 |    30   (4)| 00:00:01 |  2048 |  2048 |          |
35  |  13 |            SORT UNIQUE                       |                       |      1 |     2 |    29   (0)| 00:00:01 |  2048 |  2048 |          |
36  |* 14 |             COLLECTION ITERATOR PICKLER FETCH|                       |      1 |     2 |    29   (0)| 00:00:01 |       |       |          |
37  |* 15 |          TABLE ACCESS BY INDEX ROWID BATCHED | CLOSED_DEALS          |      1 |    62 |  1054   (1)| 00:00:01 |       |       |          |
38  |* 16 |           INDEX RANGE SCAN                   | IDX_DC_DEALDATEQ      |  12687 |       |    41   (0)| 00:00:01 |       |       |          |
39  |  17 |         NESTED LOOPS                         |                       |      1 |    48 | 11672   (1)| 00:00:01 |       |       |          |
40  |  18 |          MERGE JOIN CARTESIAN                |                       |      1 |     4 |    59   (2)| 00:00:01 |       |       |          |
41  |  19 |           SORT UNIQUE                        |                       |      1 |     2 |    29   (0)| 00:00:01 |  2048 |  2048 |          |
42  |* 20 |            COLLECTION ITERATOR PICKLER FETCH |                       |      1 |     2 |    29   (0)| 00:00:01 |       |       |          |
43  |  21 |           BUFFER SORT                        |                       |      1 |     2 |    30   (4)| 00:00:01 |  2048 |  2048 |          |
44  |  22 |            SORT UNIQUE                       |                       |      1 |     2 |    29   (0)| 00:00:01 |  2048 |  2048 |          |
45  |* 23 |             COLLECTION ITERATOR PICKLER FETCH|                       |      1 |     2 |    29   (0)| 00:00:01 |       |       |          |
46  |* 24 |          TABLE ACCESS BY INDEX ROWID BATCHED | DEALS                 |      3 |   132 | 11613   (1)| 00:00:01 |       |       |          |
47  |* 25 |           INDEX RANGE SCAN                   | IDX_D_CREATDATEQ      |  52206 |       |   285   (0)| 00:00:01 |       |       |          |
48  |  26 |     VIEW                                     |                       |     32 |   960 |     7  (15)| 00:00:01 |       |       |          |
49  |  27 |      UNION-ALL                               |                       |        |       |            |          |       |       |          |
50  |  28 |       TABLE ACCESS FULL                      | SPR_PRODUCTS          |     19 |   513 |     3   (0)| 00:00:01 |       |       |          |
51  |  29 |       HASH UNIQUE                            |                       |     13 |   403 |     4  (25)| 00:00:01 |  1034K|  1034K|          |
52  |  30 |        TABLE ACCESS FULL                     | SPR_PRODUCTS_DECODE   |     13 |   403 |     3   (0)| 00:00:01 |       |       |          |
53  |  31 |    VIEW                                      |                       |     48 | 24768 |    14  (22)| 00:00:01 |       |       |          |
54  |  32 |     SORT UNIQUE                              |                       |     48 |  3823 |    14  (22)| 00:00:01 |  4096 |  4096 |          |
55  |  33 |      UNION-ALL                               |                       |        |       |            |          |       |       |          |
56  |* 34 |       HASH JOIN ANTI NA                      |                       |     32 |  2848 |     6   (0)| 00:00:01 |   958K|   958K| 1294K (0)|
57  |  35 |        TABLE ACCESS FULL                     | SPR_TECHNOLOGY        |     32 |  2176 |     3   (0)| 00:00:01 |       |       |          |
58  |  36 |        TABLE ACCESS FULL                     | SPR_TECHNOLOGY_DECODE |     15 |   315 |     3   (0)| 00:00:01 |       |       |          |
59  |  37 |       TABLE ACCESS FULL                      | SPR_TECHNOLOGY_DECODE |     15 |   975 |     3   (0)| 00:00:01 |       |       |          |
60  |  38 |       FAST DUAL                              |                       |      1 |       |     2   (0)| 00:00:01 |       |       |          |
61  --------------------------------------------------------------------------------------------------------------------------------------------------
62   
63  Query Block Name / Object Alias (identified by operation id):
64  -------------------------------------------------------------
65   
66     1 - SEL$5A8733C9
67     4 - SEL$3        / VX@SEL$2
68     5 - SEL$3       
69     6 - SET$1        / GX@SEL$3
70     7 - SET$1       
71     8 - SEL$574ED37E
72    11 - SEL$574ED37E / KOKBF$0@SEL$6
73    14 - SEL$574ED37E / KOKBF$1@SEL$8
74    15 - SEL$574ED37E / CD@SEL$4
75    16 - SEL$574ED37E / CD@SEL$4
76    17 - SEL$72CD2FF1
77    20 - SEL$72CD2FF1 / KOKBF$2@SEL$11
78    23 - SEL$72CD2FF1 / KOKBF$3@SEL$13
79    24 - SEL$72CD2FF1 / CD@SEL$9
80    25 - SEL$72CD2FF1 / CD@SEL$9
81    26 - SET$3        / P@SEL$18
82    27 - SET$3       
83    28 - SEL$19       / PX@SEL$19
84    29 - SEL$20      
85    30 - SEL$20       / D@SEL$20
86    31 - SET$2        / T@SEL$2
87    32 - SET$2       
88    34 - SEL$AC77216A
89    35 - SEL$AC77216A / T@SEL$14
90    36 - SEL$AC77216A / SPR_TECHNOLOGY_DECODE@SEL$15
91    37 - SEL$16       / TD@SEL$16
92    38 - SEL$17       / DUAL@SEL$17
93   
94  Outline Data
95  -------------
96   
97    /*+
98        BEGIN_OUTLINE_DATA
99        IGNORE_OPTIM_EMBEDDED_HINTS
100       OPTIMIZER_FEATURES_ENABLE('19.1.0')
101       DB_VERSION('19.1.0')
102       ALL_ROWS
103       OUTLINE_LEAF(@"SEL$574ED37E")
104       UNNEST(@"SEL$8A3193DA")
105       UNNEST(@"SEL$ABDE6DFF")
106       OUTLINE_LEAF(@"SEL$72CD2FF1")
107       UNNEST(@"SEL$61262C81")
108       UNNEST(@"SEL$285A8194")
109       OUTLINE_LEAF(@"SET$1")
110       OUTLINE_LEAF(@"SEL$3")
111       OUTLINE_LEAF(@"SEL$AC77216A")
112       UNNEST(@"SEL$15")
113       OUTLINE_LEAF(@"SEL$16")
114       OUTLINE_LEAF(@"SEL$17")
115       OUTLINE_LEAF(@"SET$2")
116       OUTLINE_LEAF(@"SEL$19")
117       OUTLINE_LEAF(@"SEL$20")
118       OUTLINE_LEAF(@"SET$3")
119       OUTLINE_LEAF(@"SEL$5A8733C9")
120       MERGE(@"SEL$E409AC11" >"SEL$1")
121       OUTLINE(@"SEL$4")
122       OUTLINE(@"SEL$8A3193DA")
123       MERGE(@"SEL$8" >"SEL$7")
124       OUTLINE(@"SEL$ABDE6DFF")
125       MERGE(@"SEL$6" >"SEL$5")
126       OUTLINE(@"SEL$9")
127       OUTLINE(@"SEL$61262C81")
128       MERGE(@"SEL$13" >"SEL$12")
129       OUTLINE(@"SEL$285A8194")
130       MERGE(@"SEL$11" >"SEL$10")
131       OUTLINE(@"SEL$14")
132       OUTLINE(@"SEL$15")
133       OUTLINE(@"SEL$1")
134       OUTLINE(@"SEL$E409AC11")
135       MERGE(@"SEL$3B245A68" >"SEL$45368B57")
136       OUTLINE(@"SEL$7")
137       OUTLINE(@"SEL$8")
138       OUTLINE(@"SEL$5")
139       OUTLINE(@"SEL$6")
140       OUTLINE(@"SEL$12")
141       OUTLINE(@"SEL$13")
142       OUTLINE(@"SEL$10")
143       OUTLINE(@"SEL$11")
144       OUTLINE(@"SEL$45368B57")
145       ANSI_REARCH(@"SEL$21")
146       OUTLINE(@"SEL$3B245A68")
147       MERGE(@"SEL$1A566D0B" >"SEL$8799457D")
148       OUTLINE(@"SEL$21")
149       OUTLINE(@"SEL$8799457D")
150       ANSI_REARCH(@"SEL$DC20D792")
151       OUTLINE(@"SEL$1A566D0B")
152       ANSI_REARCH(@"SEL$2")
153       OUTLINE(@"SEL$DC20D792")
154       ANSI_REARCH(@"SEL$18")
155       OUTLINE(@"SEL$2")
156       OUTLINE(@"SEL$18")
157       NO_ACCESS(@"SEL$5A8733C9" "VX"@"SEL$2")
158       NO_ACCESS(@"SEL$5A8733C9" "P"@"SEL$18")
159       NO_ACCESS(@"SEL$5A8733C9" "T"@"SEL$2")
160       LEADING(@"SEL$5A8733C9" "VX"@"SEL$2" "P"@"SEL$18" "T"@"SEL$2")
161       USE_HASH(@"SEL$5A8733C9" "P"@"SEL$18")
162       USE_HASH(@"SEL$5A8733C9" "T"@"SEL$2")
163       USE_HASH_AGGREGATION(@"SEL$5A8733C9")
164       NO_ACCESS(@"SEL$3" "GX"@"SEL$3")
165       USE_HASH_AGGREGATION(@"SEL$3")
166       FULL(@"SEL$20" "D"@"SEL$20")
167       USE_HASH_AGGREGATION(@"SEL$20")
168       FULL(@"SEL$19" "PX"@"SEL$19")
169       FULL(@"SEL$16" "TD"@"SEL$16")
170       FULL(@"SEL$AC77216A" "T"@"SEL$14")
171       FULL(@"SEL$AC77216A" "SPR_TECHNOLOGY_DECODE"@"SEL$15")
172       LEADING(@"SEL$AC77216A" "T"@"SEL$14" "SPR_TECHNOLOGY_DECODE"@"SEL$15")
173       USE_HASH(@"SEL$AC77216A" "SPR_TECHNOLOGY_DECODE"@"SEL$15")
174       FULL(@"SEL$72CD2FF1" "KOKBF$2"@"SEL$11")
175       FULL(@"SEL$72CD2FF1" "KOKBF$3"@"SEL$13")
176       INDEX_RS_ASC(@"SEL$72CD2FF1" "CD"@"SEL$9" "IDX_D_CREATDATEQ")
177       BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$72CD2FF1" "CD"@"SEL$9")
178       LEADING(@"SEL$72CD2FF1" "KOKBF$2"@"SEL$11" "KOKBF$3"@"SEL$13" "CD"@"SEL$9")
179       USE_MERGE(@"SEL$72CD2FF1" "KOKBF$3"@"SEL$13")
180       USE_NL(@"SEL$72CD2FF1" "CD"@"SEL$9")
181       SEMI_TO_INNER(@"SEL$72CD2FF1" "KOKBF$2"@"SEL$11")
182       SEMI_TO_INNER(@"SEL$72CD2FF1" "KOKBF$3"@"SEL$13")
183       FULL(@"SEL$574ED37E" "KOKBF$0"@"SEL$6")
184       FULL(@"SEL$574ED37E" "KOKBF$1"@"SEL$8")
185       INDEX_RS_ASC(@"SEL$574ED37E" "CD"@"SEL$4" "IDX_DC_DEALDATEQ")
186       BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$574ED37E" "CD"@"SEL$4")
187       LEADING(@"SEL$574ED37E" "KOKBF$0"@"SEL$6" "KOKBF$1"@"SEL$8" "CD"@"SEL$4")
188       USE_MERGE(@"SEL$574ED37E" "KOKBF$1"@"SEL$8")
189       USE_NL(@"SEL$574ED37E" "CD"@"SEL$4")
190       SEMI_TO_INNER(@"SEL$574ED37E" "KOKBF$0"@"SEL$6")
191       SEMI_TO_INNER(@"SEL$574ED37E" "KOKBF$1"@"SEL$8")
192       END_OUTLINE_DATA
193   */
194  
195 Predicate Information (identified by operation id):
196 ---------------------------------------------------
197  
198    2 - access("T"."TKEY"="VX"."TEHNOLOGY")
199    3 - access("P"."PKEY"="VX"."PRODUCT")
200   11 - filter((VALUE(KOKBF$)<>'CreditPotential' AND VALUE(KOKBF$)<>'Restructuring' AND VALUE(KOKBF$)<>'nullProduct'))
201   14 - filter(VALUE(KOKBF$)<>'ChangeLoan')
202   15 - filter(("SROK">1 AND NVL("TEHNOLOGY",'nullTechnology')=VALUE(KOKBF$) AND NVL("PRODUCT",'nullProduct')=VALUE(KOKBF$) AND CASE  WHEN 
203               ("PRODUCT"='Oborot' AND NVL("TEHNOLOGY",'NotTechnology')='NotTechnology' AND NVL("KISTAGE",(-1))<>(-1)) THEN 0 ELSE 1 END =1 AND 
204               "AMOUNT">1 AND NVL("PRODUCT",'nullProduct')<>'Restructuring' AND NVL("PRODUCT",'nullProduct')<>'nullProduct' AND 
205               NVL("PRODUCT",'nullProduct')<>'CreditPotential' AND NVL("TEHNOLOGY",'nullTechnology')<>'ChangeLoan'))
206   16 - access("CD"."SYS_NC00074$"='1.2023')
207   20 - filter((VALUE(KOKBF$)<>'CreditPotential' AND VALUE(KOKBF$)<>'Restructuring' AND VALUE(KOKBF$)<>'nullProduct'))
208   23 - filter(VALUE(KOKBF$)<>'ChangeLoan')
209   24 - filter((NVL("TEHNOLOGY",'nullTechnology')=VALUE(KOKBF$) AND NVL("PRODUCT",'nullProduct')=VALUE(KOKBF$) AND CASE  WHEN 
210               ("PRODUCT"='Oborot' AND NVL("TEHNOLOGY",'NotTechnology')='NotTechnology' AND NVL("KISTAGE",(-1))<>(-1)) THEN 0 ELSE 1 END =1 AND "SROK">1 
211               AND "AMOUNT">1 AND NVL("PRODUCT",'nullProduct')<>'Restructuring' AND NVL("TEHNOLOGY",'nullTechnology')<>'ChangeLoan' AND 
212               NVL("PRODUCT",'nullProduct')<>'CreditPotential' AND NVL("PRODUCT",'nullProduct')<>'nullProduct'))
213   25 - access("CD"."SYS_NC00076$"='1.2023')
214   34 - access("T"."TKEY"="TOLDKEY")
215  
216 Column Projection Information (identified by operation id):
217 -----------------------------------------------------------
218  
219    1 - "VX"."ISCLIENT"[NUMBER,22], "VX"."ISCREDIT"[NUMBER,22], "VX"."DEALSOURCE"[VARCHAR2,1024], NVL("P"."PGROUP",'Нет продукта')[512], 
220        NVL("T"."TGROUP",'Нет технологии')[512], "VX"."STATUS"[VARCHAR2,1024], "VX"."ISAPPROVED"[NUMBER,22], SUM("VX"."AMSUM")[22], 
221        SUM("VX"."CNT")[22]
222    2 - (#keys=1) "VX"."ISCLIENT"[NUMBER,22], "VX"."ISCREDIT"[NUMBER,22], "VX"."DEALSOURCE"[VARCHAR2,1024], "VX"."AMSUM"[NUMBER,22], 
223        "P"."PGROUP"[VARCHAR2,512], "VX"."STATUS"[VARCHAR2,1024], "VX"."ISAPPROVED"[NUMBER,22], "VX"."CNT"[NUMBER,22], "T"."TGROUP"[VARCHAR2,512]
224    3 - (#keys=1) "VX"."ISCLIENT"[NUMBER,22], "VX"."ISCREDIT"[NUMBER,22], "VX"."DEALSOURCE"[VARCHAR2,1024], "VX"."AMSUM"[NUMBER,22], 
225        "VX"."TEHNOLOGY"[VARCHAR2,1024], "VX"."STATUS"[VARCHAR2,1024], "VX"."ISAPPROVED"[NUMBER,22], "VX"."CNT"[NUMBER,22], 
226        "P"."PGROUP"[VARCHAR2,512]
227    4 - "VX"."ISCLIENT"[NUMBER,22], "VX"."ISCREDIT"[NUMBER,22], "VX"."DEALSOURCE"[VARCHAR2,1024], "VX"."PRODUCT"[VARCHAR2,1024], 
228        "VX"."TEHNOLOGY"[VARCHAR2,1024], "VX"."STATUS"[VARCHAR2,1024], "VX"."ISAPPROVED"[NUMBER,22], "VX"."CNT"[NUMBER,22], 
229        "VX"."AMSUM"[NUMBER,22]
230    5 - "GX"."ISCLIENT"[NUMBER,22], "GX"."ISCREDIT"[NUMBER,22], "GX"."DEALSOURCE"[VARCHAR2,1024], "GX"."PRODUCT"[VARCHAR2,1024], 
231        "GX"."TEHNOLOGY"[VARCHAR2,1024], "GX"."STATUS"[VARCHAR2,1024], "GX"."ISAPPROVED"[NUMBER,22], COUNT(*)[22], SUM("GX"."AMOUNT")[22]
232    6 - "GX"."ISCLIENT"[NUMBER,22], "GX"."ISCREDIT"[NUMBER,22], "GX"."DEALSOURCE"[VARCHAR2,1024], "GX"."PRODUCT"[VARCHAR2,1024], 
233        "GX"."TEHNOLOGY"[VARCHAR2,1024], "GX"."STATUS"[VARCHAR2,1024], "GX"."ISAPPROVED"[NUMBER,22], "GX"."AMOUNT"[NUMBER,22]
234    7 - STRDEF[22], STRDEF[22], STRDEF[1024], STRDEF[1024], STRDEF[1024], STRDEF[1024], STRDEF[22], STRDEF[22]
235    8 - "CD"."DEALSOURCE"[VARCHAR2,1024], "CD"."STATUS"[VARCHAR2,1024], "PRODUCT"[VARCHAR2,1024], "TEHNOLOGY"[VARCHAR2,1024], 
236        "AMOUNT"[NUMBER,22], "CD"."ISAPPROVED"[NUMBER,22], "CD"."ISCLIENT"[NUMBER,22], "CD"."ISCREDIT"[NUMBER,22]
237    9 - VALUE(KOKBF$)[256], VALUE(KOKBF$)[256]
238   10 - (#keys=1) VALUE(KOKBF$)[256]
239   11 - VALUE(A0)[256]
240   12 - (#keys=0) VALUE(KOKBF$)[256]
241   13 - (#keys=1) VALUE(KOKBF$)[256]
242   14 - VALUE(A0)[256]
243   15 - "CD"."DEALSOURCE"[VARCHAR2,1024], "CD"."STATUS"[VARCHAR2,1024], "PRODUCT"[VARCHAR2,1024], "TEHNOLOGY"[VARCHAR2,1024], 
244        "AMOUNT"[NUMBER,22], "CD"."ISAPPROVED"[NUMBER,22], "CD"."ISCLIENT"[NUMBER,22], "CD"."ISCREDIT"[NUMBER,22]
245   16 - "CD".ROWID[ROWID,10], "CD"."SYS_NC00074$"[VARCHAR2,6]
246   17 - "CD"."DEALSOURCE"[VARCHAR2,1024], "PRODUCT"[VARCHAR2,1024], "TEHNOLOGY"[VARCHAR2,1024], "AMOUNT"[NUMBER,22], 
247        "CD"."ISAPPROVED"[NUMBER,22], "CD"."ISCLIENT"[NUMBER,22], "CD"."ISCREDIT"[NUMBER,22]
248   18 - VALUE(KOKBF$)[256], VALUE(KOKBF$)[256]
249   19 - (#keys=1) VALUE(KOKBF$)[256]
250   20 - VALUE(A0)[256]
251   21 - (#keys=0) VALUE(KOKBF$)[256]
252   22 - (#keys=1) VALUE(KOKBF$)[256]
253   23 - VALUE(A0)[256]
254   24 - "CD"."DEALSOURCE"[VARCHAR2,1024], "PRODUCT"[VARCHAR2,1024], "TEHNOLOGY"[VARCHAR2,1024], "AMOUNT"[NUMBER,22], 
255        "CD"."ISAPPROVED"[NUMBER,22], "CD"."ISCLIENT"[NUMBER,22], "CD"."ISCREDIT"[NUMBER,22]
256   25 - "CD".ROWID[ROWID,10], "CD"."SYS_NC00076$"[VARCHAR2,6]
257   26 - "P"."PKEY"[VARCHAR2,512], "P"."PGROUP"[VARCHAR2,512]
258   27 - STRDEF[512], STRDEF[512]
259   28 - "PX"."PKEY"[VARCHAR2,512], "PX"."PGROUP"[VARCHAR2,512]
260   29 - "D"."PKEY"[VARCHAR2,512], "D"."PGROUP"[VARCHAR2,512]
261   30 - (rowset=119) "D"."PKEY"[VARCHAR2,512], "D"."PGROUP"[VARCHAR2,512]
262   31 - "T"."TKEY"[VARCHAR2,512], "T"."TGROUP"[VARCHAR2,512]
263   32 - (#keys=3) STRDEF[512], STRDEF[1024], STRDEF[512]
264   33 - STRDEF[512], STRDEF[1024], STRDEF[512]
265   34 - (#keys=1) "T"."TKEY"[VARCHAR2,512], "T"."TGROUP"[VARCHAR2,512], "T"."TVALUE"[VARCHAR2,1024]
266   35 - "T"."TKEY"[VARCHAR2,512], "T"."TVALUE"[VARCHAR2,1024], "T"."TGROUP"[VARCHAR2,512]
267   36 - "TOLDKEY"[VARCHAR2,512]
268   37 - "TD"."TKEY"[VARCHAR2,512], "TD"."TVALUE"[VARCHAR2,1024], "TD"."TGROUP"[VARCHAR2,512]
269  
270 Note
271 -----
272    - Warning: basic plan statistics not available. These are only collected when:
273        * hint 'gather_plan_statistics' is used for the statement or
274        * parameter 'statistics_level' is set to 'ALL', at session or system level
275
sql коллекции oracle plsql
Источник: Stack Overflow на русском

Ответы

▲ 1

С одной коллекцией обращение по индексу в таблицу происходило 28 раз.
С двумя коллекциями благодаря MERGE JOIN CARTESIAN обращение происходит 28*28 раз.
Избавляйся от MERGE JOIN CARTESIAN.

▲ 1

почему такого не происходит, если заменить SELECT COLUMN_VALUE from table(collection) на явное перечисление значений?

Потому что когда перечисляете значения в запросе, анализатор может их посчитать и понять, что их не так много, и сделать план по проще.

Важно заметить, что план который вы привели, показывает что ваш настоящий запрос посложнее, нежели вы привели в примере. Насколько это важно не знаю, но дальше я буду писать о вашем настоящем запросе.

Дабы не сканить ваши переданные table для каждой строки deals\closedDeals, oracle строит из первых хэш-таблицы в памяти. На это и расходуется CPU. Причём, это приводит к фулскану spr_products с построением хэша для SPR_PRODUCTS_DECODE, а для второго table есть даже два фулскана SPR_TECHNOLOGY_DECODE. Почему происходит, мы опустим так как детали того, что вы передаёте неизвестны.

Я уже плохо помню oracle, но в ms sql server можно явно сделать табличную переменную индексированной, и может это немного помогло бы.

На самом деле вам это не нужно, делайте join (как сказали в комментариях) и тогда может быть заработают индексы в SPR_PRODUCTS и SPR_TECHNOLOGY, если они там есть и нужны.

И кстати, возможно вам нужен index в deals таблице не по полю с датой, а по функции вычисляющей квартал и хорошо бы туда же положить ключи для доступа к SPR_PRODUCTS и SPR_TECHNOLOGY. Ну и нормализация бы тоже не помешала, зачем вы храните эти вещи строкой?