ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Подзапрос + reg_exp + :bind + or = тормоза
7 сообщений из 7, страница 1 из 1
Подзапрос + reg_exp + :bind + or = тормоза
    #39665283
shurka22
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
shurka22
Гость
Коллеги, привет.

Вкратце:
запрос вида "
Код: 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, 12:56
| Ответить | Цитировать | Написать  
Подзапрос + reg_exp + :bind + or = тормоза
    #39665341
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
shurka22
Но жить как-то надо, поэтому выкручиваемся через union:


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

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


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

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


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

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


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

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

Намекать оптимизатору на конкатенацию пробовали?
...
Рейтинг: 0 / 0
26.06.2018, 14:56
| Ответить | Цитировать | Написать  
Подзапрос + reg_exp + :bind + or = тормоза
    #39666093
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
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
26.06.2018, 15:49
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Подзапрос + reg_exp + :bind + or = тормоза
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&tid=1883795]:
 0ms
get settings:
 14ms
get forum list:
 12ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 30ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 32ms
get tp. blocked users:
 1ms
others: 224ms
total:  330ms
созд. / загр.: 330ms
x
x
Закрыть


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