Oracle join elimination

    Оптимизатор в Oracle может применять различные способы трансформации запросов для улучшения их производительности. Одним из таких способов является join elimination. В официальной документации Oracle Database SQL Tuning Guide об этом способе сказано достаточно мало, в отличие от других.
    Приглашаю читателей под кат, чтобы поговорить об этом способе поподробнее.

    Содержание:

    Этот способ трансформации запроса впервые появился в Oracle 10.2, но в достаточно ограниченном виде — он поддерживал только inner join. В версии 11.1 и 11.2 возможности join elimination были значительно расширены.
    В документации join elimination определяется как: Удаление лишних таблиц из запроса. Таблица считается лишней, если ее колонки используются только в условии соединения, и такое соединение гарантированно не фильтрует данные и не добавляет новые строки.

    На первый взгляд это может показаться странным — зачем кто-то будет писать такой бессмысленный запрос? Но такое может происходить, если мы используем генерированный запрос или обращаемся к представлениям (view).

    Трансформация inner join


    Давайте рассмотрим небольшой пример (скрипты выполнялись на Oracle 11.2).

    Для начала создадим несколько таблиц, одну родительскую и одну дочернюю (master-detail):
    create table parent (
      id number not null,
      description varchar2(20) not null,
      constraint parent_pk primary key (id)
    );
    
    insert into parent values (1, 'первый');
    insert into parent values (2, 'второй');
    commit;
    
    create table child (
      id number not null,
      parent_id number,
      description varchar2(20) not null
    );
    
    insert into child values (1, 1, 'первый');
    insert into child values (2, 1, 'второй');
    insert into child values (3, 2, 'третий');
    insert into child values (4, 2, 'четвертый');
    commit;
    


    Теперь попробуем выполнить простой запрос и посмотрим на его план:

    explain plan for
    select c.id 
    from child c
      join parent p on c.parent_id = p.id;
    
    select * from table(dbms_xplan.display);
    
    --------------------------------------------------------------------------------
    | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |           |     4 |    36 |     2   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS      |           |     4 |    36 |     2   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| CHILD     |     4 |    24 |     2   (0)| 00:00:01 |
    |*  3 |   INDEX UNIQUE SCAN| PARENT_PK |     1 |     3 |     0   (0)| 00:00:01 |
    --------------------------------------------------------------------------------
    3 - access("C"."PARENT_ID"="P"."ID")
    

    Несмотря на то, что мы запрашиваем колонку только из таблицы child, Oracle, тем не менее, выполняет честный inner join и впустую делает обращение к таблице parent.

    Получается, оптимизатор не понимает, что в этом запросе соединение этих двух таблиц не приводит к какой-либо фильтрации или размножению строк. Значит, нужно помочь ему это понять.

    Свяжем эти таблицы с помощью foreign key из child на parent и посмотрим на то, как изменится план запроса:

    alter table child 
    add constraint child_parent_fk foreign key (parent_id) references parent(id);
    
    explain plan for
    select c.id 
    from child c
    join parent p on c.parent_id = p.id;
    
    select * from table(dbms_xplan.display);
    
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |     4 |   104 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| CHILD |     4 |   104 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
     1 - filter("C"."PARENT_ID" IS NOT NULL)
    

    Как видно из плана запроса — этого оказалось достаточно.
    Чтобы Oracle смог удалить лишние таблицы из запроса, соединенные через inner join, нужно чтобы между ними существовала связь foreign key — primary key (или unique constraint).

    Трансформация outer join


    Для того, чтобы Oracle мог убрать лишние таблицы из запроса в случае outer join — достаточно на колонке внешней таблицы, участвующей в соединении, был первичный ключ (primary key) или ограничение уникальности (unique constraint).

    Добавим еще несколько родительских таблиц
    create table parent2 (
     id number not null,
     description varchar2(20) not null,
     constraint parent2_pk primary key (id)
    );
    
    insert into parent2 values (3, 'третий');
    insert into parent2 values (4, 'четвертый');
    commit;
    
    create table parent3 (
     id number not null,
     description varchar2(20) not null,
     constraint parent3_pk primary key (id)
    );
    
    insert into parent3 values (5, 'пятый');
    insert into parent3 values (6, 'шестой');
    commit;
    
    alter table child add (parent2_id number, parent3_id number);
    alter table child add constraint child_parent2_fk foreign key (parent2_id) references parent2(id);
    
    merge into child c
    using (
    select 1 id, 3 parent2_id, null parent3_id from dual union all
    select 2 id, 4 parent2_id, 5 from dual union all
    select 3 id, 3 parent2_id, 6 from dual union all
    select 4 id, 4 parent2_id, null from dual
    ) s on (c.id = s.id)
    when matched then update set c.parent2_id = s.parent2_id, c.parent3_id = s.parent3_id;
    commit;
    


    И попробуем выполнить следующий запрос:
    explain plan for
    select c.id, c.description
    from child c
      left join parent3 p on c.parent3_id = p.id;
    
    select * from table(dbms_xplan.display);
    
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |     4 |   100 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| CHILD |     4 |   100 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    

    Как видно из плана запроса, в этом случае Oracle так же догадался, что таблица parent_3 лишняя и ее можно удалить.

    Число таблиц, которое может быть удалено из запроса, не ограничено. Join elimination удобно использовать, если существует дочерняя таблица, несколько родительских таблиц и результат их соединения выставлен в виде представления.

    Создадим такое представление, которое объединит все наши таблицы и попробуем использовать его в запросе:
    create or replace view child_parents_v
    as
    select c.id, c.parent_id, c.parent2_id, c.parent3_id, c.description, p1.description p1_desc, p2.description p2_desc, p3.description p3_desc
    from child c 
      join parent p1 on c.parent_id = p1.id
      join parent2 p2 on c.parent2_id = p2.id
      left join parent3 p3 on c.parent3_id = p3.id;
    
    explain plan for
    select id 
    from child_parents_v;
    
    select * from table(dbms_xplan.display);
    
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |     4 |   156 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| CHILD |     4 |   156 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    1 - filter("C"."PARENT2_ID" IS NOT NULL AND "C"."PARENT_ID" IS NOT NULL)
    

    Как видно из плана, Oracle отлично справился и с таким запросом тоже.

    Трансформация semi join и anti join


    Для того, чтобы была возможность таких трансформаций: между таблицами должна быть связь foreign key — primary key, как и в случае inner join.
    Сначала рассмотрим пример semi join:
    explain plan for
    select * from child c
    where exists 
      (select * from parent2 p where c.parent2_id = p.id);
    
    select * from table(dbms_xplan.display);
    
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |     4 |   256 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| CHILD |     4 |   256 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    1 - filter("C"."PARENT2_ID" IS NOT NULL)
    

    А теперь пример anti join:
    explain plan for    
    select * from child c
      where c.parent_id not in (select p.id from parent p);
    
    select * from table(dbms_xplan.display);
    
    -----------------------------------------------------------------------------------
    | Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |           |     4 |   308 |     5   (0)| 00:00:01 |
    |*  1 |  HASH JOIN ANTI SNA   |           |     4 |   308 |     5   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL   | CHILD     |     4 |   256 |     3   (0)| 00:00:01 |
    |   3 |   INDEX FAST FULL SCAN| PARENT_PK |     2 |    26 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------
    1 - access("C"."PARENT_ID"="P"."ID")
    

    Как видно, с такими типами запросов Oracle тоже научился работать.

    Трансформация self join


    Гораздо реже, но встречаются запросы с соединением одной и той же таблицы. К счастью, join elimination распространяется и на них, но с небольшим условием — нужно чтобы в условии соединения использовалась колонка с первичным ключом (primary key) или ограничением уникальности (unique constraint).

    create or replace view child_child_v
    as
    select c.id, c.description c_desc, c2.description c2_desc
    from child c 
      join child c2 on c.id = c2.id;
    
    alter table child add primary key(id);
     
    explain plan for
    select id, c2_desc
    from child_child_v;
    
    select * from table(dbms_xplan.display);
    
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |     4 |   100 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| CHILD |     4 |   100 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    

    Такой запрос тоже с успехом трансформируется:
    explain plan for  
    select c.id, c.description
    from child c
    where 
      c.parent3_id is null and
      c.id in (select c2.id from child c2 where c2.id > 1);
    
    select * from table(dbms_xplan.display);
    
    -----------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                 |     1 |    38 |     2   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| CHILD           |     1 |    38 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | SYS_C0013028957 |     3 |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------
    1 - filter("PARENT3_ID" IS NULL)
    2 - access("C2"."ID">1)
    


    Rely disable и join elimination


    Есть еще одна интересная особенность join elimination — он продолжает работать даже в том случае, когда ограничения (foreign key и primary key) выключены (disable), но помечены как доверительные (rely).

    Для начала просто попробуем отключить ограничения и посмотрим на план запроса:
    alter table child modify constraint child_parent_fk disable;
    alter table parent modify constraint parent_pk disable;
    
    explain plan for
    select c.id, c.description
    from child c
      join parent p on c.parent_id = p.id;
    
    select * from table(dbms_xplan.display);
    
    -----------------------------------------------------------------------------
    | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |        |     4 |   204 |     6   (0)| 00:00:01 |
    |*  1 |  HASH JOIN         |        |     4 |   204 |     6   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| PARENT |     2 |    26 |     3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| CHILD  |     4 |   152 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    1 - access("C"."PARENT_ID"="P"."ID")
    

    Вполне ожидаемо, что join elimination перестал работать. А теперь попробуем указать rely disable для обоих ограничений:
    alter table child modify constraint child_parent_fk rely disable;
    alter table parent modify constraint parent_pk rely disable;
    
    explain plan for
    select c.id, c.description
    from child c
      join parent p on c.parent_id = p.id;
    
    select * from table(dbms_xplan.display);
    
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |     4 |   152 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| CHILD |     4 |   152 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    1 - filter("C"."PARENT_ID" IS NOT NULL)
    

    Как видно, join elimination заработал вновь.
    На самом деле, rely предназначен для немного другой трансформации запроса . В таких случаях требуется, чтобы параметр query_rewrite_integrity был установлен в «trusted» вместо стандартного «enforced», но, в нашем случае, он ни на что не влияет и все прекрасно работает и при значении «enforced».

    К сожалению, ограничения rely disable вызывают join elimination только с inner join. Стоит так же отметить, что несмотря на то, что мы можем указывать rely disable primary key или rely disable foreign key для представлений — работать для join elimination это, к сожалению, не будет.

    Параметр _optimizer_join_elimination_enabled


    Вместе с таким замечательным способом трансформации запроса добавился еще и скрытый параметр _optimizer_join_elimination_enabled, который по умолчанию включен (true) и отвечает за использование этой трансформации.
    Если она вам надоест, то ее всегда можно выключить:
    alter session set "_optimizer_join_elimination_enabled" = false;
    
    explain plan for
    select c.id, c.description
    from child c
      join parent p on c.parent_id = p.id;
    
    select * from table(dbms_xplan.display);
    
    -----------------------------------------------------------------------------
    | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |        |     4 |   204 |     6   (0)| 00:00:01 |
    |*  1 |  HASH JOIN         |        |     4 |   204 |     6   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| PARENT |     2 |    26 |     3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| CHILD  |     4 |   152 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    1 - access("C"."PARENT_ID"="P"."ID")
    

    Подсказки ELIMINATE_JOIN и NO_ELIMINATE_JOIN


    Добавлено после комментария xtender.
    Так же, чтобы контролировать эту трансформацию, можно применять подсказки оптимизатора.
    Для того, чтобы включить трансформацию, используют подсказку ELIMINATE_JOIN:
    alter session set "_optimizer_join_elimination_enabled" = false;
    
    explain plan for
    select /*+ ELIMINATE_JOIN(p) */ c.id, c.description
    from child c
      join parent p on c.parent_id = p.id;
    
    select * from table(dbms_xplan.display);
     
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |     4 |    84 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| CHILD |     4 |    84 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    1 - filter("C"."PARENT_ID" IS NOT NULL)
    

    Для того, чтобы выключить трансформацию, используют подсказку NO_ELIMINATE_JOIN:
    alter session set "_optimizer_join_elimination_enabled" = true;
    
    explain plan for
    select /*+ NO_ELIMINATE_JOIN(p) */ c.id, c.description
    from child c
      join parent p on c.parent_id = p.id;
    
    select * from table(dbms_xplan.display);
    
    --------------------------------------------------------------------------------
    | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |           |     4 |    96 |     3   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS      |           |     4 |    96 |     3   (0)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| CHILD     |     4 |    84 |     3   (0)| 00:00:01 |
    |*  3 |   INDEX UNIQUE SCAN| PARENT_PK |     1 |     3 |     0   (0)| 00:00:01 |
    --------------------------------------------------------------------------------
    3 - access("C"."PARENT_ID"="P"."ID")
    


    Когда join elimination плохло


    В комментариях ниже xtender дал ссылку на свой интересный пример, в котором показывается, что join elimination может ухудшать план выполнения запроса. А так же дал некоторые пояснения в дальнейших комментариях.

    Трансформация одинаковых соединений


    Есть еще один вариант трансформации — удаление одинаковых соединений из запроса:
    select c.id
    from child c
      join parent p on p.id = c.parent_id
      join parent p2 on p2.id = c.parent_id
      join parent p3 on p3.id = c.parent_id
    where 
      p.description = 'первый' and 
      p2.description = 'первый' and 
      p3.description = 'первый'
    /
    select * from table(dbms_xplan.display_cursor(null, null, 'outline'))
    /
    -----------------------------------------------------------------------------
    | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |        |       |       |     6 (100)|          |
    |*  1 |  HASH JOIN         |        |     2 |   102 |     6   (0)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| PARENT |     1 |    25 |     3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| CHILD  |     4 |   104 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    1 - access("P3"."ID"="C"."PARENT_ID")
    2 - filter("P3"."DESCRIPTION"='первый')
    
    Outline Data
    -------------
    ...
    ELIMINATE_JOIN(@"SEL$EE94F965" "P"@"SEL$1")
    ELIMINATE_JOIN(@"SEL$EE94F965" "P2"@"SEL$2")
    ...
    

    Эта трансформация так же отлично работает и с подзапросами, которые превращаются в соединения (subquery unnesting):
    select c.id 
    from child c
    where 
      parent_id in (select /*+ qb_name(query_1) */ id from parent where description = 'первый') and
      parent_id in (select /*+ qb_name(query_2) */id from parent where description = 'первый') and
      parent_id in (select /*+ qb_name(query_3) */id from parent where description = 'первый') 
    /
    select * from table(dbms_xplan.display_cursor(null, null, 'outline'))
    /
    -----------------------------------------------------------------------------
    | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |        |       |       |     6 (100)|          |
    |*  1 |  HASH JOIN         |        |     2 |   102 |     6   (0)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| PARENT |     1 |    25 |     3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| CHILD  |     4 |   104 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    1 - access("PARENT_ID"="ID")
    2 - filter("DESCRIPTION"='первый')
    
    Outline Data
    -------------
    ...
    ELIMINATE_JOIN(@"SEL$45781D08" "PARENT"@"QUERY_3")
    ELIMINATE_JOIN(@"SEL$45781D08" "PARENT"@"QUERY_2")
    ...
    UNNEST(@"QUERY_3")
    UNNEST(@"QUERY_2")
    UNNEST(@"QUERY_1")
    ...
    

    Но, такой вариант трансформации имеет некоторые отличия.
    1) Для него необязательно иметь связь foreign key — primary key (или unique constraint):
    alter table child drop constraint child_parent_fk
    /
    select c.id
    from child c
      join parent p on p.id = c.parent_id
      join parent p2 on p2.id = c.parent_id
      join parent p3 on p3.id = c.parent_id
    where 
      p.description = 'первый' and 
      p2.description = 'первый' and 
      p3.description = 'первый'
    /
    select * from table(dbms_xplan.display_cursor(null, null, 'LAST OUTLINE'))
    /
    -----------------------------------------------------------------------------
    | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |        |       |       |     6 (100)|          |
    |*  1 |  HASH JOIN         |        |     2 |   102 |     6   (0)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| PARENT |     1 |    25 |     3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| CHILD  |     4 |   104 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    1 - access("P3"."ID"="C"."PARENT_ID")
    2 - filter("P3"."DESCRIPTION"='первый')
    
    Outline Data
    -------------
    ...
    ELIMINATE_JOIN(@"SEL$EE94F965" "P"@"SEL$1")
    ELIMINATE_JOIN(@"SEL$EE94F965" "P2"@"SEL$2")
    ...
    

    2) На него не влияет отключение параметра _optimizer_join_elimination_enabled:
    alter session set "_optimizer_join_elimination_enabled" = false
    /
    select c.id
    from child c
      join parent p on p.id = c.parent_id
      join parent p2 on p2.id = c.parent_id
      join parent p3 on p3.id = c.parent_id
    where 
      p.description = 'первый' and 
      p2.description = 'первый' and 
      p3.description = 'первый'
    /
    select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE'))
    /
    -----------------------------------------------------------------------------
    | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |        |       |       |     6 (100)|          |
    |*  1 |  HASH JOIN         |        |     2 |   102 |     6   (0)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| PARENT |     1 |    25 |     3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| CHILD  |     4 |   104 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    1 - access("P3"."ID"="C"."PARENT_ID")
    2 - filter("P3"."DESCRIPTION"='первый')
    
    Outline Data
    -------------
    ...
    ELIMINATE_JOIN(@"SEL$EE94F965" "P"@"SEL$1")
    ELIMINATE_JOIN(@"SEL$EE94F965" "P2"@"SEL$2")
    ...
    

    Но хотя бы действуют подсказки:
    select /*+ no_eliminate_join(p) no_eliminate_join(p2) no_eliminate_join(p3) */ c.id
    from child c
      join parent p on p.id = c.parent_id
      join parent p2 on p2.id = c.parent_id
      join parent p3 on p3.id = c.parent_id
    where 
      p.description = 'первый' and 
      p2.description = 'первый' and 
      p3.description = 'первый'
    /
    select * from table(dbms_xplan.display_cursor())
    /
    --------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |           |       |       |     8 (100)|          |
    |   1 |  NESTED LOOPS                  |           |     1 |   101 |     8   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS                 |           |     1 |   101 |     8   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS                |           |     1 |    76 |     8   (0)| 00:00:01 |
    |*  4 |     HASH JOIN                  |           |     2 |   102 |     6   (0)| 00:00:01 |
    |*  5 |      TABLE ACCESS FULL         | PARENT    |     1 |    25 |     3   (0)| 00:00:01 |
    |   6 |      TABLE ACCESS FULL         | CHILD     |     4 |   104 |     3   (0)| 00:00:01 |
    |*  7 |     TABLE ACCESS BY INDEX ROWID| PARENT    |     1 |    25 |     1   (0)| 00:00:01 |
    |*  8 |      INDEX UNIQUE SCAN         | PARENT_PK |     1 |       |     0   (0)|          |
    |*  9 |    INDEX UNIQUE SCAN           | PARENT_PK |     1 |       |     0   (0)|          |
    |* 10 |   TABLE ACCESS BY INDEX ROWID  | PARENT    |     1 |    25 |     0   (0)|          |
    --------------------------------------------------------------------------------------------
    4 - access("P"."ID"="C"."PARENT_ID")
    5 - filter("P"."DESCRIPTION"='первый')
    7 - filter("P2"."DESCRIPTION"='первый')
    8 - access("P2"."ID"="C"."PARENT_ID")
    9 - access("P3"."ID"="C"."PARENT_ID")
    10 - filter("P3"."DESCRIPTION"='первый')
    

    Итог


    Подводя краткий итог, хочется сказать, что такой способ трансформации может быть действительно полезен в ряде случаев. Но полагаться на него надо тоже с умом. Если внутри вашего представления что-то поменяется и Oracle больше не сможет гарантированно определять то, что связь с таким представлением не фильтрует или не умножает строки, вы получите неожиданную потерю скорости выполнения запроса.

    Ну и, напоследок, скрипт удаления всех созданных объектов
    drop view child_parents_v;
    drop view child_child_v;
    drop table child;
    drop table parent;
    drop table parent2;
    drop table parent3;
    

    Поделиться публикацией
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама
    Комментарии 35
    • 0
      Может быть кто-то может посоветовать годную книжку по SQL? Сколько им пользуюсь, а до сих пор не могу придумать как использовать тот же JOIN в своих поделках…
      • +1
        Ну, собственно всё, что делает JOIN — дает возможность выборки данных из связанных таблиц. В одной таблице мы храним одни данные (допустим персональные данные сотрудников), а в другой, скажем, типы ролей сотрудников. И, допустим, у нас есть третья таблица, где указано соотвествие сотрудников ролям (связь многие ко многим).

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

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

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

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

                            речь про «ORA-01417: a table may be outer joined to at most one other table» при оракловом синтаксисе.
                            в 12c это уже исправлено и оракловый синтаксис позволяет так соединять.
                        • +1
                          К слову, совсем недавно в Oracle-L я приводил пример, когда Join elimination наоборот приводит к худшему плану причем с большей стоимостью:
                          gist.github.com/xtender/f0871ffa99b1413232e6
                          • 0
                            Интересный пример, спасибо. Не могли бы Вы его немного прокомментировать?
                            Если посмотреть на планы запросов в 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")
                            
                            • +1
                              Все на самом деле просто:
                              Обращайте внимание на 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) */
                              • 0
                                Соответственно при 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 и все совпало.
                                • +1
                                  Я предполагал, что 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 сделать
                                  • 0
                                    Основное отличие в том, что 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.
                              • +1
                                Кстати, лучше проверять не просто explain'ами, а выполнением с gather_plan_statistics и анализом плана с allstats last

                            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.