Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Подзапрос + reg_exp + :bind + or = тормоза / 7 сообщений из 7, страница 1 из 1
25.06.2018, 12:56
    #39665283
shurka22
shurka22
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подзапрос + reg_exp + :bind + or = тормоза
Коллеги, привет.

Вкратце:
запрос вида "
Код: plaintext
select * from tab where fe=:1 or bo in (:2)
" - тормозил, пришлось заменить на
"
Код: plaintext
1.
2.
select * from tab where fe=:1 
union 
select * from tab where bo in (:2)
"
Но как-то криво. Нет ли решения изящнее?



Детальнее:

Я опять со своими хитрыми запросами, которые тормозят. В этот раз база одна, никаких дб-линков или политик.

Имеем таблицу TAB (миллионы записей), в которой есть 2 индекса: по полю FE, и по полю BO.
У меня есть одно значение FE (пусть 1), и несколько (обычно 1, но бывает и до 10) значений BO (в строке, через запятую, пусть '2,3,4').
Мне нужно выбрать все записи с такими FE и BO.

Надо как-то строку привести в таблицу. Гуглю, нахожу чудесную конструкцию:
Код: plaintext
SELECT REGEXP_SUBSTR(:BO, '[^,]+', 1, LEVEL) FROM DUAL CONNECT BY INSTR(:BO, ',', 1, LEVEL-1) > 0

В итоге наш АРМ (конструктор запросов) начинает тормозить.

Иду от простого:
Код: plaintext
SELECT * FROM TAB where FE = 1 or BO in (2,3,4);
работает! Встаёт на индексы и выполняется быстро.

Усложняю (добавляю разбор строки через regexp):
Код: plaintext
SELECT * FROM TAB where FE = 1 or BO in ( SELECT REGEXP_SUBSTR('2,3,4', '[^,]+', 1, LEVEL) FROM DUAL CONNECT BY INSTR('2,3,4', ',', 1, LEVEL-1) > 0 );
Тормозим.

Гуглю, добавляю хинт PRECOMPUTE_SUBQUERY
Код: plaintext
SELECT * FROM TAB where FE = 1 or BO in ( SELECT /*+ PRECOMPUTE_SUBQUERY */ REGEXP_SUBSTR('2,3,4', '[^,]+', 1, LEVEL) FROM DUAL CONNECT BY INSTR('2,3,4', ',', 1, LEVEL-1) > 0 );
И опять всё летает.


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
---------------------------------------------------------------------------------------------------------
| Id   | Operation                          | Name                     | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                   |                          |    4 |  2008 |   12 | 00:00:01 |
|    1 |   TABLE ACCESS BY INDEX ROWID      | TAB                      |    4 |  2008 |   12 | 00:00:01 |
|    2 |    BITMAP CONVERSION TO ROWIDS     |                          |      |       |      |          |
|    3 |     BITMAP OR                      |                          |      |       |      |          |
|    4 |      BITMAP CONVERSION FROM ROWIDS |                          |      |       |      |          |
|  * 5 |       INDEX RANGE SCAN             | TAB_FE_NDX               |      |       |    3 | 00:00:01 |
|    6 |      BITMAP CONVERSION FROM ROWIDS |                          |      |       |      |          |
|  * 7 |       INDEX RANGE SCAN             | TAB_PK                   |      |       |    2 | 00:00:01 |
|    8 |      BITMAP CONVERSION FROM ROWIDS |                          |      |       |      |          |
|  * 9 |       INDEX RANGE SCAN             | TAB_PK                   |      |       |    2 | 00:00:01 |
|   10 |      BITMAP CONVERSION FROM ROWIDS |                          |      |       |      |          |
| * 11 |       INDEX RANGE SCAN             | TAB_PK                   |      |       |    2 | 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("FE"=1)
* 7 - access("BO"=2)
* 9 - access("BO"=3)
* 11 - access("BO"=4)
Но АРМ не умеет отдавать такие запросы, он все параметры передаёи через bind-переменные. И в итоге запрос получается следующий:
Код: plaintext
SELECT * FROM TAB where FE = :FE or BO in ( SELECT /*+ PRECOMPUTE_SUBQUERY */ REGEXP_SUBSTR(:BO, '[^,]+', 1, LEVEL) FROM DUAL CONNECT BY INSTR(:BO, ',', 1, LEVEL-1) > 0 );
Запрос дохнет, на индекс не встаёт.

Хуже того, достаточно только значение 1 заменить на :FE (без замены '2,3,4' на :BO) - уже дохнем:
Код: plaintext
SELECT * FROM TAB where FE = :FE or BO in ( SELECT /*+ PRECOMPUTE_SUBQUERY */ REGEXP_SUBSTR('2,3,4', '[^,]+', 1, LEVEL) FROM DUAL CONNECT BY INSTR('2,3,4', ',', 1, LEVEL-1) > 0 );

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name       | Rows   | Bytes    | Cost | Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |            |   5320 |  2670640 | 2142 | 00:00:24 |
| * 1 |   FILTER                                  |            |        |          |      |          |
|   2 |    TABLE ACCESS FULL                      | TAB        | 106368 | 53396736 | 2142 | 00:00:24 |
| * 3 |    FILTER                                 |            |        |          |      |          |
| * 4 |     CONNECT BY WITHOUT FILTERING (UNIQUE) |            |        |          |      |          |
|   5 |      FAST DUAL                            |            |      1 |          |    2 | 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("FE"=TO_NUMBER(:FE) OR EXISTS (SELECT /*+ PRECOMPUTE_SUBQUERY */ 0 FROM "SYS"."DUAL" "DUAL" WHERE TO_NUMBER( REGEXP_SUBSTR ('2,3,4','[^,]+',1,LEVEL))=:B1 CONNECT BY INSTR('2,3,4',',',1,LEVEL-1)>0))
* 3 - filter(TO_NUMBER( REGEXP_SUBSTR ('2,3,4','[^,]+',1,LEVEL))=:B1)
* 4 - filter(INSTR('2,3,4',',',1,LEVEL-1)>0)

Но жить как-то надо, поэтому выкручиваемся через union:
Код: plaintext
1.
2.
select * from tab where FE = :FE
union
select * from tab where BO in ( SELECT /*+ PRECOMPUTE_SUBQUERY */ REGEXP_SUBSTR(:BO, '[^,]+', 1, LEVEL) FROM DUAL CONNECT BY INSTR(:BO, ',', 1, LEVEL-1) > 0 );
и о чудо - работает!

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
--------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                     | Name                     | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                              |                          |    2 |  1022 |   11 | 00:00:01 |
|    1 |   SORT UNIQUE                                 |                          |    2 |  1022 |   11 | 00:00:01 |
|    2 |    UNION-ALL                                  |                          |      |       |      |          |
|    3 |     TABLE ACCESS BY INDEX ROWID               | TAB                      |    1 |   502 |    4 | 00:00:01 |
|  * 4 |      INDEX RANGE SCAN                         | TAB_FE_NDX               |    1 |       |    3 | 00:00:01 |
|    5 |     NESTED LOOPS                              |                          |    1 |   520 |    5 | 00:00:01 |
|    6 |      NESTED LOOPS                             |                          |    1 |   520 |    5 | 00:00:01 |
|    7 |       VIEW                                    | VW_NSO_1                 |    1 |    18 |    3 | 00:00:01 |
|    8 |        HASH UNIQUE                            |                          |    1 |       |    3 | 00:00:01 |
|  * 9 |         CONNECT BY WITHOUT FILTERING (UNIQUE) |                          |      |       |      |          |
|   10 |          FAST DUAL                            |                          |    1 |       |    2 | 00:00:01 |
| * 11 |       INDEX UNIQUE SCAN                       | TAB_PK                   |    1 |       |    1 | 00:00:01 |
|   12 |      TABLE ACCESS BY INDEX ROWID              | TAB                      |    1 |   502 |    2 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("FE"=TO_NUMBER(:FE))
* 9 - filter(INSTR(:BO,',',1,LEVEL-1)>0)
* 11 - access("BO"=TO_NUMBER("$kkqu_col_1"))


Но нет ли какого-нибудь более изящного решения? И почему оракл так себя ведёт? Если подскажете более-менее точную страницу документации - буду благодарен.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.06.2018, 14:03
    #39665341
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подзапрос + reg_exp + :bind + or = тормоза
shurka22
Но жить как-то надо, поэтому выкручиваемся через union:


правильнее через union ALL

ну а для парсинга строк можно например такое попользовать
Код: plsql
1.
SELECT trim(COLUMN_VALUE) str FROM xmltable(('"'||REPLACE('2,3,4', ',', '","')||'"'))


уж не знаю на сколько оно шустрее
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.06.2018, 14:43
    #39665373
shurka22
shurka22
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подзапрос + reg_exp + :bind + or = тормоза
MaximaXXLправильнее через union ALL

В моём случае через union all строки дублировались, поэтому был union.

MaximaXXLну а для парсинга строк можно например такое попользовать
Код: plsql
1.
SELECT trim(COLUMN_VALUE) str FROM xmltable(('"'||REPLACE('2,3,4', ',', '","')||'"'))


уж не знаю на сколько оно шустрее
Спасибо. Завтра проверю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.06.2018, 15:08
    #39665388
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подзапрос + reg_exp + :bind + or = тормоза
shurka22MaximaXXLправильнее через union ALL

В моём случае через union all строки дублировались, поэтому был union.


во втором union all
... and LNNVL(fe=:1 )

......
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.06.2018, 05:33
    #39665669
shurka22
shurka22
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подзапрос + reg_exp + :bind + or = тормоза
shurka22
Код: plsql
1.
SELECT trim(COLUMN_VALUE) str FROM xmltable(('"'||REPLACE('2,3,4', ',', '","')||'"'))


Спасибо. Завтра проверю.
Проверил, получилось примерно в 50 раз медленнее, чем мой вариант.
Хотя, в моём случае эта скорость не важна.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.06.2018, 14:56
    #39666063
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подзапрос + reg_exp + :bind + or = тормоза
shurka22,

В прошлый раз вроде тоже была проблема с типами биндов вашего ОРМ?

Намекать оптимизатору на конкатенацию пробовали?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.06.2018, 15:49
    #39666093
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подзапрос + reg_exp + :bind + or = тормоза
envНамекать оптимизатору на конкатенацию пробовали?
1) в этом случае включить конкатенацию не так просто, т.к. понадобится полный синтаксис, типа
 
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1) PREDICATE_REORDERS((3 2) (2 3)))
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
SQL> explain plan for
  2  select/*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1) PREDICATE_REORDERS((3 2) (2 3))) */ *
  3  from t
  4  where fe=:fe
  5     or t.bo in ( SELECT x from xmltable(:bo columns x int path '.') inlist);

Explained.

SQL> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 3558469800

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                       | 10000 |   185K|    16   (0)| 00:00:01 |
|   1 |  CONCATENATION                       |                       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T                     |     1 |    19 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX_T_FE               |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   FILTER                             |                       |       |       |            |          |
|*  5 |    TABLE ACCESS FULL                 | T                     |  9999 |   185K|    12   (0)| 00:00:01 |
|*  6 |    COLLECTION ITERATOR PICKLER FETCH | XQSEQUENCEFROMXMLTYPE |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("FE"=TO_NUMBER(:FE))
   4 - filter( EXISTS (SELECT 0 FROM TABLE() "KOKBF$0" WHERE
              CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY('.' PASSING BY XMLTAB VALUE(KOKBF$) RETURNING
              SEQUENCE BY VALUE ),0,0,54525952,8192),50,1,2)) AS int )=:B1))
   5 - filter(LNNVL("FE"=TO_NUMBER(:FE)))
   6 - filter(CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY('.' PASSING BY XMLTAB
              VALUE(KOKBF$) RETURNING SEQUENCE BY VALUE ),0,0,54525952,8192),50,1,2)) AS int )=:B1)

Note
-----
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)


2) и в этом нет смысла, т.к. форсировать индексный доступ в ветке с подзапросом не получится. Проще и надежнее использовать UNION
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Подзапрос + reg_exp + :bind + or = тормоза / 7 сообщений из 7, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 371ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1883795]:
 0ms
get settings:
 9ms
get forum list:
 10ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 37ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 50ms
get tp. blocked users:
 1ms
others: 243ms
total:  371ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]