ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / условие join не всегда выполняется до группировки
6 сообщений из 6, страница 1 из 1
условие join не всегда выполняется до группировки
    #39855570
OverDrone
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
OverDrone
Гость
Добрый день!
Использую 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:03
| Ответить | Цитировать | Написать  
условие join не всегда выполняется до группировки
    #39855591
Dshedoo
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dshedoo
Участник
OverDroneДобрый день!
Я перепробовал все хинты, которые смог найти.

Это какие?
...
Рейтинг: 0 / 0
30.08.2019, 12:17
| Ответить | Цитировать | Написать  
условие join не всегда выполняется до группировки
    #39855654
Фотография SeaGate
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SeaGate
Участник
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, 13:56
| Ответить | Цитировать | Написать  
условие join не всегда выполняется до группировки
    #39855724
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
OverDrone,

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

OJPPD: OJPPD bypassed: View contains TABLE expression.

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


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