Переписать запрос / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Переписать запрос

4 сообщений из 4, страница 1 из 1

Переписать запрос

#39267661

Переписчик

Гость

Тестовые данные:

Код: plsql

1.
2.
3.
4.
5.
6.
7.

create table table1
  as 
  select round(dbms_random.value(1, 10000))  field1,
         round(dbms_random.value(1, 10000))  field2,
         round(dbms_random.value(1, 10000))  field3         
    from dual
 connect by level < 2000000

Запрос:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

select distinct field1, field2, field3, field4
  from table1 t
 where t.field1 in (select field1
                      from table1
                     group by field1
                    having count(*)>250)
    or t.field2 in (select field2
                      from table1
                     group by field2
                    having count(*)>250)

На тестовых данных выполнялся более 5 минут, не дождался. План:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

----------------------------------------
Plan hash value: 3209377373
----------------------------------------
| Id  | Operation             | Name   |
----------------------------------------
|   0 | SELECT STATEMENT      |        |
|   1 |  HASH UNIQUE          |        |
|   2 |   FILTER              |        |
|   3 |    TABLE ACCESS FULL  | TABLE1 |
|   4 |    FILTER             |        |
|   5 |     HASH GROUP BY     |        |
|   6 |      TABLE ACCESS FULL| TABLE1 |
|   7 |    FILTER             |        |
|   8 |     HASH GROUP BY     |        |
|   9 |      TABLE ACCESS FULL| TABLE1 |
----------------------------------------

При том, что первый подзапрос возвращает 3 записи, второй 2 и каждый отрабатывает очень быстро хоть и с фулсканом за секунду. Но итоговый запрос сваливается в какой-то бесконечный фулскан.

Можно разбить запрос на две части с union all:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

select distinct field1, field2, field3, field4
  from (select field1, field2, field3, field4
          from table1 t
         where t.field1 in (select field1
                              from table1
                             group by field1
                            having count(*)>250)
         union all
        select field1, field2, field3, field4
          from table1 t                            
         where t.field2 in (select field2
                              from table1
                             group by field2
                            having count(*)>250))

Запрос выполнился за 2сек. План:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

---------------------------------------------
Plan hash value: 1595607198
---------------------------------------------
| Id  | Operation                | Name     |
---------------------------------------------
|   0 | SELECT STATEMENT         |          |
|   1 |  HASH UNIQUE             |          |
|   2 |   VIEW                   |          |
|   3 |    UNION-ALL             |          |
|   4 |     HASH JOIN RIGHT SEMI |          |
|   5 |      VIEW                | VW_NSO_1 |
|   6 |       FILTER             |          |
|   7 |        HASH GROUP BY     |          |
|   8 |         TABLE ACCESS FULL| TABLE1   |
|   9 |      TABLE ACCESS FULL   | TABLE1   |
|  10 |     HASH JOIN RIGHT SEMI |          |
|  11 |      VIEW                | VW_NSO_2 |
|  12 |       FILTER             |          |
|  13 |        HASH GROUP BY     |          |
|  14 |         TABLE ACCESS FULL| TABLE1   |
PLAN_TABLE_OUTPUT
---------------------------------------------
|  15 |      TABLE ACCESS FULL   | TABLE1   |

Попробовал через аналитику:

Код: plsql

1.
2.
3.
4.
5.
6.
7.

select distinct field1, field2, field3, field4
  from (select field1, field2, field3, field4,
               count(*) over(partition by field1) cnt1,
               count(*) over(partition by field2) cnt2
          from table1)
 where cnt1 > 250
    or cnt2 > 250

Около 5сек с последующим бодрым фетчем. План:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

----------------------------------------
Plan hash value: 2052196605
----------------------------------------
| Id  | Operation             | Name   |
----------------------------------------
|   0 | SELECT STATEMENT      |        |
|   1 |  HASH UNIQUE          |        |
|   2 |   VIEW                |        |
|   3 |    WINDOW SORT        |        |
|   4 |     WINDOW SORT       |        |
|   5 |      TABLE ACCESS FULL| TABLE1 |

1. Индексы для группировки выходит, совсем не нужны?
А если объем будет например 30миллионов?
2. Пока не понял, почему жутко тормозит первый вариант (хотя вроде бы не сильно отличается по сути от второго, только разнесли группировки)
3. Возможно ли переписать более эффективно?
4. Можно ли в плскл девелопере быстро достать в буфер план в человеческом виде (по F5 при копировании в буфер результат не читабелен, а использовать explain plan for или gather_plan_statistics долго и потому не удобно по-моему)? (не говорим о том, что реальный план может отличаться).

...

Рейтинг:

0 / 0

04.07.2016, 21:27:50

| Ответить | Цитировать | Написать

Переписать запрос

#39267662

Переписчик

Гость

field4 в запросах лишнее.

...

Рейтинг:

0 / 0

04.07.2016, 21:30:07

| Ответить | Цитировать | Написать

Переписать запрос

#39267685

ora601

Участник

Сообщения: 785

Рейтинг: 0 / 0

Переписчик,

Аналитика вполне себе вариант для такого кейса, по поводу индексов при группировке - ну разве что если можно прочитать через IFFS, но сильно уж лучше от этого не будет, ввиду что 1 FULL SCAN с аналитикой > 3 хоть и оптимизированных.

...

Рейтинг:

0 / 0

05.07.2016, 00:34:09

| Ответить | Цитировать | Написать

Переписать запрос

#39267719

Добрый Э - Эх

Гость

Переписчик1. Индексы для группировки выходит, совсем не нужны?
А если объем будет например 30миллионов?
2. Пока не понял, почему жутко тормозит первый вариант (хотя вроде бы не сильно отличается по сути от второго, только разнесли группировки)
3. Возможно ли переписать более эффективно?
4. Можно ли в плскл девелопере быстро достать в буфер план в человеческом виде (по F5 при копировании в буфер результат не читабелен, а использовать explain plan for или gather_plan_statistics долго и потому не удобно по-моему)? (не говорим о том, что реальный план может отличаться).

1. В данном случае трудно придумать эффективную схему индексирования. По итогу в выборку выбираются все поля. Стало быть, либо индекс должен быть составным (а в этом случае его размеры будут приближаться к размерам таблицы), либо придется читать не только индекс, но и таблицу (и в этом случае есть сильные сомнения, что оптимизатор "подпишется" на такое)
2. Смотреть план, много думать.
3. Более эффективно или нет - сказать трудно, но как вариант - можно сделать через LEFT JOIN SEMI:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

select distinct t.field1, t.field2, t.field3
  from table1 t
  left join 
         (
           select field1
             from table1
            group by field1
           having count(*)>250
         ) v1
    on t.field1 = v1.field1
  left join
         (
           select field2
             from table1
            group by field2
           having count(*)>250
         ) v2
    on t.field2 = v2.field2
 where v1.field1 is not null
    or v2.field2 is not null;

4. Я бы не сочел за труд делать через explain plan + dbms_xplan.

...

Рейтинг:

0 / 0

05.07.2016, 06:34:10

| Ответить | Цитировать | Написать

4 сообщений из 4, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Переписать запрос

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&msg=39267685&tid=1887963]:	0ms
get settings:	7ms
get forum list:	15ms
check forum access:	3ms
check topic access:	3ms
track hit:	200ms
get topic data:	8ms
get forum data:	2ms
get page messages:	32ms
get tp. blocked users:	1ms
others:	199ms

total:	470ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы