Добро пожаловать, Гость
Логин: Пароль: Запомнить меня
Администрирование Oracle Database:
- Установка и настройка базы данных
- Обновление, применение патчей
- Оптимизация, настройка производительности
- Обучение, подготовка персонала, оптимизация
  • Страница:
  • 1

ТЕМА: oracle план выполнения запроса - Есть ли возможность захинтовать left join как use_hash?

oracle план выполнения запроса - Есть ли возможность захинтовать left join как use_hash? 20 янв 2012 14:08 #4042

  • Westwife
  • Westwife аватар Автор темы
  • Не в сети
  • Новый участник
  • Новый участник
  • Сообщений: 14
  • Спасибо получено: 0
День добрый
Есть запрос, в упрощенном виде он выглядит так:
SELECT *
  FROM table1 t1
  LEFT JOIN table2 t2 ON t2.id_t = t1.id AND
                         t1.TYPE = 'value';

Цель: убрать из дочерней таблицы записи, что соответствуют таким же родительским определенного типа. Но, тут проблемка, если не делать фильтрацию, обработка запроса проходит мгновенно, а если добавить фильтр по типу, то происходит замена плана с HASH JOIN OUTER на NESTED LOOPS OUTER, что увеличивает время обработки запроса в разы.

Можно попробовать переписать соединение в оракловый синтаксис
SELECT *
  FROM table1 t1,
       (SELECT t2.*
          FROM table1 t1, table2 t2
         WHERE t2.id_t = t1.id AND
               t1.TYPE = 'value') t2
 WHERE t2.id_t(+) = t1.id;

тогда HASH JOIN снова на верхнем уровне и все работает без сучка и задоринки :)

Но, мне кажется, такая запись будет менее прозрачной. А потому очень хотелось бы обойтись left join (про баги связанные с ansi join знаю).
Помогите решить пробемку! Пробовал использовать use_hash(t1 t2) или no_use_nl - не работает как хотелось бы

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

Re: oracle план выполнения запроса - Есть ли возможность захинтовать left join как use_hash? 20 янв 2012 14:10 #4043

Один из самых простых способов:
AND t1.TYPE||'' = 'value'

немного сложнее будет:
/*+ use_hash(<second_tab_alias>)*/

А самым правильным и самым сложным будет:
проанализировать почему оптимизатор принимает такое решение и попробовать исправить ситуацию собрав статистику или через outline

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

Re: oracle план выполнения запроса - Есть ли возможность захинтовать left join как use_hash? 20 янв 2012 14:16 #4044

  • Westwife
  • Westwife аватар Автор темы
  • Не в сети
  • Новый участник
  • Новый участник
  • Сообщений: 14
  • Спасибо получено: 0
если я тебя правильно понял, то это будет писаться так:
/*+ use_hash(<alias1> <alias2>)*/.

Как по мне, то статистика совершенно не поможет. Сам запрос намного сложнее, просто я сделал его максимально простым, оставив тот же эффект.

Если смотреть более конкретно, то:
SQL> create table t as
  2  (select 1 id, '111' val from dual union all select 2 id, '222' val from dual);
 
Table created
SQL> create table t1 as
  2  (select 101 id1, 1 id_t from dual union all select 102 id1, 2 id_t from dual);
 
Table created
SQL> explain plan for
  2  select *
  3  from t
  4  left join t1 on t.id = t1.id_t;
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2281690008
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    88 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     2 |    88 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     2 |    36 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |     2 |    52 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T"."ID"="T1"."ID_T"(+))
Note
-----
   - dynamic sampling used for this statement
 
19 rows selected

Теперь добавляем фильтр:
SQL> explain plan for
  2  select /*+ use_hash(t t1) */ *
  3  from t t
  4  left join t1 t1 on t.id = t1.id_t and t.val = '111';
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 994908191
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     2 |    88 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER  |      |     2 |    88 |     9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T    |     2 |    36 |     3   (0)| 00:00:01 |
|   3 |   VIEW               |      |     1 |    26 |     3   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T"."VAL"='111')
   5 - filter("T"."ID"="T1"."ID_T")
Note
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement
 
22 rows selected
SQL> explain plan for
  2  select /*+ no_use_nl */ *
  3  from t t
  4  left join t1 t1 on t.id = t1.id_t and t.val = '111';
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 994908191
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     2 |    88 |     9   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER  |      |     2 |    88 |     9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T    |     2 |    36 |     3   (0)| 00:00:01 |
|   3 |   VIEW               |      |     1 |    26 |     3   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T"."VAL"='111')
   5 - filter("T"."ID"="T1"."ID_T")
Note
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement
 
22 rows selected

Это немного не удачный пример, потому как тут nl действительно оправдано оптимизатором... но у меня это приводит к нежелательным последствиям. Статистика в порядке, остается одно - oracle так left join с фильтром по основной таблице трактует...

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

Re: oracle план выполнения запроса - Есть ли возможность захинтовать left join как use_hash? 20 янв 2012 14:20 #4045

Приведенный вами пример исключительно удачен! Так как демонстрирует причину подобного поведения оптимизатора.
Обращаю ваше внимание на предикаты фильтра
4 - filter("T"."VAL"='111')

Прикол в том, что, указав этот фильтр в кляузе on left-join, Вы сказали серверу:

- соединять с t1, ЕСЛИ VAL='111'
- не соединять в противном случае.

Переписав на подзапрос, изменяете это условие:

"все соединить, оставить в выборке только VAL='111'"

Попрошу не путать - это совершенно не эквивалентные условия.
Если вы имели ввиду именно второй вариант тогда:


Ну и если имелось ввиду имено второе, то:
explain plan for
select *
from ane_t t
left join ane_t1 t1 on t.id = t1.id_t
where t.val = '111';
 
Explained
select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2742530107
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        | 10000 |   429K| 43900   (1)| 00:08:47 |
|*  1 |  HASH JOIN OUTER   |        | 10000 |   429K| 43900   (1)| 00:08:47 |
|*  2 |   TABLE ACCESS FULL| ANE_T  | 10000 |   175K| 21955   (1)| 00:04:24 |
|   3 |   TABLE ACCESS FULL| ANE_T1 |  1000K|    24M| 21938   (1)| 00:04:24 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T"."ID"="T1"."ID_T"(+))
   2 - filter("T"."VAL"='111')
Note
-----
   - 'PLAN_TABLE' is old version
 
20 rows selected

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

Последнее редактирование: от Mirzan.

Re: oracle план выполнения запроса - Есть ли возможность захинтовать left join как use_hash? 20 янв 2012 14:24 #4046

  • Westwife
  • Westwife аватар Автор темы
  • Не в сети
  • Новый участник
  • Новый участник
  • Сообщений: 14
  • Спасибо получено: 0
Если б все было так как вы предлагаете, отпала бы необходимость внешнего соединения.

Мои запросы все-же эквивалентны и возвращают:
ID VAL        ID1       ID_T
---------- --- ---------- ----------
         1 111        101          1
         2 222            

Если я правильно понял, то при указанном мной ansi join оракл не может сделать hash join даже с хинтом.

Это у меня только с этим проблемы или может еще кто с подобным сталкивался - поделитесь решением.

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

Re: oracle план выполнения запроса - Есть ли возможность захинтовать left join как use_hash? 20 янв 2012 14:28 #4047

одним словом я думаю, что дело не в анси синтаксисе или оракловом, а в предикате соединения.
SQL> create table t as
  2  (select 1 id, '111' val from dual union all select 2 id, '222' val from dual);
 
Table created
SQL> create table t1 as
  2  (select 101 id1, 1 id_t from dual union all select 102 id1, 2 id_t from dual);
 
Table created
SQL> explain plan for
  2  select /*+ use_nl(t t1) */ *
  3  from t t
  4  left join t1 t1 on t.id = t1.id_t and t.val = '111';
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 994908191
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     2 |    88 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER  |      |     2 |    88 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T    |     2 |    36 |     2   (0)| 00:00:01 |
|   3 |   VIEW               |      |     1 |    26 |     2   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| T1   |     1 |    26 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T"."VAL"='111')
   5 - filter("T"."ID"="T1"."ID_T")
Note
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement
 
22 rows selected
SQL> explain plan for
  2  select /*+ use_hash(t t1) */ *
  3  from t t
  4  left join t1 t1 on t.id = t1.id_t and t.val = '111';
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 994908191
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     2 |    88 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER  |      |     2 |    88 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T    |     2 |    36 |     2   (0)| 00:00:01 |
|   3 |   VIEW               |      |     1 |    26 |     2   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| T1   |     1 |    26 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T"."VAL"='111')
   5 - filter("T"."ID"="T1"."ID_T")
Note
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement
 
22 rows selected
SQL> explain plan for
  2  select /*+ use_nl(t t1) */ *
  3  from t t
  4  left join t1 t1 on decode(t.val,'111',t.id) = t1.id_t;
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2785737985
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    88 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|      |     2 |    88 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     2 |    36 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |    26 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."ID_T"(+)=DECODE("T"."VAL",'111',"T"."ID"))
Note
-----
   - dynamic sampling used for this statement
 
19 rows selected
SQL> explain plan for
  2  select /*+ use_hash(t t1) */ *
  3  from t t
  4  left join t1 t1 on decode(t.val,'111',t.id) = t1.id_t;
 
Explained
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2281690008
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    88 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     2 |    88 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     2 |    36 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |     2 |    52 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID_T"(+)=DECODE("T"."VAL",'111',"T"."ID"))
Note
-----
   - dynamic sampling used for this statement
 
19 rows selected

Оракл для некоторых предикатов может сделать хеш, а для некоторых не может. Дело в том, что для оракл синтаксиса намного сложнее придумать такой предикат для которого это бы не удалось. Например для left join.

Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе.

  • Страница:
  • 1