Гость
Войти | Профиль | Очистить
Map
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как избежать query rewrite union all -> left join / 14 сообщений из 14, страница 1 из 1
27.11.2021, 20:09
    #40115563
qwerty005
qwerty005
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
Здравствуйте, уважаемые участники!
Имеется запрос вида

Код: plsql
1.
select tab1.col1, tab2.col2 from tab1 left join tab2 on tab1.col=tab2.col


работает он примерно 5 минут.
Если запускаю запросы по отдельности
Код: plsql
1.
select tab1.col1, tab2.col2  from tab1 join tab2 on tab1.col=tab2.col


и
Код: plsql
1.
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, 22:11
    #40115576
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
Попробуйте это:

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:19
    #40115578
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
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
1.
select tab1.col1, tab2.col2 from tab1 left join tab2 on tab1.col=tab2.col
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.11.2021, 23:03
    #40115588
qwerty005
qwerty005
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
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
1.
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:13
    #40115590
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
qwerty005
Еще забыла сразу написать - tab1 и tab2 - это не таблицы, а CTE

СТЕ - это конечно же Chronic traumatic encephalopathy?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2021, 00:10
    #40115596
Бельфя
Бельфя
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
qwerty005, без планов судить сложно...
Но, возможно, имеет смысл материализовать CTE.
Предполагаю, что в запросе с union all СВО так и поступает, чтобы 2 раза одни и теже подзапрсы не крутить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2021, 00:37
    #40115603
Бельфя
Бельфя
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
Бельфя
qwerty005, без планов судить сложно...
Но, возможно, имеет смысл материализовать CTE.
Предполагаю, что в запросе с union all СВО так и поступает, чтобы 2 раза одни и теже подзапрсы не крутить.

В запросе с left join *
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2021, 00:49
    #40115606
qwerty005
qwerty005
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
Всем спасибо!
Удалось побороть, дублировав один из 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, 01:25
    #40115613
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
qwerty005
Удалось побороть, дублировав один из CTE

Откройте "страшную тайну": что такое CTE?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.11.2021, 10:37
    #40115648
Бельфя
Бельфя
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
SQL*Plus
qwerty005
Удалось побороть, дублировав один из CTE

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

Common Table Expression
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.11.2021, 10:36
    #40115806
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
qwerty005

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


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

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

ps
во второй добавить заведомо истинное условие (rownum>0.5, sign()<2 тощо)
....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.11.2021, 10:48
    #40115809
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
Бельфя
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, 15:32
    #40115912
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
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, 19:51
    #40115971
SQL*Plus
SQL*Plus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как избежать query rewrite union all -> left join
Sayan Malakshinov
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).

Это очень косвенное косвенное упоминание приведено ТОЛЬКО в документе "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
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как избежать query rewrite union all -> left join / 14 сообщений из 14, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 781ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1879713]:
 0ms
get settings:
 22ms
get forum list:
 9ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 43ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 315ms
get tp. blocked users:
 1ms
others: 373ms
total:  781ms
x
x
Закрыть


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