Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 35
Может быть кто-то может посоветовать годную книжку по SQL? Сколько им пользуюсь, а до сих пор не могу придумать как использовать тот же JOIN в своих поделках…
Ну, собственно всё, что делает JOIN — дает возможность выборки данных из связанных таблиц. В одной таблице мы храним одни данные (допустим персональные данные сотрудников), а в другой, скажем, типы ролей сотрудников. И, допустим, у нас есть третья таблица, где указано соотвествие сотрудников ролям (связь многие ко многим).
JOIN тут как раз и нужен, чтобы сделать выборку — у каких сотрудников какие роли. Или сколько сотрудников в компании с ролью R.
JOIN тут как раз и нужен, чтобы сделать выборку — у каких сотрудников какие роли. Или сколько сотрудников в компании с ролью R.
А как вы таблицы в запросах соединяете?
Если такая необходимость появляется(что бывает довольно редко), то делаю это на стороне приложения, запросив таблицы по отдельности. Может когда вам способ хранения данных навязали или вы работаете в highload ситуация другая, не в курсе. Но мне обычно хватает для манипуляций id строки из второй таблицы в поле первой…
Подход замечательный, только если у Вас хайлоад. Вот тут про такое действо пишут. Но, в Вашем случае, подход крайне нехороший и никогда так не делайте без осознанной необходимости.
Если вдруг просто по самим айдишникам недостаточно сортировки, пишу нечто такое:
Это, наверное, не эффективно, но зато на мой вкус нагляднее и на моих уровнях нагрузки все равно отрабатывает мгновенно)
SELECT * FROM 'people' WHERE 'cid' IN (SELECT 'id' FROM 'cities' WHERE %condtion%)
Это, наверное, не эффективно, но зато на мой вкус нагляднее и на моих уровнях нагрузки все равно отрабатывает мгновенно)
Вроде в запросах построенных таким образом, Оракл сам проводит трансформацию к виду JOIN.
Потому и работает быстро. Посмотрите на план выполнения
На вообще конечно ужас-ужас.
Вы чужой код как читать собираетесь?
Потому и работает быстро. Посмотрите на план выполнения
На вообще конечно ужас-ужас.
Вы чужой код как читать собираетесь?
Ну пока не было таких проектов, где требовалось плотно работать с базами данных, сайтики не в счет. А на сайтиках с малой нагрузкой нормально отрабатывает(только там не Oracle, а MySQL).
Чужой код я нормально читаю) Можно понимать английскую речь без способности связно на ней говорить :)
Чужой код я нормально читаю) Можно понимать английскую речь без способности связно на ней говорить :)
А как вы сейчас решаете задачу вывода значений из нескольких таблиц? Select * from table1, table2, table3 where table1.id = table2.id and table 2.name = table3.name?
«Введение в SQL» Мартин Грабер
Крис Дж. Дейт «Введение в системы баз данных»
Хоть и капитанство, но раз тема про Oracle, то предположу что Вы желаете его изучать, посему посоветую Тома Кайта.
Хорошую тему подняли.
Есть еще несколько видов преобразований cbo, которые достойны статей. На примере 11g:
— генерация и добавление в план filter-предикатов на основании check или not-null constraints, которые должны быть в состоянии enabled и validated;
— преобразование set-to-join (ему посвящен даже отдельный хинт set_to_join/no_set_to_join);
— преобразование вложенных подзапросов в join (тоже есть отдельный хинт unnest / no_unnest).
Кстати самая кровавая вещь на моей памяти, которая вызвала много проблем производительности при миграции 10g -> 11g.
Есть еще несколько видов преобразований cbo, которые достойны статей. На примере 11g:
— генерация и добавление в план filter-предикатов на основании check или not-null constraints, которые должны быть в состоянии enabled и validated;
— преобразование set-to-join (ему посвящен даже отдельный хинт set_to_join/no_set_to_join);
— преобразование вложенных подзапросов в join (тоже есть отдельный хинт unnest / no_unnest).
Кстати самая кровавая вещь на моей памяти, которая вызвала много проблем производительности при миграции 10g -> 11g.
Спасибо за комментарий. Учту на будущее.
Строго говоря, и для join elimination есть хинты: ELIMINATE_JOIN / NO_ELIMINATE_JOIN
C 11g есть вьюха v$sql_hint, в ней можете посмотреть остальные хинты с подробной информацией.
Еще подбробнее можно с v$sql_feature: blog.tanelpoder.com/2013/04/01/understanding-what-a-hint-affects-using-the-vsql_feature-views/
C 11g есть вьюха v$sql_hint, в ней можете посмотреть остальные хинты с подробной информацией.
Еще подбробнее можно с v$sql_feature: blog.tanelpoder.com/2013/04/01/understanding-what-a-hint-affects-using-the-vsql_feature-views/
— преобразование вложенных подзапросов в join (тоже есть отдельный хинт unnest / no_unnest).
Кстати, в этих примерах с exists/in/not in эта трансформация уже была: сначала выполняется subquery unnesting и только потом join elimination.
Есть еще полезная трансформация, похожая на оба этих случая — coalesce subquery (хинты COALESCE_SQ/NO_COALESCE_SQ)
Этот способ трансформации запроса впервые появился в Oracle 10.2, но в достаточно ограниченном виде. Он поддерживал только inner join, написанный в традиционном (не-ANSI) стиле Oracle.Непонятно, что речь? Вообще-то почти все ANSI-запросы трансформируются оптимизатором оракла в оракловые(исключений немного: native full outer join и outer с предикатами по столбцам двух и более разных таблиц).
Больше похоже, что вы напоролись на какую-то недоделку, баг или трансформацию с латералами.
Установил Oracle 10.2, проверил еще раз — трансформация работает с обоими вариантами синтаксиса. Поправил текст в статье, спасибо.
Все-таки, даже судя по документации, у синтаксиса ANSI-соединений больше возможностей, чем у традиционного синтаксиса. На мой взгляд, было бы странно в таком случае приводить запрос к традиционному синтаксису. Тем более, Вы сами отметили, что это происходит не во всех случаях. Но если это действительно так — не могли бы Вы рассказать, в какой момент выполнения запроса происходит это преобразование? И еще больше я был бы признателен за какую-либо ссылку на документацию по этому вопросу.
Все-таки, даже судя по документации, у синтаксиса ANSI-соединений больше возможностей, чем у традиционного синтаксиса. На мой взгляд, было бы странно в таком случае приводить запрос к традиционному синтаксису. Тем более, Вы сами отметили, что это происходит не во всех случаях. Но если это действительно так — не могли бы Вы рассказать, в какой момент выполнения запроса происходит это преобразование? И еще больше я был бы признателен за какую-либо ссылку на документацию по этому вопросу.
Дело в том, что Oracle RDBMS появилась раньше официально признанного стандарта, да и, кроме того, у всех РСУБД есть и были разногласия со стандартом. А реальных методов соединений, фильтрации и доступа не так много и практически все покрываются старым оракловым синтаксисом.
В трассировке 10053(секция «Final query after transformations») вы можете увидеть, что как запрос трансформируется. В принципе есть способ попроще, но менее надежный и показывается не совсем точная копия трансформированного запроса:
в 11g — dbms_sql2.expand_sql_text
в 12с = dbms_utility.expand_sql_text
Вообще, советую прочитать Cost-Based Oracle fundamentals Джонатана Льюиса. Ее просто необходимо прочесть наряду с его же «Oracle Core», если хотите заниматься оптимизацией производительности.
не могли бы Вы рассказать, в какой момент выполнения запроса происходит это преобразование?
В трассировке 10053(секция «Final query after transformations») вы можете увидеть, что как запрос трансформируется. В принципе есть способ попроще, но менее надежный и показывается не совсем точная копия трансформированного запроса:
в 11g — dbms_sql2.expand_sql_text
в 12с = dbms_utility.expand_sql_text
Вообще, советую прочитать Cost-Based Oracle fundamentals Джонатана Льюиса. Ее просто необходимо прочесть наряду с его же «Oracle Core», если хотите заниматься оптимизацией производительности.
да, забыл пояснить изначально:
речь про «ORA-01417: a table may be outer joined to at most one other table» при оракловом синтаксисе.
в 12c это уже исправлено и оракловый синтаксис позволяет так соединять.
outer с предикатами по столбцам двух и более разных таблиц
речь про «ORA-01417: a table may be outer joined to at most one other table» при оракловом синтаксисе.
в 12c это уже исправлено и оракловый синтаксис позволяет так соединять.
К слову, совсем недавно в Oracle-L я приводил пример, когда Join elimination наоборот приводит к худшему плану причем с большей стоимостью:
gist.github.com/xtender/f0871ffa99b1413232e6
gist.github.com/xtender/f0871ffa99b1413232e6
Интересный пример, спасибо. Не могли бы Вы его немного прокомментировать?
Если посмотреть на планы запросов в Oracle 12.1:
1) c join eliminate
2) без join eliminate
то видно, что действительно, cost второго запроса ниже, не смотря на то, что сам запрос более сложный. Но, получается, что шаги 3-5 во втором случае тоже самое, что и шаг 3 в первом случае и, помимо этого, во втором случае присутствует еще один цикл, в то время как в первом случае происходит batched table access. Почему же cost так сильно отличается и во втором случае он в несколько раз меньше?
Кстати, в Oracle 11.2 запросы получаются немного другие и их cost одинаков:
1) с join elimination
2) без join elimination
Если посмотреть на планы запросов в Oracle 12.1:
1) c join eliminate
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 164 | 3608 | 167 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| XT_BIG_TAB | 164 | 3608 | 167 (0)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | IX_BIG_TAB | 164 | | 166 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
1 - filter(SYSDATE@!>=SYSDATE@!-.01)
3 - access("T"."DT">=SYSDATE@!-.01 AND "T"."DT"<=SYSDATE@!)
filter("T"."DT">=SYSDATE@!-.01 AND "T"."DT"<=SYSDATE@!)
2) без join eliminate
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 164 | 4100 | 22 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 164 | 4100 | 22 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | SYS_C00459678 | 10 | 30 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IX_BIG_TAB | 16 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| XT_BIG_TAB | 16 | 352 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
1 - filter(SYSDATE@!>=SYSDATE@!-.01)
5 - access("T"."NUMS_ID"="N"."ID" AND "T"."DT">=SYSDATE@!-.01 AND
"T"."DT"<=SYSDATE@!)
то видно, что действительно, cost второго запроса ниже, не смотря на то, что сам запрос более сложный. Но, получается, что шаги 3-5 во втором случае тоже самое, что и шаг 3 в первом случае и, помимо этого, во втором случае присутствует еще один цикл, в то время как в первом случае происходит batched table access. Почему же cost так сильно отличается и во втором случае он в несколько раз меньше?
Кстати, в Oracle 11.2 запросы получаются немного другие и их cost одинаков:
1) с join elimination
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 164 | 3608 | 13 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| XT_BIG_TAB | 164 | 3608 | 13 (0)| 00:00:01 |
|* 3 | INDEX SKIP SCAN | IX_BIG_TAB | 164 | | 12 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
1 - filter(SYSDATE@!>=SYSDATE@!-.01)
3 - access("T"."DT">=SYSDATE@!-.01 AND "T"."DT"<=SYSDATE@!)
filter("T"."DT">=SYSDATE@!-.01 AND "T"."DT"<=SYSDATE@!)
2) без join elimination
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 164 | 4100 | 13 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 164 | 4100 | 13 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| XT_BIG_TAB | 164 | 3608 | 13 (0)| 00:00:01 |
|* 4 | INDEX SKIP SCAN | IX_BIG_TAB | 164 | | 12 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0013510913 | 1 | 3 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
1 - filter(SYSDATE@!>=SYSDATE@!-.01)
4 - access("T"."DT">=SYSDATE@!-.01 AND "T"."DT"<=SYSDATE@!)
filter("T"."DT">=SYSDATE@!-.01 AND "T"."DT"<=SYSDATE@!)
5 - access("T"."NUMS_ID"="N"."ID")
Все на самом деле просто:
Обращайте внимание на ACCESS и FILTER предикаты в плане:
xt_nums — таблица маленькая и легко вычитывается за один проход и если начинать от нее, то мы получаем значения лидирующего столбца в индексе для INDEX RANGE SCAN(далее я буду сокращать как привык — IRS). Соответственно при IRS мы будем заходить сразу с верхними и нижними границами по каждому из 10 значений полученных из xt_nums. А Index skip scan, при сработавшем Join elimination, вычитывает весь индекс IX_BIG_TAB, а не спускается по дереву как IRS.
Кроме того, мне кажется что у вас занижен параметр optimizer_index_cost_adj или что-то не так со статистикой — слишком маленькая стоимость у INDEX SKIP SCAN. В общем у вас получилась проблема неправильного порядка таблиц во втором плане — попробуйте изменить в нем хинт на
Обращайте внимание на ACCESS и FILTER предикаты в плане:
xt_nums — таблица маленькая и легко вычитывается за один проход и если начинать от нее, то мы получаем значения лидирующего столбца в индексе для INDEX RANGE SCAN(далее я буду сокращать как привык — IRS). Соответственно при IRS мы будем заходить сразу с верхними и нижними границами по каждому из 10 значений полученных из xt_nums. А Index skip scan, при сработавшем Join elimination, вычитывает весь индекс IX_BIG_TAB, а не спускается по дереву как IRS.
Кстати, в Oracle 11.2 запросы получаются немного другие и их cost одинаков:Вообще-то этот пример я как раз делал на 11.2. У вас, наверное какая-нибудь низкая версия типа 11.2.0.1 — 11.2.0.2 — это заметно по 0 в стоимости INDEX UNIQUE SCAN, насколько помню это в каком-то промежуточном патче было исправлено.
Кроме того, мне кажется что у вас занижен параметр optimizer_index_cost_adj или что-то не так со статистикой — слишком маленькая стоимость у INDEX SKIP SCAN. В общем у вас получилась проблема неправильного порядка таблиц во втором плане — попробуйте изменить в нем хинт на
/*+ leading(n t) use_nl(t) index(t (NUMS_ID, DT)) NO_ELIMINATE_JOIN(n) */Соответственно при IRS мы будем заходить сразу с верхними и нижними границами по каждому из 10 значений полученных из xt_nums. А Index skip scan, при сработавшем Join elimination, вычитывает весь индекс IX_BIG_TAB, а не спускается по дереву как IRS.
Я предполагал, что INDEX SCIP SCAN (ISS) логически разобьет индекс IX_BIG_TAB на 10 небольших индексов и уже последовательно пройдется по ним, используя access(«T».«DT»>=SYSDATE@!-.01 AND «T».«DT»<=SYSDATE@!). Т.е. сделает подобие IRS 10 раз, и, логически получится тоже самое что и в шагах 3-5 из запроса без join elimination. Но, судя по вашим словам, ISS отработает на подобии INDEX FULL SCAN раз ему придется вычитывать весь индекс IX_BIG_TAB?
Буду очень признателен если Вы поподробнее поясните это.
Вообще-то этот пример я как раз делал на 11.2. У вас, наверное какая-нибудь низкая версия типа 11.2.0.1 — 11.2.0.2 — это заметно по 0 в стоимости INDEX UNIQUE SCAN, насколько помню это в каком-то промежуточном патче было исправлено.
Я пробовал на Oracle 11.2.0.4.0. Параметр optimizer_index_cost_adj = 100 (значение по умолчанию).
Попробовал выполнить запрос используя Ваш хинт, и получил такой же план как и в Oracle 12.1, но его стоимость оказалась выше чем у других планов для Oracle 11.2:
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 164 | 4100 | 22 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 164 | 4100 | 22 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 164 | 4100 | 22 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | SYS_C0013510913 | 10 | 30 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IX_BIG_TAB | 16 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| XT_BIG_TAB | 16 | 352 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
1 - filter(SYSDATE@!>=SYSDATE@!-.01)
5 - access("T"."NUMS_ID"="N"."ID" AND "T"."DT">=SYSDATE@!-.01 AND "T"."DT"<=SYSDATE@!)
Видимо различия в каких-то еще параметрах.
Проверил все планы запросов для Oracle 11.2 получив их через dbms_xplan.display_cursor и все совпало.
Я предполагал, что INDEX SKIP SCAN (ISS) логически разобьет индекс IX_BIG_TAB на 10 небольших индексов и уже последовательно пройдется по ним, используя access(«T».«DT»>=SYSDATE@!-.01 AND «T».«DT»<=SYSDATE@!). Т.е. сделает подобие IRS 10 раз, и, логически получится тоже самое что и в шагах 3-5 из запроса без join elimination.
Основное отличие в том, что Oracle заранее не знает, какие значения у лидирующего столбца в индексе, чтобы спускаться по дереву к каждому левому значению, и поэтому не знает где очередное начинается и где заканчивается, поэтому ему нужно отлавливать, когда значение лидирующего в branch-блоках изменяется и брать его уже для прохода по leaf-блокам, а остальные leaf-блоки skip'ает.
В принципе можете прочитать тут и статью и комментарии: richardfoote.wordpress.com/2008/03/10/index-skip-scan-does-index-column-order-matter-any-more-warning-sign/
Видимо различия в каких-то еще параметрах.проще трассировку 10053 сделать
Основное отличие в том, что Oracle заранее не знает, какие значения у лидирующего столбца в индексе, чтобы спускаться по дереву к каждому левому значению, и поэтому не знает где очередное начинается и где заканчивается, поэтому ему нужно отлавливать, когда значение лидирующего в branch-блоках изменяется и брать его уже для прохода по leaf-блокам, а остальные leaf-блоки skip'ает.
В принципе можете прочитать тут и статью и комментарии: richardfoote.wordpress.com/2008/03/10/index-skip-scan-does-index-column-order-matter-any-more-warning-sign/
Спасибо за пояснение и ссылку, очень познавательно. Я попробовал графически выразить свое понимание принципа работы ISS. Если я ошибся поправьте меня пожалуйста:
— представим что есть составной индекс (CHAR, NUM) и идет поиск строк с условием NUM = 2.
Кстати, лучше проверять не просто explain'ами, а выполнением с gather_plan_statistics и анализом плана с allstats last
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Oracle join elimination