How to become an author
Search
Write a publication
Pull to refresh

Comments 35

Может быть кто-то может посоветовать годную книжку по SQL? Сколько им пользуюсь, а до сих пор не могу придумать как использовать тот же JOIN в своих поделках…
Total votes 4: ↑2 and ↓20
Ну, собственно всё, что делает JOIN — дает возможность выборки данных из связанных таблиц. В одной таблице мы храним одни данные (допустим персональные данные сотрудников), а в другой, скажем, типы ролей сотрудников. И, допустим, у нас есть третья таблица, где указано соотвествие сотрудников ролям (связь многие ко многим).

JOIN тут как раз и нужен, чтобы сделать выборку — у каких сотрудников какие роли. Или сколько сотрудников в компании с ролью R.
Total votes 1: ↑1 and ↓0+1
А как вы таблицы в запросах соединяете?
Total votes 1: ↑1 and ↓0+1
Если такая необходимость появляется(что бывает довольно редко), то делаю это на стороне приложения, запросив таблицы по отдельности. Может когда вам способ хранения данных навязали или вы работаете в highload ситуация другая, не в курсе. Но мне обычно хватает для манипуляций id строки из второй таблицы в поле первой…
Total votes 3: ↑0 and ↓3-3
Подход замечательный, только если у Вас хайлоад. Вот тут про такое действо пишут. Но, в Вашем случае, подход крайне нехороший и никогда так не делайте без осознанной необходимости.
This comment wasn’t rated yet0
Я ж не говорю, что это правильно. Если бы я считал, что я всё делаю верно, стал ли бы я спрашивать совета? Но сливающим карму минусаторам пофиг…
This comment wasn’t rated yet0
Если вдруг просто по самим айдишникам недостаточно сортировки, пишу нечто такое:
SELECT * FROM 'people' WHERE  'cid' IN (SELECT 'id' FROM 'cities' WHERE %condtion%)

Это, наверное, не эффективно, но зато на мой вкус нагляднее и на моих уровнях нагрузки все равно отрабатывает мгновенно)
This comment wasn’t rated yet0
Вроде в запросах построенных таким образом, Оракл сам проводит трансформацию к виду JOIN.
Потому и работает быстро. Посмотрите на план выполнения
На вообще конечно ужас-ужас.
Вы чужой код как читать собираетесь?
This comment wasn’t rated yet0
Ну пока не было таких проектов, где требовалось плотно работать с базами данных, сайтики не в счет. А на сайтиках с малой нагрузкой нормально отрабатывает(только там не Oracle, а MySQL).
Чужой код я нормально читаю) Можно понимать английскую речь без способности связно на ней говорить :)
This comment wasn’t rated yet0
А как вы сейчас решаете задачу вывода значений из нескольких таблиц? Select * from table1, table2, table3 where table1.id = table2.id and table 2.name = table3.name?
This comment wasn’t rated yet0
Пишу так, чтобы не нужно было вытягивать что-то одновременно из нескольких таблиц, в основном.
This comment wasn’t rated yet0
То есть полная денормализация? Мой Вам совет, изучите матчасть по БД, не изобретайте велосипед. Лучше сразу писать и думать правильно, чем потом переучиваться.
This comment wasn’t rated yet0
Лол, я именно для этого и написал начальный коммент) Хотя текущих моих заказчиков по фрилансу полностью устраивает то, как я это делаю сейчас.
This comment wasn’t rated yet0
«Введение в SQL» Мартин Грабер
Total votes 2: ↑2 and ↓0+2
Крис Дж. Дейт «Введение в системы баз данных»
Total votes 2: ↑2 and ↓0+2
Хоть и капитанство, но раз тема про Oracle, то предположу что Вы желаете его изучать, посему посоветую Тома Кайта.
Total votes 3: ↑3 and ↓0+3
Хорошую тему подняли.
Есть еще несколько видов преобразований 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.
Total votes 1: ↑1 and ↓0+1
Спасибо за комментарий. Учту на будущее.
This comment wasn’t rated yet0
Строго говоря, и для 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/
Total votes 1: ↑1 and ↓0+1
Спасибо, добавил к статье.
This comment wasn’t rated yet0
— преобразование вложенных подзапросов в join (тоже есть отдельный хинт unnest / no_unnest).

Кстати, в этих примерах с exists/in/not in эта трансформация уже была: сначала выполняется subquery unnesting и только потом join elimination.
Есть еще полезная трансформация, похожая на оба этих случая — coalesce subquery (хинты COALESCE_SQ/NO_COALESCE_SQ)
This comment wasn’t rated yet0
Этот способ трансформации запроса впервые появился в Oracle 10.2, но в достаточно ограниченном виде. Он поддерживал только inner join, написанный в традиционном (не-ANSI) стиле Oracle.
Непонятно, что речь? Вообще-то почти все ANSI-запросы трансформируются оптимизатором оракла в оракловые(исключений немного: native full outer join и outer с предикатами по столбцам двух и более разных таблиц).
Больше похоже, что вы напоролись на какую-то недоделку, баг или трансформацию с латералами.
Total votes 1: ↑1 and ↓0+1
Установил Oracle 10.2, проверил еще раз — трансформация работает с обоими вариантами синтаксиса. Поправил текст в статье, спасибо.
Все-таки, даже судя по документации, у синтаксиса ANSI-соединений больше возможностей, чем у традиционного синтаксиса. На мой взгляд, было бы странно в таком случае приводить запрос к традиционному синтаксису. Тем более, Вы сами отметили, что это происходит не во всех случаях. Но если это действительно так — не могли бы Вы рассказать, в какой момент выполнения запроса происходит это преобразование? И еще больше я был бы признателен за какую-либо ссылку на документацию по этому вопросу.
Total votes 1: ↑1 and ↓0+1
Дело в том, что 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», если хотите заниматься оптимизацией производительности.
Total votes 1: ↑1 and ↓0+1
Спасибо за подробный ответ.
Вообще, советую прочитать Cost-Based Oracle fundamentals Джонатана Льюиса. Ее просто необходимо прочесть наряду с его же «Oracle Core», если хотите заниматься оптимизацией производительности.

Эти книжки как раз на очереди.
Total votes 1: ↑1 and ↓0+1
да, забыл пояснить изначально:
outer с предикатами по столбцам двух и более разных таблиц

речь про «ORA-01417: a table may be outer joined to at most one other table» при оракловом синтаксисе.
в 12c это уже исправлено и оракловый синтаксис позволяет так соединять.
Total votes 1: ↑1 and ↓0+1
Интересный пример, спасибо. Не могли бы Вы его немного прокомментировать?
Если посмотреть на планы запросов в 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")
This comment wasn’t rated yet0
Все на самом деле просто:
Обращайте внимание на 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) */
Total votes 1: ↑1 and ↓0+1
Соответственно при 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 и все совпало.
This comment wasn’t rated yet0
Я предполагал, что 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 сделать
Total votes 1: ↑1 and ↓0+1
Основное отличие в том, что 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.
This comment wasn’t rated yet0
Кстати, лучше проверять не просто explain'ами, а выполнением с gather_plan_statistics и анализом плана с allstats last
Total votes 1: ↑1 and ↓0+1
Sign up to leave a comment.

Articles

Show the best of all time
Unlock dark mode

Your account

Sections

Information

Services

Support
© 2006–2024, Habr