Друзья помогите оптимизировать regexp_substr / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Друзья помогите оптимизировать regexp_substr

22 сообщений из 22, страница 1 из 1

Друзья помогите оптимизировать regexp_substr

#40002561

Reest199

Гость

Привет, помогите решить вопрос не прибегая к индексации, причина в чем, у меня есть права только на Select.

Есть задача вытащить из большого массива определенные айдишки по TYPE_ID

Вид данных TYPE_ID : "H.235774465.0" , "S.132355636.69" , "R.6234123.0"

Мне нужно их вытащить из массива много раз, учитывая 2 последние цифры от 2-рой точки. Тоесть для H - 65, S - 36, R - 23

Запрос выглядит следующим образом:

SELECT *
FROM test_data t
WHERE
t.data_load is null
AND
regenxp_substr (t.type_id , '(..)\.' , 1 , 1 , null , 1) = '01'

Такой запрос отрабатывает около 12-15 минут, это крайне критично для меня, т.к. после этого мне необходимо продолжить список

обращений к базе, ='01' , = '02' , = '03' , и т.д.

...

Рейтинг:

0 / 0

25.09.2020, 14:59

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002567

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

Reest199,

не совсем понял то надо

выбирайте за раз

SELECT t.*
,regenxp_substr (t.type_id , '(..)\.' , 1 , 1 , null , 1) xx
FROM test_data t
WHERE
t.data_load is null

.....
stax

...

Рейтинг:

0 / 0

25.09.2020, 15:22

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002569

123йй

Участник

Сообщения: 1 960

Рейтинг: 0 / 0

Reest199,

тебе уже подсказывали в предыдущем топике. substr

...

Рейтинг:

0 / 0

25.09.2020, 15:25

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002575

Reest199

Гость

Stax,

Так не получиться, необходимо много обращений, чтобы разбить данные

...

Рейтинг:

0 / 0

25.09.2020, 15:33

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002576

Reest199

Гость

123йй,

это уже совсем другая история

...

Рейтинг:

0 / 0

25.09.2020, 15:34

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002583

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

Reest199

Stax,
Так не получиться, необходимо много обращений, чтобы разбить данные

шот я туплю, не могу понять задачку

можете на примере показать что надо
есть - получить

.....
stax

...

Рейтинг:

0 / 0

25.09.2020, 15:38

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002587

Reest199

Гость

Stax

Reest199

Stax,
Так не получиться, необходимо много обращений, чтобы разбить данные

шот я туплю, не могу понять задачку

можете на примере показать что надо
есть - получить

.....
stax

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

...

Рейтинг:

0 / 0

25.09.2020, 15:43

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002591

serpv

Гость

Reest199,

Код: plsql

1.
2.
3.
4.
5.

SELECT *
FROM test_data t
WHERE
t.data_load is null
AND t.type_id like '%01.%';

тоже долго?

...

Рейтинг:

0 / 0

25.09.2020, 15:54

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002595

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

Reest199

так я и предлогаю за раз (15мин) выбрать сразу все нужные коды

substr будет чутку быстрее, но не на много

зы
ускорить без индекса врядли получится

.....
stax

...

Рейтинг:

0 / 0

25.09.2020, 16:08

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002621

Reest199

Гость

serpv

Reest199,

Код: plsql

1.
2.
3.
4.
5.

SELECT *
FROM test_data t
WHERE
t.data_load is null
AND t.type_id like '%01.%';

тоже долго?

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

...

Рейтинг:

0 / 0

25.09.2020, 17:39

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002623

Reest199

Гость

Stax,

Да, очень странно, я сам думал что лайк вообще будет более длителен, но он зарешал

...

Рейтинг:

0 / 0

25.09.2020, 17:42

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002642

serpv

Гость

Reest199, a regexp_substr случайно тоже быстрым не стал, просто от того что предыдущие многострадания таблицу в кеш уместили?
А вообще, правильное решение Stax под постановку задачи уже озвучил - получаете за один проход по таблице разбивку всех type_id по их двум цифирям.
Хотите узнать причины "ускорений", то гуглИте комбинации "план запроса" "set autotrace traceonly" "explain plan for dbms.xplan".

...

Рейтинг:

0 / 0

25.09.2020, 18:23

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002676

НеофитSQL

Гость

Reest199

Stax,

Да, очень странно, я сам думал что лайк вообще будет более длителен, но он зарешал

лайк (и его брат substr) значительно быстрее regexp_substr, который тут вряд ли нужен.

> Мне нужно их вытащить из массива много раз, учитывая 2 последние цифры от 2-рой точки. Тоесть для H - 65, S - 36, R - 23

Если нужно часто искать 'H.....65.' и подобные предсказуемые выражения, почему не завести виртуальную колонку с индексом?

Я попробовал так, у меня работает:

Код: plsql

1.
2.
3.
4.
5.
6.
7.

create table TEST1
(
  type_id    VARCHAR2(50),
  type_short as (SUBSTR("TYPE_ID",1,2)|| REGEXP_SUBSTR ("TYPE_ID",'\d\d\.')) -- это новая колонка для поиска
);

create index TYPE_SHORT_INDEX on TEST1 (SUBSTR(TYPE_ID,1,2)|| REGEXP_SUBSTR (TYPE_ID,'\d\d\.'));

Код: plaintext

1.
2.
3.
4.

   	TYPE_ID		TYPE_SHORT
1	H.235774465.0	H.65.
2	S.132355636.69	S.36.
3	R.6234123.0	R.23.

после этого ищешь на равенство, ='H.01.' равенства по индексу оракл любит.

...

Рейтинг:

0 / 0

25.09.2020, 20:29

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002681

НеофитSQL

Гость

> меня есть права только на Select.

Этого я не заметил. Т.е. никаких прав по записи вообще?
Тогда пусть DBA обеспечит индекс для скорости, это разумный запрос для решения бизнес задачи

...

Рейтинг:

0 / 0

25.09.2020, 20:46

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002683

Reest199

Гость

serpv

Друг, не все так просто, моя ошибка что я не описал саму суть как она есть, смотри я шина передачи между 2 ух баз, у одной забрать в другую прокинуть, в моем случае важно для каждой передачи обусловиться 01 , 02 , 03 , 04 , 05, 06 так это будут разные вызовы передачи а тоесть разными запросами, тоесть если я в один запрос вмещю 01, 02, 03 то принимающая база ляжет от такой подачи массива... но у меня была трабла как мне минимизировать время такой подачи данных исходя из каждой подгруппы 01, 02 , 03... решением по факту остался за like, нежели за rexgen,
При передачи rexgen выдавал 240 строк в секунду когда like 14.000 стро в секунду, что меня абсолютно устроило

...

Рейтинг:

0 / 0

25.09.2020, 20:47

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002684

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

НеофитSQL

Если нужно часто искать 'H.....65.' и подобные предсказуемые выражения, почему не завести виртуальную колонку с индексом?

Я попробовал так, у меня работает:

А не должно:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> create table TEST1
  2  (
  3    type_id    VARCHAR2(50),
  4    type_short as (SUBSTR("TYPE_ID",1,2)|| REGEXP_SUBSTR ("TYPE_ID",'\d\d\.')) -- это новая колонка для поиска
  5  );

Table created.

SQL>
SQL> create index TYPE_SHORT_INDEX on TEST1 (SUBSTR(TYPE_ID,1,2)|| REGEXP_SUBSTR (TYPE_ID,'\d\d\.'));
create index TYPE_SHORT_INDEX on TEST1 (SUBSTR(TYPE_ID,1,2)|| REGEXP_SUBSTR (TYPE_ID,'\d\d\.'))
                                                                                     *
ERROR at line 1:
ORA-54018: A virtual column exists for this expression


SQL>

SY.

...

Рейтинг:

0 / 0

25.09.2020, 20:58

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002692

НеофитSQL

Гость

SY,

Это странно, т.к. я скопировал код из "показать sql". Я таблицы пока из GUI создаю.

Мне показалось странным что индекс повторил формулу вместо названия колонки, но поскольку автор кода оракл, то я доверился что так и надо.

...

Рейтинг:

0 / 0

25.09.2020, 21:27

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002762

НеофитSQL

Гость

У меня та же ошибка на моем 11.2, который собственно и сгенерил эти команды.

заменил на имя поля вместо формулы - создало индех..
create index TYPE_SHORT_INDEX on TEST2 ( type_short );

открываю свойства этой второй созданной таблицы, жму "показать sql" и..

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

create table TEST2
(
  type_id    VARCHAR2(50),
  type_short as (SUBSTR("TYPE_ID",1,2)|| REGEXP_SUBSTR ("TYPE_ID",'\d\d\.'))
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255;
-- Create/Recreate indexes 
create index TYPE_SHORT_INDEX on TEST2 (SUBSTR(TYPE_ID,1,2)|| REGEXP_SUBSTR (TYPE_ID,'\d\d\.'))
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;

странно это все..

...

Рейтинг:

0 / 0

26.09.2020, 05:27

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002836

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

НеофитSQL

странно это все..

Небось сгенерировано каким-нибудь левым GUI:

Код: 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.

SQL> create table TEST1
  2  (
  3    type_id    VARCHAR2(50),
  4    type_short as (SUBSTR("TYPE_ID",1,2)|| REGEXP_SUBSTR ("TYPE_ID",'\d\d\.')) -- это новая колонка для поиска
  5  );

Table created.

SQL>
SQL> create index TYPE_SHORT_INDEX on TEST1(type_short);

Index created.

SQL> set long 10000
SQL> select dbms_metadata.get_ddl('INDEX','TYPE_SHORT_INDEX') from dual;

DBMS_METADATA.GET_DDL('INDEX','TYPE_SHORT_INDEX')
--------------------------------------------------------------------------------

  CREATE INDEX "SCOTT"."TYPE_SHORT_INDEX" ON "SCOTT"."TEST1" ("TYPE_SHORT")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS"


SQL>

SY.

...

Рейтинг:

0 / 0

26.09.2020, 13:20

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002982

НеофитSQL

Гость

SY>Небось сгенерировано каким-нибудь левым GUI

В моем случае, это PL/SQL Developer, AllRound Automations.
Мой первый GUI для Оракл :)

Попробовал твою команду, select dbms_metadata.get_ddl('INDEX','TYPE_SHORT_INDEX') from dual;
Получил правильный результат. В процессе открыл для себя пару важных вещей.

- у юзерских индексов глобальный scope, т.е. двум таблицам TEST1/TEST2 нельзя иметь индекс с одинаковым названием
Вместо TYPE_SHORT_INDEX было бы лучше назвать его XIF01TEST1, с привязкой к имени таблицы.
- Мой GUI не использует dbms_metadata.get_ddl() для показа "Show SQL" в свойствах таблицы, хотя это кажется самый простой метод показать правильный DDL из самого надежного источника. Почему - непонятно.

По поводу наименования индексов - есть ли традиция как называть индексы, или тут поле для творчества?
Я заметил что для главного ключа автоматически создаваемый индекс совпадает по имени с ключом.

...

Рейтинг:

0 / 0

27.09.2020, 01:30

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40002986

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

НеофитSQL

- у юзерских индексов глобальный scope, т.е. двум таблицам TEST1/TEST2 нельзя иметь индекс с одинаковым названием.
Я заметил что для главного ключа автоматически создаваемый индекс совпадает по имени с ключом.

Scope индекса не глобальный а в пределах схемы и не у "юзерских" а у всех включая SYS. Автоматически создаваемый constraint supporting индекс получает имя constraint'а как для PK так и для UK.

SY.

...

Рейтинг:

0 / 0

27.09.2020, 02:53

| Ответить | Цитировать | Написать

Друзья помогите оптимизировать regexp_substr

#40015968

booby

Участник

Сообщения: 2 667

Рейтинг: 1 / 0

НеофитSQL

...
- Мой GUI не использует dbms_metadata.get_ddl() для показа "Show SQL" в свойствах таблицы, хотя это кажется самый простой метод показать правильный DDL из самого надежного источника. Почему - непонятно.

...

потому, что ты правую кнопку мыши еще не освоил.

...

Рейтинг:

0 / 0

06.11.2020, 14:46

| Ответить | Цитировать | Написать

22 сообщений из 22, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Друзья помогите оптимизировать regexp_substr

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&fpage=33&tid=1880743]:	0ms
get settings:	9ms
get forum list:	11ms
check forum access:	3ms
check topic access:	3ms
track hit:	45ms
get topic data:	11ms
get forum data:	2ms
get page messages:	52ms
get tp. blocked users:	2ms
others:	329ms

total:	467ms