как хинтами добиться нужного плана / Oracle

ReSQL.ru

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

Гость

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

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

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

38 сообщений из 38, показаны все 2 страниц

все

как хинтами добиться нужного плана

#39577491

niv76

Гость

Всем привет.
Есть Oracle 11.2.0.4 и такая упрощенная версия запроса

Код: plsql

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

select
               (select  MAX(ct1.settlement_id)
                from  client c1,
                      cum_money_in_out_hist ct1,
                      client c2,
                      cum_money_in_out_hist ct2                     
                where c1.account_number = a.account_number
                  and ct1.client_id = c1.client_id
                  and ct1.dc_type_id = 4
                  and c2.account_number = a.account_number
                  and c2.client_id      = ct2.client_id 
                  and ct2.dc_type_id    = 5                  
                  and ct1.rc_cl_trade_id = ct2.cl_trade_id
                  and c1.client_id <> c2.client_id
                  and c1.balance_currency <> c2.balance_currency) LAST_EXCHANGE
          from account a
          where a.account_id = :aaa
;

подскажите пожалуйста как с помощью хинтов добиться чтобы окакл делал такой план:
HASH_JOIN (
NESTED_LOOP(a, c1, ct1),
NESTED_LOOP(a, c2, ct2)
)

Спасибо.

...

Рейтинг:

0 / 0

28.12.2017, 17:29

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

как хинтами добиться нужного плана

#39577495

MaximaXXL

Участник

Откуда: Киев

Сообщения: 653

Рейтинг: 0 / 0

niv76
подскажите пожалуйста как с помощью хинтов добиться чтобы окакл делал такой план

Переписать запрос не предлагать?

...

Рейтинг:

0 / 0

28.12.2017, 17:48

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

как хинтами добиться нужного плана

#39577501

niv76

Гость

MaximaXXL,

Тоже было бы интересно. Но вопрос с хинтами не снимается.
Хочу научиться задавать хинтами такой порядок джойна.
Спасибо.

...

Рейтинг:

0 / 0

28.12.2017, 18:12

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

как хинтами добиться нужного плана

#39577505

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

niv76Хочу научиться задавать хинтами такой порядок джойна.Насколько мне, дилетанту, известно, в существующих версиях оракла ты никогда не получишь nested loops(a, c1) и nested loops(a, c2) в таком вариант запроса. Как говорится, хинтуй - не хинтуй, все равно получишь ... сам знаешь что.
Поэтому, прежде чем соваться с хинтами, я бы переписал запрос без скаляра и избыточных джойнов, заменив их группировкой.

...

Рейтинг:

0 / 0

28.12.2017, 18:21

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

как хинтами добиться нужного плана

#39577509

MaximaXXL

Участник

Откуда: Киев

Сообщения: 653

Рейтинг: 0 / 0

niv76,

Ну первое что напрашивается - написать одним запросом ... ввести account в единый запрос
Второе вынести пару c2 и ct2 в exists
Далее, надо вникнуть в логику данных ... очень похоже что можно переписать не используя c2, ct2

...

Рейтинг:

0 / 0

28.12.2017, 18:28

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

как хинтами добиться нужного плана

#39577530

niv76

Гость

AmKad,

скальярный подзапрос это часть довольно большой и сложной вьюшки, по которой раняться разные запросы.
Если мы избавимся от скаляра, то будут проблемы с пушеньем предикатов внутрь группировки с вероятностью 99%.
( Оракл будет стараться фулсканить таблицы которые группирует, а это будет еще хуже чем есть ). А мне нужно прочитать C1, Ct1 по индексу и c2, ct2 по индексу. А результаты соединить уже хешем.

...

Рейтинг:

0 / 0

28.12.2017, 19:25

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

как хинтами добиться нужного плана

#39577538

Valergrad

Участник

Сообщения: 601

Рейтинг: 0 / 0

niv76AmKad,

скальярный подзапрос это часть довольно большой и сложной вьюшки, по которой раняться разные запросы.
Если мы избавимся от скаляра, то будут проблемы с пушеньем предикатов внутрь группировки с вероятностью 99%.
( Оракл будет стараться фулсканить таблицы которые группирует, а это будет еще хуже чем есть ). А мне нужно прочитать C1, Ct1 по индексу и c2, ct2 по индексу. А результаты соединить уже хешем.

1. Хинтом можно запретить пуш предикатов.
2. Тюнить запросы хинтами - должно рассматриваться как самый последний аргумент, если только нет другого выхода. Почему Оракл в данном конкретном случае сам не выбирает правильный план? Статистика лажовая?

...

Рейтинг:

0 / 0

28.12.2017, 19:40

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

как хинтами добиться нужного плана

#39577542

niv76

Гость

MaximaXXL,

Не все понял с Вашего совета, уловил мысль с экзистом.
Получил такой вот запрос:

Код: plsql

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

select 
     (select  MAX(ct1.settlement_id)
      from  client c1,
            cum_money_in_out_hist ct1
      where c1.account_number = a.account_number
        and ct1.client_id = c1.client_id
        and ct1.dc_type_id = 4
        and exists (select 1
                    from   client c2,  cum_money_in_out_hist ct2                     
                    where  c2.account_number = c1.account_number
                        and c2.client_id      = ct2.client_id 
                        and ct2.dc_type_id    = 5                  
                        and ct1.rc_cl_trade_id = ct2.cl_trade_id
                        and c1.client_id <> c2.client_id
                        and c1.balance_currency <> c2.balance_currency
        )
      ) LAST_EXCHANGE
from account a
where a.account_id = :aaa

Но он ничего не решает. У нас на одно значение С1.account_number могут быть сотни c1.client_id, аналогично для С2.account_number могут быть сотни c2.client_id. Поэтому как не крути получается план который все четыре таблицы C1, CT1, C2, CT2 джойнить NESTED_LOOPом. Что приводит к замножению строк.
Суть запроса примерно такая: Ищутся два типа транзакций ct2.dc_type_id = 5 и ct1.dc_type_id = 4 у различных CLIENT_ID в рамках одного ACCOUNT_NUMBER. И поскольку разных CLIENT_ID может быть довольно много, то получается замножение строк.

вот как это выглядит на плане:

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

-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                           | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   8 |    SORT AGGREGATE                         |                                |      1 |      1 |    65 |            |      1 |00:00:04.67 |    1060K|
|   9 |     NESTED LOOPS                          |                                |      1 |      1 |    65 |     9   (0)|   7363 |00:00:01.25 |    1060K|
|  10 |      NESTED LOOPS                         |                                |      1 |      1 |    65 |     9   (0)|    531K|00:00:04.62 |     528K|
|  11 |       NESTED LOOPS                        |                                |      1 |      1 |    51 |     8   (0)|    531K|00:00:02.56 |     102K|
|  12 |        NESTED LOOPS                       |                                |      1 |      1 |    34 |     5   (0)|   1152 |00:00:00.01 |     622 |
|  13 |         TABLE ACCESS BY GLOBAL INDEX ROWID| CLIENT                         |      1 |      1 |    17 |     3   (0)|     35 |00:00:00.01 |      19 |
|* 14 |          INDEX RANGE SCAN                 | CLIENT_UNQ2                    |      1 |      1 |       |     2   (0)|     35 |00:00:00.01 |       2 |
|* 15 |         TABLE ACCESS BY GLOBAL INDEX ROWID| CLIENT                         |     35 |      1 |    17 |     2   (0)|   1152 |00:00:00.01 |     603 |
|* 16 |          INDEX RANGE SCAN                 | CLIENT_UNQ2                    |     35 |      1 |       |     1   (0)|   1225 |00:00:00.01 |       8 |
|* 17 |        TABLE ACCESS BY GLOBAL INDEX ROWID | CUM_MONEY_IN_OUT_HIST          |   1152 |      1 |    17 |     3   (0)|    531K|00:00:02.22 |     101K|
|* 18 |         INDEX RANGE SCAN                  | CUM_MONEY_IN_OUT_HIST_CLID_IDX |   1152 |      2 |       |     2   (0)|    945K|00:00:00.56 |    9279 |
|* 19 |       INDEX UNIQUE SCAN                   | CUM_MONEY_IN_OUT_HIST_PK       |    531K|      1 |       |     0   (0)|    531K|00:00:01.30 |     426K|
|* 20 |      TABLE ACCESS BY GLOBAL INDEX ROWID   | CUM_MONEY_IN_OUT_HIST          |    531K|      1 |    14 |     1   (0)|   7363 |00:00:00.87 |     531K|

   7 - access("C"."ACCOUNT_NUMBER"=:B1)
  14 - access("C1"."ACCOUNT_NUMBER"=:B1)
  15 - filter(("C1"."CLIENT_ID"<>"C2"."CLIENT_ID" AND "C1"."BALANCE_CURRENCY"<>"C2"."BALANCE_CURRENCY"))
  16 - access("C2"."ACCOUNT_NUMBER"=:B1)
  17 - filter("CT1"."RC_CL_TRADE_ID" IS NOT NULL)
  18 - access("CT1"."CLIENT_ID"="C1"."CLIENT_ID" AND "CT1"."DC_TYPE_ID"=4)
       filter("CT1"."DC_TYPE_ID"=4)
  19 - access("CT1"."RC_CL_TRADE_ID"="CT2"."CL_TRADE_ID")
  20 - filter(("CT2"."DC_TYPE_ID"=5 AND "CT2"."CLIENT_ID"="C2"."CLIENT_ID"))

...

Рейтинг:

0 / 0

28.12.2017, 19:56

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

как хинтами добиться нужного плана

#39577544

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

niv76,

Иногда, чтобы сделать одно заключение на множестве строк, необязательно делать джойны. Читай - exists - тот же джойн. Некоторые порядочные люди используют для этого агрегацию.

...

Рейтинг:

0 / 0

28.12.2017, 20:06

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

как хинтами добиться нужного плана

#39577546

Добрый Э - Эх

Гость

niv76,

Это просто праздник какой-то.....
Ты правда считаешь, что проблема замножения строк кроется в выбранном способе соединения таблиц? Ты серьёзно думаешь, что в случае с hash join-ом результат не будет «
Замножаться».
Ну или объясни, что ты имеешь ввиду под этим «термином»

...

Рейтинг:

0 / 0

28.12.2017, 20:11

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

как хинтами добиться нужного плана

#39577552

niv76

Гость

Valergrad,

1. Мне наоборот нужно пушить и читать по индексу.
2. Статистика свежая но данные сильно не однородные.
У большинства строк на account_number одна строка client_id, но есть такие аккаунты где может быть для одного account_number сотни client_id.

В качестве эксперемента ручками уменьшить distinct_value для client.account_number в 100 раз, глянуть какой план получиться.
Поменялся порядок джойнов на более удачный, но желаемого плана пока нет:

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

-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                           | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   8 |    SORT AGGREGATE                         |                                |      1 |      1 |    65 |            |      1 |00:00:00.41 |   54696 |
|   9 |     NESTED LOOPS                          |                                |      1 |      1 |    65 |   570   (0)|   7363 |00:00:00.28 |   54696 |
|  10 |      NESTED LOOPS                         |                                |      1 |     14 |    65 |   570   (0)|  17681 |00:00:00.46 |   37015 |
|  11 |       NESTED LOOPS                        |                                |      1 |     14 |   672 |   556   (0)|  17681 |00:00:00.39 |   35249 |
|  12 |        NESTED LOOPS                       |                                |      1 |    162 |  5508 |   394   (0)|  17693 |00:00:00.15 |    3352 |
|  13 |         TABLE ACCESS BY GLOBAL INDEX ROWID| CLIENT                         |      1 |    123 |  2091 |    55   (0)|     35 |00:00:00.01 |      19 |
|* 14 |          INDEX RANGE SCAN                 | CLIENT_UNQ2                    |      1 |    120 |       |     2   (0)|     35 |00:00:00.01 |       2 |
|* 15 |         TABLE ACCESS BY GLOBAL INDEX ROWID| CUM_MONEY_IN_OUT_HIST          |     35 |      1 |    17 |     3   (0)|  17693 |00:00:00.10 |    3333 |
|* 16 |          INDEX RANGE SCAN                 | CUM_MONEY_IN_OUT_HIST_CLID_IDX |     35 |      2 |       |     2   (0)|  30998 |00:00:00.03 |     358 |
|* 17 |        TABLE ACCESS BY GLOBAL INDEX ROWID | CUM_MONEY_IN_OUT_HIST          |  17693 |      1 |    14 |     1   (0)|  17681 |00:00:00.15 |   31897 |
|* 18 |         INDEX UNIQUE SCAN                 | CUM_MONEY_IN_OUT_HIST_PK       |  17693 |      1 |       |     0   (0)|  17693 |00:00:00.07 |   14204 |
|* 19 |       INDEX UNIQUE SCAN                   | CLIENT_PK                      |  17681 |      1 |       |     0   (0)|  17681 |00:00:00.04 |    1766 |
|* 20 |      TABLE ACCESS BY GLOBAL INDEX ROWID   | CLIENT                         |  17681 |      1 |    17 |     1   (0)|   7363 |00:00:00.05 |   17681 |

  14 - access("C1"."ACCOUNT_NUMBER"=:B1)
  15 - filter("CT1"."RC_CL_TRADE_ID" IS NOT NULL)
  16 - access("CT1"."CLIENT_ID"="C1"."CLIENT_ID" AND "CT1"."DC_TYPE_ID"=4)
       filter("CT1"."DC_TYPE_ID"=4)
  17 - filter("CT2"."DC_TYPE_ID"=5)
  18 - access("CT1"."RC_CL_TRADE_ID"="CT2"."CL_TRADE_ID")
  19 - access("CT2"."CLIENT_ID"="C2"."CLIENT_ID")
       filter("C1"."CLIENT_ID"<>"C2"."CLIENT_ID")
  20 - filter(("C2"."ACCOUNT_NUMBER"=:B1 AND "C1"."BALANCE_CURRENCY"<>"C2"."BALANCE_CURRENCY"))

...

Рейтинг:

0 / 0

28.12.2017, 20:22

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

как хинтами добиться нужного плана

#39577556

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

niv76эксперементаА как правильно? Эксперимент или экскремент?

...

Рейтинг:

0 / 0

28.12.2017, 20:26

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

как хинтами добиться нужного плана

#39577559

niv76

Гость

Добрый Э - Эх,

да, согласен. Замножение есть хоть в любом случае. Но в том плане, который я хочу - промежуточные результаты строк соединения таблиц будут минимальными, соответсвенно и самый быстрый план.

...

Рейтинг:

0 / 0

28.12.2017, 20:31

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

как хинтами добиться нужного плана

#39577562

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

niv76соответсвенно и самый быстрый план.М-м-м, какие мы самоуверенные!

...

Рейтинг:

0 / 0

28.12.2017, 20:35

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

как хинтами добиться нужного плана

#39577563

niv76

Гость

AmKad,

Виноват, Эксперимент .
У меня правильнописание хромает. Оно хорошее, но почему-то хромает. (с)

:(

...

Рейтинг:

0 / 0

28.12.2017, 20:36

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

как хинтами добиться нужного плана

#39577565

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

niv76AmKad,

Виноват, Эксперимент .
У меня правильнописание хромает. Оно хорошее, но почему-то хромает. (с)

:(Да ладно, не заморачивайся. Ошибки правописания - не самые серьезные ошибки в нашей жизни.

...

Рейтинг:

0 / 0

28.12.2017, 20:38

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

как хинтами добиться нужного плана

#39577585

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

niv76,

А на досуг тебе задачка для размышления. Есть две сущности: классы и ученики. Вывести все классы, в которых есть хотя бы по одному Ивану и Владимиру, но нет ни одного Дениса и Вадима.

...

Рейтинг:

0 / 0

28.12.2017, 21:36

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

как хинтами добиться нужного плана

#39577587

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

niv76,

Первое и главное что надо понять для твоего случая - компоненты оптимизатора.
About Optimizer Components

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

Если мы хотим два результата NL соединить потом как HASH, то создадим две дополнительные inline view.

Код: 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.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.

SQL> select (select --+ use_hash(t1 t2)
  2           max(settlement_id) last_exchange
  3            from (select --+
  4                   ct1.settlement_id,
  5                   ct1.rc_cl_trade_id,
  6                   c1.client_id,
  7                   c1.balance_currency
  8                    from client c1, cum_money_in_out_hist ct1
  9                   where c1.account_number = a.account_number
 10                     and ct1.client_id = c1.client_id
 11                     and ct1.dc_type_id = 4) t1
 12            join (select --+
 13                  ct2.cl_trade_id, c2.client_id, c2.balance_currency
 14                   from client c2, cum_money_in_out_hist ct2
 15                  where c2.account_number = a.account_number
 16                    and c2.client_id = ct2.client_id
 17                    and ct2.dc_type_id = 5) t2
 18              on t1.rc_cl_trade_id = t2.cl_trade_id
 19             and t1.client_id <> t2.client_id
 20             and t1.balance_currency <> t2.balance_currency) last_exchange
 21    from account a
 22   where a.account_id = 1;

LAST_EXCHANGE
-------------


SQL> select * from table(dbms_xplan.display_cursor(format => 'BASIC'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select (select --+ use_hash(t1 t2)          max(settlement_id)
last_exchange           from (select --+
ct1.settlement_id,                  ct1.rc_cl_trade_id,
 c1.client_id,                  c1.balance_currency
from client c1, cum_money_in_out_hist ct1                  where
c1.account_number = a.account_number                    and
ct1.client_id = c1.client_id                    and ct1.dc_type_id = 4)
t1           join (select --+                 ct2.cl_trade_id,
c2.client_id, c2.balance_currency                  from client c2,
cum_money_in_out_hist ct2                 where c2.account_number =
a.account_number                   and c2.client_id = ct2.client_id
              and ct2.dc_type_id = 5) t2             on
t1.rc_cl_trade_id = t2.cl_trade_id            and t1.client_id <>
t2.client_id            and t1.balance_currency <> t2.balance_currency)
last_exchange   from account a  where a.account_id = 1

Plan hash value: 2392317869

-------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                           |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                                |
|   1 |  NESTED LOOPS OUTER                        |                                |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED      | ACCOUNT                        |
|   3 |    INDEX RANGE SCAN                        | AAA_IDX                        |
|   4 |   VIEW PUSHED PREDICATE                    | VW_SSQ_1                       |
|   5 |    SORT GROUP BY                           |                                |
|   6 |     NESTED LOOPS                           |                                |
|   7 |      NESTED LOOPS                          |                                |
|   8 |       NESTED LOOPS                         |                                |
|   9 |        NESTED LOOPS                        |                                |
|  10 |         TABLE ACCESS BY INDEX ROWID BATCHED| CLIENT                         |
|  11 |          INDEX RANGE SCAN                  | CLIENT_UNQ2                    |
|  12 |         TABLE ACCESS BY INDEX ROWID BATCHED| CUM_MONEY_IN_OUT_HIST          |
|  13 |          INDEX RANGE SCAN                  | CUM_MONEY_IN_OUT_HIST_CLID_IDX |
|  14 |        TABLE ACCESS BY INDEX ROWID         | CUM_MONEY_IN_OUT_HIST          |
|  15 |         INDEX UNIQUE SCAN                  | CUM_MONEY_IN_OUT_HIST_PK       |
|  16 |       INDEX UNIQUE SCAN                    | CLIENT_PK                      |
|  17 |      TABLE ACCESS BY INDEX ROWID           | CLIENT                         |
-------------------------------------------------------------------------------------


43 rows selected.

Что мы имеем? Все по прежнему.

Теперь отключим view merging.

Код: plsql

SQL> select (select --+ use_hash(t1 t2)
  2           max(settlement_id) last_exchange
  3            from (select --+ no_merge
  4                   ct1.settlement_id,
  5                   ct1.rc_cl_trade_id,
  6                   c1.client_id,
  7                   c1.balance_currency
  8                    from client c1, cum_money_in_out_hist ct1
  9                   where c1.account_number = a.account_number
 10                     and ct1.client_id = c1.client_id
 11                     and ct1.dc_type_id = 4) t1
 12            join (select --+ no_merge
 13                  ct2.cl_trade_id, c2.client_id, c2.balance_currency
 14                   from client c2, cum_money_in_out_hist ct2
 15                  where c2.account_number = a.account_number
 16                    and c2.client_id = ct2.client_id
 17                    and ct2.dc_type_id = 5) t2
 18              on t1.rc_cl_trade_id = t2.cl_trade_id
 19             and t1.client_id <> t2.client_id
 20             and t1.balance_currency <> t2.balance_currency) last_exchange
 21    from account a
 22   where a.account_id = 1;

LAST_EXCHANGE
-------------


SQL> select * from table(dbms_xplan.display_cursor(format => 'BASIC'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select (select --+ use_hash(t1 t2)          max(settlement_id)
last_exchange           from (select --+ no_merge
ct1.settlement_id,                  ct1.rc_cl_trade_id,
 c1.client_id,                  c1.balance_currency
from client c1, cum_money_in_out_hist ct1                  where
c1.account_number = a.account_number                    and
ct1.client_id = c1.client_id                    and ct1.dc_type_id = 4)
t1           join (select --+ no_merge                 ct2.cl_trade_id,
c2.client_id, c2.balance_currency                  from client c2,
cum_money_in_out_hist ct2                 where c2.account_number =
a.account_number                   and c2.client_id = ct2.client_id
              and ct2.dc_type_id = 5) t2             on
t1.rc_cl_trade_id = t2.cl_trade_id            and t1.client_id <>
t2.client_id            and t1.balance_currency <> t2.balance_currency)
last_exchange   from account a  where a.account_id = 1

Plan hash value: 4191647387

-----------------------------------------------------------------------------------
| Id  | Operation                                | Name                           |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                                |
|   1 |  SORT AGGREGATE                          |                                |
|   2 |   HASH JOIN SEMI                         |                                |
|   3 |    VIEW                                  |                                |
|   4 |     NESTED LOOPS                         |                                |
|   5 |      NESTED LOOPS                        |                                |
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| CLIENT                         |
|   7 |        INDEX RANGE SCAN                  | CLIENT_UNQ2                    |
|   8 |       INDEX RANGE SCAN                   | CUM_MONEY_IN_OUT_HIST_CLID_IDX |
|   9 |      TABLE ACCESS BY INDEX ROWID         | CUM_MONEY_IN_OUT_HIST          |
|  10 |    VIEW                                  |                                |
|  11 |     NESTED LOOPS                         |                                |
|  12 |      NESTED LOOPS                        |                                |
|  13 |       TABLE ACCESS BY INDEX ROWID BATCHED| CLIENT                         |
|  14 |        INDEX RANGE SCAN                  | CLIENT_UNQ2                    |
|  15 |       INDEX RANGE SCAN                   | CUM_MONEY_IN_OUT_HIST_CLID_IDX |
|  16 |      TABLE ACCESS BY INDEX ROWID         | CUM_MONEY_IN_OUT_HIST          |
|  17 |  TABLE ACCESS BY INDEX ROWID BATCHED     | ACCOUNT                        |
|  18 |   INDEX RANGE SCAN                       | AAA_IDX                        |
-----------------------------------------------------------------------------------


44 rows selected.

Я надеюсь есть понимание, что не смотря на соединение по трем предикатам, хеш строится только по тому, где равенство.

PS. Потрудись в след раз проявить уважение к помогающим и предоставить скрипты для таблиц и индексов.
Мне пришло потратить почти 10 минут чтоб родить сие.

Код: plsql

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

drop table client;
drop table cum_money_in_out_hist;
drop table account;

create table client(client_id, account_number, balance_currency)
as (select rownum,rownum,rownum from dual connect by level <= 1e6);

create table cum_money_in_out_hist(client_id, dc_type_id, cl_trade_id, rc_cl_trade_id,settlement_id)
as (select rownum,rownum,rownum,rownum,rownum from dual connect by level <= 1e6);

create table account(account_id, account_number)
as (select rownum,rownum from dual connect by level <= 1e6);

create index client_unq2 on client(account_number);

create unique index client_pk on client(client_id);

create index cum_money_in_out_hist_clid_idx on cum_money_in_out_hist(client_id , dc_type_id)

create unique index cum_money_in_out_hist_pk on cum_money_in_out_hist(rc_cl_trade_id)

create index aaa_idx on account(account_id);

Абсолютно очевидно, что оно не отражает твои распределения и до конца не понятно откуда желание иметь именно такой план.

...

Рейтинг:

0 / 0

28.12.2017, 21:43

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

как хинтами добиться нужного плана

#39577893

niv76

Гость

dbms_photoshop,

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

1. Вы наверное олаживали свой запрос на ORACLE 12? т.к. на 11.2.0.4 я получил ошибку:

ORA-00904 "A"."ACCOUNT_NUMBER": invalid identifier

Из прочитаного по вашей ссылке, делаю вывод что 11ка не умеет делать такую трансформацию, которую я хочу, поэтому и план не получиться такой получить. Правильно?

2. По воводу почему хочу получить такой план. На глаз показалось что это самый быстрый вариант джойна c1, ct1, c2, ct2. Сделал выборку

Код: plsql

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

 select ct1.dc_type_id, count(1)
  from client c1, cum_money_in_out_hist ct1, account a
 where c1.account_number = a.account_number
   and ct1.client_id = c1.client_id
   and ct1.dc_type_id IN (4,5)                   
   and a.account_id = 1 
group by ct1.dc_type_id
;

получил:
4	31004
5	30940

данный немного поменялись со вчерашнего дня...

Да, признаю. Вариант (мой второй трейс) будет лучше.

3. Вижу, что AmKad знает, как сделать запрос с группировкой, но пока не могу сообразить как. Не очень тренерован в sql.
я так понял, что задумка выбрать сразу строки 4,5 типов , и дальше агрегируя получить нужный результат. Написал это, а дальше сообразить не могу:

Код: plsql

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

 select     (   select 
                    from client c1, cum_money_in_out_hist ct1
                   where c1.account_number = a.account_number
                     and ct1.client_id = c1.client_id
                     and ct1.dc_type_id IN (4,5)                   
                     )  last_exchange
    from account a
   where a.account_id = 1;

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

...

Рейтинг:

0 / 0

29.12.2017, 13:55

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

как хинтами добиться нужного плана

#39577917

Dshedoo

Участник

Сообщения: 310

Рейтинг: 0 / 0

niv76,

Код: plsql

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

select max(ct1.settlement_id) as LAST_EXCHANGE
from account a
join client c1 on c1.account_number = a.account_number
join cum_money_in_out_hist ct1 on ct1.client_id = c1.client_id and ct1.dc_type_id = 4
where exists 
(select * 
   from client c2 join cum_money_in_out_hist ct2 on c2.client_id = ct2.client_id and ct2.dc_type_id = 5 
  where c2.account_number = a.account_number
    and ct1.rc_cl_trade_id = ct2.cl_trade_id
    and c1.client_id <> c2.client_id
    and c1.balance_currency <> c2.balance_currency)
and a.account_id = :aaa

...

Рейтинг:

0 / 0

29.12.2017, 14:19

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

как хинтами добиться нужного плана

#39577928

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

niv76делаю вывод что 11ка не умеет делать такую трансформациюДело не в трансформации, а в видимости в скаляре всего на один уровень в 11. Можно обойти если сильно захотеть.
niv76Можете кинуть Ваше решение, аналогичный пример, или хотя бы наводящие подсказки?Полагаю AmKad намекает, что от semi join можно уйти если написать хитрое выражение в having + decode.
И это таки возможно. Начни с задачки 21070947 .

...

Рейтинг:

0 / 0

29.12.2017, 14:26

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

как хинтами добиться нужного плана

#39577945

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

dbms_photoshopИ это таки возможно.Из-за self join по неравенству думаю, что нет. Так что не заморачивайся.

...

Рейтинг:

0 / 0

29.12.2017, 15:12

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

как хинтами добиться нужного плана

#39578049

niv76

Гость

1. в задачку кажись решил, и да, применительно к моему примеру сложно применить из-за предикатов на неравенство :

Код: plsql

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

create table stdnt (id  number, name varchar2(100), cls_id number );

insert into stdnt (id, name, cls_id)
select rownum, substr(object_name,1, 1), mod(rownum, 10)+1
from dba_objects
where rownum < 501;


-- найдем классы в которых есть A, C но нет F, G

select s.cls_id
from stdnt s
group by s.cls_id
having 
          decode(sum(decode(name, 'A',1,0)), 0,0,1)*
          decode(sum(decode(name, 'C',1,0)), 0,0,1)*
          decode(sum(decode(name, 'F',1,0)), 0,1,0)*
          decode(sum(decode(name, 'G',1,0)), 0,1,0) = 1
;

2. dbms_photoshop , заинтересовала ваша фраза:

Код: plsql

Дело не в трансформации, а в видимости в скаляре всего на один уровень в 11. Можно обойти если сильно захотеть.

Можете для моего примера написать, как это можно обойти?
Для поднятия общей образованности. Сам придумать не могу.
Спасибо.

...

Рейтинг:

0 / 0

29.12.2017, 19:00

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

как хинтами добиться нужного плана

#39578071

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

dbms_photoshopdbms_photoshopИ это таки возможно.Из-за self join по неравенству думаю, что нет. Так что не заморачивайся.Рано сдаешься. Равенства по номеру счета должно хватить.

...

Рейтинг:

0 / 0

29.12.2017, 20:49

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

как хинтами добиться нужного плана

#39578073

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

niv762. dbms_photoshop , заинтересовала ваша фраза:

Код: plsql

Дело не в трансформации, а в видимости в скаляре всего на один уровень в 11. Можно обойти если сильно захотеть.

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

select (select --+ use_hash(t1 t2)
         max(settlement_id) last_exchange
          from (select --+ no_merge
                 ct1.settlement_id,
                 ct1.rc_cl_trade_id,
                 c1.client_id,
                 c1.balance_currency,
                 account_number
                  from client c1, cum_money_in_out_hist ct1
                 where 1=1
                   and ct1.client_id = c1.client_id
                   and ct1.dc_type_id = 4) t1
          join (select --+ no_merge
                ct2.cl_trade_id, c2.client_id, c2.balance_currency,
                account_number
                 from client c2, cum_money_in_out_hist ct2
                where 1=1
                  and c2.client_id = ct2.client_id
                  and ct2.dc_type_id = 5) t2
            on t1.rc_cl_trade_id = t2.cl_trade_id
           and t1.client_id <> t2.client_id
           and t1.balance_currency <> t2.balance_currency
        where
              t1.account_number = a.account_number
          and t2.account_number = a.account_number
        ) last_exchange
  from account a
 where a.account_id = 1;

...

Рейтинг:

0 / 0

29.12.2017, 20:51

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

как хинтами добиться нужного плана

#39578077

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

AmKadРавенства по номеру счета должно хватить.С удовольствием посмотрю на твое решение, учитывая что для одного счета может быть дохрена записей 4 и 5 из cum_money_in_out_hist
niv76

Код: plsql

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

 select ct1.dc_type_id, count(1)
  from client c1, cum_money_in_out_hist ct1, account a
 where c1.account_number = a.account_number
   and ct1.client_id = c1.client_id
   and ct1.dc_type_id IN (4,5)                   
   and a.account_id = 1 
group by ct1.dc_type_id
;

получил:
4	31004
5	30940

данный немного поменялись со вчерашнего дня...

и их необходимо схлопнуть учитывая условие для self join

Код: plsql

1.
2.
3.

            on t1.rc_cl_trade_id = t2.cl_trade_id
           and t1.client_id <> t2.client_id
           and t1.balance_currency <> t2.balance_currency

Можно обойтись без джойна с помощью model, но это баловство.

...

Рейтинг:

0 / 0

29.12.2017, 21:07

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

как хинтами добиться нужного плана

#39578083

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

dbms_photoshopучитывая что для одного счета может быть дохрена записей 4 и 5 из cum_money_in_out_histНу вот чтобы не гадать, пусть автор дает данные, обладающие свойствами реальных. И посмотрим, что можно с ними сделать. Правда я оперативность ответа не гарантирую, не знаю когда до оракла доберусь.

...

Рейтинг:

0 / 0

29.12.2017, 21:38

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

как хинтами добиться нужного плана

#39578092

dbms_photoshop

Участник

Откуда: sqlmdx.net

Сообщения: 5 140

Рейтинг: 0 / 0

AmKad,

Автор получив ответы на свои вполне может потерять интерес к теме.
Я до Оракла доберусь в 2018, но могу дать заготовку.

Код: plsql

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

with
client(client_id, account_number, balance_currency)
as (select 1,1,'USD' from dual union all select 2,1,'GBP' from dual)
,
cum_money_in_out_hist(client_id, dc_type_id, cl_trade_id, rc_cl_trade_id,settlement_id)
as (select 1,4,0,0,1 from dual union all select 2,5,0,0,1 from dual)
select *
  from client c1, cum_money_in_out_hist ct1, account a
 where c1.account_number = a.account_number
   and ct1.client_id = c1.client_id
   and ct1.dc_type_id IN (4,5)                   
   and a.account_id = 1

Думаю, очевидно, что не стоит заклыдваться что здесь по одной строке на 4 и 5.
Я не тороплю. :))

...

Рейтинг:

0 / 0

29.12.2017, 22:12

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

как хинтами добиться нужного плана

#39578139

andrey_anonymous

Участник

Откуда: Москва

Сообщения: 18 719

Рейтинг: 0 / 0

dbms_photoshopДумаю, очевидно, что не стоит заклыдваться что здесь по одной строке на 4 и 5.

А если предположить, что trade_id суть идентификатор сделки, при этом интересуют только сделки, содержащие ровно по одному участнику на каждой из сторон?

...

Рейтинг:

0 / 0

30.12.2017, 01:35

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

как хинтами добиться нужного плана

#39578220

MaximaXXL

Участник

Откуда: Киев

Сообщения: 653

Рейтинг: 0 / 0

andrey_anonymous,

Да там мутняк отчасти, то что я вижу это сделки в приделах одного счета.
Но сделка должна быть между разными клиентами и разными валютами, что, по моему, делаеться только через SWIFT в Украине.
Но вот что удивляет, что ищут MAX(settlement_id) могу только предполагать что это идентификатор валютных операций и если нет конвертации он равен 0 например. Посему мне кажеться что это и есть достаточный признак для того чтоб сказать что была конвертация. А то что не через себя (c1.client_id <> c2.client_id), можно проверить и по другому т.к. client_id - primary key.

В общем очень бы хотелось увидеть пример от автора, не обязательно много (сотни) клиентов, на 2-х, 3-х все будет понятно. Потому как у меня не очень сходится если на один счет (a.account_id = 1) будет 3 клиента со сделками ct1.dc_type_id = 4 какой информативности будет единый результат MAX(settlement_id) ... это говорит или об утере данных о 2 сделках клиентов, или что с ct1.dc_type_id = 4 может быть только 1 клиент, или еще хз что. Но постановка/пример хотелось бы увидеть, а то как в хрустальный шар.

...

Рейтинг:

0 / 0

30.12.2017, 13:57

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

как хинтами добиться нужного плана

#39578659

niv76

Гость

Спасибо за ответы.
Поспрашивал немного насчет физического смысла запроса. По сути account_number, я так понял, идетнифицирует одного клиента банка. Client_id - это ид счета в разной валюте. две строки ( ct2.dc_type_id = 4 (transfer from) и ct2.dc_type_id = 5 (transfer to) ) фактически это операция конвертации с одной валюты в другую. Settlement_id - это фактически дата банковского дня.
Соответственно строк ct2.dc_type_id = 5 , ct2.dc_type_id = 4 может быть много и не всегда между разными валютами, но всегда между разными client_id. Фактически фильтр на неравенство client_id - избыточный.
Попробую сегодня сделать немного репрезентавивных тестовых данных....

...

Рейтинг:

0 / 0

02.01.2018, 11:58

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

как хинтами добиться нужного плана

#39578660

niv76

Гость

Но фильтр на не равенство валют, актуальный:

c1.balance_currency <> c2.balance_currency

...

Рейтинг:

0 / 0

02.01.2018, 12:02

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

как хинтами добиться нужного плана

#39578687

MaximaXXL

Участник

Откуда: Киев

Сообщения: 653

Рейтинг: 0 / 0

niv76,

Можно так попробовать, если я не запутался =)

Код: plsql

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

select max(prior ct1.settlement_id)
  from client c1, cum_money_in_out_hist ct1
 where c1.account_number = 1 /*account_number*/  
   and ct1.client_id = c1.client_id  
   and ct1.dc_type_id IN (4,5)                    
   and level = 2
   START WITH ct1.dc_type_id = 4 
   connect by nocycle prior ct1.rc_cl_trade_id = ct1.cl_trade_id and prior c1.balance_currency != c1.balance_currency and prior ct1.client_id != ct1.client_id

Т.к. без данных можно только прикидывать

...

Рейтинг:

0 / 0

02.01.2018, 13:45

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

как хинтами добиться нужного плана

#39578701

MaximaXXL

Участник

Откуда: Киев

Сообщения: 653

Рейтинг: 0 / 0

MaximaXXL,

надо еще дописать в конце

Код: plsql

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

select max(prior ct1.settlement_id)
  from client c1, cum_money_in_out_hist ct1
 where c1.account_number = 1 /*account_number*/  
   and ct1.client_id = c1.client_id  
   and ct1.dc_type_id IN (4,5)                    
   and level = 2
   START WITH ct1.dc_type_id = 4 
   connect by nocycle prior ct1.rc_cl_trade_id = ct1.cl_trade_id and prior c1.balance_currency != c1.balance_currency and prior ct1.client_id != ct1.client_id and ct1.dc_type_id = 5

... а то может случиться ссылка ct1.dc_type_id = 4 на ct1.dc_type_id = 4.

...

Рейтинг:

0 / 0

02.01.2018, 14:41

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

как хинтами добиться нужного плана

#39578708

niv76

Гость

cl_trade_id ПК транзакции. rc_cl_trade_id - ссылка на связанную парную транзакцию.
Транзакции dc_type_id in (4,5) могут быль либо одиночными (если деньги заводяться или выводятся из системы). Для таких строк rc_cl_trade_id = NULL. Либо парными - когда деньги перебрасываются в системе между разными счетами (rc_cl_trade_id IS NOT NULL).

Скрипт с данными в файле.

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

select a.account_number,
               (select  MAX(ct1.settlement_id)
                from  client c1,
                      cum_money_in_out_hist ct1,
                      client c2,
                      cum_money_in_out_hist ct2                     
                where c1.account_number = a.account_number
                  and ct1.client_id = c1.client_id
                  and ct1.dc_type_id = 4
                  and c2.account_number = a.account_number
                  and c2.client_id      = ct2.client_id 
                  and ct2.dc_type_id    = 5                  
                  and ct1.rc_cl_trade_id = ct2.cl_trade_id
                  and c1.client_id <> c2.client_id 
                  and c1.balance_currency <> c2.balance_currency
                  ) LAST_EXCHANGE
          from account a
order by 1          
;

account_number, last_exchange
1000067	20170623
1000097	
1000103	
1011408	20170710
5050005	20171229

...

Рейтинг:

0 / 0

02.01.2018, 15:12

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

как хинтами добиться нужного плана

#39578711

MaximaXXL

Участник

Откуда: Киев

Сообщения: 653

Рейтинг: 0 / 0

niv76,

21078527
Вернул Ваш результат

...

Рейтинг:

0 / 0

02.01.2018, 15:26

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

как хинтами добиться нужного плана

#39578715

niv76

Гость

Остался спортивный интерес сделать с группировкой и проверкой c1.balance_currency <> c2.balance_currency, не делая второго джойна ( client c2, cum_money_in_out_hist ct2 )
Похоже даже у самого получилось, может кто-то красивее может:

Код: plsql

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

select a.account_number,
               (select  max(MAX(decode(dc_type_id,4,ct1.settlement_id)))
                from  client c1,
                      cum_money_in_out_hist ct1
                where c1.account_number = a.account_number
                  and ct1.client_id = c1.client_id
                  and ct1.dc_type_id in (4,5) and
                  ct1.rc_cl_trade_id is not null
                group by decode(ct1.dc_type_id, 4, ct1.cl_trade_id, 5, ct1.rc_cl_trade_id)
                having count(distinct c1.balance_currency) = 2
                  ) LAST_EXCHANGE
          from account a
order by 1

...

Рейтинг:

0 / 0

02.01.2018, 15:36

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

как хинтами добиться нужного плана

#39578716

niv76

Гость

MaximaXXL,

да, спасибо. Вариант рабочий.

...

Рейтинг:

0 / 0

02.01.2018, 15:41

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

38 сообщений из 38, показаны все 2 страниц

все

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

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

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

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

start [/forum/topic.php?all=1&fid=52&tid=1884639]:	0ms
get settings:	9ms
get forum list:	14ms
check forum access:	3ms
check topic access:	3ms
track hit:	49ms
get topic data:	10ms
get forum data:	2ms
get page messages:	85ms
get tp. blocked users:	1ms
others:	214ms

total:	390ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы