Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / условие join не всегда выполняется до группировки / 6 сообщений из 6, страница 1 из 1
30.08.2019, 12:03
    #39855570
OverDrone
OverDrone
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
условие join не всегда выполняется до группировки
Добрый день!
Использую oracle 11g.
В некоторых случаях оракл понимает что на выборку накладывается ограничение (н.р. join on) и сначала обрабатывает это ограничение и только потом делает group by. Подробности в примере ниже.
Я перепробовал все хинты, которые смог найти. Желаемого результата, чтобы TBL сначала фильтровался, потом группировался, я так и не смог достичь.
На реальном примере я в итоге сделал подзапросом, но из-за того, что нужно было выбирать несколько полей, пришлось на каждое поле писать подзапрос и судя по графику выполнения эти подзапросы в один не сливаются.
Вопрос: Как переписать запрос для оракл 11g (я видел в 12 появились какие-то таблицы-подзапросы), какие добавить хинты чтобы второй запрос отработал аналогично первому?


--Первоначальное заполнение:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
CREATE TABLE TBL(ID1 NUMBER, ID2 NUMBER);
CREATE INDEX IX_TBL ON TBL(ID1);

INSERT INTO TBL(ID1, ID2)
SELECT T1.ID, T2.ID
FROM (
  SELECT LEVEL AS ID
  FROM DUAL
  CONNECT BY LEVEL <= 1000000
) T1,
(
  SELECT LEVEL AS ID
  FROM DUAL
  CONNECT BY LEVEL <= 10
) T2;
COMMIT;



--Вариант 1: Оракл понимает что TBL сначала надо отфильтровать по ID1=1001,1002 и только потом сгруппировать (по индексу).
--Выполняется за доли секунды
SELECT *
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
FROM (
  SELECT 1001 AS ID1
  FROM DUAL
  UNION ALL
  SELECT 1002 AS ID1
  FROM DUAL
) D
INNER JOIN (
  SELECT ID1, COUNT(*) AS CNT
  FROM TBL
  GROUP BY ID1
) T
ON T.ID1 = D.ID1



--Вариант 2: Хотя запрос идентичный, но теперь оракл сначала группирует по всей выборке TBL (10млн) и только потом применяет фильтрацию.
--Работает несколько секунд
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT *
FROM (
  SELECT LEVEL + 1000 AS ID1
  FROM DUAL
  CONNECT BY LEVEL <= 2
) D
INNER JOIN (
  SELECT ID1, COUNT(*) AS CNT
  FROM TBL
  GROUP BY ID1
) T
ON T.ID1 = D.ID1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 12:17
    #39855591
Dshedoo
Dshedoo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
условие join не всегда выполняется до группировки
OverDroneДобрый день!
Я перепробовал все хинты, которые смог найти.

Это какие?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 13:56
    #39855654
SeaGate
SeaGate
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
условие join не всегда выполняется до группировки
OverDrone,

В доступе только 11.2.0.2. Там можно через подзапрос. Иначе не вижу простых вариантов JPPD. В 12c LATERAL (в 11g это есть, но не официально).
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
SQL> explain plan for
  2  SELECT *
  3  FROM (
  4  SELECT ID1, COUNT(*) AS CNT
  5  FROM TBL
  6  GROUP BY ID1
  7  ) T
  8  where t.id1 in (
  9  SELECT LEVEL + 1000 AS ID1
 10  FROM DUAL
 11  CONNECT BY LEVEL <= 2
 12  )
 13    ;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 4032449242

------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |          |    10 |   180 |     6  (34)| 00:00:01 |
|   1 |  HASH GROUP BY                            |          |    10 |   180 |     6  (34)| 00:00:01 |
|   2 |   NESTED LOOPS                            |          |    10 |   180 |     5  (20)| 00:00:01 |
|   3 |    VIEW                                   | VW_NSO_1 |     1 |    13 |     3  (34)| 00:00:01 |
|   4 |     HASH UNIQUE                           |          |     1 |       |     3  (34)| 00:00:01 |
|*  5 |      CONNECT BY WITHOUT FILTERING (UNIQUE)|          |       |       |            |          |
|   6 |       FAST DUAL                           |          |     1 |       |     2   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN                       | IX_TBL   |    10 |    50 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(LEVEL<=2)
   7 - access("ID1"="ID1")


Можно отказаться от CONNECT BY
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
SQL> explain plan for
  2  SELECT *
  3  FROM (select rownum + 1000 as id1 from tbl where rownum <= 2) d,
  4  (
  5  SELECT ID1, COUNT(*) AS CNT
  6  FROM TBL
  7  GROUP BY ID1
  8  ) T
  9  where t.id1 = d.id1  ;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 2142932133

----------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     2 |    52 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS           |        |     2 |    52 |     8   (0)| 00:00:01 |
|   2 |   VIEW                  |        |     2 |    26 |     2   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY        |        |       |       |            |          |
|   4 |     TABLE ACCESS FULL   | TBL    |     2 |       |     2   (0)| 00:00:01 |
|   5 |   VIEW PUSHED PREDICATE |        |     1 |    13 |     3   (0)| 00:00:01 |
|*  6 |    FILTER               |        |       |       |            |          |
|   7 |     SORT AGGREGATE      |        |     1 |     5 |            |          |
|*  8 |      INDEX RANGE SCAN   | IX_TBL |    10 |    50 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(ROWNUM<=2)
   6 - filter(COUNT(*)>0)
   8 - access("ID1"="D"."ID1")
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.08.2019, 15:36
    #39855724
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
условие join не всегда выполняется до группировки
OverDrone,

У JPPD есть известные ограничения на обычные популярные генераторы:
OJPPD: OJPPD bypassed: query block contains START WITH/CONNECT BY.

OJPPD: OJPPD bypassed: View contains TABLE expression.

замените генератор на любой, который OJPPD поддерживает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.09.2019, 17:02
    #39856387
OverDrone
OverDrone
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
условие join не всегда выполняется до группировки
SeaGate, connect by я привел просто в качестве примера. На проде у меня там piped function. Поведение оракла одинаковое в этих случаях, поэтому я для простоты не стал просто упоминать функцию.
Проблема даже не в том, что оракл не может предугадать число строк (я пробовал и cardinality hint и ASSOCIATE STATISTICS с объектом), а в том, что если ограничения на айдишник он не может представить в виде условия, а не набора записей, то он не пытается этот набор записей подсунуть до group by, даже если там 1-2 записи.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.09.2019, 17:04
    #39856388
OverDrone
OverDrone
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
условие join не всегда выполняется до группировки
xtender, Вот у меня там как раз table(<pipelined function>) на проде. Получается что либо переходить на 12 (lateral) или делать подзапросом, как я и сделал в итоге...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / условие join не всегда выполняется до группировки / 6 сообщений из 6, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 413ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1882133]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 60ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 47ms
get tp. blocked users:
 1ms
others: 259ms
total:  413ms
x
x
Закрыть


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