Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / как хинтами добиться нужного плана / 25 сообщений из 38, страница 1 из 2
  1  все
28.12.2017, 17:29
    #39577491
niv76
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:48
    #39577495
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
niv76
подскажите пожалуйста как с помощью хинтов добиться чтобы окакл делал такой план

Переписать запрос не предлагать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 18:12
    #39577501
niv76
niv76
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
MaximaXXL,

Тоже было бы интересно. Но вопрос с хинтами не снимается.
Хочу научиться задавать хинтами такой порядок джойна.
Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 18:21
    #39577505
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
niv76Хочу научиться задавать хинтами такой порядок джойна.Насколько мне, дилетанту, известно, в существующих версиях оракла ты никогда не получишь nested loops(a, c1) и nested loops(a, c2) в таком вариант запроса. Как говорится, хинтуй - не хинтуй, все равно получишь ... сам знаешь что.
Поэтому, прежде чем соваться с хинтами, я бы переписал запрос без скаляра и избыточных джойнов, заменив их группировкой.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 18:28
    #39577509
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
niv76,

Ну первое что напрашивается - написать одним запросом ... ввести account в единый запрос
Второе вынести пару c2 и ct2 в exists
Далее, надо вникнуть в логику данных ... очень похоже что можно переписать не используя c2, ct2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 19:25
    #39577530
niv76
niv76
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
AmKad,

скальярный подзапрос это часть довольно большой и сложной вьюшки, по которой раняться разные запросы.
Если мы избавимся от скаляра, то будут проблемы с пушеньем предикатов внутрь группировки с вероятностью 99%.
( Оракл будет стараться фулсканить таблицы которые группирует, а это будет еще хуже чем есть ). А мне нужно прочитать C1, Ct1 по индексу и c2, ct2 по индексу. А результаты соединить уже хешем.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 19:40
    #39577538
Valergrad
Valergrad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
niv76AmKad,

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

1. Хинтом можно запретить пуш предикатов.
2. Тюнить запросы хинтами - должно рассматриваться как самый последний аргумент, если только нет другого выхода. Почему Оракл в данном конкретном случае сам не выбирает правильный план? Статистика лажовая?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 19:56
    #39577542
niv76
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, 20:06
    #39577544
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
niv76,

Иногда, чтобы сделать одно заключение на множестве строк, необязательно делать джойны. Читай - exists - тот же джойн. Некоторые порядочные люди используют для этого агрегацию.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 20:11
    #39577546
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
niv76,

Это просто праздник какой-то.....
Ты правда считаешь, что проблема замножения строк кроется в выбранном способе соединения таблиц? Ты серьёзно думаешь, что в случае с hash join-ом результат не будет «
Замножаться».
Ну или объясни, что ты имеешь ввиду под этим «термином»
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 20:22
    #39577552
niv76
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:26
    #39577556
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
niv76эксперементаА как правильно? Эксперимент или экскремент?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 20:31
    #39577559
niv76
niv76
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
Добрый Э - Эх,

да, согласен. Замножение есть хоть в любом случае. Но в том плане, который я хочу - промежуточные результаты строк соединения таблиц будут минимальными, соответсвенно и самый быстрый план.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 20:35
    #39577562
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
niv76соответсвенно и самый быстрый план.М-м-м, какие мы самоуверенные!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 20:36
    #39577563
niv76
niv76
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
AmKad,

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

:(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 20:38
    #39577565
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
niv76AmKad,

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

:(Да ладно, не заморачивайся. Ошибки правописания - не самые серьезные ошибки в нашей жизни.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 21:36
    #39577585
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
niv76,

А на досуг тебе задачка для размышления. Есть две сущности: классы и ученики. Вывести все классы, в которых есть хотя бы по одному Ивану и Владимиру, но нет ни одного Дениса и Вадима.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.12.2017, 21:43
    #39577587
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
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
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.
77.
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
| Ответить | Цитировать | Написать  
29.12.2017, 13:55
    #39577893
niv76
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, 14:19
    #39577917
Dshedoo
Dshedoo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
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:26
    #39577928
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
niv76делаю вывод что 11ка не умеет делать такую трансформациюДело не в трансформации, а в видимости в скаляре всего на один уровень в 11. Можно обойти если сильно захотеть.
niv76Можете кинуть Ваше решение, аналогичный пример, или хотя бы наводящие подсказки?Полагаю AmKad намекает, что от semi join можно уйти если написать хитрое выражение в having + decode.
И это таки возможно. Начни с задачки 21070947 .
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.12.2017, 15:12
    #39577945
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
dbms_photoshopИ это таки возможно.Из-за self join по неравенству думаю, что нет. Так что не заморачивайся.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.12.2017, 19:00
    #39578049
niv76
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
1.
Дело не в трансформации, а в видимости в скаляре всего на один уровень в 11. Можно обойти если сильно захотеть.



Можете для моего примера написать, как это можно обойти?
Для поднятия общей образованности. Сам придумать не могу.
Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.12.2017, 20:49
    #39578071
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
dbms_photoshopdbms_photoshopИ это таки возможно.Из-за self join по неравенству думаю, что нет. Так что не заморачивайся.Рано сдаешься. Равенства по номеру счета должно хватить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.12.2017, 20:51
    #39578073
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
как хинтами добиться нужного плана
niv762. dbms_photoshop , заинтересовала ваша фраза:
Код: plsql
1.
Дело не в трансформации, а в видимости в скаляре всего на один уровень в 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
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / как хинтами добиться нужного плана / 25 сообщений из 38, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 511ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1884639]:
 0ms
get settings:
 8ms
get forum list:
 15ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 156ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 63ms
get tp. blocked users:
 1ms
others: 252ms
total:  511ms
x
x
Закрыть


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