Всем добрый вечер. В нашей системе очень часто встречаются места, когда поиск в запросе ведётся с оператором like по большим таблицам.
К примеру есть вот такой подзапрос
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 || '%'
План запроса просто ужасен:
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
Делал вот такое извращение, которое помогает сделать более менее адекватный план
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 || '%'
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 заметно меньшее количество записей и план очень похож на реальный, и в принципе устраивает за исключением одного момента, который хотелось бы поправить:
1.
INDEX RANGE SCAN | AK_G_ACCBLN_CODE | 234999 |
Собственно вопросы:
1. Можно ли как-то указать оптимизатору, что именно по этому самому индексу AK_G_ACCBLN_CODE и будет всего 1 запись?
2. Есть ли какие-то менее варварские способы указать оптимизатору, что лайк - это скорее оператор "=", нежели диапазон
