Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Еще раз про оптимизацию like запросов / 10 сообщений из 10, страница 1 из 1
01.03.2017, 22:04
    #39412847
feagor
feagor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Еще раз про оптимизацию like запросов
Всем добрый вечер. В нашей системе очень часто встречаются места, когда поиск в запросе ведётся с оператором like по большим таблицам.
К примеру есть вот такой подзапрос

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select DEP_ID, ID
from   S_PAYSYS op
where  ORIGINACC like :flt_ACC_CODE || '%'
union
select op.dep_id, op.id
from   G_ACCBLN a, S_ORDPAY op
where  op.acc_id = a.id
and    op.dep_id = a.dep_id
and    a.code like :flt_ACC_CODE || '%'



План запроса просто ужасен:
Код: 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.
 Plan Hash Value  : 3770852514 

----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                  | Rows    | Bytes    | Cost  | Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                       | 3279990 | 95050578 | 92586 | 00:21:37 |
|   1 |   SORT UNIQUE                    |                       | 3279990 | 95050578 | 92586 | 00:21:37 |
|   2 |    UNION-ALL                     |                       |         |          |       |          |
|   3 |     TABLE ACCESS BY INDEX ROWID  | S_PAYSYS              | 3127092 | 87558576 |     0 | 00:00:01 |
| * 4 |      INDEX RANGE SCAN            | IE_S_PAYSYS_ORIGINACC |       1 |          |     0 | 00:00:01 |
| * 5 |     HASH JOIN                    |                       |  152898 |  7492002 | 76134 | 00:17:46 |
|   6 |      TABLE ACCESS BY INDEX ROWID | G_ACCBLN              | 1305549 | 41777568 | 51945 | 00:12:08 |
| * 7 |       INDEX RANGE SCAN           | AK_G_ACCBLN_CODE      |  234999 |          |   526 | 00:00:08 |
|   8 |      VIEW                        | index$_join$_003      | 3127100 | 53160700 | 18115 | 00:04:14 |
| * 9 |       HASH JOIN                  |                       |         |          |       |          |
|  10 |        INDEX FAST FULL SCAN      | FK_S_ORDPAY_T_ACC     | 3127100 | 53160700 |  6569 | 00:01:32 |
|  11 |        INDEX FAST FULL SCAN      | PK_S_ORDPAY           | 3127100 | 53160700 |  5660 | 00:01:20 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("ORIGINACC" LIKE :FLT_ACC_CODE||'%')
* 4 - filter("ORIGINACC" LIKE :FLT_ACC_CODE||'%')
* 5 - access("OP"."ACC_ID"="A"."ID" AND "OP"."DEP_ID"="A"."DEP_ID")
* 7 - access("A"."CODE" LIKE :FLT_ACC_CODE||'%')
* 7 - filter("A"."CODE" LIKE :FLT_ACC_CODE||'%')
* 9 - access(ROWID=ROWID)



Проблема, как видно в том, что оптимизатор считает, что раз у нас есть лайк - будет много записей. например по индексу AK_G_ACCBLN_CODE - 234999 записей
Но я точно знаю, что в большинстве случаев, на которые и хотелось расчитывать записей по индексу будет около 1

Делал вот такое извращение, которое помогает сделать более менее адекватный план
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select /*+CARDINALITY(op,1)*/
 DEP_ID, ID
from   S_PAYSYS op
where  ORIGINACC like :flt_ACC_CODE || '%'
union
select /*+CARDINALITY(a,1)*/
 op.dep_id, op.id
from   G_ACCBLN a, S_ORDPAY op
where  op.acc_id = a.id
and    op.dep_id = a.dep_id
and    a.code like :flt_ACC_CODE || '%'



Код: plsql
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                  |                       |      2 |    77 | 51950 | 00:12:08 |
|   1 |   SORT UNIQUE                     |                       |      2 |    77 | 51950 | 00:12:08 |
|   2 |    UNION-ALL                      |                       |        |       |       |          |
|   3 |     TABLE ACCESS BY INDEX ROWID   | S_PAYSYS              |      1 |    28 |     0 | 00:00:01 |
| * 4 |      INDEX RANGE SCAN             | IE_S_PAYSYS_ORIGINACC |      1 |       |     0 | 00:00:01 |
|   5 |     NESTED LOOPS                  |                       |        |       |       |          |
|   6 |      NESTED LOOPS                 |                       |      1 |    49 | 51948 | 00:12:08 |
|   7 |       TABLE ACCESS BY INDEX ROWID | G_ACCBLN              |      1 |    32 | 51945 | 00:12:08 |
| * 8 |        INDEX RANGE SCAN           | AK_G_ACCBLN_CODE      | 234999 |       |   526 | 00:00:08 |
| * 9 |       INDEX RANGE SCAN            | FK_S_ORDPAY_T_ACC     |      1 |       |     2 | 00:00:01 |
|  10 |      TABLE ACCESS BY INDEX ROWID  | S_ORDPAY              |      1 |    17 |     3 | 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("ORIGINACC" LIKE :FLT_ACC_CODE||'%')
* 4 - filter("ORIGINACC" LIKE :FLT_ACC_CODE||'%')
* 8 - access("A"."CODE" LIKE :FLT_ACC_CODE||'%')
* 8 - filter("A"."CODE" LIKE :FLT_ACC_CODE||'%')
* 9 - access("OP"."DEP_ID"="A"."DEP_ID" AND "OP"."ACC_ID"="A"."ID")



Как видим - за счёт хинта мы получаем в костах и Rows заметно меньшее количество записей и план очень похож на реальный, и в принципе устраивает за исключением одного момента, который хотелось бы поправить:

Код: plsql
1.
INDEX RANGE SCAN           | AK_G_ACCBLN_CODE      | 234999 |



Собственно вопросы:
1. Можно ли как-то указать оптимизатору, что именно по этому самому индексу AK_G_ACCBLN_CODE и будет всего 1 запись?
2. Есть ли какие-то менее варварские способы указать оптимизатору, что лайк - это скорее оператор "=", нежели диапазон
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.03.2017, 22:36
    #39412857
Takurava
Takurava
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Еще раз про оптимизацию like запросов
feagor, _like_with_bind_as_equality ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.03.2017, 22:48
    #39412863
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Еще раз про оптимизацию like запросов
feagor1. Можно ли как-то указать оптимизатору, что именно по этому самому индексу AK_G_ACCBLN_CODE и будет всего 1 запись? OPT_ESTIMATE
feagor2. Есть ли какие-то менее варварские способы указать оптимизатору, что лайк - это скорее оператор "=", нежели диапазонЕсть более варварский путь - сделать свою UDF + associate statistics.
Estimated rows у тебя будут красивые только работать будет долго из-за переключений контекста для UDF.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.03.2017, 10:48
    #39412993
inse
inse
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Еще раз про оптимизацию like запросов
feagor,

А создать подходящий индекс, чтобы можно было обойтись без like , не вариант?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.03.2017, 13:00
    #39413068
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Еще раз про оптимизацию like запросов
inse,

Только domain index.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.03.2017, 13:37
    #39413106
inse
inse
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Еще раз про оптимизацию like запросов
dbms_photoshop,

Я подумал в другом направлении. Наверняка поле ORIGINACC составное. Следует разбить его, либо добавлением столбца, либо использовать function based index.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.03.2017, 13:51
    #39413117
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Еще раз про оптимизацию like запросов
inse,

Когда думаешь - не стесняйся пробовать. Так приходит опыт и понимание, какие идеи работают а какие нет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.03.2017, 14:05
    #39413138
inse
inse
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Еще раз про оптимизацию like запросов
dbms_photoshop,

И что тут пробовать. Подобные запросы явный признак ошибки при проектировании базы данных. Если есть потребность поиска данных по определенным критериям, предусмотри это при проектирование структуры бд. С function based index обычно предпочитаю не связываться, но в крайних случаях вполне рабочий вариант.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.03.2017, 14:12
    #39413144
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Еще раз про оптимизацию like запросов
inseС function based index обычно предпочитаю не связываться, но в крайних случаях вполне рабочий вариант.Ну давай уж продемонстрируй для случая ТС, проектировщик.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.03.2017, 15:38
    #39413193
feagooor
feagooor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Еще раз про оптимизацию like запросов
dbms_photoshopfeagor1. Можно ли как-то указать оптимизатору, что именно по этому самому индексу AK_G_ACCBLN_CODE и будет всего 1 запись? OPT_ESTIMATE
feagor2. Есть ли какие-то менее варварские способы указать оптимизатору, что лайк - это скорее оператор "=", нежели диапазонЕсть более варварский путь - сделать свою UDF + associate statistics.
Estimated rows у тебя будут красивые только работать будет долго из-за переключений контекста для UDF.

Код: 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.
select /*+CARDINALITY(op,1)*/
 DEP_ID, ID
from   S_PAYSYS op
where  ORIGINACC like :flt_ACC_CODE || '%'
union
select /*+OPT_ESTIMATE(TABLE a ROWS=1) OPT_ESTIMATE(INDEX_SCAN a AK_G_ACCBLN_CODE ROWS=2)*/
 op.dep_id, op.id
from   G_ACCBLN a, S_ORDPAY op
where  op.acc_id = a.id
and    op.dep_id = a.dep_id
and    a.code like :flt_ACC_CODE || '%'

 Plan Hash Value  : 1252556588 

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                  | Rows | Bytes | Cost | Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |    2 |    77 |   10 | 00:00:01 |
|   1 |   SORT UNIQUE                     |                       |    2 |    77 |   10 | 00:00:01 |
|   2 |    UNION-ALL                      |                       |      |       |      |          |
|   3 |     TABLE ACCESS BY INDEX ROWID   | S_PAYSYS              |    1 |    28 |    0 | 00:00:01 |
| * 4 |      INDEX RANGE SCAN             | IE_S_PAYSYS_ORIGINACC |    1 |       |    0 | 00:00:01 |
|   5 |     NESTED LOOPS                  |                       |      |       |      |          |
|   6 |      NESTED LOOPS                 |                       |    1 |    49 |    8 | 00:00:01 |
|   7 |       TABLE ACCESS BY INDEX ROWID | G_ACCBLN              |    1 |    32 |    5 | 00:00:01 |
| * 8 |        INDEX RANGE SCAN           | AK_G_ACCBLN_CODE      |    2 |       |    3 | 00:00:01 |
| * 9 |       INDEX RANGE SCAN            | FK_S_ORDPAY_T_ACC     |    1 |       |    2 | 00:00:01 |
|  10 |      TABLE ACCESS BY INDEX ROWID  | S_ORDPAY              |    1 |    17 |    3 | 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("ORIGINACC" LIKE :FLT_ACC_CODE||'%')
* 4 - filter("ORIGINACC" LIKE :FLT_ACC_CODE||'%')
* 8 - access("A"."CODE" LIKE :FLT_ACC_CODE||'%')
* 8 - filter("A"."CODE" LIKE :FLT_ACC_CODE||'%')
* 9 - access("OP"."DEP_ID"="A"."DEP_ID" AND "OP"."ACC_ID"="A"."ID")


Красота:)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Еще раз про оптимизацию like запросов / 10 сообщений из 10, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 690ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1886342]:
 0ms
get settings:
 8ms
get forum list:
 13ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 367ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 35ms
get tp. blocked users:
 1ms
others: 252ms
total:  690ms
x
x
Закрыть


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