Как уговорить базу использовать nested loops?
От: vsb Казахстан  
Дата: 26.06.20 17:29
Оценка:
Задача примитивная, но не пойму, как решить. База Oracle 9i.

Есть две таблицы, один-ко-многим. Для примера customer -< order (у одного клиента много заказов). В обоих таблицах относительно много строк (миллионы в первой, десятки миллионов во второй). В таблице order есть высокоселективное поле (к примеру некий код товара), не уникальное, но поиск по нему выдаёт обычно несколько десятков строк.

create table customer (
    id numeric primary key,
    name varchar2(100) not null
);

create table order (
    id numeric primary key,
    customer_id numeric not null references customer(id),
    product_number varchar2(20) not null
);

create index on order (customer_id);
create index on order (product_number);


Если я делаю select customer_id from order where product_number = :number, запрос использует индекс и отрабатывает моментально. Если я делаю select name from customer where id = :id, запрос использует индекс и тоже отрабатывает моментально. Мне интересен запрос вида

select customer.name
from customer
join order on customer.id = order.customer_id
where order.product_number = :number


То бишь логично использовать nested loops: сначала выбрать записи в order, пройти по ним и выбрать соответствующие записи в customer, из которых и вытащить name.

По факту база делает hash join. С одной стороны делает выборку по индексу в order, с другой стороны делает тупо полную выборку в customer. В итоге оно дико тормозит.

Пробовал менять порядок в запросе, делать разные варианты — не помогает. Пробовал использовать хинт /*+ use_nl */ и /*+ use_nl_with_index */, тоже не помогает.
Re: Как уговорить базу использовать nested loops?
От: Softwarer http://softwarer.ru
Дата: 26.06.20 17:52
Оценка: 15 (1) +1
Здравствуйте, vsb, Вы писали:

Во-первых, если база на столь простом запросе строит явно неверный план — это значит либо то, что в ней категорически неадекватна статистика (и надо её пересобрать) либо то, что Вы чего-то не договариваете (например, высокая селективность оказывается только на некоторых особенных значениях поля).

Во-вторых, если база отказывается подчиняться хинту, это значит опять же одно из двух: либо он написан с ошибкой, либо запрос по каким-то причинам не может быть выполнен так, как его пытается заставить программист. В Вашем случае, я так думаю, первый вариант. А самый простой способ подтолкнуть базу к выбору nested loop — хинт first_rows (но самый правильный, по-прежнему — собрать таки статистику). Для сведения:

SQL> explain plan for select * from top_dev_style ds, top_dev_style_color dsc where ds.code = dsc.dev_style_code;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2471906211
--------------------------------------------------------------------------------
| Id  | Operation                  | Name                | Rows  | Bytes |TempSp
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                     |   336K|    55M|
|*  1 |  HASH JOIN                 |                     |   336K|    55M|    22
|   2 |   TABLE ACCESS STORAGE FULL| TOP_DEV_STYLE       |   198K|    20M|
|   3 |   TABLE ACCESS STORAGE FULL| TOP_DEV_STYLE_COLOR |   336K|    20M|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DS"."CODE"="DSC"."DEV_STYLE_CODE")
15 rows selected

SQL> explain plan for select /* use_nl(ds, dsc) */ * from top_dev_style ds, top_dev_style_color dsc where ds.code = dsc.dev_style_code;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2471906211
--------------------------------------------------------------------------------
| Id  | Operation                  | Name                | Rows  | Bytes |TempSp
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                     |   336K|    55M|
|*  1 |  HASH JOIN                 |                     |   336K|    55M|    22
|   2 |   TABLE ACCESS STORAGE FULL| TOP_DEV_STYLE       |   198K|    20M|
|   3 |   TABLE ACCESS STORAGE FULL| TOP_DEV_STYLE_COLOR |   336K|    20M|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DS"."CODE"="DSC"."DEV_STYLE_CODE")
15 rows selected

SQL> explain plan for select /*+ use_nl(ds, dsc) */ * from top_dev_style ds, top_dev_style_color dsc where ds.code = dsc.dev_style_code;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1924271327
--------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cos
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |   336K|    55M|   3
|   1 |  NESTED LOOPS                |                     |   336K|    55M|   3
|   2 |   TABLE ACCESS STORAGE FULL  | TOP_DEV_STYLE_COLOR |   336K|    20M|  22
|   3 |   TABLE ACCESS BY INDEX ROWID| TOP_DEV_STYLE       |     1 |   108 |
|*  4 |    INDEX UNIQUE SCAN         | TOP_DEV_STYLE_PK    |     1 |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("DS"."CODE"="DSC"."DEV_STYLE_CODE")
16 rows selected

SQL> explain plan for select /*+ first_rows */ * from top_dev_style ds, top_dev_style_color dsc where ds.code = dsc.dev_style_code;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1924271327
--------------------------------------------------------------------------------
| Id  | Operation                             | Name                | Rows  | By
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                     |   336K|
|   1 |  NESTED LOOPS                         |                     |   336K|
|   2 |   TABLE ACCESS STORAGE FULL FIRST ROWS| TOP_DEV_STYLE_COLOR |   336K|
|   3 |   TABLE ACCESS BY INDEX ROWID         | TOP_DEV_STYLE       |     1 |
|*  4 |    INDEX UNIQUE SCAN                  | TOP_DEV_STYLE_PK    |     1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("DS"."CODE"="DSC"."DEV_STYLE_CODE")
16 rows selected
Re[2]: Как уговорить базу использовать nested loops?
От: vsb Казахстан  
Дата: 26.06.20 18:22
Оценка:
Единственное, что я тут не договариваю, это, что таблица customer партиционирована (на самом деле там не customer, а логи, а order это привязанные к записи лога детали; партиционирование идёт по дате и я, честно говоря, сам не понимаю, зачем оно нужно, но так сделали деды). Но вроде это не должно никак влиять... За запросы спасибо, поэкспериментирую. Я писал /*+ use_nl */ (без дополнительных скобок).
Отредактировано 26.06.2020 18:23 vsb . Предыдущая версия .
Re: Как уговорить базу использовать nested loops?
От: IZM  
Дата: 26.06.20 21:01
Оценка: 10 (1)
Через коррелированный подзапрос тоже не работает?
select o.*, (select c.Name from customer c where c.id=o.customer_id)
    from "ORDER" o
where o.product_number = :number;


Можно еще попробовать заставить в добровольно-принудительном порядке сервер представление делать(Без Хинта через аналит. фкнкцию)
Порой тоже помогает
select c."NAME", q.nn
from
(
  select o.customer_id, row_number() over (order by o.customer_id) as NN
    from "ORDER" o
   where o.product_number = :number
) q
 Join customer c on (c.id=q.customer_id)
Отредактировано 26.06.2020 21:46 IZM . Предыдущая версия .
Re: Как уговорить базу использовать nested loops?
От: vsb Казахстан  
Дата: 29.06.20 14:02
Оценка:
use_nl(table1, table2) в итоге сработало, спасибо большое. Почему оно не использует этот вариант по умолчанию, не понятно, ну да ладно. Вариант IZM тоже работает, но там нужно много полей на самом деле, поэтому решил так не делать.
Re[2]: Как уговорить базу использовать nested loops?
От: wildwind Россия  
Дата: 03.07.20 06:47
Оценка:
Здравствуйте, vsb, Вы писали:

vsb>use_nl(table1, table2) в итоге сработало, спасибо большое. Почему оно не использует этот вариант по умолчанию, не понятно, ну да ладно. Вариант IZM тоже работает, но там нужно много полей на самом деле, поэтому решил так не делать.

То есть самый правильный совет (иметь актуальную статистику) ты решил проигнорировать?
Re[3]: Как уговорить базу использовать nested loops?
От: wildwind Россия  
Дата: 03.07.20 06:49
Оценка:
Здравствуйте, vsb, Вы писали:

vsb>Единственное, что я тут не договариваю, это, что таблица customer партиционирована (на самом деле там не customer, а логи, а order это привязанные к записи лога детали; партиционирование идёт по дате и я, честно говоря, сам не понимаю, зачем оно нужно, но так сделали деды). Но вроде это не должно никак влиять...

Еще как должно и еще как влияет. Главный вопрос: твой "высокоселективный" индекс так же партиционирован, как и таблица?
Re[3]: Как уговорить базу использовать nested loops?
От: vsb Казахстан  
Дата: 03.07.20 08:28
Оценка:
Здравствуйте, wildwind, Вы писали:

vsb>>use_nl(table1, table2) в итоге сработало, спасибо большое. Почему оно не использует этот вариант по умолчанию, не понятно, ну да ладно. Вариант IZM тоже работает, но там нужно много полей на самом деле, поэтому решил так не делать.

W>То есть самый правильный совет (иметь актуальную статистику) ты решил проигнорировать?

Статистика собрана и пересобрана.
Re[4]: Как уговорить базу использовать nested loops?
От: vsb Казахстан  
Дата: 03.07.20 08:30
Оценка:
Здравствуйте, wildwind, Вы писали:

vsb>>Единственное, что я тут не договариваю, это, что таблица customer партиционирована (на самом деле там не customer, а логи, а order это привязанные к записи лога детали; партиционирование идёт по дате и я, честно говоря, сам не понимаю, зачем оно нужно, но так сделали деды). Но вроде это не должно никак влиять...

W>Еще как должно и еще как влияет. Главный вопрос: твой "высокоселективный" индекс так же партиционирован, как и таблица?

В таблице customer первичный ключ по id. Индекс, насколько я понимаю, глобальный, не партиционирован.
Re[5]: Как уговорить базу использовать nested loops?
От: wildwind Россия  
Дата: 03.07.20 09:38
Оценка:
Здравствуйте, vsb, Вы писали:

vsb>В таблице customer первичный ключ по id. Индекс, насколько я понимаю, глобальный, не партиционирован.

Если хочешь предметного разговора, выкладывай DDL обоих таблиц и индексов, планы, статистику выполнения. Ну как положено.
Отредактировано 03.07.2020 9:38 wildwind . Предыдущая версия .
Re: Как уговорить базу использовать nested loops?
От: MadHuman Россия  
Дата: 03.07.20 10:09
Оценка:
Здравствуйте, vsb, Вы писали:

vsb>
vsb>select customer.name
vsb>from customer
vsb>join order on customer.id = order.customer_id
vsb>where order.product_number = :number
vsb>


а если так?..
select customer.name
from order
  left join customer on customer.id = order.customer_id
where order.product_number = :number
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.