Как избежать query rewrite union all -> left join / Oracle

ReSQL.ru

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

Гость

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

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

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

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

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как избежать query rewrite union all -> left join

14 сообщений из 14, страница 1 из 1

Как избежать query rewrite union all -> left join

#40115563

qwerty005

Гость

Здравствуйте, уважаемые участники!
Имеется запрос вида

Код: plsql

select tab1.col1, tab2.col2 from tab1 left join tab2 on tab1.col=tab2.col

работает он примерно 5 минут.
Если запускаю запросы по отдельности

Код: plsql

select tab1.col1, tab2.col2  from tab1 join tab2 on tab1.col=tab2.col

Код: plsql

select tab1.col1, null as col2  from tab1 left join tab2 on tab1.col=tab2.col where tab2.col is null

то каждый из них работает секунд по 20

Но когда пишу

Код: plsql

1.
2.
3.

select tab1.col1, tab2.col2  from tab1 join tab2 on tab on tab1.col=tab2.col
union all
select tab1.col1, null as col2  from tab1 left join tab2 on tab on tab1.col=tab2.col where tab2.col is null

оракл переписывает запрос так, что план генерируется как для left-join-а. И работает это всё 5 минут.

Мой вопрос в том - как "перекрыть" оптимизатору конкретно эту возможность переписывания запроса ?

...

Рейтинг:

0 / 0

27.11.2021, 20:09

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

Как избежать query rewrite union all -> left join

#40115576

SQL*Plus

Участник

Откуда: Россия, Москва

Сообщения: 8 665

Рейтинг: 0 / 0

Попробуйте это:

NO_QUERY_TRANSFORMATION Hint

Код: plaintext

/*+ NO_QUERY_TRANSFORMATION */

The NO_QUERY_TRANSFORMATION hint instructs the optimizer to skip all query
transformations, including but not limited to OR-expansion, view merging, subquery
unnesting, star transformation, and materialized view rewrite. For example:

Код: plaintext

1.
2.
3.

SELECT /*+ NO_QUERY_TRANSFORMATION */ employee_id, last_name
FROM (SELECT * FROM employees e) v
WHERE v.last_name = 'Smith';

...

Рейтинг:

0 / 0

27.11.2021, 22:11

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

Как избежать query rewrite union all -> left join

#40115578

Elic

Участник

Сообщения: 30 360

Рейтинг: 0 / 0

qwerty005

Код: plsql

1.
2.
3.

select tab1.col1, tab2.col2  from tab1 join tab2 on tab on tab1.col=tab2.col
union all
select tab1.col1, null as col2  from tab1 left join tab2 on tab on tab1.col=tab2.col where tab2.col is null

А чем не устраивает простой эквивалент

Код: plsql

select tab1.col1, tab2.col2 from tab1 left join tab2 on tab1.col=tab2.col

...

Рейтинг:

0 / 0

27.11.2021, 22:19

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

Как избежать query rewrite union all -> left join

#40115588

qwerty005

Гость

Elic

qwerty005

Код: plsql

1.
2.
3.

select tab1.col1, tab2.col2  from tab1 join tab2 on tab on tab1.col=tab2.col
union all
select tab1.col1, null as col2  from tab1 left join tab2 on tab on tab1.col=tab2.col where tab2.col is null

А чем не устраивает простой эквивалент

Код: plsql

select tab1.col1, tab2.col2 from tab1 left join tab2 on tab1.col=tab2.col

Изначально и был left join, работающий 5 минут.
Была поставлена задача оптимизировать, поэтому и была предпринята попытка переписать через union all - вдруг станет быстрее....
И действительно, по отдельности эти запросы работают быстро.
Еще забыла сразу написать - tab1 и tab2 - это не таблицы, а CTE

...

Рейтинг:

0 / 0

27.11.2021, 23:03

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

Как избежать query rewrite union all -> left join

#40115590

SQL*Plus

Участник

Откуда: Россия, Москва

Сообщения: 8 665

Рейтинг: 0 / 0

qwerty005

Еще забыла сразу написать - tab1 и tab2 - это не таблицы, а CTE

СТЕ - это конечно же Chronic traumatic encephalopathy?

...

Рейтинг:

0 / 0

27.11.2021, 23:13

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

Как избежать query rewrite union all -> left join

#40115596

Бельфя

Гость

qwerty005, без планов судить сложно...
Но, возможно, имеет смысл материализовать CTE.
Предполагаю, что в запросе с union all СВО так и поступает, чтобы 2 раза одни и теже подзапрсы не крутить.

...

Рейтинг:

0 / 0

28.11.2021, 00:10

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

Как избежать query rewrite union all -> left join

#40115603

Бельфя

Гость

Бельфя

В запросе с left join *

...

Рейтинг:

0 / 0

28.11.2021, 00:37

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

Как избежать query rewrite union all -> left join

#40115606

qwerty005

Гость

Всем спасибо!
Удалось побороть, дублировав один из CTE, немного изменив текст запроса без изменения сути внутри него (добавлением where 1=1 и тд)
т.е. примерно так:

Код: plsql

1.
2.
3.

select tab1.col1, tab2.col2  from tab1 join tab2 on tab on tab1.col=tab2.col
union all
select tab3.col1, null as col2  from tab3 left join tab2 on tab on tab3.col=tab2.col where tab2.col is null

где tab3 и tab1 - почти идентичные подзапросы, различающиеся по виду, но не по содержанию

...

Рейтинг:

0 / 0

28.11.2021, 00:49

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

Как избежать query rewrite union all -> left join

#40115613

SQL*Plus

Участник

Откуда: Россия, Москва

Сообщения: 8 665

Рейтинг: 0 / 0

qwerty005

Удалось побороть, дублировав один из CTE

Откройте "страшную тайну": что такое CTE?

...

Рейтинг:

0 / 0

28.11.2021, 01:25

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

Как избежать query rewrite union all -> left join

#40115648

Бельфя

Гость

SQL*Plus

qwerty005

Удалось побороть, дублировав один из CTE

Откройте "страшную тайну": что такое CTE?

Common Table Expression

...

Рейтинг:

0 / 0

28.11.2021, 10:37

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

Как избежать query rewrite union all -> left join

#40115806

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

qwerty005

где tab3 и tab1 - почти идентичные подзапросы, различающиеся по виду, но не по содержанию

два with неудобно поддерживать

если ети условия добавить во второй юнион, всеравно "трансформирует"?

ps
во второй добавить заведомо истинное условие (rownum>0.5, sign()<2 тощо)
....
stax

...

Рейтинг:

0 / 0

29.11.2021, 10:36

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

Как избежать query rewrite union all -> left join

#40115809

SQL*Plus

Участник

Откуда: Россия, Москва

Сообщения: 8 665

Рейтинг: 0 / 0

Бельфя

SQL*Plus

пропущено...

Откройте "страшную тайну": что такое CTE?

Common Table Expression

В документации Oracle Database термина "Common Table Expression" не нашел.
Используется понятие WITH Clause.

В документации на Oracle MySQL пишут
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/with.html 13.2.15 WITH (Common Table Expressions)

A common table expression (CTE) is a named temporary result set that exists
within the scope of a single statement and that can be referred to later within
that statement, possibly multiple times.

...

Рейтинг:

0 / 0

29.11.2021, 10:48

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

Как избежать query rewrite union all -> left join

#40115912

Sayan Malakshinov

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

Откуда: Мск

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

Рейтинг: 0 / 0

SQL*Plus

В документации Oracle Database термина "Common Table Expression" не нашел.

Это общий термин из стандарта SQL:1999 и в документацию он уже попал:

12.6.3 Polymorphic Table Function Invocation
12.6.3 Polymorphic Table Function Invocation

Код: plsql

1.
2.
3.

WITH e AS 
 (SELECT * FROM emp NATURAL JOIN dept)
SELECT t.* FROM noop(e) t;

The input table argument must be a basic table name.

The name resolution rules of the table identifier are (in priority order) as follows :

Identifier is resolved as a column name (such as a correlated column from an outer query block).
Identifier is resolved as a Common Table Expression (CTE) name in the current or some outer query-block. CTE is commonly known as the WITH clause.
Identifier is resolved as a schema-level table, view, or table-function (regular or polymorphic, and defined either at the schema-level or inside a package).

...

Рейтинг:

0 / 0

29.11.2021, 15:32

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

Как избежать query rewrite union all -> left join

#40115971

SQL*Plus

Участник

Откуда: Россия, Москва

Сообщения: 8 665

Рейтинг: 0 / 0

Sayan Malakshinov

SQL*Plus

В документации Oracle Database термина "Common Table Expression" не нашел.

Код: plsql

1.
2.
3.

WITH e AS 
 (SELECT * FROM emp NATURAL JOIN dept)
SELECT t.* FROM noop(e) t;

The input table argument must be a basic table name.

The name resolution rules of the table identifier are (in priority order) as follows :

Identifier is resolved as a column name (such as a correlated column from an outer query block).
Identifier is resolved as a Common Table Expression (CTE) name in the current or some outer query-block. CTE is commonly known as the WITH clause.
Identifier is resolved as a schema-level table, view, or table-function (regular or polymorphic, and defined either at the schema-level or inside a package).

Это очень косвенное косвенное упоминание приведено ТОЛЬКО в документе "Database PL/SQL Language Reference"
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html

В руководстве по языку SQL ничего такого нет.

А могли бы дать явное определение и привести свою терминологию к стандарту.

Сами такие стандарты мало, кто читает, кроме разработчиков таких стандартов и разработчиков СУБД.

Лично за почти 28 лет использования SQL ни разу не читал ни одного стандарта по языку SQL,
поскольку это вряд ли чем-то могло помочь в конкретной работе.

Спасибо за найденный контекст!

...

Рейтинг:

0 / 0

29.11.2021, 19:51

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

14 сообщений из 14, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как избежать query rewrite union all -> left join

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

start [/forum/topic.php?fid=52&msg=40115588&tid=1879713]:	0ms
get settings:	18ms
get forum list:	6ms
check forum access:	1ms
check topic access:	1ms
track hit:	36ms
get topic data:	3ms
get forum data:	1ms
get page messages:	268ms
get tp. blocked users:	1ms
others:	354ms

total:	689ms