Добро пожаловать, Гость |
Администрирование Oracle Database:
- Установка и настройка базы данных
- Обновление, применение патчей
- Оптимизация, настройка производительности
- Обучение, подготовка персонала, оптимизация
- Установка и настройка базы данных
- Обновление, применение патчей
- Оптимизация, настройка производительности
- Обучение, подготовка персонала, оптимизация
ТЕМА:
oracle план выполнения запроса - Есть ли возможность захинтовать left join как use_hash? 12 года 2 мес. назад #4042
День добрый Есть запрос, в упрощенном виде он выглядит так: 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? 12 года 2 мес. назад #4043
Один из самых простых способов: AND t1.TYPE||'' = 'value' немного сложнее будет: /*+ use_hash(<second_tab_alias>)*/ А самым правильным и самым сложным будет: проанализировать почему оптимизатор принимает такое решение и попробовать исправить ситуацию собрав статистику или через outline | |
Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе. |
Re: oracle план выполнения запроса - Есть ли возможность захинтовать left join как use_hash? 12 года 2 мес. назад #4044
если я тебя правильно понял, то это будет писаться так: /*+ 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? 12 года 2 мес. назад #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? 12 года 2 мес. назад #4046
Если б все было так как вы предлагаете, отпала бы необходимость внешнего соединения. Мои запросы все-же эквивалентны и возвращают: ID VAL ID1 ID_T
---------- --- ---------- ----------
1 111 101 1
2 222 Если я правильно понял, то при указанном мной ansi join оракл не может сделать hash join даже с хинтом. Это у меня только с этим проблемы или может еще кто с подобным сталкивался - поделитесь решением. | |
Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе. |
Re: oracle план выполнения запроса - Есть ли возможность захинтовать left join как use_hash? 12 года 2 мес. назад #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. | |
Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе. |
Время создания страницы: 0.215 секунд